What should I do if two mysql masters and slaves are synchronized?-Mysql Tutorial-php.cn

What should I do if two mysql masters and slaves are synchronized?

(*-*)浩

Release： 2020-09-15 17:38:20

Original

3466 people have browsed it

Mysql has two masters and slaves, which means master server and slave server. The structure has one master and one slave: one Master and one Slave. One master and multiple slaves: one Master and multiple slaves.

Recommended course: MySQL tutorial

What should I do if two mysql masters and slaves are synchronized?

##Master-slave synchronization solution

First go to the Master library:

mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。 
show 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)

Copy after login

Then check on the Slave

mysql> show slave status\G 
Slave_IO_Running: Yes 
Slave_SQL_Running: No

Copy after login

It can be seen that the Slave is out of sync

Here are two solutions:

Method 1: Ignore the error and continue synchronization

This method is suitable for situations where the data in the master-slave database are not very different, or the data does not need to be completely unified, and the data requirements are not Strict situation

Solution:

stop slave; 
#表示跳过一步错误，后面的数字可变 
set global sql_slave_skip_counter =1; 
start slave; 
之后再用mysql> show slave status\G 查看： 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
ok，现在主从同步状态正常了。。。

Copy after login

Method 2: Re-do master-slave, complete synchronization

This method is suitable for master-slave database data The difference is large, or the data is required to be completely unified

The solution steps are as follows:

1. First enter the main database and lock the table to prevent data from being written

Use the command :

mysql> flush tables with read lock;

Copy after login

Note: This area is locked in a read-only state, and the statements are not case-sensitive

2. Back up the data

#Back up the data to mysql.bak. sql file

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

Copy after login

Note here: database backup must be done regularly. You can use shell script or python script, which are more convenient to ensure that the data is foolproof

3. Check the 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)

Copy after login

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

#Use the scp command

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

Copy after login

5. Stop the slave state

mysql> stop slave;

Copy after login

6 .Then execute the mysql command in the slave library and import the data backup

mysql> source /tmp/mysql.bak.sql

Copy after login

7. Set up the synchronization from the slave library. Pay attention to the synchronization point there, which is the two items | File | Position in the show master status information of the master library

change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

Copy after login

8. Restart slave synchronization

mysql> stop slave;

Copy after login

9. Check synchronization status

mysql> show slave status\G 查看; 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

Copy after login

The above is the detailed content of What should I do if two mysql masters and slaves are synchronized?. For more information, please follow other related articles on the PHP Chinese website!