Home Database Mysql Tutorial 主从(master/slave)默认引擎不一致导致复制失败解决_MySQL

主从(master/slave)默认引擎不一致导致复制失败解决_MySQL

Jun 01, 2016 pm 01:33 PM
error master server

bitsCN.com

主从(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,在维护窗口再重启生效

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Detailed explanation of CentOS installation fuse and CentOS installation server Detailed explanation of CentOS installation fuse and CentOS installation server Feb 13, 2024 pm 08:40 PM

Detailed explanation of CentOS installation fuse and CentOS installation server

How to solve the problem that eMule search cannot connect to the server How to solve the problem that eMule search cannot connect to the server Jan 25, 2024 pm 02:45 PM

How to solve the problem that eMule search cannot connect to the server

Solution to the inability to connect to the RPC server and the inability to enter the desktop Solution to the inability to connect to the RPC server and the inability to enter the desktop Feb 18, 2024 am 10:34 AM

Solution to the inability to connect to the RPC server and the inability to enter the desktop

Best Practice Guide for Building IP Proxy Servers with PHP Best Practice Guide for Building IP Proxy Servers with PHP Mar 11, 2024 am 08:36 AM

Best Practice Guide for Building IP Proxy Servers with PHP

How to enable TFTP server How to enable TFTP server Oct 18, 2023 am 10:18 AM

How to enable TFTP server

How to configure Dnsmasq as a DHCP relay server How to configure Dnsmasq as a DHCP relay server Mar 21, 2024 am 08:50 AM

How to configure Dnsmasq as a DHCP relay server

What should I do if I can't enter the game when the epic server is offline? Solution to why Epic cannot enter the game offline What should I do if I can't enter the game when the epic server is offline? Solution to why Epic cannot enter the game offline Mar 13, 2024 pm 04:40 PM

What should I do if I can't enter the game when the epic server is offline? Solution to why Epic cannot enter the game offline

How to install PHP FFmpeg extension on server? How to install PHP FFmpeg extension on server? Mar 28, 2024 pm 02:39 PM

How to install PHP FFmpeg extension on server?

See all articles