MariaDB – Slave Error

使用主從式資料庫 (Master Slave Replication) 的技術. Master 的資料庫會透過 Binlog 的機制將資料同步到 Slave .

Master 不是真的會把資料複製一份到 Slave，而是 Master 會把所有會變動到資料的指令儲存到 Binlog ,其中包含 CREATE , ALTER TABLE , INSERT , UPDATE , DELETE … (不包含 SELECT 和 SHOW 這一類不影響資料的) .

Slave 透過 IO Thread 的方式 ( IO Thread 會請 Master 發送 Binlog 有更新的部分,並儲存至 Slave 的 Relay Log )
Slave 再透過 SQL Thread 讀取剛剛 IO Thread 儲存的 Relay Log 來執行有更新的部分(指令), 讓兩邊透過非同步的方式來同步資料.

圖片出自於 – https://gilbertasm.github.io/mysql/2018/04/15/understanding-mysql-replication-coordinates.html

更多關於 Master Slave Replication 請參考 – https://benjr.tw/102278

測試環境為 CentOS 8 x86_64 (虛擬機)

下面是當 Slave 沒辦法跟 Master 同步時的錯誤訊息.

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.111.9
                   Master_User: slaveuser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-bin.000002
           Read_Master_Log_Pos: 343
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 739
         Relay_Master_Log_File: master-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB: testdb
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 1062
                    Last_Error: Could not execute Write_rows_v1 event on table testdb.sale; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000001, end_log_pos 1760
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 1489
               Relay_Log_Space: 3384
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 1062
                Last_SQL_Error: Could not execute Write_rows_v1 event on table testdb.sale; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000001, end_log_pos 1760
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 1
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.001 sec)

下面幾種解決方式.

• sql_slave_skip_counter
  這個是最快也最簡單的方式,直接設定 sql_slave_skip_counter=1 可以跳過這一筆同步錯誤的 SQL 語法，直接繼續下一筆 SQL 語法,讓主從同步恢復,但卻是可能造成下次同步失敗的主因.

  MariaDB [(none)]> STOP SLAVE;
  Query OK, 0 rows
  affected (0.00 sec)
   
  MariaDB [(none)]> SET GLOBAL sql_slave_skip_counter=1;
  Query OK, 0 rows
  affected (0.00 sec)
   
  MariaDB [(none)]> START SLAVE;
  Query OK, 0 rows
  affected (0.00 sec)
  

• Last_SQL_Error
  正規的方式還是先檢視問題出在哪裡,以剛剛的範例.

  Last_SQL_Error: Could not execute Write_rows_v1 event on table testdb.sale; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000001, end_log_pos 1760
  

  看得出來是 PRIMARY Key Duplicate 問題,在 Last_SQL_Error 最後面顯示問題現在卡在 master-bin.000001 檔案中 end_log_pos 1760 的位置, Binlog 檔案可以透過下面方式來詳細檢視目前卡在哪一條 SQL 語法上.

  回到 Master 機台來檢視 Binlog ,可以透過 Linux 下的指令 mysqlbinlog 來查看內容.

  [root@localhost ~]# mysqlbinlog -v /var/lib/mysql/master-bin.000001 > binlog.txt
  

  參數 -v – verbose ( 更多的說明 )

  找到 1760 的位置.

  [root@localhost ~]# vi binlog.txt
  
  #Q> INSERT INTO `sale` (`K1`, `staff`, `saleitem`, `salenum`) VALUES (NULL, 'Ben', 'Apple12', '100')
  #201208 21:47:33 server id 1  end_log_pos 1706 CRC32 0x71a6e935         Table_map: `testdb`.`sale` mapped to number 114
  # at 1706
  #201208 21:47:33 server id 1  end_log_pos 1760 CRC32 0x5ec1db3c         Write_rows: table id 114 flags: STMT_END_F
  
  BINLOG '
  9YPPXxMBAAAAOAAAAKoGAAAAAHIAAAAAAAEABnRlc3RkYgAEc2FsZQAEAw8PAwTHAMcAADXppnE=
  9YPPXxcBAAAANgAAAOAGAAAAAHIAAAAAAAEABP/wAwAAAANCZW4HQXBwbGUxMmQAAAA828Fe
  '/*!*/;
  ### INSERT INTO `testdb`.`sale`
  ### SET
  ###   @1=3
  ###   @2='Ben'
  ###   @3='Apple12'
  ###   @4=100
  # Number of rows: 1
  # at 1760
  

  可以知道是 Master 的 INSERT SQL 語法讓 Slave 無法同步,再依據前面的資料得知是 PRIMARY Key (3) Duplicate 問題,到 Slave 找出佔用該 PRIMARY Key (3) 的資料並刪除即可恢復主從同步.

  MariaDB [(none)]> START SLAVE;
  Query OK, 0 rows affected (0.002 sec)