主从复制常见错误处理

冷不防 2021-12-07 11:23 353阅读 0赞

（1）

Last\_Errno: 1782  
Last\_Error: Error executing row event: '@@SESSION.GTID\_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID\_MODE = ON.'

>stop slave sql\_thread;

>set GLOBAL GTID\_MODE = ON\_PERMISSIVE;

\]>set GLOBAL GTID\_MODE = OFF\_PERMISSIVE;

>set GLOBAL GTID\_MODE = OFF;

>start slave sql\_thread;

>start slave;

Last\_Error: @@SESSION.GTID\_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID\_MODE = OFF.

Last\_IO\_Error: The replication receiver thread cannot start because the master has GTID\_MODE = ON and this server has GTID\_MODE = OFF.  
Last\_SQL\_Errno: 1781  
Last\_SQL\_Error: @@SESSION.GTID\_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID\_MODE = OFF.

>set GLOBAL GTID\_MODE = OFF\_PERMISSIVE;

>set GLOBAL GTID\_MODE = ON\_PERMISSIVE;

>set GLOBAL GTID\_MODE = ON;

>start slave;

（2）

Last\_IO\_Errno: 1236  
Last\_IO\_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER\_AUTO\_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

在主库上查看GTID的执行情况：

root@localhost \[(none)\]>show global variables like 'GTID\_EXECUTED';  
ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect...  
Connection id: 11  
Current database: \*\*\* NONE \*\*\*

\+---------------+-------------------------------------------------------------------------------------+  
| Variable\_name | Value |  
\+---------------+-------------------------------------------------------------------------------------+  
| gtid\_executed | 254032d4-9231-11e9-9a92-000c2986318c:1-20,  
3115bb62-9231-11e9-a78a-000c29213d3f:1-2 |  
\+---------------+-------------------------------------------------------------------------------------+  
1 row in set (0.01 sec)

在从库上接上该GTID并开启slave

root@localhost \[(none)\]>show global variables like 'GTID\_EXECUTED';

root@localhost \[(none)\]>set global GTID\_PURGED='254032d4-9231-11e9-9a92-000c2986318c:1-20,  
'> 3115bb62-9231-11e9-a78a-000c29213d3f:1-2';  
ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect...  
Connection id: 10  
Current database: \*\*\* NONE \*\*\*

Query OK, 0 rows affected (0.01 sec)

root@localhost \[(none)\]>start slave;  
Query OK, 0 rows affected (0.00 sec)

root@localhost \[(none)\]>show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.132.162  
Master\_User: repl  
Master\_Port: 3366  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin.000010  
Read\_Master\_Log\_Pos: 194  
Relay\_Log\_File: mysql-relay-bin.000004  
Relay\_Log\_Pos: 367  
Relay\_Master\_Log\_File: mysql-bin.000010  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

转载于:https://www.cnblogs.com/102x/p/10984473.html