Mysql Master/Slave databases are not synchronized

Mysql Master/Slave databases are not synchronized today

First go to the Master database:

Mysql> show processlist; check whether there are too many Sleep processes. It is normal.

Show master status; also normal.

Mysql> show master status;

+ ------------------- + ---------- + -------------- + ----------------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------- + ---------- + -------------- + ----------------------------- +

| Mysqld-bin.000001 | 3260 | mysql, test, information_schema |

+ ------------------- + ---------- + -------------- + ----------------------------- +

1 row in set (0.00 sec)

Go to Slave to view

Mysql> show slave statusG

Slave_IO_Running: Yes

Slave_ SQL _Running: No

Obviously, Slave is not synchronized.

The following describes two solutions:

Method 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.

Solution:

Stop slave;

# Indicates that the error is skipped. The subsequent number is variable.

Set global SQL _slave_skip_counter = 1;

Start slave;

Then use mysql> show slave statusG to view the information:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

OK. Now the master-slave synchronization status is normal...

Method 2: Re-master the slave, full synchronization

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

The solution is as follows:

1. First enter the master database and lock the table to prevent data writing.

Run the following command:

Mysql> flush tables with read lock;

Note: The lock is read-only and the statements are case insensitive.

2. Back up data

# Back up data to the mysql. bak. SQL File

[Root @ server01 mysql] # mysqldump-uroot-p-hlocalhost> mysql. bak. SQL

Note: Database Backup must be performed on a regular basis. You can use shell scripts or python scripts to make sure that the data is safe.

3. View master Status

Mysql> show master status;

+ ------------------- + ---------- + -------------- + ----------------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------- + ---------- + -------------- + ----------------------------- +

| Mysqld-bin.000001 | 3260 | mysql, test, information_schema |

+ ------------------- + ---------- + -------------- + ----------------------------- +

1 row in set (0.00 sec)

4. Upload the mysql backup file to the slave database machine for data recovery.

# Use the scp command

[Root @ server01 mysql] # scp mysql. bak. SQL root@192.168.128.101:/tmp/

5. Stop slave database status

Mysql> stop slave;

6. Run the mysql command in the slave database to import the data backup.

Mysql> source/tmp/mysql. bak. SQL

7. Set slave Database Synchronization. Note that the synchronization point here is | File | Position in the show master status information of the master database.

Change master to master_host = '192. 168.128.100 ', master_user = 'rsync', master_port = 192, master_password = '', master_log_file = 'mysqld-bin.000001', master_log_pos = 3306;

8. enable synchronization again

Mysql> start slave;

9. View synchronization status

View mysql> show slave statusG:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Now, synchronization is complete.