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

Because the transaction log of Mysql contains binary log and storage engine log, when a crash recovery occurs, the MySQL master node recovers through redo log, while in the master-slave replication environment, the slaver node synchronizes data according to the binlog of the master node. Such an architecture then has two basic requirements for mysql binary logs and redo log: first, ensure that the transactions existing in binlog must exist in redo log, that is, binlog will not have more transactions than redo log (can be less, because some of the transactions recorded in redo log may not have commit, and these transactions may eventually be rollback). 2. The order is the same, which is also very important. If the transaction order of the two records is not the same, then the order similar to the master database transaction execution is ta, tb, tc,td, but the binlog records ta,tc, tb, and td,binlog are copied to the slave database, resulting in inconsistent master-slave data. In order to achieve the two points mentioned above, how does mysql achieve it? Yes, the answer is internal xa transactions (core is 2pc)

The transaction submission process of the first and second phases:

(1) first, call the prepare method of binglog_hton and innobase_hton to complete the first phase. The papare method of binlog_hton actually does nothing. The prepare of innodb sets the transaction state to TRX_PREPARED, and brushes redo log to disk.

(2) if the prepare of all storage engines involved in the transaction is executed successfully, call TC_LOG_BINLOG::log_xid to write the SQL statement to binlog, and the transaction is bound to commit. Otherwise, the ha_rollback_trans is called to roll back the transaction, and the SQL statement is not actually written to binlog.

Finally, the commit of the engine is called to complete the commit of the transaction. In fact, binlog_hton- > commit does nothing (because (2) binlog has been written to disk), while innobase_hton- > commit clears undo information, brushes the redo log, and sets the transaction to TRX_NOT_STARTED state

II. Transaction recovery process:

When Innodb resumes, transactions in different states will be handled differently (see trx_rollback_or_clean_all_without_sess function):

For TRX_COMMITTED_IN_MEMORY transactions, clear the rollback segment, and then set the transaction to TRX_NOT_STARTED

For TRX_NOT_STARTED transactions, indicates that the transaction has been committed, skip

For TRX_PREPARED transactions, it is necessary to decide the fate of the transaction according to binlog. Skip temporarily.

For TRX_ACTIVE transactions, roll back.

To put it simply, when crash recovery occurs, the database recovers data according to redo log and checks the transaction status of each redo one by one. If, according to the above diagram, it has reached the TRX_NOT_STARTED phase, that is, the storage engine commit phase, then redo log and binlog are consistent, and the recovery can be carried out normally according to redo. The transaction status is TRX_ACTIVE, needless to say, not written into binlog, and directly rolled back. But if the transaction state is TRX_PREPARED, it will be divided into two cases, first check whether the binlog has been written successfully? If the write is not successful, roll back even if it is in TRX_PREPARED status. If the write is successful, then proceed to the final step, call the storage engine commit, and change the transaction state to TRX_NOT_STARTED, that is, the actual commit state, which can be used for data recovery.

As can be seen from the above analysis, writing binlog is data that must have been committed, and as long as you write binlog, the transaction is bound to be committed successfully. Because in the master-slave replication environment, if you write binlog, it is possible to transfer directly to the slave node application, so the two-phase commit maintains good data consistency and sequence.

3. Introduction of relevant parameters:

1 、 innodb_support_xa

The 2PC used to control whether innodb supports XA transactions. The default is TRUE. If closed, innodb does nothing during the prepare phase; this may cause the order of binlog to be inconsistent with the order in which innodb commits (for example, A transaction writes binlog before B transaction, but within innodb, A transaction commits after B transaction), which results in different data during recovery or slave.

2 、 sync_binlog

Mysql calls MYSQL_LOG::write to complete the write binlog when committing the transaction, and decides whether or not to flush the disk according to sync_binlog. The default value is 0, which means that the disk is not brushed, thus giving control to OS. If set to 1, a flush occurs every time the transaction is committed; this has an impact on performance (binlog group is already supported in 5. 6), so many people set it to 100.

3 、 innodb_flush_log_at_trx_commit

The values of 0, 1 and 2 of innodb_flush_log_at_trx_commit represent different landing strategies of redo log. 0 means that the flush is performed once a second, but the commit does nothing for each transaction (call fsync every second to land the data to the disk, but it should be noted here that if there is a cache in the underlying storage, such as raid cache, then the data will not really land at this time, but because the raid card usually has a backup power supply, the data is generally considered to be secure at this time). 1 means that flush occurs every time a transaction is committed, which is the safest mode. 2 means flush per second. Instead of flush each transaction commit, call write to swipe the redo log in redo log buffer to os page cache.

Now let's compare the advantages and disadvantages of the three strategies: 1 because each transaction commit will be redo log landing, so it is the safest, but due to the increase in the number of fsync performance degradation is relatively severe. 0 means flush per second, and no action is performed on each transaction commit, so an one-second transaction is lost in mysql crash or os crash. 2 is too much than 0 when each transaction commit will have a write operation, although the data has not landed on the disk, but as long as there is no os crash, even if mysql crash, then the transaction will not be lost. 2 is a little bit safer compared to 0.

Different values of the above three parameters will have different effects. Only when all three are set to 1 (TRUE) can the data be truly secure. Sync_binlog is not 1, which may result in binlog loss (OS hanging up), which is inconsistent with the data at the innodb level. Innodb_flush_log_at_trx_commit is not 1, which may result in data loss at the innodb level (OS hanging up), which is inconsistent with binlog

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.