主从(master/slave)默认引擎不一致导致复制失败解决_MySQL
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
Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Solution: 1. Check the eMule settings to make sure you have entered the correct server address and port number; 2. Check the network connection, make sure the computer is connected to the Internet, and reset the router; 3. Check whether the server is online. If your settings are If there is no problem with the network connection, you need to check whether the server is online; 4. Update the eMule version, visit the eMule official website, and download the latest version of the eMule software; 5. Seek help.

What should I do if the RPC server is unavailable and cannot be accessed on the desktop? In recent years, computers and the Internet have penetrated into every corner of our lives. As a technology for centralized computing and resource sharing, Remote Procedure Call (RPC) plays a vital role in network communication. However, sometimes we may encounter a situation where the RPC server is unavailable, resulting in the inability to enter the desktop. This article will describe some of the possible causes of this problem and provide solutions. First, we need to understand why the RPC server is unavailable. RPC server is a

As a LINUX user, we often need to install various software and servers on CentOS. This article will introduce in detail how to install fuse and set up a server on CentOS to help you complete the related operations smoothly. CentOS installation fuseFuse is a user space file system framework that allows unprivileged users to access and operate the file system through a customized file system. Installing fuse on CentOS is very simple, just follow the following steps: 1. Open the terminal and Log in as root user. 2. Use the following command to install the fuse package: ```yuminstallfuse3. Confirm the prompts during the installation process and enter `y` to continue. 4. Installation completed

The role of a DHCP relay is to forward received DHCP packets to another DHCP server on the network, even if the two servers are on different subnets. By using a DHCP relay, you can deploy a centralized DHCP server in the network center and use it to dynamically assign IP addresses to all network subnets/VLANs. Dnsmasq is a commonly used DNS and DHCP protocol server that can be configured as a DHCP relay server to help manage dynamic host configurations in the network. In this article, we will show you how to configure dnsmasq as a DHCP relay server. Content Topics: Network Topology Configuring Static IP Addresses on a DHCP Relay D on a Centralized DHCP Server

In network data transmission, IP proxy servers play an important role, helping users hide their real IP addresses, protect privacy, and improve access speeds. In this article, we will introduce the best practice guide on how to build an IP proxy server with PHP and provide specific code examples. What is an IP proxy server? An IP proxy server is an intermediate server located between the user and the target server. It acts as a transfer station between the user and the target server, forwarding the user's requests and responses. By using an IP proxy server

Methods to view server status include command line tools, graphical interface tools, monitoring tools, log files, and remote management tools. Detailed introduction: 1. Use command line tools. On Linux or Unix servers, you can use command line tools to view the status of the server; 2. Use graphical interface tools. For server operating systems with graphical interfaces, you can use the graphics provided by the system. Use interface tools to view server status; 3. Use monitoring tools. You can use special monitoring tools to monitor server status in real time, etc.

The steps to start the TFTP server include selecting the TFTP server software, downloading and installing the software, configuring the TFTP server, and starting and testing the server. Detailed introduction: 1. When choosing TFTP server software, you first need to choose the TFTP server software that suits your needs. Currently, there are many TFTP server software to choose from, such as Tftpd32, PumpKIN, tftp-hpa, etc., which all provide simple and easy-to-use functions. interface and configuration options; 2. Download and install TFTP server software, etc.

What should I do if I can’t enter the game when the epic server is offline? This problem must have been encountered by many friends. When this prompt appears, the genuine game cannot be started. This problem is usually caused by interference from the network and security software. So how should it be solved? The editor of this issue will explain I would like to share the solution with you, I hope today’s software tutorial can help you solve the problem. What to do if the epic server cannot enter the game when it is offline: 1. It may be interfered by security software. Close the game platform and security software and then restart. 2. The second is that the network fluctuates too much. Try restarting the router to see if it works. If the conditions are OK, you can try to use the 5g mobile network to operate. 3. Then there may be more
