1. 配置
1.1 主机
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
ft_min_word_len = 4
#binlog文件名，可随意取名，是从机复制数据的源头，开启二进制日志
log-bin = mysql-bin
#主机id
server-id = 1
#binlog格式
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
interactive_timeout = 28800
wait_timeout = 28800
default_authentication_plugin=mysql_native_password
gtid_mode = on
enforce_gtid_consistency = on
lower-case-table-names = 1
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
1.2 从机
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
ft_min_word_len = 4
log-bin = mysql-bin
#relaylog名，中继日志
relay-log = mysql-relay
relay-log-index = mysql-relay.index
#从机id
server-id = 2
#二进制日志模式
binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
interactive_timeout = 28800
wait_timeout = 28800
default_authentication_plugin=mysql_native_password
gtid_mode = on
enforce_gtid_consistency = on
lower-case-table-names = 1
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
另外有两个重要配置：
#无需复制的库
binlog-ignore-db=mysql
#需要复制的库
binlog-do-db=testdb..........
#STATEMENT，sql 原样输出，遇到函数容易出问题，例如NOW()
#ROW，行模式，记录每一条记录的值，大面积修改数据时，本可以通过一个语句批量修改，但是在行模式下，是一条一条的记录，所以效率低下
#MIXED，对上面的两种模式进行来回切换，但是遇到系统变量（例如@@host_name）也会出现主从不一致的问题
binlog-format=STATEMENT (默认)
2. 命令
2.1 主机
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lao_wang@2021';
set global max_connections=5000;set global time_zone = '+8:00'; set time_zone = '+8:00';
#创建从服务器用户，分配权限
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'slave_user@2021' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';
#GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'masterpwd' WITH GRANT OPTION;
flush privileges;
SET PERSIST_ONLY gtid_mode=ON;
SET PERSIST_ONLY enforce_gtid_consistency=true;
#查binlog
show master status\G;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1611 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.2 从机
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lao_wang@2021';
set global max_connections=5000;set global time_zone = '+8:00'; set time_zone = '+8:00';
SET PERSIST_ONLY gtid_mode=ON;
SET PERSIST_ONLY enforce_gtid_consistency=true;
FLUSH PRIVILEGES;
#指定主机
change master to master_host="127.0.0.1",master_port=3306,master_user="slave_user",master_password="slave_user@2021",master_log_file="mysql-bin.000003",master_log_pos=1611,MASTER_AUTO_POSITION=0, MASTER_SSL=1;
#开启从机
start slave;
show slave status\G;
#启动过工程中遇到问题可以尝试用一下几个命令解决
#reset slave 命令在start 之后stop之前才可执行
reset slave;
stop slave;
reset master；
Slave failed to initialize relay log info structure from the repository, Error_code: 1872解决方案binlog-do-db
网友评论