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

This article is mainly about how to solve the master-slave synchronization of mysql database. If you are interested, let's take a look at this article. I believe that after reading how to solve the master-slave synchronization of mysql database, it is of some reference value to everyone.

Today, it was found that the master-slave database of Mysql was not synchronized.

Go to the Master library first:

Mysql > show processlist; to see if the process has too many Sleep. It turns out it's normal.

Show master status; is 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 it on Slave again.

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: No

It can be seen that the Slave is out of sync

Here are two solutions:

Method 1: continue to synchronize after ignoring the error

This method is suitable for situations where there is little difference between master and slave database data, or when the data is not completely unified, and the data requirements are not strict.

Resolve:

Stop slave

# means to skip a step error, and the following number is variable

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-master and slave, complete synchronization

This method is suitable for situations where there is a large difference between master and slave database data, or when the data is required to be completely unified.

The resolution steps are as follows:

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

Use the command:

Mysql > flush tables with read lock

Note: this is where the lock is read-only and the statement is case-insensitive.

two。 Make a data backup

# backup data to mysql.bak.sql file

[root@server01 mysql] # mysqldump-uroot-p123456-A > mysql.bak.sql

One thing to note here: database backup must be carried out regularly. You can use shell script or python script, both of which are 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 the mysql backup files 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 state of the slave library

Mysql > stop slave

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

Mysql > source / tmp/mysql.bak.sql

7. Set the slave database synchronization. Pay attention to the synchronization point there, that is, the | File | Position items in the show master status information of the master database.

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

8. Re-enable slave synchronization

Mysql > start slave

9. View synchronization status

Mysql > show slave status\ G View:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

10. Unlock the lock table of the main database

Mysql > unlock tables

All right, synchronization is complete.

# Master-slave synchronization &

Configure 171for master database cloud server

# vim / etc/my.cnf

[mysqld]

Log-bin=master171

Server_id=171

: wq

# service mysql restart

# mysql-uroot-p123

Mysql > grant replication slave on *. * to slaveuser@ "172.40.50.107" identified by "123456"

Mysql > show master status\ G

* * 1. Row *

File: master171.000001

Position: 335

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

Configure slave database cloud server 107

Mysql-h272.40.50.171-uslaveuser-p123456

Mysql > quit

Vim / etc/my.cnf

[mysqld]

Server_id=107

Log-bin=jing # optional

: wq

Service mysql restart

Mysql-uroot-p999

Mysql > show slave status

Empty set (0.00 sec)

Mysql > change master to

Master_host= "172.40.50.171"

Master_user= "slaveuser"

Master_password= "123456"

Master_log_file= "master171.000001"

Master_log_pos=335

Mysql > start slave

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The above details about how to solve the master-slave non-synchronization of mysql database are helpful to you? If you want to know more about it, you can continue to follow 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.