主從(master/slave)默認引擎不一致導致復制失敗解決 同事告知，一台slave服務器復制中斷，查看slave服務器,如下錯誤 Error 'Specified key was too long; max keylength is 1000 bytes' on query mysql> show slave status \G; *************************** 1. row*************************** Slave_IO_State: Waiting formaster to send event Master_Host: 192.xxx.xxx.146 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.004369 Read_Master_Log_Pos: 717957570 Relay_Log_File: relay-bin.013649 Relay_Log_Pos: 571890895 Relay_Master_Log_File: mysql-bin.004368 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1071 Last_Error: Error 'Specified key was too long; max key length is 1000 bytes' onquery. Default database: 'analyse_xxx'. Query: 'CREATE TABLE`meta_topic_scan` ( `domain` varchar(200) NOT NULL COMMENT '' , `topic_name`varchar(200) NOT NULL COMMENT '' , `topic_url` varchar(200) NOT NULL COMMENT '', `topic_pv` int NULL DEFAULT 0 COMMENT '' , `topic_uv` int NULL DEFAULT 0COMMENT '' , `pv` int NULL DEFAULT 0 COMMENT '' , `uv` int NULL DEFAULT 0COMMENT '' , `shopcart_pv` int NULL DEFAULT 0 COMMENT '' Skip_Counter: 0 Exec_Master_Log_Pos: 571890750 Relay_Log_Space: 1802530754 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: 1071 Last_SQL_Error: Error 'Specified keywas too long; max key length is 1000 bytes' on query. Default database:'analyse_product'. Query: 'CREATE TABLE `meta_topic_scan` ( `domain` varchar(200)NOT NULL COMMENT '' , `topic_name` varchar(200) NOT NULL COMMENT '' ,`topic_url` varchar(200) NOT NULL COMMENT '' , `topic_pv` int NULL DEFAULT 0COMMENT '' , `topic_uv` int NULL DEFAULT 0 COMMENT '' , `pv` int NULL DEFAULT 0COMMENT '' , `uv` int NULL DEFAULT 0 COMMENT '' , `shopcart_pv` int NULL DEFAULT0 1 row in set (0.00 sec) ERROR: No query specified 創建表竟然失敗,索引長度超過了最大值1000，看看報錯日志 [root@db~]# tail -n 500 /my/data/mysql/dbxxxxxx.err 130606 10:57:20 [ERROR] Slave SQL: Error'Specified key was too long; max key length is 1000 bytes' on query. Defaultdatabase: 'analyse_xxx'. Query: 'CREATE TABLE `meta_topic_scan` ( `domain` varchar(200)NOT NULL COMMENT '' , `topic_name` varchar(200) NOT NULL COMMENT '' ,`topic_url` varchar(200) NOT NULL COMMENT '' , `topic_pv` int NULL DEFAULT 0 COMMENT '' , `topic_uv` intNULL DEFAULT 0 COMMENT '' , `pv` int NULL DEFAULT 0 COMMENT '' , `uv` int NULLDEFAULT 0 COMMENT '' , `shopcart_pv` int NULL DEFAULT 0 130606 10:57:20 [Warning] Slave: Specifiedkey was too long; max key length is 1000 bytes Error_code: 1071 130606 10:57:20 [ERROR] Error runningquery, slave SQL thread aborted. Fix the problem, and restart the slave SQLthread with "SLAVE START". We stopped at log 'mysql-bin.004368' position571890750 日志顯示在上午10點57分建表失敗，很明顯的錯誤，索引長度超過了1000字節，然後告知同事，創建表索引長度超過了1000字節，導致slave io_thread中斷。然後同事很奇怪的說，上午master上面創建表沒問題，順利的創建，為什麼在slave上復制報錯？ 後來通過在slave上手工建表發現master和slave 默認存儲引擎不一致，不一致會引起中斷？是的，建表語句是否明確指定使用那種存儲引擎如下語句 CREATE TABLE `meta_topic_scan` ( `domain` varchar(200) NOT NULL , `topic_name` varchar(200) NOT NULL, `topic_url` varchar(200) NOT NULL , `topic_pv` int(11) DEFAULT '0', `topic_uv` int(11) DEFAULT '0', PRIMARY KEY (`domain`,`topic_name`,`topic_url`) ); 如果在建表時不指定存儲引擎，在master上默認引擎是innodb，創建的表就是innodb類型，寫到binlog日志裡的語句是什麼格式呢？ 我們利用mysqlbinlog去查找這條語句，如下格式去分析 [root@aeolus1 ~]# mysqlbinlog -uroot--start-position=1914 /usr/local/mysql/data/mysql-bin.000014 發現建表語句以這樣的格式寫入binlog二進制日志中 CREATE TABLE `meta_topic_scan` ( `domain` varchar(200) NOT NULL comment '', `topic_name` varchar(200) NOT NULL comment '', `topic_url` varchar(200) NOT NULL comment '', `topic_pv` int(11) DEFAULT '0' comment '', `topic_uv` int(11) DEFAULT '0' comment '', PRIMARY KEY (`domain`,`topic_name`,`topic_url`) ); 這樣io_thread把binlog從master服務器傳送到slave，sql_thread在slave上執行此語句，由此可知slave默認是什麼引擎，就創建默認引擎的表。由於slave默認是mysiam，索引在innodb和mysiam兩個引擎上索引長度不一樣，導致主從復制中斷。 修改my.cnf，改默認引擎innodb，重啟服務(業務不是很重要，允許重啟)，ok。如果不允許重啟，那就在slave上創建表，然後跳過出錯的event，在維護窗口再重啟生效
