環境是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語句當然無法執行了。
到主庫看一下這個表是否存在:
[[email protected] ~]# 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/