[size=small]-实际遇到的问题：
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'antiy_bbs'.
Query: 'INSERT INTO bbs_common_searchindex SET `srchmod`='2' , `keywords`='apk版本' , `searchstring`='forum|title|YXBr54mI5pys|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\
'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'
51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' ,
`dateline`='1420450904' , `expiration`='1420454504' , `num`='0' , `ids`='0''
从报错来看，应该是插入的值违反了主键的约束，但看表结构，主键是自增类型，就不晓得哪里出了问题
主库可看到插入了两条记录，后做了truncate
[root@newmysql5 mysql]# mysqlbinlog mysql-bin.000008|grep bbs_common_searchindex
INSERT INTO bbs_common_searchindex SET `srchmod`='2' , `keywords`='apk版本' , `searchstring`='forum|title|YXBr54mI5pys|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , `dateline`='1420450904' , `expiration`='1420454504' , `num`='0' , `ids`='0'
INSERT INTO bbs_common_searchindex SET `srchmod`='2' , `keywords`='liqintao' , `searchstring`='forum|title|bGlxaW50YW8=|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , `dateline`='1420451052' , `expiration`='1420454652' , `num`='0' , `ids`='0'
TRUNCATE bbs_common_searchindex
而从库只看到insert into，而没有truncate，所以查询记录还有2条，应该是插入的时候卡住了就没往下走。
mysql> show create table bbs_common_searchindex;
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbs_common_searchindex | CREATE TABLE `bbs_common_searchindex` (
`searchid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`srchmod` tinyint(3) unsigned NOT NULL,
`keywords` varchar(255) NOT NULL DEFAULT '',
`searchstring` text NOT NULL,
`useip` varchar(15) NOT NULL DEFAULT '',
`uid` mediumint(10) unsigned NOT NULL DEFAULT '0',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`expiration` int(10) unsigned NOT NULL DEFAULT '0',
`threadsortid` smallint(6) unsigned NOT NULL DEFAULT '0',
`num` smallint(6) unsigned NOT NULL DEFAULT '0',
`ids` text NOT NULL,
PRIMARY KEY (`searchid`),
KEY `srchmod` (`srchmod`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show index from bbs_common_searchindex;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bbs_common_searchindex | 0 | PRIMARY | 1 | searchid | A | 0 | NULL | NULL | | BTREE | | |
| bbs_common_searchindex | 1 | srchmod | 1 | srchmod | A | NULL | NULL | NULL | | BTREE | | |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
用网上介绍的方法跳过之后，
stop slave;
set global sql_slave_skip_counter=1;
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.101
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 48320168
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 4840169
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 4841595
Relay_Log_Space: 48321003
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: 34552 ---这里却在持续增大
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 14bfd13a-1920-11e4-ac07-000c296c30fd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: altering table ---显示正在变更表
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> use antiy_bbs
Database changed
mysql> select count(*) from bbs_common_searchindex; --从库看到还有2条记录，而主库是0
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select * from bbs_common_searchindex;
Empty set (0.00 sec)
mysql> select count(*) from bbs_common_searchindex; ---等待了一会之后，记录消失，同步恢复。
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.101
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 49058519
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 10539748
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 49058519
Relay_Log_Space: 49057429
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 14bfd13a-1920-11e4-ac07-000c296c30fd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> show variables like 'sql_slave_skip_counter'; --退出会话后，自动变回原来的0，因此不用修改。
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| sql_slave_skip_counter | 0 |
+------------------------+-------+
1 row in set (0.00 sec) [/size]
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'antiy_bbs'.
Query: 'INSERT INTO bbs_common_searchindex SET `srchmod`='2' , `keywords`='apk版本' , `searchstring`='forum|title|YXBr54mI5pys|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\
'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'
51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' ,
`dateline`='1420450904' , `expiration`='1420454504' , `num`='0' , `ids`='0''
从报错来看，应该是插入的值违反了主键的约束，但看表结构，主键是自增类型，就不晓得哪里出了问题
主库可看到插入了两条记录，后做了truncate
[root@newmysql5 mysql]# mysqlbinlog mysql-bin.000008|grep bbs_common_searchindex
INSERT INTO bbs_common_searchindex SET `srchmod`='2' , `keywords`='apk版本' , `searchstring`='forum|title|YXBr54mI5pys|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , `dateline`='1420450904' , `expiration`='1420454504' , `num`='0' , `ids`='0'
INSERT INTO bbs_common_searchindex SET `srchmod`='2' , `keywords`='liqintao' , `searchstring`='forum|title|bGlxaW50YW8=|0||\\\'65\\\',\\\'55\\\',\\\'70\\\',\\\'69\\\',\\\'71\\\',\\\'72\\\',\\\'73\\\',\\\'74\\\',\\\'2\\\',\\\'48\\\',\\\'38\\\',\\\'67\\\',\\\'47\\\',\\\'79\\\',\\\'56\\\',\\\'58\\\',\\\'59\\\',\\\'66\\\',\\\'51\\\',\\\'60\\\',\\\'53\\\',\\\'68\\\',\\\'77\\\',\\\'75\\\',\\\'76\\\',\\\'78\\\',\\\'63\\\'|0|0|all|||0' , `useip`='192.168.10.241' , `uid`='54' , `dateline`='1420451052' , `expiration`='1420454652' , `num`='0' , `ids`='0'
TRUNCATE bbs_common_searchindex
而从库只看到insert into，而没有truncate，所以查询记录还有2条，应该是插入的时候卡住了就没往下走。
mysql> show create table bbs_common_searchindex;
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bbs_common_searchindex | CREATE TABLE `bbs_common_searchindex` (
`searchid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`srchmod` tinyint(3) unsigned NOT NULL,
`keywords` varchar(255) NOT NULL DEFAULT '',
`searchstring` text NOT NULL,
`useip` varchar(15) NOT NULL DEFAULT '',
`uid` mediumint(10) unsigned NOT NULL DEFAULT '0',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`expiration` int(10) unsigned NOT NULL DEFAULT '0',
`threadsortid` smallint(6) unsigned NOT NULL DEFAULT '0',
`num` smallint(6) unsigned NOT NULL DEFAULT '0',
`ids` text NOT NULL,
PRIMARY KEY (`searchid`),
KEY `srchmod` (`srchmod`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show index from bbs_common_searchindex;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bbs_common_searchindex | 0 | PRIMARY | 1 | searchid | A | 0 | NULL | NULL | | BTREE | | |
| bbs_common_searchindex | 1 | srchmod | 1 | srchmod | A | NULL | NULL | NULL | | BTREE | | |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
用网上介绍的方法跳过之后，
stop slave;
set global sql_slave_skip_counter=1;
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.101
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 48320168
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 4840169
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 4841595
Relay_Log_Space: 48321003
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: 34552 ---这里却在持续增大
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 14bfd13a-1920-11e4-ac07-000c296c30fd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: altering table ---显示正在变更表
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> use antiy_bbs
Database changed
mysql> select count(*) from bbs_common_searchindex; --从库看到还有2条记录，而主库是0
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select * from bbs_common_searchindex;
Empty set (0.00 sec)
mysql> select count(*) from bbs_common_searchindex; ---等待了一会之后，记录消失，同步恢复。
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.12.101
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 49058519
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 10539748
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 49058519
Relay_Log_Space: 49057429
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 14bfd13a-1920-11e4-ac07-000c296c30fd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> show variables like 'sql_slave_skip_counter'; --退出会话后，自动变回原来的0，因此不用修改。
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| sql_slave_skip_counter | 0 |
+------------------------+-------+
1 row in set (0.00 sec) [/size]
