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