MySQL中主从复制重复键问题修复方法
-------------------quotebegin------------------------3.Ifyoudecidethatyoucanskipthenextstatementfromthemaster,issuethefollowingstatements:mysql>SETGLOBALSQL_SLAVE_SKIP_COUNTER=n;mysql>STARTSLAVE;Thevalueofnshouldbe1ifthenextstatementfromthemasterdoesnotuseAUTO_INCREMENTorLAST_INSERT_ID().Otherwise,thevalueshouldbe2.Thereasonforusingavalueof2forstatementsthatuseAUTO_INCREMENTorLAST_INSERT_ID()isthattheytaketwoeventsinthebinarylogofthemaster.
-------------------quoteend------------------------
MySQL文档中的意思是当master传到slave的语句中要用到auto_increment,或者last_insert_id()时,需要skip两个event.但实际情况并非如此
测试过程如下:172.16.161.26为master172.16.161.15为slave同步c2cdb，初始状态ok
1.在master上创建测试表
mysql>createtabletmp_test_0208(idintnotnullauto_increment,namevarchar(30),primarykey(id))engine=innodb; QueryOK,0rowsaffected(0.20sec)
2,在salve上insert3条记录
mysql>insertintotmp_test_0208values(1,'a'),(2,'b'),(3,'c'); QueryOK,3rowsaffected(0.00sec) Records:3Duplicates:0Warnings:0 mysql>select*fromtmp_test_0208; +----+------+ |id|name| +----+------+ |1|a| |2|b| |3|c| +----+------+ 3rowsinset(0.00sec)
3,在master上insert3条记录
mysql>insertintotmp_test_0208(name)values('a'),('b'),('c');
QueryOK,3rowsaffected(0.02sec)
Records:3Duplicates:0Warnings:0
mysql>select*fromtmp_test_0208;
+----+------+
|id|name|
+----+------+
|1|a|
|2|b|
|3|c|
+----+------+
3rowsinset(0.00sec)
4, slave的sqlthread中止
/usr/local/mysql/bin/mysql-uroot-pxxxc2cdb-s-e"showslavestatus\G"|egrep"Slave_IO_Running|Sl ave_SQL_Running" Slave_IO_Running:Yes Slave_SQL_Running:No
5, skipnextstatemate后startslave正常
mysql>SETGLOBALSQL_SLAVE_SKIP_COUNTER=1; QueryOK,0rowsaffected(0.00sec) mysql>slavestart; QueryOK,0rowsaffected(0.00sec) /usr/local/mysql/bin/mysql-uroot-pxxxc2cdb-s-e"showslavestatus\G"|egrep"Slave_IO_Running|Sl ave_SQL_Running" Slave_IO_Running:Yes Slave_SQL_Running:Yes
slave端errlog如下：07020816:07:59[ERROR]Slave:Error'Duplicateentry'1'forkey1'onquery.Defaultdatabase:'c2cdb'.Query:'insertintotmp_test_0208(name)values('a'),('b'),('c')',Error_code:1062
07020816:07:59[ERROR]Errorrunningquery,slaveSQLthreadaborted.Fixtheproblem,andrestarttheslaveSQLthreadwith"SLAVE START".Westoppedatlog'db_auction1-bin.000203'position14215101
07020816:09:59[Note]SlaveSQLthreadinitialized,startingreplicationinlog'db_auction1-bin.000203'atposition14215101,relaylog'./db_auction1_b-relay-bin.000457'position:200682931
master羰binlog中相应的记录如下：
#at14215101#07020816:08:00serverid1 log_pos14215101 IntvarSETINSERT_ID=1;#at14215129#07020816:08:00serverid1 log_pos14215129 Query thread_id=2744782 exec_time=0 error_code=0SETTIMESTAMP=1170922080;insertintotmp_test_0208(name)values('a'),('b'),('c');
总结：使用SETGLOBALSQL_SLAVE_SKIP_COUNTER命令跳过失败的SQL