Mysql master from inconsistent solution _ MySQL

Mysql master from inconsistent solution bitsCN.com

Solution to inconsistent mysql master

Method 1: ignore errors and synchronize

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 skipping a step. the following number can be set global SQL _slave_skip_counter = 1; start slave; then use mysql> show slave status/G to view: Slave_IO_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.

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

[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.000003 |     4563 |              | 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.

[root@server01 mysql]# scp mysql.bak.sql rep@192.168.1.2:/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.1.1', master_user = 'repl', master_port=3306, master_password='repldaba', master_log_file = 'mysqld-bin.000003',master_log_pos=4563;

8. enable synchronization again

mysql> start slave;

9. View synchronization status

Mysql> show slave status/G View: Slave_IO_Running: YesSlave_ SQL _Running: Yes

Now, synchronization is complete.

BitsCN.com