mysql主从同步集群在生产环境使用时,如果主从服务器之间网络通信条件差或者数据库数据量非常大,容易导致mysql主从同步延迟。此时一旦主库宕机,会导致部分数据没有及时同步至从库,重新启动主库,会导致从库与主库同步错误。快速恢复主从同步关系有如下两种方法。
方法一:忽略错误后,继续同步。(适用于主从数据库相差不大的情况)
1、master端执行命令,将数据库设置为全局读锁,不允许写入新数据。
MariaDB [(none)]> flush tables with read lock;
2、slave端停止slave的I/O及SQL线程。
MariaDB [(none)]> stop slave;
3、slave端将同步错误的sql跳过一次(跳过操作会导致主从数据不一致)。
MariaDB [(none)]> set global sql_slave_skip_counter=1;
4、slave端启动slave,恢复同步状态。
MariaDB [(none)]> start slave;
5、master端解除数据库全局读锁。
MariaDB [(none)]> unlock tables;
方法二:重新做主从同步,使数据完全同步(适用于主从数据库相差很大的情况)
1、master端执行命令,将数据库设置为全局读锁,不允许写入新数据。
MariaDB [(none)]> flush tables with read lock;
2、master端查看master状态。
MariaDB [(none)]> show master status \G
3、master端对数据库进行完整备份。
mysqldump -uroot -pserver8 --databases newdb > /root/newdb.sql;
4、master端使用scp命令传输数据库文件newdb.sql到从服务器/root根目录。
scp /root/newdb.sql root@192.168.1.210:/root
5、slave端导入master端复制过来的数据库。
mysql -uroot -pclient8a < /root/newdb.sql
6、slave端登录数据库,停止slave的I/O及SQL线程。
mysql -uroot -p
MariaDB [(none)]> stop slave;
7、slave端重新配置主从关系(master_log_file与master_log_pos是主服务器master状态下的File与Position)。
MariaDB [(none)]> use mysql; #切换数据库
MariaDB [mysql]> change master to
master_host='192.168.1.200',
master_user='new',
master_password='new',
master_log_file='mysql-bin.000001',
master_log_pos=653; #配置主从同步参数
8、slave端启动slave。
MariaDB [mysql]> start slave;
9、slave端查看slave状态,确认slave端I/O线程和SQL线程均为yes。
MariaDB [mysql]> show slave status \G
10、master端解除数据库全局读锁。
MariaDB [(none)]> unlock tables;