Shulou(Shulou.com)05/31 Report--

This article focuses on "scenario analysis of data loss in MySQL database". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "MySQL database lost data scenario analysis" it!

Scenario Analysis of data loss in MySQL Database 1. Engine layer data loss scenario 1.1.InnoDB data loss scenario Analysis InnoDB as a storage engine for MySQL to support transactions, similar to Oracle, redo and undo log are required when a transaction is committed. InnoDB uses a pre-write log (Write Ahead Log) strategy, where changes to data pages are first done in memory, while the transaction sequence is recorded in redo log. The completion of the above operation indicates that the transaction has been completed and can be returned to the submitted information of the application. But at this time, the actual changed data pages are still saved in memory (called dirty pages), and there is no flush to the disk, that is, there is no landing. Generally speaking, when certain conditions are met, the checkpoint mechanism will be triggered. At this time, dirty pages in memory will be merged and written to disk to complete data synchronization. A policy that is controlled by the innodb_flush_log_at_trx_commit parameter. Note: this should be redo log, not binlog.

Parameter innodb_flush_log_at_trx_commit:

= 0: write os cache & flush disk per second

= 1: write os cache & flush disk every time commit

= 2: write os cache every commit, and then flush disk according to the innodb_flush_log_at_timeout parameter (default is 1s)

Innodb_flush_log_at_trx_commit=1 is the most secure because every time commit ensures that redo log is written to disk. However, the performance of this method is relatively low for DML performance. In our tests, we found that if we set it to 2, the performance of DML is about 10 times higher than if it is set to 1 (so when there is a writing problem on the SMS platform, this parameter has been set to 2).

The difference between an innodb_flush_log_at_trx_commit of 0 or 2 is mainly reflected in the type of transaction lost in mysql service crash or system crash:

1) when mysql service crash is set to 0, all committed and uncommitted transactions within 1 second will be lost and cannot be rolled back (because redo log is still recorded in log buffer and has not been dropped to redo log). When set to 2, each commit will be written to the os cache. Even if service crash is dropped, only all uncommitted transactions within 1 second will be lost, and the committed transactions will be written to the redo log and can be rolled back.

2) when system crash, it is similar to the above.

Therefore, the consensus in the industry is that in some scenarios where DML operations are frequent, the parameter innodb_flush_log_at_trx_commit is set to 2.

Although there is a risk of data loss: when mysql service crash occurs, InnoDB will crash recovery after restart, and the committed data within innodb_flush_log_at_timeout seconds will be lost. Uncommitted data can be processed by the transaction compensation mechanism in the application. But IO performance can be improved at least tenfold.

PS: when internal XA transactions are enabled (default is enabled) and binlog is enabled, the situation is slightly different. See below.

Scenario Analysis of data loss in 1.2.MyISAM

The MyISAM storage engine is largely unused in our production environment. And our online version 5.6 has changed the default storage engine of system tables such as data dictionary table metadata table to InnoDB.

Because MyISAM does not support transactions, and there is no data cache, all DML operations are only written to OS cache, and flush disk operations are completed by OS, so if the server goes down, this part of the data will definitely be lost.

two。 Scenarios in which master-slave replication leads to data inconsistency

MySQL master-slave replication principle: the MySQL master library writes binlog when the transaction is committed, and uses the sync_binlog parameter to control the binlog refresh to the disk "landing". There are two threads from the library: the IO thread is responsible for reading the binlog from the master library and recording it to the local relay log; the SQL thread applies the records from the relay log to the slave library. As shown in the following figure:

2.1.binlog refresh mechanism

Master writing binlog is similar to innodb engine writing redo, which is controlled by the parameter sync_binlog:

= 0: indicates that MySQL does not control the refresh of binlog, while the file system controls the flushing operation of binlog cache.

= N: indicates that every sync_binlog after N transactions is committed, the MySQL calls the flush operation of the file system to flush the contents of the binlog cache

Sync_binlog=1 is the most secure, which means that every time a transaction commits, MySQL will flush disk the contents of the binlog cache. In this way, in the case of power outage, the system is only likely to lose the data of one transaction. But when sync_binlog is 1, the IO consumption of the system is very large.

However, the value of N is not easy to be too large, otherwise more transactions will be lost when the system is powered off. At present, our production system is set to 100.

2.2. Internal XA transaction principle

The distributed transaction between MySQL storage engine and MySQL service layer, or between storage engine and storage engine, is called MySQL internal XA transaction. The most common internal XA transactions exist between binlog and the InnoDB storage engine. When a transaction commits, write the binary log first, and then write the redo log of the InnoDB storage engine. For this operation, it must be atomic, that is, to ensure that both are written at the same time. The internal XA transaction mechanism is to ensure that both are written at the same time.

The general flow of a XA transaction:

1) after the transaction is committed, the InnoDB storage engine first does a PREPARE operation to write the XID of the transaction to the redo log

2) write binlog

3) write the commit information of the transaction to redo log

If steps 1 and 2 fail, the entire transaction will be rolled back, and if step 3 fails, the MySQL database will first check whether PREPARE's XID transaction has been committed after restarting, and if not, commit again at the storage engine layer. This ensures the consistency of redo and binlog and prevents the loss of transactions.

2.3. Data inconsistency caused by non-real-time writing of redo log and binlog in the main library

Above we introduced the internal XA transaction process of MySQL, but this process is not seamless. If the ib_logfile and binlog logs of redo are set to non-real-time flush, the following data inconsistencies may occur:

1) if the trx_prepare of Redo log is not written, but the binlog has been written, then the slave database after crash recovery has more data than the master database.

2) if both trx_prepare and commit of Redo log are written, but not written by binlog, the amount of data in the slave database after crash recovery is less than that in the master database.

At present, performance can only be sacrificed for data security. Redo log and binlog must be set for real-time flushing. If the performance requirement is very high, consider using SSD instead of mechanical disk.

2.4. Data inconsistency caused by non-real-time writing of redo log and binlog from the database

The master library is normal, but the slave library is down due to an exception. If data is lost, will the SQL thread of the slave library be reapplied? We need to understand the mechanism of SQL threads first.

After reading the binlog log of the master database from the library, you need to land 3 files:

Relay log: the main library binlog read by IO Thread. The content format is the same as the binlog of the main library.

Relay log info: record the location, file number and other information of the relay log applied by SQL Thread

Master info: record the location, file number, delay and other information of the binlog read by IO Thread from the main library

Therefore, if these three files are not landed in time, system crash will lead to data inconsistency.

Prior to MySQL 5.6.2, the master library information recorded from the slave library and the information about applying binlog to the slave library were stored in files, namely master.info and relay-log.info. After version 5.6.2, logging is allowed in table with the following parameters:

The tables for master-info-repository = TABLE relay-log-info-repository = TABLE are mysql.slave_master_info and mysql.slave_relay_log_info, respectively, and both tables are innodb engine tables.

Master info and relay info also have three parameters to control refresh:

1) sync_relay_log: default is 10000, that is, every 10000 sync_relay_log events will be flushed to disk. A value of 0 means no refresh and is controlled by cache of OS.

2) sync_master_info: if master-info-repository is FILE, each sync_master_info event will be flushed to disk (default is 10000 times) when set to 0; if master-info-repository is set to 0, the table will not be updated if set to 0, and the table will be updated each time. The default is 10000.

3) sync_relay_log_info: if relay _ log_info_repository is FILE, when set to 0, the disk will be refreshed by OS. The default is 10000 flushes to the disk. If relay_log_info_repository is TABLE, no matter what the value is, evnet will update the table every time.

If the parameters are set as follows:

Sync_relay_log = 1

Sync_master_info = 1 sync_relay_log_info = 1 master-info-repository = TABLE relay-log-info-repository = TABLE

It will cause the call to fsync () / fdatasync () to increase as the number of master transactions increases, and if slave's binlog and redo are also refreshed in real time, it will cause serious IO performance bottlenecks.

2.5. Data inconsistencies caused by failure to recover in time after the main database is down

When the master library fails, the binlog is not pulled to the slave library in time, or the binlog received from the slave library is inconsistent (mostly due to network reasons). And the main library cannot be restored at the first time:

1) if the master library is not switched, the application can only read and write to the master library. If there is a read-write separation scenario, it will affect the application (in the read-write separation scenario, it will be from the library).

2) if a slave database is promoted to a new master database (such as MHA), the binlog data that the original master database did not have time to transfer to the slave database will be lost, and the following two problems will be involved:

A) the binlog received from each slave library is inconsistent. If a slave library is forced to be used as the new master library, the data between the slave libraries will be inconsistent.

B) after the original master database returns to normal, this part of the binlog during the failure period will be added because the new master database log discards part of the binlog log of the original master database.

For the above problems, the industry already has more mature methods to solve them:

2.5.1 ensure that all binlog are passed to the slave library

Option 1: use the semisync replication (semi-synchronous replication) plug-in. The characteristic of semi-synchronous replication is that the master database can not commit the transaction until there is a commit in the slave library. The advantage is that it ensures the data consistency between the master and slave libraries, while the disadvantage is that it has a great impact on performance and depends on the network, so it is suitable for scenarios with low tps pressure.

Solution 2: double-write binlog, copy to the standby machine through the file system of the DBDR OS layer, or use a shared disk to save the binlog log. The advantages are similar to those of scheme 1, but the disadvantages of this scheme are obvious:

1) DBDR needs to deploy its own services

2) severe cerebral fissure in DBDR. When disaster scenarios occur, they often fail to switch correctly.

3) it is necessary to establish heartbeat mechanism. To ensure the survival of the monitored computer.

Scheme 3: adjust the architecture level and introduce message queue to do asynchronous message processing. For example, to ensure that the database is written successfully, and then write a copy of the asynchronous queue, some of the business can be solved with the help of design and data flow.

2.5.2 ensure data loss is minimized

The above scheme design and architecture are relatively complex, if you can tolerate the loss of data, you can consider using MHA.

When master goes down, MHA can either specify a slave library with the lowest latency or the latest binlog pos, and promote it to the master library.

After MHA switches master, the original master can be repaired and rejoin the cluster as the slave role of the new master. In order to achieve high availability.

3. Summary

Through the above summary and analysis, there are many scenarios of data loss in MySQL, mainly related to engine layer data loss scenarios, master-slave data inconsistency scenarios and so on.

According to the CAP theory of distributed domain (Consistency consistency, Availability high availability, Partition tolerance partition tolerance), any distributed system can only meet two points at the same time, and there is no way to take into account all three. The master-slave environment of MySQL satisfies Availability, and the data can achieve strong data consistency in semi-synchronous scenarios, which can meet Consistency. But it can not fully satisfy the Partition tolerance.

Therefore, there are many solutions to deal with transaction (data) loss in the industry, such as transaction compensation mechanism, semi-synchronous replication, double write mechanism, asynchronous message queue and so on. Even, you can select the corresponding data products according to which two of the CAP are more required by the business. If you need to strike a balance between partition tolerance and high availability, you can use Cassandra and other column storage. Can meet the corresponding data consistency requirements of the business.

At this point, I believe you have a deeper understanding of the "scenario analysis of lost data in MySQL database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.