MYSQL_GTIDS_SLAVE服务器报错1236-Toy模板网

这篇具有很好参考价值的文章主要介绍了MYSQL_GTIDS_SLAVE服务器报错1236。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

故障原因：

是主服务器数据丢失使用xtrbackup备份恢复后导致mysql的slave服务器gitd状态报错

1.slave服务器

老规矩看报错信息：

MYSQL_GTIDS_SLAVE服务器报错1236,mysql,运维,数据库

Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been'
这里可以反应出是主服务器的gtid小于slave服务器导致不可用（也就是可能是主服务器事物和从服务器的事物不一致，很有可能我恢复时覆盖了binlog日志导致）

解决思路：

主从复制原理说人话就是两个线程，一个sql线程和一个binlogdump线程两者之间传输复制日志，其中binlogdump线程负责读取master的binlog日志，而io线程负责读取binlog线程的日志而写入slave服务器的中继日志，再通过slave自己的sql线程写入到或者落盘到slave服务器。通过原理不难发现既然报错说的是：slave服务器的gtid大于master的gtid事物id，就已经能够判定不存在123层原因（最近123层设备有点拉），且slave的sql线程挂了，就说明在读取relay日志写入到mysql时出错，重点排查slave读取master的gtid位置

解决步骤

排查已经处理gtid事物

1.slave服务器

mysql> show global variables like '%gtid%';
+----------------------------------+---------------------------------------------+
| Variable_name                    | Value                                       |
+----------------------------------+---------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                          |
| enforce_gtid_consistency         | ON                                          |
| gtid_executed                    | b3c0ef51-0fab-11ee-8901-0050563ef6ab:5681-2 | ###这里
| gtid_executed_compression_period | 1000                                        |
| gtid_mode                        | ON                                          |
| gtid_owned                       |                                             |
| gtid_purged                      |                                             |
| session_track_gtids              | OFF                                         |
+----------------------------------+---------------------------------------------+

2.master服务器

mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                       |
| enforce_gtid_consistency         | ON                                       |
| gtid_executed                    | b3c0ef51-0fab-11ee-8901-0050563ef6ab:1-2 | ##这里
| gtid_executed_compression_period | 1000                                     |
| gtid_mode                        | ON                                       |
| gtid_owned                       |                                          |
| gtid_purged                      |                                          |
| session_track_gtids              | OFF                                      |
+----------------------------------+------------------------------------------+

好家伙差这么多

slave重置gtid处理值

mysql> STOP slave;##停止主从
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> RESET SLAVE ALL;##从新设置主从
Query OK, 0 rows affected (0.00 sec)
mysql> RESET MASTER;##重新设置以复制状态集
Query OK, 0 rows affected (0.02 sec)
mysql>  change master to master_host='10.0.0.161',master_user='用户',master_password='密码不',master_port=3306,master_auto_position=1;##从新建立gtid
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;##启动复制
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G;
......................
             Slave_IO_Running: Yes ##两个大yes 代表ok
            Slave_SQL_Running: Yes ##两个大yes 代表ok
.................