MySQL master-Slave synchronization configuration for database backup

As the main backup means of the database, master-slave synchronization can realize the asynchronous synchronization of data from the main library (that is, the currently used business database) to the slave library (Backup library), when the main library database or host computer fails to start, you can switch to the library to realize the rapid recovery of the business system.

First introduce my environment, I have an already used MySQL database A, and then I installed a MySQL database B as a from the library.

First, master library a settings

Modify the MySQL configuration first

VIM/ETC/MY.CNF Insert the following 2 lines server-id=1 #这个ID是唯一的, not with the other main library or the same log-bin=mysql-bin from the library #这个一定得设置, otherwise there is no log, from the database will be error, And it's very important that you can't sync from the library to the data.

Need to restart MySQL database for configuration to take effect after setup.

mysqld restart

Now we also need a MySQL user that can be used to synchronize, log in database A to add a Mysqlsync user

Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' password '; flush privileges;

Since the main library is already running the data, all need to lock the library not to write data to the database, log in to database A, execute:

Flush tables with read lock;

In the case of not exiting MySQL, open another session to back up the current database:

Mysqldump-uuser-ppassword-a-B--events >/root/back.sql

Where-a represents all databases,-B represents the specified library, and--events represents the export event.

Record the parameters of the master library, etc., when you set the slave library

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/79/82/wKiom1aTJuCADVHtAAAj--dviB4473.png "style=" float: none; "title=" QQ picture 20160111114908.png "alt=" Wkiom1atjucadvhtaaaj--dvib4473.png "/>

Master Library is basically ready, remember the last in the slave library after the need to unlock the master library, not to write.

Second, slave library settings

First modify the MySQL configuration file my.cnf

VIM/ETC/MY.CNF Insert the following 1 lines server-id=2 #这个ID是唯一的, cannot be #log-bin=mysql-bin with the other main library or the same as from the library #这个不一定得设置, if this slave library needs to cascade, and must write a #log-slave-update

Restart MySQL

mysqld restart

Log in to mysqld database, execute

change master to master_host= ' 1.1.1.1 ',      #master库的IP地址                     master_ port= ' 3306 '            #master库的端口 If you have to modify                                   master_user= ' Mysqlsync ',                                                        master_password= ' Password ' ,                  master_ Log_file= ' mysql-bin.000002 ',     #按刚才记录的填写                                    master_log_pos=2450906;         #按刚才记录的填写

Start slave;show slave status\g;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/79/82/wKiom1aTMsnwjPD0AAUuR2MO4rw925.jpg "title=" Qq20160111124218.jpg "alt=" Wkiom1atmsnwjpd0aauur2mo4rw925.jpg "/>

Finally unlock the database on the master library (you can actually unlock it after change master)

Unlock tables;

It's done! Try to build a database on Master and show it in slave!

This is the simplest master-slave synchronization configuration, in fact, in the MY.CNF can also be configured to let slave only synchronize a certain database or a table and so on settings.

This article is from the "Technology" blog, be sure to keep this source http://shevastar.blog.51cto.com/309735/1733708

MySQL master-Slave synchronization configuration for database backup