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

The following mainly brings you two solutions of MySQL database master-slave synchronization. I hope these contents can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

Method 1: ignore the error and continue to synchronize

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.

Mysql > stop slave; # close the slave library

Mysql > set global sql_slave_skip_counter = 1; # means skip one step error, the following number is variable

Mysql > start slave; # enable slave library

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.

Repair master-slave library in hot standby mode

Operation process

1. Close the slave database first

2. Record the log_file file name and location point of the main library

3. Export the database of the master database and copy it to the slave machine

4. Delete the old library from the library and import the new library copied from the main library.

5. Log_file and location points of changes main library

6. Open the slave library

Tip: the passwords of master and slave libraries are the same. Please follow the steps from top to bottom step by step.

Main library machine: 192.168.1.1

Slave machine: 192.168.1.2

Operation from the library

1. Disable synchronization from the slave library

Mysql > stop slave

Main library operation

1. Record the log_file file name and location point of the main library

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysqld-bin.000001 | 3260 | | mysql,test,information_schema |

+-+

2. Export the database of the master library, copy it to the slave machine, delete the old library from the library, and import the new library copied from the master library.

# mysqldump-uroot-p123456-hlocalhost-B-C mysqldb-- single-transaction > / opt/mysqldb .sql &

3. The master database machine copies the database to the slave database machine

# scp / opt/mysqldb .sql root@192.168.1.2:/opt/

Operation from the library

1. Delete the old library

Mysql > drop database mysqldb

2. Log file name and log point of change master

# mysqldump-uroot-p123456 mysqldb

Change master to

Master_host='192.168.1.1'

Master_user='root'

Master_port='3306'

Master_password='123456'

Master_log_file='mysqld-bin.000001'

Master_log_pos=3260

3. Open the slave library

Mysql > start slave

Synchronous completion

For the above two solutions about the master-slave synchronization of MySQL database, do you find it very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.