mysql ab主從複制出錯及解決過程

一、

mysql主從伺服器報錯描述：

Slave_IO_Running=NO，Slave_SQL_Running=YES，Last_Errno=0 mysql slave stop ; mysql slave start; mysql show slave status ; 如果Slave_IO_Running=YES ...

解決過程 ：

1 如果：Slave_IO_Running=NO，Slave_SQL_Running=YES，Last_Errno=0

mysql> slave stop ;

mysql> slave start;

mysql> show slave status ;

如果Slave_IO_Running=YES，Slave_SQL_Running=YES，Last_Errno=0

那麼，資料庫的replication已經成功啟動。

2 如果提示有duplicated資料，直接delete那條資料之後的，然後

3 如果還不行，檢視錯誤日志，看看是卡在那個binlog上了，比如 ： 卡在000079

則：

SLAVE STOP;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000080', MASTER_LOG_POS=0;

SLAVE START;

4 如果錯誤提示： [mysql]Table tblName is marked as crashed and should be repaired

Mysql提示tblName表格已損壞，需要修複，解決方法：

進入到對應的資料庫目錄：

cd /var/lib/mysql/dbname

使用myisamchk修複：

shell> myisamchk -r tblName

如果提示failed

shell> myisamchk -f tblName

強制修複

恢複時間比較長，耐心等待修複完成

然後重新開機mysql就行 

二、

報警mysqla is down...機房人員回報是HARDWARE ERROR（後面省略），讓機房人員記下錯誤 資訊，讓他們幫忙重新開機，結果又正常工作了，（導出資料），過 了一會又出現報警，mysql_AB error。

解決過程 ：1、檢查從庫show slave status \G;顯示

   Slave_IO_Running:Yes

 Slave_SQL_Running:No

        還出現 了1062錯誤，還提示：

Last_SQL_Error: Error 'Duplicate entry '1001-164761-0' for key 'PRIMARY''  on query. Default database: 'bug'. Query: 'insert into misdata (uid,mid,pid,state,mtime) values (164761,1001,0,-1,1262623560)'

很顯然，由于主庫重新開機導緻 從庫資料不同步而且主鍵沖突。檢視error 日志發現error日志檔案變得好大，比以前大了将近好幾倍，

tail -f mysql_error.log 最開始檢視到的是這條資訊

發現這條資訊

 [ERROR] Slave SQL: Error 'Duplicate entry '1007-443786-0' for key 'PRIMARY'' on query. Default database: 'ufo'. Query: 'insert into misdata (uid,mid,pid,sta

te,mtime) values (443786,1007,0,-1,1262598003)', Error_code: 1062

100104 17:39:05 [Warning] Slave: Duplicate entry '1007-443786-0' for key 'PRIMARY' Error_code: 1062

100104 17:39:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'ufolog.000058

8' position 55793296

報錯和上面的意思差不多,最先想到的就是首先手動同步一下，從庫上首先 stop slave;停止同步

進入主庫鎖表，

FLUSH TABLES WITH READ LOCK；

mysql> show master status;

+-------------------+-----------+--------------+------------------+

| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

| ufo.000063 | 159164526 |              |                  |

1 row in set (0.00 sec)

進入從庫

mysql>change master to master_host='192.168.1.141', master_user='slave',

master_password='xxx',

master_port=3306,

master_log_file='ufo.000063',

master_log_pos=159164526;

完成上面這些後

start slave;

回到主庫

unlock tables; 解鎖

回到從庫 檢視

show slave status \G;

發現正常了，長處了一口氣。可是還沒過一分鐘，發現又開始報錯了，還是最開始那個錯誤，再次檢視一下日志，讓我感到意外的是tail -f mysql_error.log 出現大量的

.......

100106 16:54:21 [Warning] Statement may not be safe to log in statement format. Statement: delete from `system_message_1` where `to_uid` = 181464 ORDER BY `id` ASC LIMIT 1

.........

日志裡面有大量的這種警告，意思應該是statement 格式不安全，用vim 打開他看了一下，發現好多這類警告，我說為什麼錯誤日志怎麼變這麼大了呢！！

statement format 應該是 binlog的一種格式，進入從庫檢視一下

show global variables like 'binlog_format';

果然目前的格式為statement

我需要把格式改為 mixed格式

修改從庫的 my.cfg

在[mysqld]下面加入下面這行

binlog_format=mixed

然後重新開機mysql服務，發現錯誤日志裡的 警告 都停止了。

三、

報錯描述：

MySQL5.6之前的備庫複制有兩個工作線程：IO線程和SQL線程。SQL線程異常導緻複制被中斷。

許多DBA會遭受備庫複制線程中斷的報警，具體說來常見的錯誤有如下幾類：

    1032錯誤 – HA_ERR_KEY_NOT_FOUND

    1062錯誤 – HA_ERR_FOUND_DUPP_KEY （HA_ERR_FOUND_DUPP_KEY 或 HA_ERR_FOUND_DUPP_UNIQUE）

問題背景：

主備資料一不緻，往往是由于MySQL的Bug導緻，其中MySQL複制相關的Bug從每次Release Notes都可以看到相關的身影，就我們最近兩周線上遇到的列舉如下：

    Table map set to 0 after altering MyISAM table （對InnoDB表也會觸發）

    Failing assertion: trx->active_trans when renaming a table with active trx 

    Querying I_S.GLOBAL_TEMPORARY_TABLES or TEMPORARY_TABLES crashes threads working with temp tables（非DEBUG版本也存在）

當然，最大的一個未知的因素是主庫當機，甚至為保證服務可用而做的主備切換，有多少資料不一緻，我們心裡沒有譜。隻有依賴後續的定時掃描來做事後檢測。雖然我們目前做的MHA從一定程度上回減少資料不一緻的數量，但也無法做到資料嚴格一緻。這個問題是MySQL社群的一大難題，在此不展開讨論。

另外，有發生異常時，DBA的不嚴格操作也可能會加劇這種資料不一緻的影響，例如，遇到錯誤時，跳過若幹錯誤，直到複制能正常工作。

    ​​root@(none​​) 09:43:26>set global sql_slave_skip_counter=1;

解決思路：

對于我們線上最常見的兩種類型進行智能化地處理，即 HA_ERR_KEY_NOT_FOUND 或 HA_ERR_FOUND_DUPP_KEY的錯誤，利用ROW模式複制的特點，在執行事件遇到錯誤時，進行如下轉化：

    HA_ERR_KEY_NOT_FOUND：

         對于update事件 UPDATE_ROWS_EVENT 直接插入前鏡像再做更新

        對于delete事件 DELETE_ROWS_EVENT 直接插入前鏡像再做删除。

    HA_ERR_FOUND_DUPP_KEY：

        對于update事件 UPDATE_ROWS_EVENT先删除後鏡像再更新

        對于insert事件 WRITE_ROWS_EVENT 直接以覆寫形式插入（即先删再插或轉化成更新）。

新增變量及狀态

對slave_exec_mode增加 SMART 模式(STRICT |　IDEMPOTENT | SMART)

    ​​root@(none​​) 01:39:34>set global slave_exec_mode=SMART;

    Query OK, 0 rows affected (0.00 sec)

    ​​root@(none​​) 01:39:39>select @@slave_exec_mode;

    +——————-+

    | @@slave_exec_mode |

    | SMART |

    1 row in set (0.00 sec)

增加對smart處理結果的統計：

    ​​root@(none​​) 01:46:51>show status like ‘smart%’;

    +———————+——-+

    | Variable_name | Value |

    | Smart_handle_dup_pk | 1 |

    | Smart_handle_failed | 0 |

    | Smart_handle_no_pk | 1 |

    3 rows in set (0.00 sec)

運作情況：

從線上運作情況下，此patch解決了絕大部分複制中斷異常，大大減少DBA半夜三更被警告吵醒手動修複異常複制的頻率。總體而言，是一個非常成功的更新檔。

但仍然存一此尚未完善的場景，例如唯一鍵限制或外鍵限制情況下，更新失敗還是無法處理。線上上某個應用上，SMART還是不能處理unique key更新失敗問題。

四、

slave日志中的錯誤代碼1：

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position; the last event was read from 'mysql-bin.000016' at 455562731, the last byte read was read from 'mysql-bin.000016' at 4. ( server_errno=1236) 

120725 23:19:17 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the last event was read from 'mysql-bin.000016' at 455562', Error_code: 1236

解決辦法：

先檢視主伺服器中對應日志檔案是否有此行，使用的指令是

mysqlbinlog  /YOURPATH/'mysql-bin.000016 >>/test

查找有沒有455562對應的行 或者和這個數字很接近的行 然後使用chang master to 指令跳過這行，指令格式如下

stop slave;  

CHANGE MASTER TO MASTER_HOST='192.168.192.45',MASTER_USER='XXXX',MASTER_PASSWORD='XXXX',MASTER_LOG_FILE='mysql-bin.000016',MASTER_LOG_POS=455678;  

start slave;  

如果你找不到455562或者和這個數字很接近的行，你可以使用如下指令檢視'mysql-bin.000016 的行數

ls -la mysql-bin.000016  

如果統計的行數遠遠少于455562，你可以跳過這個日志，從下一個日志開始複制，指令如下

CHANGE MASTER TO MASTER_HOST='192.168.192.45',MASTER_USER='backup',MASTER_PASSWORD='weiphone',MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=4;  

五、

錯誤類型代碼2：

mysql slave Error_code: 1062  1054

如果日志中出現了這樣代碼，可能是錯誤的select，或update操作，master是跳過這些操作，但是被記錄到了二進制日志中，slave會依據二進制中的語句做相同的動作，就會報錯，知道原理了要做的操作就很簡單了

set gloable  sql_slave_skip_counter=n;    //n為你想跳過錯誤語句的個數  

如果你不想再遇到這種錯誤，可以寫到slave配置檔案中格式如下：

slave_skip_errors = 1062 

slave_skip_errors = all   //跳過所有類型的錯誤碼

六、

錯誤提示：

mysql> start slave;

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

[root@slave ~]# tail /var/log/mysqld.log

090105 11:53:52 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000013' (relay_log_pos 235)

090105 11:53:52 [ERROR] Could not find target log during relay log initialization

...

[root@slave mysql]# ls /var/run/mysqld/

mysqld-relay-bin.000001  mysqld-relay-bin.000003  mysqld-relay-bin.000005  mysqld-relay-bin.index   

mysqld-relay-bin.000002  mysqld-relay-bin.000004  mysqld-relay-bin.000006

[root@slave mysql]# cat master.info 

14

mysql-bin.000010

1235

192.168.3.21

slave

29019853

3307

60

[root@slave mysql]# cat relay-log.info 

/var/run/mysqld/mysqld-relay-bin.000013

235

3

50703

解決過程：

[root@slave ~]# service mysqld stop

[root@slave mysql]# rm -fr master.info 

[root@slave mysql]# rm -fr relay-log.info

[root@slave ~]# grep skip-slave-start /etc/my.cnf

skip-slave-start

[root@slave ~]# service mysqld start

[root@master ~]# mysql -uroot -p

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| mysql-bin.000011 |       98 |              |                  | 

[root@slave ~]# mysql -uroot -p

mysql> change master to 

master_log_file='mysql-bin.000011',

master_log_pos=98,

master_host='192.168.3.21',

master_port=3307,

master_user='slave',

master_password='12345678';

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.3.21

                Master_User: slave

                Master_Port: 3307

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000011

        Read_Master_Log_Pos: 98

             Relay_Log_File: mysqld-relay-bin.000002

              Relay_Log_Pos: 235

      Relay_Master_Log_File: mysql-bin.000011

           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: 98

            Relay_Log_Space: 235

            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: 0

導緻 mysql 主從複制出錯的原因有很多修複的辦法也很多，

希望遇到和這次一樣問題的朋友看到這篇文章後會得到 一些啟發和解決問題的方法。

标題：個人整理