主从复制常见错误处理

 

（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