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是不会被复制的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15456724/viewspace-682681/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15456724/viewspace-682681/