Solution to inconsistent mysql master

Solution 1: Ignore errors. This method is applicable to situations where the data in the master and slave databases is not significantly different, or the data may be not completely unified. The solution should be implemented when the data requirements are not strict:

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 database. full synchronization is applicable to situations where the data in the Master/Slave database is significantly different or the data must be completely unified. first, go to the master database and lock the table to prevent data writing. Use the following command:

mysql> flush tables with read lock;

 

Note: The lock is read-only and the statement is 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. Check the 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.