How to Avoid synchronization of the MySQL database master

How to Avoid synchronization of the MySQL database master

Fault description:

Go to the slave Database

Mysql> show slave status \ G;

The result is as follows:

...

Slave_IO_Running: Yes

Slave_ SQL _Running: No

Last_errno.: 1062

...

Cause:

1. The program may write on slave.

2. It may be caused by transaction rollback after the slave machine is restarted.

Most of them are caused by the second type.

Solution 1: Ignore errors and continue Synchronization

This method is applicable when the data in the Master/Slave database is not much different or the data requirements are not strict.

Mysql> slave stop;
Mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;
Mysql> slave start;

Solution 2: Force synchronization from a certain point

This method may cause some non-synchronous data loss, and the deletion record synchronization on the master server will also have some error messages, without affecting the use

Check the host status on the master server:
Record the value corresponding to File and Position.
Mysql> show master status;
+ ------------------ + ----------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ----------- + -------------- + ------------------ +
| Mysql-bin.000021 | 135617782 |
+ ------------------ + ----------- + -------------- + ------------------ +
1 row in set (0.00 sec)

3. Execute manual synchronization on the slave server:
Mysql> change master
> Master_host = 'master _ ip ',
> Master_user = 'user ',
> Master_password = 'pwd ',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.20.21 ',
> Master_log_pos = 135617782;
1 row in set (0.00 sec)
Mysql> slave start;
1 row in set (0.00 sec)
View the slave status again and find:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
...
Seconds_Behind_Master: 0

Solution 3: Re-master the master node and complete synchronization

This method is applicable when the data in the Master/Slave database is significantly different or the data must be completely unified.

1. Enter the master database for Data Backup

Mysqldump-u root-p -- opt -- master-data -- single-transaction-B> mysql. bak. SQL

2. Upload the mysql backup file to the slave database and import it to the slave database.

Mysql> stop slave;

Mysql-u root-p <mysql. bak. SQL

3. Find file and position in the backup file.

Grep-I "change master to" mysql. bak. SQL will get the following similar content:

Change master to MASTER_LOG_FILE = 'mysql-bin.000021 ', MASTER_LOG_POS = 135617782;

4. Set slave Database Synchronization

Mysql> change master
> Master_host = 'master _ ip ',
> Master_user = 'user ',
> Master_password = 'pwd ',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.20.21 ',
> Master_log_pos = 135617782;

Mysql> slave start;
1 row in set (0.00 sec)
View the slave status again and find:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
...
Seconds_Behind_Master: 0

This article permanently updates the link address: