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

Environment description: the requirement is to create a master-slave replication environment, which requires the master side to synchronize only the drsj library in the master library to the slave side

Master side IP:192.168.1.100 slave side IP:192.168.1.200

Master-slave replication conditions:

1. Enable the binlog function of the main library

two。 Open the master-slave library server-id

3. Make sure that all server-id of the master-slave configuration file my.cnf are different

4. The main library establishes a synchronous account rep and authorizes replication

5. Lock the table flush tables with read lock; in the main library and view the binlog file and location points on the main library

Show master status\ G; record the binlog file and location points.

6. Open a new mysql window on the masterside to export the full set, and restore the backup data to the slave database.

7. Log in to the slave library, CHANGE MASTER TO.

Automatically generating the configuration file master.info from the library

Turn on the copy switch start slave;show slave status\ G

8. Unlock, open user write function

Operation procedure:

Main library my.cnf configuration file component parameters:

Vi / etc/my.cnfbinlog-format = MIXEDserver-id=107log-bin=mysql-binbinlog-do-db=drsjlog-slave-updates=1

Restart the masterside mysql

From the library my.cnf configuration file component parameters:

Vim / etc/my.cnfserver-id=109

Restart the slave mysql

1. Operation on master:

The main library establishes a synchronous account rep and authorizes replication; to lock the table flush tables with read lock; in the main library and view the binlog files and location points on the main library

Show master status\ G; record the binlog file and location points.

Mysql-uroot-paired Xpaired Xwe3Gf0x'-e "grant replication slave on *. * to rep@'192.168.1.200' identified by 'JuwoSweTbUser'; flush privileges;flush tables with read lock; show master status;"

Back up specified database data

Mysqldump-uroot-paired Xpacks Xwe3Gf0x'-B drsj-- single-transaction-- events | gzip > / root/drsj_$ (date +% F) .sql.gz

Transfer data to slave slave machine

Scp-rp-P 52110 drsj.gz root@ slave library IP:/root/

2. Operation on the server:

Login slave library: gzip-d drsj.sql.gz; source / root/drsj.sql

Stop slave;reset slave all;stop slave;CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_PORT=3306, MASTER_USER='rep11', MASTER_PASSWORD='JuwoSweTbUser', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=345; start slave; show slave status\ G

three。 Finally, perform the unlock operation on master: unlock tables

The configuration is complete here. Create a test table test on master

Tip: synchronize all information about the drsj library, including the user who created the administrative drsj library on the main library. For example, create a test account on the master library to manage the drsj library, and the test user will synchronize to the slave slave library.

Tip:

Binlog-do-db=drsj

When log-slave-updates=1 synchronizes only one library to the slave slave library alone, these two parameters are also applicable to MySQL5.6.20, which I have personally tested.

Reference: https://www.cnblogs.com/rwxwsblog/p/4542417.html

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.