Brief analysis of MySQL Replication (this article is transferred from the network, not I wrote)

Lu Fei

Source: Dodba (Mysqlcode)

0. Guidance

This article covers almost all of the knowledge points of MySQL Replication (master-slave replication), including the Replication principle, binlog format, how to ensure data consistency in replication, group submission, replication optimization, semi-synchronous replication, and multi-source replication.

At present, many companies in the production environment in the use of MySQL Replication, also known as MySQL replication, configuration easy to build a lot of features such as the application of MySQL Replication is very extensive, we have used a master drag more than 20 from the library to share the business pressure. There are also a lot of articles on MySQL Replication, but most of them are about how to build MySQL Replication, and it's not clear how to build a high-reliability MySQL Replication. This article is also semi-synchronous replication, lossless replication, multi-source replication has been explained.

What are the uses of replication

• Read/write separation

• Disaster preparedness

• Highly Available

• Off-line statistics

• Backup

How replication works

As you can see, the main steps of replication are:

1. Master changes the record to binary log

2. The IO thread on the slave copies the logs on the main library to its own relay log

3. Slave on the SQL thread to play back the contents of the trunk log so that the data on the slave is consistent with the master

Binlog binary log file for recording MySQL data changes.

Relay-log the trunk log file, slave I/O thread reads the binlog of master, logs it to Relay-log, and the SQL thread reads the contents of the Relay-log log and applies it to the slave server.

Format of the Binlog record

STATEMENT (SQL statement that records operations)

• Advantage reduces binlog log volume, saves io, improves performance, and is easy to understand

• The disadvantage is that not all DML statements can be duplicated, and some function uuid (), found_rows (), and USER () cannot be copied

Row (Record the change information for each row of the operation, RC isolation level, must be row format)

• Advantage any situation can be copied, ROW mode is the most secure and reliable

• Shortcomings generate a large number of logs, especially the DDL of copy data will cause the log to skyrocket

• The recommendation table must have a primary key

MIXED (Mixed mode)

• Using the STATEMENT mode to record the Binlog, using ROW mode for operations that cannot be copied STATEMENT mode Binlog,mysql the logging method based on the SQL statement executed. More bugs, not recommended.

Binlog Events

We all know that the Binlog log is used to record all changes to the operation of MySQL, and each of these changes will correspond to the event, that is, the Event,index file records all Binlog locations, each binlog will have headers event,rotate three E The structure of the Vent,binlog is as follows.

The common event is as follows:

• Format_desc: The new binlog log file

• Rotate: Log Splitting

• Table_map: Table, column, and other meta-data

• Query: Querying

• Write_rows: inserting

• Update_rows: Update

• Delete_rows: Delete

After understanding the above basics, we can take the following three questions to learn how replication works.

• How is the transaction submitted? Does the transaction commit first write binlog or redo log?

• Why does MySQL have binlog, and redo log?

• How to ensure that the two parts of the log consistency?

How is the service submitted? Does the transaction commit first write binlog or redo log?

As you can see in the above picture, the commit of a transaction is divided into three main steps:

1. The InnoDB layer writes prepare log, at which time SQL is executed successfully and generates XID information and memory logs for redo and undo, written to the redo log file

2. MySQL Server Layer Write Binlog (write--"Fsync")

3. InnoDB Write commit log, InnoDB within the storage engine, so that undo and redo are permanently written to disk

Why does MySQL have binlog, and redo log?

This is because of the MySQL architecture, MySQL is a multi-storage engine, regardless of the use of the storage engine, there will be binlog, and not necessarily have redo log, simply said, Binlog is the MySQL Server layer, redo log is the InnoDB layer.

How to ensure that the two parts of the log consistency?

The process of committing the transaction has been mentioned above, need to write redo log also write Binlog, then how to ensure the consistency of the data, if not guaranteed to write these two files in the same transaction, then will result in inconsistent data, this inconsistency includes MySQL crash and master-slave replication data inconsistency.

One of the frequently asked questions during an interview, what are the parameters that affect MySQL write performance and data consistency? is undoubtedly a two-parameter innodb_flush_log_at_trx_commit and Sync_binlog, these two parameters are to control the MySQL disk write policy and data security key parameters, MySQL in order to ensure the master and slave data consistency, The consistency of the Binlog and InnoDB redo logs must be ensured.

The parameters are described as follows:

Innodb_flush_log_at_trx_commit (Redo)

• 0 Log buffer is written to log file once per second with flush operation

• 1 log buffer data is written to log file for each transaction commit and flush operation

• 2 MySQL will write log buffer data to log file for each transaction commit, without flush operation

Sync_binlog (Binlog)

• 0 refreshing the information in Binlog_cache to disk is determined by the OS

• n Flushes information from Binlog_cache to disk per N transaction commit

So how to ensure binlog and InnoDB redo log consistency, MySQL use internal distributed things to ensure consistency, MySQL in the prepare phase will be generated XID,XID will be written to prepare log, will also be written to Binlog, when the recovery will be XID in two files than this transaction, if there is a XID corresponding to the object will be committed, and vice versa will rollback this transaction, the following are several situation analysis:

1. When a transaction is crash in the prepare phase, the transaction is rollback.

2. When the transaction is crash in the Binlog phase, the log is not successfully written to disk, and the transaction is rollback at startup

3. When a transaction is crash after the Binlog log has been Fsync () to disk, but InnoDB is not committed, the transaction is re-made and committed at MySQL startup, so InnoDB log and redo of the Binlog storage engine are always Remain consistent.

Summing up is that if a thing in the prepare log stage is successful, and in the MySQL Server layer Binlog also write success, then this transaction must commit success.

Group Submission

Now looking back at the diagram of how the transaction is submitted, it will be found that each transaction in the INNODB layer is parallel, but at the time of writing Binlog, the MySQL server layer becomes serial, because each commit will apply Prepare_commit_mutex this lock, In MySQL version 5.6, a binary LOG group commit is provided, which is a group commit, and group commit is divided into three phases.

• Flush stage:leader thread traverses flush_stage linked list, writes binary log cache

• Sync Stage: Sync Binlog cache to disk, and the binary log cache for all the queue transactions is permanently written to disk when sync_binlog=1

• Commit Stage:leader the InnoDB storage engine to complete the commit according to order

Here is a diagram submitted by our test group, and we can see that the number of TPS submitted by the group is much higher.

1062, 1053 replication error

It is possible that DBAs will encounter 1062 of errors in the process of using MySQL replication during slave outages or exceptions, and everyone is resolved using the following method.

• SET GLOBAL sql_slave_skip_counter = 1

• GTID by means of an empty transaction

But why is the data conflicting? We break down the copied steps, here's a good picture, the image comes from the web.

We can see that there are two files saved replication Matadata,

• Relay-info.log saved the Relay_log_name and relay_log_pos that the SQL thread played back, as well as the master_log_name and Master_log_pos of the corresponding master.

• Master-info.log holds information such as user, password, port, Master_log_name and Master_log_pos connected to master.

For example, if the SQL thread is playing back, after the playback, has not come and written to the replication Matadata file, the outage, at this time after restarting slave, there will be a 1062 error.

In MySQL 5.6, the Sql/io thread Crash-safe feature is provided. By writing Relay_log_info_repository=table,relay-info information into the Mysql.slave_relay_log_info table, you can not only guarantee consistency (writing files into atomic operations of the same thing), Write performance has also been improved.

Such as. IO thread in the same vein, slightly different is that when the Relay-log-recover is set to 1, the IO thread of slave reads events and re-reads it from where it was played back from the SQL thread.

Replication of optimal parameter configurations

So much of it, in fact, is the most reliable parameter configuration for MySQL Replication.

Master

binlog_format = ROW 
transaction-isolation = READ-COMMITTE
Dexpire_logs_days = 30 
server-id = 327 
sync_binlog = 1 
innodb_flush_log_at_trx_commit = 1 
innodb_support_xa = 1

Slave

log_slave_updates=1 
server-id = 328 
relay_log_recover = 1 
relay_log_info_repository = TABLE 
master_info_repository = TABLE
read_only = 1

How to improve replication efficiency?

MySQL 5.6 provides parallel replication, but this parallelism is only database-based. If there is still no real parallel playback based on a single database, many DBAs will split the database vertically, splitting it into several databases, by setting the Slave_parallel_workers=n, Database-level parallel replication is possible, but the latency for hot-spot business replication is still unresolved.

MySQL 5.6 also introduced the Gtid, not only reduces the master-slave failover, the difficulty of looking for filename,position, but also joined the group submission, which has created the MySQL 5.7 version of the multi-threaded slave appeared. For example, a group of transactions can be played back in parallel.

In the test, MySQL 5.7 's multi-threaded replication greatly increased the latency efficiency, while 30 threads concurrent operation can guarantee an average delay of about 5.9 seconds, and single-threaded replication latency rate has been basically rising.

Multi-threaded Slave Related parameters

slave-parallel-type= DATABASE /LOGICAL_CLOCK-- DATABASE -- 基于库级别的并行复制 与5.6相同
-- LOGICAL_CLOCK -- 逻辑时钟，主上怎么并行执行，从上怎么回放。

slave-parallel-workers=16  -- 并行复制的线程数

slave_preserve_commit_order=1 --commit的顺序保持一致

Semi-synchronous

As we all know, the default MySQL Replication replication is asynchronous mode, asynchronous also indicates there is the possibility of loss of data, MySQL in version 5.5 provides the Semi-sync Replication, that is, semi-synchronous, but semi-synchronous can only say reduce the risk of data loss, So in MySQL version 5.7, MySQL provides the lossless semi-sync replication, which is lossless replication, which minimizes data loss (lossless replication will switch to asynchronous replication as in the case of a semi-synchronous problem).

In a semi-synchronization, at least one slave node receives binlog and then returns, not completely avoiding data loss, and after time-out, the asynchronous replication is cut back. In the process of committing a thing, after the commit log phase of the INNODB layer, the Master node needs to receive at least one ACK from the slave node to continue with the next thing.

Lossless replication

In lossless replication, Master sends Binlog to slave, and only if Slave writes Binlog to the local relay-log, master commits the transaction to the storage engine layer, and then returns the request to the client before the client can see the transaction that was just committed. In the process of committing a thing, after the binlog phase of the MySQL Server layer, the master node needs to receive an ACK from at least one slave node to continue with the next thing.

Comparison of semi-synchronous replication and lossless replication

Different time points for ACK

• Semi-synchronous replication waits for an ACK after the commit log in the INNODB layer.

• Lossless replication waits for an ACK after the write binlog on the MySQL server layer.

Master-Slave Data consistency

• Semi-synchronous replication means that on the master node, the newly committed object changes the database and is visible to other things.

• Lossless replication After the write Binlog is complete, it transmits the binlog, but has not yet written the commit log, which means that the current object changes the database, other things are not visible.

Semi-synchronous correlation parameters

rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count=1

Semi-synchronous correlation event statistics

Rpl_semi_sync_master_tx_avg_wait_time--开启Semi_sync，平均需要额外等待的时间
Rpl_semi_sync_master_net_avg_wait_time--事务进入等待队列后，到网络平均等待时间Semi-sync的网络消耗有多大。Rpl_semi_sync_master_status-- 则表示当前Semi-sync是否正常工作
Rpl_semi_sync_master_no_times--可以知道一段时间内，Semi-sync是否有超时失败过，记录了失败次数。

  multi-source

In MySQL version 5.7, however, multi-source replication has been provided, and the advent of multi-source replication has greatly facilitated the operation of the Library sub-table, and we have deployed multiple sets of multi-source replication for statistical use.

For example, multi-source replication uses a multichannel mode, which is separated from normal replication by using the for channel.

MASTER TO .... FOR CHANNEL ‘m1‘;CHANGE MASTER TO .... FOR CHANNEL ‘m2‘;

We also said that in order to improve the efficiency of replication, many DBAs will split the DB according to the business, but after the split is faced with a new problem, that is, Join,join is definitely the most common feature in a relational database, but in a distributed environment, join is the most difficult problem to solve, Using multi-source replication will be a good solution to this problem.

If the database, table name consistent how to use multi-source replication? , in fact, as long as the problem of resolving data conflicts can be used.

such as the Library sub-table schema, you can use the following parameters to implement the odd-even insertion method to solve.

auto_increment_offset=1…n
auto_increment_increment=n

But this approach needs to consider extensibility in advance.

Brief analysis of MySQL Replication (this article is transferred from the network, not I wrote)