MySQL Slave备份异常处理

昨天Mysql备份突然出现问题，从库一直没读到数据

1、执行 show slave status
发现一直处于Reading event from the relay log，Seconds_Behind_Master 为3000多，说明延迟了很久

2、查看本地同步文件，发现很多等待同步的文件。说明主库没有问题，还在同步，从库出现了问题

3、再次分析 show slave status信息

Relay_Log_File: mysql-relay.001295
Relay_Log_Pos: 43557398

Relay_Log_Pos 一直不动，说明卡在这里了

5、查询这个文件 43557398 行的sql信息

show relaylog events in mysql-relay.001295' FROM 43557398LIMIT 0 ,100

发现执行了大量的删除和更新操作。导致同步语句执行失败，卡住了

6、跳过这次操作，继续执行同步

stop slave;
set global sql_slave_skip_counter=1;
start slave;

7、重新查看 show slave status，发现 Relay_Log_Pos有了变化。

很快，本地的同步文件就执行完成并删除了，Seconds_Behind_Master = 0，同步完成。

注：切忌执行大量删除sql，反复删除创建同一个表等，这样很容易导致同步卡住。

附录：

sync_binlog 参数说明

sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

innodb_flush_log_at_trx_commit 参数值说明

innodb_flush_log_at_trx_commit是配置MySql日志何时写入硬盘的参数：

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时mysql都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作

修改命令（这样只是本次生效，重启mysql后恢复）

 -- 修改全局配置
      set global sync_binlog=100;
      set global innodb_flush_log_at_trx_commit=2;

  -- 查看配置
      SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'
      SHOW VARIABLES LIKE 'sync_binlog'

也可以在my.ini或my.cnf中设置，重启mysql生效（这样是永久生效）。

sync_binlog=20
innodb_flush_log_at_trx_commit=2