windows环境下主、中继、从服务器级联复制

windows MySQL A->B->C主从级联复制

三台主机：

环境如下：
A数据库：MySQL版本5.6，IP为192.168.1.106
B数据库：MySQL版本5.6，IP为192.168.1.123
C数据库：MySQL版本5.6，IP为192.168.1.72

1. master 服务器配置
   server-id=1
   log-bin=mysql-bin #开启二进制文件
   binlog_do_db=liuzhuang_pt
   创建用户用于同步信息
   mysql> grant replication slave on . to MySlave@‘192.168.1.%’ identified by ‘123456’;

2. 中间的级联从服务器my.ini
   server-id=2
   log-bin=mysql-bin #开启二进制文件
   binlog_format=row
   binlog_do_db=liuzhuang_pt
   log_slave_updates // #级联复制中间节点的必选项 关键
   read-only
   设置从从服务器的同步账户

   CREATE USER ‘MySlave’@‘192.168.1.%’ IDENTIFIED BY ‘123456’;
   GRANT REPLICATION SLAVE ON . TO ‘MySlave’@‘192.168.1.%’;
   FLUSH PRIVILEGES;

设置此服务器的主节点

CHANGE MASTER TO
MASTER_HOST='192.168.1.106',
MASTER_USER='MySlave',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='m-bin.000001', MASTER_LOG_POS=400;   

mysql 
mysql> set sql_log_bin=0;
mysql> show master status;  #记录二进制位置，给第三个节点使用  
mysql> set sql_log_bin=0;
mysql> start slave;
获取主节点的状态：

show master status;
mysql-bin.000002 680712624

mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql  做备份
scp /data/all.sql 最终的从服务器上:/data

3. 最后的从服务器
   在my.ini中配置
   server-id=3
   read-only
   设置此服务器的主节点

   CHANGE MASTER TO
   MASTER_HOST=‘192.168.1.123’,
   MASTER_USER=‘MySlave’,
   MASTER_PASSWORD=‘123456’,
   MASTER_PORT=3306,
   MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=680712624;

mysql> start slave;

show slave status;

MySQL主从复制常见问题原因和解决方案

1. 如果主从同时不操作，网络修复的时候，是能够自动同步的，如果主做了操作后，网络才恢复的，需要重新启动从端的从属服务才能恢复。
   stop slave;
   start slave;
   #重启slave服务才能恢复。

2. 2021-07-07 13:31:10 10000 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236)
   2021-07-07 13:31:10 10000 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’, Error_code: 1236
   2021-07-07 13:31:10 10000 [Note] Slave I/O thread exiting, read up to log ‘binlog-bin.000572’, position 257630129
   在中继服务器中配置需要生成日志的数据库binlog_do_db=liuzhuang_pt并重新设置同步。

3. Slave_IO_Running状态为Connectioning，从库无法进行同步。可能是从库无法连接到中间库。 在中间库中创建相应的slave账户。
   CREATE USER ‘MySlave’@‘192.168.1.%’ IDENTIFIED BY ‘123456’;
   GRANT REPLICATION SLAVE ON . TO ‘MySlave’@‘192.168.1.%’;
   FLUSH PRIVILEGES;

4. 级联同步master服务器关机重启后无法正常启动mysql
   2021-07-07 15:05:25 2374 InnoDB: Error: page 24869 log sequence number 72818688388
   InnoDB: is in the future! Current system log sequence number 71971085836.
   InnoDB: Your database may be corrupt or you may have copied the InnoDB
   InnoDB: tablespace but not the InnoDB log files. See
   InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
   InnoDB: for more information.
   innodb_force_recovery = 1
   尝试修改innodb_force_recovery 参数启动，发现无法启动

5. 级联同步master服务器关机重启后,从库能够自动同步

6. 从库数据与主库冲突在从库配置文件中配置，直接跳过不影响业务的错误号
   slave-skip-errors = 1032,1062,1007