歡迎光臨
每天分享高質量文章

解決方案 | MySQL DBA主從複製出錯怎麼辦?

MySQL主從複製中若是出現錯誤,一般有哪些解決方法?通讀本文,相信你會有答案。

 

主從複製中若是出現錯誤可以通過幾個方法來進行解決:

1. 如果主從複製時發生了主鍵衝突,從而阻止了主從複製,可以使用sql_slave_skip_counter這個變數來忽略錯誤將其排除

2. 如果發生了較大的錯誤,可以考慮使用reset slave的方法重新配置從服務器來恢復錯誤

 

以下演示如何使用這兩種方法解決錯誤,及相關操作的詳細說明

  • reset slave的使用方法
  • 環境準備搭建主從同步
  • 主節點配置

 

1. 修改配置檔案

 

[[email protected] ~]# vim /etc/my.cnf[mysqld]log-bin=/data/bin/mysql-binbinlog-format=rowserver-id=1

 

2. 創建二進制日誌目錄

 

[[email protected] ~]# mkdir /data/bin[[email protected] ~]# chown -R mysql.mysql /data/bin

 

3. 啟動mysqld服務

[[email protected] ~]# systemctl start mariadb

 

4. 查看主服務器日誌位置

 

[[email protected] ~]# mysql -e "SHOW MASTER LOGS;"+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |     26753 || mysql-bin.000002 |    921736 || mysql-bin.000003 |       245 |+------------------+-----------+

 

5. 創建一個用來複制資料的賬戶

[[email protected] ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.73.%' IDENTIFIED BY 'CentOS';"

 

從節點配置

 

1. 修改配置檔案

 

[[email protected] ~]# vim /etc/my.cnf[mysqld]read-onlyserver-id=2

 

2. 啟動服務

[[email protected] ~]# systemctl start mariadb

 

此處開始構建錯誤配置,以下所有CHANGE MASTER TO配置均為錯誤

 

3. 配置CHANGE MASTER TO

  MariaDB [(none)]> CHANGE MASTER TO       ->   MASTER_HOST='master2.mycompany.com',       ->   MASTER_USER='replication',       ->   MASTER_PASSWORD='bigs3cret',       ->   MASTER_PORT=3306,       ->   MASTER_LOG_FILE='master2-bin.001',       ->   MASTER_LOG_POS=4,       ->   MASTER_CONNECT_RETRY=10;    Query OK, 0 rows affected (0.00 sec)

 

4. 查看下SLAVE STATUS

    MariaDB [(none)]> SHOW SLAVE STATUS\G;    *************************** 1. row ***************************                  Slave_IO_State:                      Master_Host: master2.mycompany.com                     Master_User: replication                     Master_Port: 3306                   Connect_Retry: 10                 Master_Log_File: master2-bin.001             Read_Master_Log_Pos: 4                  Relay_Log_File: mariadb-relay-bin.000001                   Relay_Log_Pos: 4           Relay_Master_Log_File: master2-bin.001                Slave_IO_Running: No               Slave_SQL_Running: No          ...以下省略...

 

5. 啟動複製執行緒

MariaDB [(none)]> START SLAVE;

 

6. 再次查看SLAVE STATUS

 

    MariaDB [(none)]> SHOW SLAVE STATUS\G;    *************************** 1. row ***************************                  Slave_IO_State: Connecting to master                     Master_Host: master2.mycompany.com                     Master_User: replication                     Master_Port: 3306                   Connect_Retry: 10                 Master_Log_File: master2-bin.001             Read_Master_Log_Pos: 4                  Relay_Log_File: mariadb-relay-bin.000001                   Relay_Log_Pos: 4           Relay_Master_Log_File: master2-bin.001                Slave_IO_Running: Connecting               Slave_SQL_Running: Yes          ...以下省略...

 

執行緒已經正常啟動

 

主服務器匯入資料進行測試

 [[email protected] ~]# mysql < hellodb_innodb.sql     [[email protected] ~]# mysql -e "SHOW DATABASES;"    +--------------------+    | Database           |    +--------------------+    | information_schema |    | hellodb            |    | mysql              |    | performance_schema |    | test               |    +--------------------+

 

從服務器查看是否同步(CHANGE MASTER TO信息不對怎麼可能同步)

 

    MariaDB [(none)]> SHOW DATABASES;    +--------------------+    | Database           |    +--------------------+    | information_schema |    | mysql              |    | performance_schema |    | test               |    +--------------------+    4 rows in set (0.00 sec)

 

 

以下為錯誤解決方法

 

由於錯誤發生在CHANGE MASTER TO所以此處將CHANG MASTER TO部分糾正就行

 

1. 首先將從服務器的複製執行緒停止

 

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

 

2. 將從服務器上的SLAVE信息重置

 

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

 

3. 重新輸入正確的CHANGE MASTER TO信息

 

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.110',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=245;Query OK, 0 rows affected (0.01 sec)

 

4. 查看SLAVE STATUS;

MariaDB [(none)]> SHOW SLAVE STATUS\G;*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 192.168.73.110                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 245               Relay_Log_File: mariadb-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: No            Slave_SQL_Running: No              Replicate_Do_DB: #此處信息已經改為正確

 

5. 重新啟動執行緒

 

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

 

6. 再次查看SLAVE STATUS;

 

MariaDB [(none)]> SHOW SLAVE STATUS\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.73.110                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 7384     #已經有資料複製過來了               Relay_Log_File: mariadb-relay-bin.000002                Relay_Log_Pos: 7668        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: Yes            Slave_SQL_Running: Yes#IO和SQL執行緒已經啟動

 

7. 查看下從節點內的庫是否已經同步

 

MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || hellodb            |          #hellodb庫已經從主節點中複製過來了| mysql              || performance_schema || test               |+--------------------+5 rows in set (0.01 sec)

 

  • 其他說明:

 

如果生產中,發生主從節點之間的資料偏差較大並且遲遲不能同步,可以考慮將從服務器全部清除從新配置從服務器。

 

關於sql_slave_skip_counter的使用方法

 

當發生主鍵衝突時,從服務器會卡在出錯的位置不再進行服務,此種錯誤一般會出現在主主複製或者從服務器已經占用了某條記錄的情況下,此時可以使用此選項來忽略錯誤。

構建錯誤

 

此處繼續沿用剛纔的主從複製環境

 

1. 在從服務器上創建一條記錄

 

MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Li Xiaolong',30,'M');Query OK, 1 row affected (0.00 sec)

 

2. 在主服務器上也創建一條主鍵相同的記錄

 

MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Xiao Yan',20,'M');Query OK, 1 row affected (0.00 sec)

 

3. 傳回從節點查看SLAVE STATUS

 

MariaDB [(none)]> SHOW SLAVE STATUS\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.73.110                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 7576               Relay_Log_File: mariadb-relay-bin.000002                Relay_Log_Pos: 7668        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: Yes            Slave_SQL_Running: No              Replicate_Do_DB:           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 event on table hellodb.teachers; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 7549                 Skip_Counter: 0          Exec_Master_Log_Pos: 7384              Relay_Log_Space: 8156              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1062               Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 7549  Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

 

4. 從節點已經出錯,在主節點繼續添加記錄

 

 

MariaDB [(none)]> INSERT hellodb.teachers VALUE (6,'Xiao Xuner',20,'M');Query OK, 1 row affected (0.00 sec)

 

5. 此時從節點已經不會再繼續從主節點複製信息

 

 

MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4;+-----+-------------+-----+--------+| TID | Name      | Age |Gender |+-----+-------------+-----+--------+|   5 | Li Xiaolong |  30 | M      |   #此為剛纔從節點添加的記錄+-----+-------------+-----+--------+1 row in set (0.00 sec)

 

排錯

 

1. 使用sql_slave_skip_counter變數忽略錯誤

 

 

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

 

2. 停止執行緒並重新啟動

 

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

 

3. 查看slave status狀態,此時已經沒有報錯的信息

 

MariaDB [(none)]> SHOW SLAVE STATUS\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.73.110                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 7770               Relay_Log_File: mariadb-relay-bin.000003                Relay_Log_Pos: 529        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 7770              Relay_Log_Space: 8634              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: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

 

4. 在從服務器上查看teachers表



MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4;+-----+-------------+-----+--------+| TID | Name        | Age | Gender |+-----+-------------+-----+--------+|   5 | Li Xiaolong |  30 | M      ||   6 | Xiao Xuner  |  20 | M      |    #此時剛纔在主節點插入的6號記錄已經複製過來+-----+-------------+-----+--------+2 rows in set (0.00 sec)

 

以上為主從複製時出錯的一些相關的修複方法,如果有幫助,感謝分享+在看;大家對什麼內容感興趣,也歡迎大家在留言區評論哦。

 

出處:https://www.linuxidc.com/Linux/2019-05/158646p2.htm

編輯:尹文敏

赞(0)

分享創造快樂