MySQL master-slave problem how to diagnose fault points how to recover data

1reasons for Master-slave problems

generally lead to master-slave problems are generally the following: a main library from the library too much, from the library hardware than the main library, slow SQL statement too many master-slave replication single-threaded, main library write concurrency too large to transfer to from the library. Network latency between master and slave libraries. Replication delays due to machine configuration issues, including disk IO,CPU, memory and other factors

2Master-Slave issues

The master-slave problem is many, the error code is not the same, can be executed from the library show slave status\g see if the master-slave synchronization, if the SQL and lo thread state is not Yes, Indicates that there is a problem with master-slave synchronization.

Instance 1-1 Writing data conflicts from a library

For example:show slave status\g View master-Slave synchronization, the following error code appears:

Last_sql_error:error ' Can ' t createdatabase ' Mao '; Database exists ' on query. Default database: ' Mao '. Query: ' Create Database Mao '

In general, there are code hints for errors, as can be seen because a database created from the library with the same name as the main library creates a conflict.

Instance 1-2 Server-id same

In addition, it is often the same as the master and slave server-id configuration, causing the IO thread from the library to be in a connected state (connecting), which has not been able to convert yes.

if the Server-id conflict, the primary server will kill the IO thread that is connected from the server first , and then the IO with the same server-id in the back connection The thread overwrites the IO thread that previously had the same Server-id.

You can generally modify the Server-id from the library's configuration file , unlike themain library.

Instance 1-3 uuid same conflict

Configuration MySQL master and slave, because it is a copy of the MySQL directory, resulting in the same master- slave mysql uuid, slave_io can not start, the error message is as follows:

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

Workaround : Modify The value of the UUID in the auto.cnf file in the Mysqldata directory , make the two MySQL different, modify and restart MySQL service.

3Other complementary Workarounds

Method One: If the data requirements are not strict, you can ignore the error, continue to synchronize, the method is suitable for the master-slave library data is not small, or require the data can not be completely unified situation.

Stop slave;

# indicates a skip step error, followed by a variable number

Set global sql_slave_skip_counter = 1;

Start slave;

Method Two:

It is configured in the configuration file according to the error number that can be ignored, skipping the specified error that does not affect the business data.

[[email protected] logs]# grep slave-skip/etc/my.cnf

Slave-skip-errors = 1032,1062,1007

Method Three: Master-slave database data is relatively large, or require the data to be fully unified situation can be re-decision from, fully synchronized.

MySQL master-slave problem how to diagnose fault points how to recover data