mysql 主从的复制恢复
备份恢复##
1.从库查询状态###
slave status
Master_Log_File: mysql-bin.000120
Slave_IO_Running: Yes
Slave_SQL_Running: No
RELAY_Master_Log_FILE: mysql-bin.000119
Exec_Master_Log_Pos: 87663322
Last_Error: xxxxx
这种状态就是要从mysql-bin.000119 的87663322 行开始恢复,只能一点点比对了。
2.主库查询binlog###
show binlog events in 'mysql-bin.000119' from 87663322 limits 100000;
然后看卡在哪个事务上了。
3.从库跳过错误的事务###
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
然后回到步骤1 继续找。如果每个事务都不长,可以调整SLAVE_SLAVE_SKIP_COUNTER的值一次跳过多个事务。
4.slave的过滤配置###
官方手册 https://dev.mysql.com/doc/refman/5.7/en/replication-rules.html
按照官方建议,主库的binlogxxx配置有一定作用,在从库设置过滤规则可以让断开复制的几率更小点。
replicate-wild-do-table = xyz.abc_%
类似这种,表名如果有_需要转译。跨库的问题,只能注意了。
5.其它###
记录些相关表
mysql.slave_master_info
mysql.slave_relay_log_info
mysql.slave_worker_info
performance_schema.replication_applier_configuration
performance_schema.replication_applier_filters
performance_schema.replication_applier_global_filters
performance_schema.replication_applier_status
performance_schema.replication_applier_status_by_coordinator
performance_schema.replication_applier_status_by_worker
performance_schema.replication_connection_configuration
performance_schema.replication_connection_status
performance_schema.replication_group_member_stats
performance_schema.replication_group_members