Shulou(Shulou.com)06/01 Report--

The following brings you about the solution to mysql two master-slave asynchronous method, interested in the words to take a look at this article, I believe that after reading the solution to mysql two master-slave asynchronous method to everyone how much help.

master-slave asynchronous solution

First up Master Library:

mysql>show processlist; see if the next process sleeps too much. It was 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)

Check out Slave.

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

Slave is out of sync.

Here are two solutions:

Method 1: Ignore errors and continue synchronization

This method is suitable for the situation that the data difference between master and slave database is not big, or the data requirement can not be completely unified, and the data requirement is not strict.

Solution:

stop slave;#indicates skipping a step error, the following number can be changed to set global sql_slave_skip_counter =1; start slave; then use mysql> show slave status\G to view: Slave_IO_Running: Yes Slave_SQL_Running: Yes ok, now the master-slave synchronization status is normal..

Method 2: Re-do master-slave, completely synchronized

This method is suitable for the situation that the data of master and slave databases are quite different or the data are completely unified.

The solution steps are as follows:

1. First enter the main library, lock the table to prevent data writing

Use the command:

mysql> flush tables with read lock;

Note: This is the lock is read-only state, the statement is case-insensitive

2. data backup

#Backup data to mysql.bak.sql file

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

Note here: Database backup must be carried out regularly, you can use shell script or python script, are more convenient, to ensure that the data is foolproof

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. Transfer mysql backup files to slave machine for data recovery

#Use scp command

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

5. Stop from library status

mysql> stop slave;

6. Then execute mysql command from the library to import data backup

mysql> source /tmp/mysql.bak.sql

7. Set synchronization from the slave library. Note the synchronization point there, which is in the show master status message of the master library.| File| Position two terms

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 synchronization

mysql> stop slave;

9. view sync status

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

Read the above on the solution to mysql two master-slave asynchronous method details, whether there is any harvest. If you want to know more about it, you can continue to pay attention to our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.