当前位置：Gxlcms > 数据库问题 > mysql主从复制跳过错误项

mysql主从复制跳过错误项

时间：2021-07-01 10:21:17 帮助过：7人阅读

mysql主从复制，经常会遇到错误而导致slave端复制中断，这个时候一般就需要人工干预，跳过错误才能继续

1 跳过错误有两种方式：

1.1 跳过指定数量的事务：

  1. <code class="language-shell">mysql>stop slave;
  2. mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;        #跳过一个事务
  3. mysql>start slave;</code>

1.2 修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误

  1. <code class="language-shell">vi /etc/my.cnf
  2. [mysqld]
  3. #slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
  4. #slave-skip-errors=all #跳过所有错误</code>

2 例

下面模拟一个错误场景
环境（一个已经配置好的主从复制环境）
master数据库IP：192.168.247.128
slave数据库IP：192.168.247.130
mysql版本：5.6.14
binlog-do-db = mydb

在master上执行以下语句：

  1. <code class="language-shell">mysql>use mysql;
  2. mysql>create table t1 (id int);
  3. mysql>use mydb;
  4. mysql>insert into mysql.t1 select 1;</code>

在slave上查看复制状态

  1. <code class="language-shell">mysql> show slave status \G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.247.128
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000017
  9.           Read_Master_Log_Pos: 2341
  10.                Relay_Log_File: DBtest1-relay-bin.000011
  11.                 Relay_Log_Pos: 494
  12.         Relay_Master_Log_File: mysql-bin.000017
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: No
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.       Replicate_Wild_Do_Table: 
  20.   Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno: 1146
  22.                    Last_Error: Error ‘Table ‘mysql.t1‘ doesn‘t exist‘ on query. Default database: ‘mydb‘. Query: ‘insert into mysql.t1 select 1‘
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 1919
  25.               Relay_Log_Space: 1254
  26.               Until_Condition: None
  27.                Until_Log_File: 
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.               Master_SSL_Cert: 
  33.             Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.         Seconds_Behind_Master: NULL
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error: 
  39.                Last_SQL_Errno: 1146
  40.                Last_SQL_Error: Error ‘Table ‘mysql.t1‘ doesn‘t exist‘ on query. Default database: ‘mydb‘. Query: ‘insert into mysql.t1 select 1‘
  41.   Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id: 1
  43.                   Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
  44.              Master_Info_File: /var/lib/mysql/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: 
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind: 
  50.       Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 131210 21:37:19
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.             Executed_Gtid_Set: 
  56.                 Auto_Position: 0
  57. 1 row in set (0.00 sec)</code>

由结果可以看到，Read_Master_Log_Pos: 2341，Exec_Master_Log_Pos: 1919 时出错了Last_SQL_Error: Error ‘Table ‘mysql.t1‘ doesn‘t exist‘ on query.
因为只对mydb记录了binlog,当在mydb库操作其它数据库的表，但该表在slave上又不存在时就出错了。

我们可在看看binlog里的事务内容，这里一行代表一个事务

  1. <code class="language-shell">mysql> SHOW BINLOG EVENTS in ‘mysql-bin.000017‘ from 1919\G
  2. *************************** 1. row ***************************
  3.    Log_name: mysql-bin.000017
  4.         Pos: 1919
  5.  Event_type: Query
  6.   Server_id: 1
  7. End_log_pos: 1999
  8.        Info: BEGIN
  9. *************************** 2. row ***************************
  10.    Log_name: mysql-bin.000017
  11.         Pos: 1999
  12.  Event_type: Query
  13.   Server_id: 1
  14. End_log_pos: 2103
  15.        Info: use `mydb`; insert into mysql.t1 select 1
  16. *************************** 3. row ***************************
  17.    Log_name: mysql-bin.000017
  18.         Pos: 2103
  19.  Event_type: Xid
  20.   Server_id: 1
  21. End_log_pos: 2134
  22.        Info: COMMIT /* xid=106 */
  23. *************************** 4. row ***************************
  24.    Log_name: mysql-bin.000017
  25.         Pos: 2134
  26.  Event_type: Query
  27.   Server_id: 1
  28. End_log_pos: 2213
  29.        Info: BEGIN
  30. *************************** 5. row ***************************
  31.    Log_name: mysql-bin.000017
  32.         Pos: 2213
  33.  Event_type: Query
  34.   Server_id: 1
  35. End_log_pos: 2310
  36.        Info: use `mydb`; insert into t1 select 9
  37. *************************** 6. row ***************************
  38.    Log_name: mysql-bin.000017
  39.         Pos: 2310
  40.  Event_type: Xid
  41.   Server_id: 1
  42. End_log_pos: 2341
  43.        Info: COMMIT /* xid=107 */
  44. 6 rows in set (0.00 sec)</code>

由上面的结果可知，我们需要跳过两个事务（Pos: 1999 insert,Pos: 2103 commit)
跳过操作：

  1. <code class="language-shell">mysql>stop slave;
  2. mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;        跳过一个事务
  3. mysql>start slave;
  4. mysql> show slave status\G
  5. ERROR 2006 (HY000): MySQL server has gone away
  6. No connection. Trying to reconnect...
  7. Connection id:    3
  8. Current database: mydb
  9. *************************** 1. row ***************************
  10.                Slave_IO_State: Waiting for master to send event
  11.                   Master_Host: 192.168.247.128
  12.                   Master_User: repl
  13.                   Master_Port: 3306
  14.                 Connect_Retry: 60
  15.               Master_Log_File: mysql-bin.000017
  16.           Read_Master_Log_Pos: 3613
  17.                Relay_Log_File: DBtest1-relay-bin.000018
  18.                 Relay_Log_Pos: 283
  19.         Relay_Master_Log_File: mysql-bin.000017
  20.              Slave_IO_Running: Yes
  21.             Slave_SQL_Running: Yes
  22.               Replicate_Do_DB: 
  23.           Replicate_Ignore_DB: 
  24.            Replicate_Do_Table: 
  25.        Replicate_Ignore_Table: 
  26.       Replicate_Wild_Do_Table: 
  27.   Replicate_Wild_Ignore_Table: 
  28.                    Last_Errno: 0
  29.                    Last_Error: 
  30.                  Skip_Counter: 0
  31.           Exec_Master_Log_Pos: 3613
  32.               Relay_Log_Space: 458
  33.               Until_Condition: None
  34.                Until_Log_File: 
  35.                 Until_Log_Pos: 0
  36.            Master_SSL_Allowed: No
  37.            Master_SSL_CA_File: 
  38.            Master_SSL_CA_Path: 
  39.               Master_SSL_Cert: 
  40.             Master_SSL_Cipher: 
  41.                Master_SSL_Key: 
  42.         Seconds_Behind_Master: 0
  43. Master_SSL_Verify_Server_Cert: No
  44.                 Last_IO_Errno: 0
  45.                 Last_IO_Error: 
  46.                Last_SQL_Errno: 0
  47.                Last_SQL_Error: 
  48.   Replicate_Ignore_Server_Ids: 
  49.              Master_Server_Id: 1
  50.                   Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
  51.              Master_Info_File: /var/lib/mysql/master.info
  52.                     SQL_Delay: 0
  53.           SQL_Remaining_Delay: NULL
  54.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  55.            Master_Retry_Count: 86400
  56.                   Master_Bind: 
  57.       Last_IO_Error_Timestamp: 
  58.      Last_SQL_Error_Timestamp: 
  59.                Master_SSL_Crl: 
  60.            Master_SSL_Crlpath: 
  61.            Retrieved_Gtid_Set: 
  62.             Executed_Gtid_Set: 
  63.                 Auto_Position: 0
  64. 1 row in set (0.01 sec)</code>

复制状态正常。

mysql主从复制跳过错误项

标签：ase   int   文件   nlog   lang   my.cnf   error:   erro   event   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！