MYSQL 主从同步延迟 Got fatal error 1236

【原因】

由于存储日志的磁盘空间满而导致MYSQL没有将日志完全写入磁盘，binglog event 被截断。slave读取该binlog file时就报以上图片错误

【解决】

利用mysqlbinlog工具还原数据

/usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -vv master-bin.000259 --stop-position=719197584 |tail -20

或者 /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -vv master-bin.000259 |grep -A 20 '719197584'  

或者 /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -vv master-bin.000259 > 259.log

解析binlog日志文件，找到离719197584前后的最近位置的position号719197500

然后登录从库，重新指定主从同步位置

stop slave;

change master to master_log_file='master-bin.000259',master_log_pos=719197500;

start slave;

发现开启还是报错，继续查看master-bin.000256的日志头内容，看下日志开头有没说明是继续上一个binlog的最后一个事务

重新指定日志看是否生效

change master to master_host='',master_port=3306,naster_user='repl_user',master_password='',master_log_file='master-bin.000260',master_log_pos=0;

开启发现报另外一个错误1032，这里使用跳过错误event

先跳过一条错误event,让主从同步恢复正常

stop slave;

set global sql_slave_skip_counter=1;

start slave;

这里环境还是依旧无法跳过，手工跳过几次之后，还是报错。如果你相要你的环境先开启，可以使用修改参数slave_exec_mode

set global slave_exec_mode=IDEMPOTENT;

如果从库状态正常了，跟上了主库的变更，重新设置为原值set global slave_exec_mode=STRICT;

但是现在两边数据是不一致的，接下来就是修复数据了。可以使用pt工具进行修复检查两边哪些表数据不一致；或者重建从库。

因为我数据相差太大了，这里不使用pt工具修复。采用了重新搭建从库方式。

 

不停机还原备库:

1. 重新备份主库

innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=xxx --port=3306 --backup --stream=tar /data/mysql | gzip -> /nta/alldbfullbackup.tar.gz

# 如果添加--slave-info参数，在备份集中还会包含一个名为xtrabackup_slave_info的文件，这个文件中提供了change master to的语句
备份完成后将主库的备份文件，传输到备库上。

2. 恢复数据库

在备库服务器上，用传输过来的主库备份，恢复数据库。

2.1 停止备库的服务 

stop slave;

reset slave; 

service mysql stop

 2.2恢复数据库

删除备库data目录下的数据库文件

[root@slave /]# cd /data/mysql/
[root@slave mysql]# rm -rf *
解压备份文件，将解压后的文件拷贝到data目录下

[root@slave /]# cd /data/mysql/

[root@slave mysql]# tar -xvf /nta/alldbfullbackup.tar.gz /data/mysql/

注意修改恢复出来的数据文件的属主为mysql用户。

恢复数据库

innobackupex --apply-log /data/mysql/
# 看到completed OK表示正常完成
data目录下解压出来的备份文件中的xtrabackup_info，其中有备份时记录的binlog_pos信息。

得知创建复制环境所需要的日志文件名和日志位置：

- 日志文件名：master-bin.00297      - 日志位置：884129122

2.3 启动数据库

从库的数据文件已经恢复完成，启动从库的服务。
[root@slave data]# service mysql start
Starting MySQL (Percona Server)..... SUCCESS! 

mysql -uroot -pxxx

mysql > change master to master_host='',master_port=3306,naster_user='repl_user',master_password='',master_log_file='master-bin.000297',master_log_pos=884129122;

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看从库服务是否正常，如果设置有问题需要重新设置从库，则可以通过“reset slave”命令。

show slave status\G  (Slave_IO_Running和Slave_SQL_Running显示均为“Yes”)

从库同步主库状态正常，后续可测试验证。