mysql master-slave複製錯誤［解決事例］

環境是MySQL Master-Slave模式，Linux下5.1版本，今天在MySQL從庫上看到一個錯誤：

100901 18:16:16 [ERROR] Slave SQL: Error 'Table 'maopaodb.g_group_member' doesn't exist' on query. Default database: 'maopaodb'. Query: 'ALTER table g_group_member CHANGE
`create_time` `create_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
change `update_time` `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', Error_code: 1146
100901 18:16:16 [Warning] Slave: Table 'maopaodb.g_group_member' doesn't exist Error_code: 1146
100901 18:16:16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000010' position 91185240

意思是maopaodb這個庫的一個表g_group_member在從庫上不存在，而更改這個表的SQL語句當然無法執行了。

到主庫看一下這個表是否存在：

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8390
Server version: 5.1.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use maopaodb;
Database changed
mysql> show tables;
+--------------------------+
| Tables_in_maopaodb       |
+--------------------------+
......
| g_group_member           |
......
+--------------------------+
31 rows in set (0.00 sec)

主庫上是有的，那麼一定是複製過程中這個表沒有同步過來，而後續的SQL在操作這個表的時候就報錯了。

我們先著力解決掉眼前的問題，在主庫檢視這個表的建表語句：

mysql> show create table g_group_member;

| Table          | Create Table

| g_group_member | CREATE TABLE `g_group_member` (
`g_group_id` int(11) NOT NULL,
`u_user_id` int(11) NOT NULL,
`u_username` varchar(64) DEFAULT NULL,
`level` enum('admin','member','block') DEFAULT 'member',
`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`g_group_id`,`u_user_id`),
KEY `idx_user` (`u_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

去從庫上執行這個語句手動建立這個表：

mysql> CREATE TABLE `g_group_member` (
->   `g_group_id` int(11) NOT NULL,
->   `u_user_id` int(11) NOT NULL,
->   `u_username` varchar(64) DEFAULT NULL,
->   `level` enum('admin','member','block') DEFAULT 'member',
->   `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
->   PRIMARY KEY (`g_group_id`,`u_user_id`),
->   KEY `idx_user` (`u_user_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

然後開啟同步：

mysql> slave start;
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.0.33
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 93693598
Relay_Log_File: mysql-relay-bin.000008
Relay_Log_Pos: 5729999
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: maopaodb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1061
Last_Error: Error 'Duplicate key name 'idx_user'' on query. Default database: 'maopaodb'. Query: 'alter table `maopaodb`.`g_group_member` add index `idx_user` (`u_user_id`)'
Skip_Counter: 0
Exec_Master_Log_Pos: 93460249
Relay_Log_Space: 5963503
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: 1061
Last_SQL_Error: Error 'Duplicate key name 'idx_user'' on query. Default database: 'maopaodb'. Query: 'alter table `maopaodb`.`g_group_member` add index `idx_user` (`u_user_id`)'
1 row in set (0.00 sec)

繼續報錯，這說明idx_user這個索引已經存在了，是的，我們的建表語句包括了這個索引。可以去主庫確認一下：

mysql> show index from g_group_member;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| g_group_member |          0 | PRIMARY  |            1 | g_group_id  | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| g_group_member |          0 | PRIMARY  |            2 | u_user_id   | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| g_group_member |          1 | idx_user |            1 | u_user_id   | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

看一下從庫上該表的索引：

mysql> show index from g_group_member;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| g_group_member |          0 | PRIMARY  |            1 | g_group_id  | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| g_group_member |          0 | PRIMARY  |            2 | u_user_id   | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| g_group_member |          1 | idx_user |            1 | u_user_id   | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

這時從庫的error日誌記錄的資訊如下：

100902 13:57:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000010' at position 91185240, relay log './mysql-relay-bin.000008' position: 3454990
100902 13:57:55 [ERROR] Slave SQL: Error 'Duplicate key name 'idx_user'' on query. Default database: 'maopaodb'. Query: 'alter table `maopaodb`.`g_group_member` add index `idx_user` (`u_user_id`)', Error_code: 1061
100902 13:57:55 [Warning] Slave: Duplicate key name 'idx_user' Error_code: 1061
100902 13:57:55 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000010' position 93460249

可以手動刪掉已有的索引，以便同步繼續進行：

mysql> drop index idx_user on g_group_member;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> slave start;
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.0.33
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 93702873
Relay_Log_File: mysql-relay-bin.000008
Relay_Log_Pos: 5972623
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: maopaodb
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: 93702873
Relay_Log_Space: 5972778
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

其實到這裡我們只是粗暴的解決了問題，這裡的根本原因是為什麼那個表沒有同步到從庫，實際上是因為從庫配置了--replicate-do-db引數引起的，如果主庫是在這個引數指定之外的其他資料庫上跨庫操作，那麼其binlog是不會被複制的。