Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to diagnose the fault point and recover the data after the mysql master-slave problem arises. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great reference significance to everyone. The following is to understand with the author how to diagnose the fault point and recover the data after the mysql master-slave problem occurs.

1 cause of master-slave problem

Generally speaking, the following factors lead to the master-slave problem: there are too many slave libraries in a master library, the hardware of the slave library is worse than that of the master library, too many slow SQL statements, too many master-slave replication single threads, and too much write concurrency of the master library to transfer to the slave library. Network delay between master and slave libraries. Replication delays are caused by machine configuration problems, including disk IO,CPU, memory and other factors.

2 master-slave problem

There are many master-slave problems, and the error code is also different. You can execute show slave status\ G on the slave library to see if the master-slave synchronization has occurred. If the sql and lo thread status is not yes, there is a problem with the master-slave synchronization.

Example 1-1 writes data conflicts from the library

For example: show slave status\ G View Master-Slave synchronization, the following error code will appear:

Last_SQL_Error: Error 'Can't createdatabase' mao'; database exists' on query. Default database: 'mao'. Query:'create database mao'

Generally, there will be a code hint when an error occurs. As you can see above, it is because the slave database has created a database with the same name as the master database, resulting in a conflict.

Instance 1-2 server-id is the same

In addition, there are often master and slave server-id configurations with the same configuration, resulting in the slave library IO thread has been in the state of connecting (connecting), has not been able to transform the yes.

If there is a server-id conflict, the master CVM will kill the IO thread connected to the slave server first, and the IO thread with the same server-id will overwrite the IO thread with the same server-id.

Generally, you can modify the server-id in the configuration file of the slave library, which is different from the master library.

Instance 1-3 uuid same conflict

When configuring MySQL master / slave, the mysql uuid of master / slave is the same as the copied mysql directory, so Slave_IO cannot be started. The error message is as follows:

The slave I/O thread stops becausemaster and slave have equal MySQL server UUIDs; these UUIDs must be differentfor replication to work.

Solution: modify the value of uuid in the auto.cnf file under the mysqldata directory to make the two mysql different, and restart the mysql service after modification.

3 other supplementary solutions

Method 1: if the data requirements are not strict, you can ignore the error and continue to synchronize; this method is suitable for the situation where there is little difference between the master and slave database data, or the data can not be completely unified.

Stop slave

# means to skip a step error, and the following number is variable

Set global sql_slave_skip_counter = 1

Start slave

Method 2:

The error number that can be ignored is configured in the configuration file in advance, and the specified error that does not affect the business data is skipped.

[root@DB01 logs] # grep slave-skip/etc/my.cnf

Slave-skip-errors = 1032, 1062, 1007.

Method 3: there is a big difference between master and slave database data, or if the data is required to be completely unified, you can re-do master and slave and completely synchronize.

After reading the above about how to diagnose fault points and recover data after mysql master-slave problems, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.