[ERROR] Slave SQL: Error 'Duplicate entry '7301' for key 'PRIMARY'' on query. Default database: 'rt_roledb0301'. Query: 'insert into gm_proc_run_info (run_name, des, err_code) values ('pc_rank_of_day', 'begin', 0)', Error_code: 1062
111027 4:00:12 [Warning] Slave: Duplicate entry '7301' for key 'PRIMARY' Error_code: 1062
111027 4:00:12 [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-binlog.000009' position 31542061
定位分析:在主从切换以后,由于没有关闭从上的计划任务
- (root@localhost:)[(none)]> show global variables like 'event_sch%';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | event_scheduler | ON |
- +-----------------+-------+
- 1 row in set (0.00 sec)
解决方法:关闭从服务器上的计划任务,对从服务器进行数据恢复。
-------------------quote begin------------------------
3. If you decide that you can skip the next statement from the master, issue the following
statements:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
mysql> START SLAVE;
The value of n should be 1 if the next statement from the master does not use
AUTO_INCREMENT or LAST_INSERT_ID(). Otherwise, the value should be 2. The
reason for using a value of 2 for statements that use AUTO_INCREMENT or
LAST_INSERT_ID() is that they take two events in the binary log of the master.
-------------------quote end------------------------
MySQL文档中的意思是当master传到slave的语句中要用到auto_increment,或者last_insert_id()时,需要skip两个event. 但实际情况并非如此
测试过程如下:
192.168.1.1 为master
192.168.1.2 为slave
同步test,初始状态ok
1. 在master上创建测试表
点击(此处)折叠或打开
- mysql> create table tmp_test_0208(id int not nullauto_increment,name varchar(30),primary key(id)) engine=innodb;
- Query OK, 0 rows affected (0.20 sec)
2, 在salve上insert 3条记录
点击(此处)折叠或打开
- mysql> insert into tmp_test_0208 values(1,'a'),(2,'b'),(3,'c');
- Query OK, 3 rows affected (0.00 sec)
- Records: 3 Duplicates: 0 Warnings: 0
-
- mysql> select * from tmp_test_0208;
- +----+------+
- | id | name |
- +----+------+
- | 1 | a |
- | 2 | b |
- | 3 | c |
- +----+------+
- 3 rows in set (0.00 sec)
3, 在master上insert 3条记录
点击(此处)折叠或打开
- mysql> insert into tmp_test_0208(name) values('a'),('b'),('c');
- Query OK, 3 rows affected (0.02 sec)
- Records: 3 Duplicates: 0 Warnings: 0
-
- mysql> select * from tmp_test_0208;
- +----+------+
- | id | name |
- +----+------+
- | 1 | a |
- | 2 | b |
- | 3 | c |
- +----+------+
- 3 rows in set (0.00 sec)
4, slave 的sql thread 中止
点击(此处)折叠或打开
- /usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e"show slave status\G" |egrep "Slave_IO_Running|Sl
- ave_SQL_Running"
- Slave_IO_Running: Yes
- Slave_SQL_Running: No
5, skip next statemate后start slave正常
点击(此处)折叠或打开
- mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> slave start;
- Query OK, 0 rows affected (0.00 sec)
-
- /usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e"show slave status\G" |egrep "Slave_IO_Running|Sl
- ave_SQL_Running"
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
slave端errlog如下:
130416 13:20:57 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into tmp_test_0208(name) values('a'),('b'),('c')', Error_code: 1062
130416 13:20:57 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062
130416 13:20:57 [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.004707' position 39082343
master binlog中相应的记录如下:
SET INSERT_ID=1/*!*/;
# at 39082439
#130416 13:16:07 server id 1 end_log_pos 39082557 Query thread_id=12589 exec_time=0 error_code=0
SET TIMESTAMP=1366089367/*!*/;
insert into tmp_test_0208(name) values('a'),('b'),('c')
/*!*/;
# at 39082557
#130416 13:16:07 server id 1 end_log_pos 39082584 Xid = 409512836
COMMIT/*!*/;
总结:使用SET GLOBAL SQL_SLAVE_SKIP_COUNTER 命令跳过失败的SQL