線上Slave報1062的案例

線上Slave報1062的案例

最近經常線上的Slave老報1062的錯誤，蛋碎一地，幸好Slave暫時沒有用到業務上，也就是說沒有做讀寫分離，所以Slave有問題，影響也不大，但每隔一陣子就報1062主鍵衝突的錯誤，讓我好糾結，如果不解決的話，我都不敢上Atlas，所以一直在排查到底是什麼引起的。雖然大家都知道當Master插入的資料所包含的主鍵或者唯一鍵在Slave上已經存在的時候，就會報Last_Errno: 1062，主從同步就斷開了。但是奇怪的是每次報1062的時候，Slave上的資料都和Master想插入的資料一樣的，這足以排除人為手動插入資料的可能。

排查過程：

    1、如果經常出現1062錯誤的時候，要注意出現的時間點，錯誤判在那個庫那個表，下次再出現的時候是否又是它。

    2、當出現1062錯誤的時候，查看Slave最近的一次備份，看這資料是否早存在Slave上了

    3、當出現1062錯誤的時候，查看Master和Slave的行記錄是否一樣，如果每次都是一樣的，這時可以考慮是是否有定時器調預存程序進行Insert操作。

Slave上報錯1062的資訊如下：

查一下Master binlog的記錄：

可以看到Master binlog是插入了一條記錄，登入Master查一下：

之前用的binlog格式是本來是用了預設的mixed，後來以為有可能是binlog的日誌格式導致了資料問題，把它修改為ROW了，但問題依舊存在。

查看Slave上的資訊，可以看到binlog格式也是ROW，而且設定為read_only，行資料記錄和Master是完全一樣的，如下：

到這裡是不是覺得有點怪呢，到底Slave上的資料是怎麼來的呢？後來查看了一下與這個表相關的預存程序和定時器，如下：（相關的表名我用數字代替了，請見諒！）

Create Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `_sp_1036`()
BEGIN DECLARE _count INT UNSIGNED DEFAULT 0; DECLARE _current_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP();SELECT COUNT(*) INTO _count FROM _1030 WHERE F04 IS NOT NULL AND F05 > _current_time;
INSERT INTO _1036 SET F01 = DATE(_current_time), F02 = HOUR(_current_time), F03 = _count ON DUPLICATE KEY UPDATE F03 = VALUES(F03);END

Create Event

CREATE DEFINER=`root`@`localhost` EVENT `_daily_sp_1036` ON SCHEDULE EVERY 1 HOUR STARTS '2014-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO CALL _sp_1036()

這個定時器一個小時運行一次，調用預存程序，向表裡插入資料，其實這裡的預存程序和定時器寫得都沒什麼問題，問題在 CREATE DEFINER=`root`@`localhost`，曆史留下的坑好大啊，Slave上設定了read_only只對普通使用者有用，對管理層級的使用者是沒用的，所以Slave上也執行了定時器到時間就執行預存程序，為了證明Slave有自己產生資料，我們做了測試，把Slave的SQL線程停掉：

可以看到主從同步斷開的情況，每個小時整點Slave也會產生一條記錄。Slave上的資料是怎麼來的，已經很明顯了。

從上面可以看到Master的資料和Slave的是一樣的，這樣先把主從同步處理好，通過set global sql_slave_skip_counter=1  跳過一個事務，如果資料不一致的情況，以Master的資料記錄為準：

可以看到出現了跳過一個事務後，報了一條很有趣的Log: the event's master log FIRST 。這時還是報同一條記錄的主鍵衝突，再執行一次

可以看到同步正常了，雖然是正常了，為了保證資料的完整性，建議使用我之前寫的pt-table-checksum校正一個資料的完整性。

討論幾個問題：

    一、為什麼上面的情況有時會有報1062的錯誤，有時候沒有呢？

    二、是master同步資料過來的時候報了1062錯誤，還是slave上執行定時器調預存程序時把資料插入slave的時候報1062呢？

嘻嘻，歡迎大家討論

總結：

    一、管理好MySQL的許可權，實現許可權最小化管理，需要什麼許可權，開什麼許可權，禁止管理員層級的使用者運行程式相關的任何東西。

    二、定期進行主從的資料完整性校正，確保主從的資料是一致性，特別是讀寫分離情境，一定要重視這類問題

本文永久更新連結地址：