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

Click here to install mysql

MySQL Master and Slave introduction

MySQL master-slave is also called Replication, AB replication. To put it simply, after the two machines An and B are master and slave, they will write data on A, and the other B will also write data, and the two data will be synchronized in real time.

The master and slave of MySQL is based on binlog, and the master must turn on binlog in order to carry out the master and slave.

Specify the database synchronization operation:

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

Master side IP:10.10.10.1 slave side IP:10.10.10.2

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.cnfserver-id=1111 (different from slave library) log-bin=mysql-bin (open binlog) binlog-do-db= database name (record the binlog of the specified library, the record result is different according to the mode. ) binlog_format=mixed (set binlog mode) expire_logs_days = 7 (keep binlog for 7 days)

Restart the masterside mysql

Centos 6:/etc/init.d/mysql restartcentos 7:systemctl restart mysql

From the library my.cnf configuration file component parameters:

Vim / etc/my.cnfserver-id=2222 (ID of the slave library, as long as it does not conflict with the master library)

Restart the slave mysql

Centos 6:/etc/init.d/mysql restartcentos 7:systemctl restart 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-p 'database password'-e "grant replication slave on *. * to rep@'10.10.10.2' identified by 'rep@back123'; flush privileges;flush tables with read lock; show master status;"

Back up specified database data

Mysqldump-uroot-p 'database password'-B database name-- single-transaction-- events | gzip > / root/ database name _ $(date +% F). Sql.gz (export compressed sql file) mysqldump-uroot-p 'database password'-B database name-single-transaction-events > / root/ database name _ $(date +% F). Sql (export sql file)

Transfer data to slave slave machine

Scp-rp-P ssh service port sql file name root@ slave library IP:/root/

2. Operation on the server:

Log in to the slave library:

Gzip-d test.sql.gz; source / root/test.sql; stop slave;reset slave all;stop slave;CHANGE MASTER TOMASTER_HOST=' main library address', MASTER_PORT=3306,-> Port MASTER_USER=' New Database user', MASTER_PASSWORD=' New user's password', MASTER_LOG_FILE=' Log File name', MASTER_LOG_POS= Log Positioning value; start slave;show slave status\ G

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

Unlock tables

Complete the configuration here:

Create a test table test on master:

Synchronizing a single database can be configured on a master server or a slave server:

Master server: binlog-do-db= only synchronizes specified library binlog-ignore-db= ignores specified library slave server: replicate_do_db= synchronizes specified library replicate_ignore_db= ignores specified library replicate_do_table= synchronizes specified table replicate _ ignore_table= ignores specified table replicate _ wild_do_table= such as aming.%, supports wildcard replicate_wild_ignore_table=

Master-slave process

The master records the change operation in binlog

Synchronize the master binlog event (SQL statement) to the local machine from and record it in relaylog

Execute sequentially according to the SQL statements in relaylog

There are three threads in this process, and there is a log dump thread on the master, which is used to pass binlog; with the slave I / o thread, and there are two threads on the slave, in which the iUnip thread is used to synchronize the master binlog and generate relaylog, and the other SQL thread is used to land the SQL statement in the relaylog.

Application environment

Back up important data

Share the data reading pressure of the main database

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.