Two Solutions of Master-Slave synchronization in MySQL Database
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
< /opt/mysqldb .sql & mysql>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.
