1、主库报同步异常(主从是双向复制的),报错信息显示insert语句的目标表不存在
2、检查主从库的GTID信息发现,主库的GTID比从库的高(应该是从库做过reset master的操作,重置了GTID),导致有些事务被跳过,从而引发报错。这里被跳过的是建表语句。
3、处理方法是推高从库的GTID到主库的水平,从而消除冲突,保证从库过来的事务都可以在主库执行。
4、第二天检查发现还是有些事务被自动跳过,被跳过的是创建临时表的语句。查询文档得知:临时表的创建操作不会同步到备库执行,而临时表的相关操作事务,也会代之以空事务。
下面是详细处理过程:
有个主库最近同步从库信息经常报错,跳过对应的事务之后第二天还是有同样的问题,报错如下:
Last_Error: Error 'Table 'mysql.ibbackup_binlog_marker' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO ibbackup_binlog_marker VALUES(1)'
其中一次详细信息如下:
mysql> show slave status \G
*************************** 1. row ***************************
Relay_Master_Log_File: mysql-bin.000134
Slave_IO_Running: Yes
Last_Errno: 1146
Last_Error: Error 'Table 'mysql.ibbackup_binlog_marker' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO ibbackup_binlog_marker VALUES(1)'
Skip_Counter: 0
Exec_Master_Log_Pos: 1534712
Relay_Log_Space: 8079
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'mysql.ibbackup_binlog_marker' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO ibbackup_binlog_marker VALUES(1)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3809805896
Master_UUID: 44d3c576-ca26-11e3-a90a-a0369f38458a
Master_Info_File: mysql.slave_master_info
Retrieved_Gtid_Set: 44d3c576-ca26-11e3-a90a-a0369f38458a:28-1420
Executed_Gtid_Set: 258cacac-ca16-11e3-a8a1-a0369f35d966:1-160657756,
44d3c576-ca26-11e3-a90a-a0369f38458a:1-5:7-19:21-1294:1296-1307:1309-1320:1322-1333:1335-1346:1348-1359:1361-1372:1374-1412:1414-1425:1427-1438:1440-1451:1453-1464:1466-1478:1480-1491:1493-1504:1506-1517:1519-1530:1532-1543:1545-1556:1558-1570:1572-1583:1585-1596:1598-1609:1611-1622:1624-1635:1637-1648:1650-1662:1664-1675:1677-1688:1690-1701:1703-1714:1716-1727:1729-1740:1742-1754:1756-1767:1769-1780:1782-1793:1795-1806:1808-1819:1821-1832:1834-1846:1848-1859:1861-1872:1874-1885:1887-1898:1900-1911:1913-1924:1926-1938:1940-1951:1953-1964:1966-1977:1979-1990:1992-2003:2005-2016:2018-2030:2032-2043:2045-2056:2058-2069:2071-2082:2084-2095:2097-2108:2110-2122:2124-2135:2137-2148:2150-2161:2163-2174:2176-2187:2189-2200:2202-2214:2216-2227:2229-2240:2242-2253:2255-2266:2268-2279:2281-2292:2294-2306:2308-2319:2321-2332:2334-2345:2347-2358:2360-2371:2373-2384:2386-2398:2400-2411:2413-2424:2426-2437:2439-2450:2452-2463:2465-2476:2478-2490:2492-2503:2505-2516:2518-2529:2531-2542:2544-2555:2557-2568:2570-2582:2584-2595:2597-2608:2610-2621:2623-2634
报错的是一个insert语句,但目标表找不到。这个语句在从库的mysql-bin.000134 日志上,Exec_Master_Log_Pos是1534712。
于是到从库检查对应binlog,在对应位置可以看到,insert语句的GTID是xxx:1413,而在insert语句之前就是建表语句,对应的GTID是1412:
# at 1534324
#160301 0:21:00 server id 3809805896 end_log_pos 1534372 CRC32 0x9cf47996 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '44d3c576-ca26-11e3-a90a-a0369f38458a:1411'/*!*/;
# at 1534372
#160301 0:21:00 server id 3809805896 end_log_pos 1534519 CRC32 0x0e9481da Query thread_id=947514 exec_time=0 error_code=0
SET TIMESTAMP=1456762860/*!*/;
DROP TABLE IF EXISTS `ibbackup_binlog_marker` /* generated by server */
/*!*/;
# at 1534519