MySQL Database Transactions and replication

Haven't written technical articles for a long time, because have been thinking "back-end distributed" This series exactly how to write just right. Recently, the basic idea is that "back-end distributed" includes "Distributed Storage" and "distributed computing" in two categories. Actual work encountered problems, in order to find the answer to the way to anatomy technology, many times we are not in the creation of new technologies, but in the application of technology. For more efficient and effective use of good technology, we need to understand some of the principles of technology and working methods. With the problem from the user's point of view to analyze the principle of technology, and open source technology products and frameworks as a kind of technology reference implementation to explain. To clarify the principle as the main purpose, for the specific implementation of technical details if nothing special, as far as possible to stop.

Transactions and Replication

Recently participated in a data distribution related project, involving the data distribution of MySQL database. In short, it is necessary to achieve a multi-point writable data Center in a remote location and ensure that the distributed data can achieve final consistency. Previously, MySQL data distribution is only read and write separation, through the database of its own master-slave replication can be implemented to write the main library, read from the library. Now it is necessary to double write the main library and achieve the final consistency after a short delay, the problem is more complex at first, but ultimately the final consistency of the data.

Back to the simplest case, how is data consistency guaranteed when there is only one MySQL database? Knowledge of the database is known, this is ensured by the transaction characteristics of the database, the transaction includes four features:

• atomicity Atomic Nature

• Consistency consistency

• Isolation isolation

• Durability Persistence

The ACID four characteristics of the transaction is not the focus of this article, do not start to do academic commentary, do not understand in the following references [3] to see related articles. Here only to ask a question, a single database transaction can guarantee the consistency of data, then MySQL in the deployment of master-slave architecture, how to ensure the consistency between the master and slave data?

MySQL introduced a new log file called Binlog in order to provide master-slave replication, which contains a collection of event logs that raise data changes. The request from the library to the main library sends Binlog and the log event restores the data written from the library, so the data source from the library is Binlog. In this way, the MySQL main library only needs to do binlog with the local data to ensure that the master-slave database data is consistent (ignoring the master-slave inconsistency caused by network transmission). We know how to ensure that local data consistency is achieved by database transaction characteristics, and how is database transaction implemented? Let's look at the following picture:

MySQL itself does not provide transactional support, but instead opens up the storage engine interface, implemented by a specific storage engine, specifically the storage engine that supports MySQL transactions is InnoDB. The common way that the storage engine implements transactions is based on redo log and undo log. Simply put, redo log records the transaction's modified data, and the undo log records the original data before the transaction. So when a transaction executes, the actual process simplification is described as follows:

1. Log the Undo/redo log first to make sure that the logs are flashing to the disk for persistent storage.

2. Updates the data record, caches the operation, and asynchronously brushes the disk.

3. Commits the transaction and writes a commit record in the redo log.

If a failure is interrupted during MySQL execution, the transaction can be redo by redo log or rolled back through the undo log to ensure data consistency. These are all done by the transactional storage engine, but the binlog is not within the scope of the transactional storage engine, but is logged by MySQL Server. Then it is necessary to ensure consistency between the binlog data and the redo log, so the actual transaction execution after opening the binlog is one more step, as follows:

1. Log the Undo/redo log first to make sure that the logs are flashing to the disk for persistent storage.

2. Updates the data record, caches the operation, and asynchronously brushes the disk.

3. Persist the transaction log to Binlog.

4. Commits the transaction and writes the commit record in the redo log.

In this case, as long as the binlog is not successful, the entire transaction needs to be rolled back, and Binlog after the successful writing, even if the MySQL Crash can resume the transaction and complete the submission. To achieve this, it is necessary to associate Binlog with transactions, and only ensure the consistency of binlog and transactional data to ensure the consistency of master-slave data. So the Binlog write process has to be embedded into the pure transactional storage engine execution and complete the two-phase commit in the form of an internal distributed transaction (XA transaction). Further details will not be expanded, as can be found in the following references [5].

Summary

We first put forward a question, and then from the perspective of data consistency, referring to the implementation of MySQL. This paper analyzes how MySQL single-machine environment can ensure the consistency of data of replication mechanism, that is, binlog and transactional data. Later, we can implement replication based on Binlog and ensure the consistency of master-slave replication. The master-slave replication introduces network factors, which further increases the complexity of maintaining master-slave data consistency, and further analyzes the problem later.

Reference

[1] MySQL internals Manual. [Replication] (http://dev.mysql.com/doc/internals/en/replication.html).

[2] MySQL internals Manual. [The Binary Log] (http://dev.mysql.com/doc/internals/en/binary-log.html).

[3] in355hz. [Implementation of database ACID] (http://in355hz.iteye.com/blog/2029963).

[4] jb51. [MySQL's handling instructions for Binlog] (http://www.jb51.net/article/27556.htm).

[5] Repls. [Brief analysis of Innodb_support_xa and Innodb_flush_log_at_trx_commit] (http://www.2cto.com/database/201306/221413.html).

[6] 68IDC. [MySQL 5.6 DBA and Developer's Guide] (http://www.68idc.cn/help/mysqldata/mysql/20150127191299.html).

MySQL Database Transactions and replication