Home >Database >Mysql Tutorial >What should I do if two mysql masters and slaves are synchronized?
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
##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)Then check on the Slave
mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: NoIt 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,现在主从同步状态正常了。。。
Method 2: Re-do master-slave, complete synchronization
mysql> flush tables with read lock;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.sqlNote 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)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/5. Stop the slave state
mysql> stop slave;6 .Then execute the mysql command in the slave library and import the data backup
mysql> source /tmp/mysql.bak.sql7. 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;8. Restart slave synchronization
mysql> stop slave;9. Check synchronization status
mysql> show slave status\G 查看; Slave_IO_Running: Yes Slave_SQL_Running: Yes
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!