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

Replication is to transfer the DDL and DML operations of the master database to the slave database through the binary log, and then redo it from the slave database, so as to keep the data synchronized between the slave database and the master database. MySQL can be copied from one master library to multiple slave libraries at the same time, and the slave library can also be used as the master library of other slave libraries to achieve chain replication.

Advantages of MySQL replication:

If the master database fails, you can quickly switch to the slave library to provide services; perform query operations in the slave database to reduce the access pressure of the master database; perform backups in the slave database to avoid the impact on the master database during the backup

Principle of MySQL replication

1. When a transaction is committed, the MySQL main library records the data changes in the Binlog as an event Events, and the sync_binlog parameter on the main library controls the Binlog log to be flushed to disk.

2. The master library pushes the events in the Binlog to the Relay Log of the slave library, and then redoes the slave library according to the Relay Log. The data of the master and slave database is consistent through logical replication.

MySQL completes data replication between master and slave libraries through three threads: the Binlog Dump thread runs on the master database, and the I / O thread and SQL thread run on the slave database. When starting replication (Start Slave) from the slave library, first create the Binlog Dump O thread to connect to the master database, then create the SQL thread to read the database events and send them to the I / O thread, then update the event data to the Relay Log of the slave database after obtaining the event data, and then read the updated database events from the SQL thread on the library and apply

As shown in the following figure:

View the main library:

Mysql > show processlist\ G * * 1. Row * * Id: 3 User: root Host: 10.24.33.187 User 54194 db: NULL Command: Sleep Time: 176 State: Info: NULL * * * 2. Row * * Id: 4 User: root Host: 10.24.33.187 User 54195 db: NULL Command: Sleep Time: 176 State: Info: NULL * * 3. Row * * Id: 8 User: root Host: localhost db: test Command: Query Time: 0 State: starting Info: show processlist * * 4. Row * * Id: 12 User: repl Host: dsz884.hcg.homecredit.net:39731 Db: NULL Command: Binlog Dump-- Binlog Dump thread Time: 87 State: Master has sent all binlog to slave Waiting for more updates-thus, synchronizing Info: NULL 4 rows in set (0.00 sec) ERROR: No query specified in a "push" way

View the backup library:

Mysql > show processlist\ G * * 1. Row * * Id: 1 User: system user Host: db: NULL Command: Connect Time: 4427 State: Waiting for master to send event Info: NULL * * * 2. Row * Id: 2 User: system user Host: db: NULL Command: Connect Time: 2044 State: Slave has read all relay log Waiting for more updates Info: NULL

Thus it can be seen that MySQL replication is asynchronous, and there is a certain delay between the slave library and the master library.

Copy related logs

1. BinlogBinlog records all data modification operations in mysql. You can view the format of Binlog in the following ways, which are Statement, Row and Mixed:

Mysql > show variables like'% binlog_format%'; +-+-+ | Variable_name | Value | +-+-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec)

2. The file format and content of Relay LogRelay Log are the same as Binlog, except that after executing the events in the current Relay Log from the SQL thread on the library, the SQL thread will automatically delete the Relay Log, thus freeing up space. In order to ensure that after restarting from the library Crash, the slave I / O thread and the SQL thread can still know where to start replication, two log files master.info and relay-log.info will be created by default from the library to save the progress of the replication, which record the progress of the current reading of the master library Binlog from the I / O thread of the library and the progress of the SQL thread applying the SQL thread, respectively.

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.24.33.186-- Master library IP Master_User: repl-- user account of master library for master-slave replication Master_Port: 3306-- main library port Connect_Retry: 60 Master_Log_File: mysql-bin.000005-- current read main library Binlog file name Read_Master_Log_Pos: 4356 from the slave library Imax O thread-- location of the main library Binlog read by the slave library Imax O thread Relay_Log_File: strong-relay-bin.000006-- Relay Log Relay where the SQL thread is being applied _ Log_Pos: 320-- location of Relay Log Relay_Master_Log_File: mysql-bin.000005-- Relay Log corresponding to Binlog Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_ Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4356-- the location of the Binlog corresponding to the location where the SQL thread is applying Relay Log Relay_Log_Space: 1153 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_ Id: 1 Master_UUID: 2a3e3fd9-0587-11e8-bdb8-0800272325a8 Master_Info_File: / usr/local/mysql-5.7.21-el7-x86_64/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql >

MySQL replication mode

There are three formats of Binlog, which correspond to three technologies of MySQL replication.

MySQL replication architecture

The common architectures of MySQL replication are one-master-multi-slave replication architecture, multi-level replication architecture and dual-master replication (Dual Master) architecture.

1. One-master-multi-slave architecture in the scenario where the read request pressure of the master database is very high, the read-write separation is achieved by configuring the one-master multi-slave replication architecture, and the read requests that do not require high real-time performance are distributed to multiple slave databases through load balancing. so as to reduce the reading pressure of the master database, as shown in the figure:

2. Multi-level replication architecture-one master-multi-slave architecture can solve the needs of most scenarios where the pressure of read requests is very high. Because the replication of MySQL is that the master library pushes Binlog to the slave library, the Imax O pressure and network pressure of the master library will increase with the increase of the slave library (each slave library will have an independent Binlog Dump thread on the master database to send Binlog events), while the multi-level replication architecture can solve the situation of one master and multiple slaves. The scenario of additional Imax O and network pressure in the main database, as shown in the figure:

3. Dual master replication / Dual Master architecture dual master replication / Dual Master architecture is especially suitable for scenarios where DBA maintenance requires master-slave switching. This architecture avoids the trouble of repeatedly building slave databases, as shown in the figure:

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.