MySQL InnoDB in the transaction log Ib_logfile
The transaction log or the redo log, in MySQL by default in the Ib_logfile0,ib_logfile1 name exists, you can manually modify the parameters, adjust
Open a few sets of logs to serve the current MySQL database, MySQL in order, circular write, every time a transaction is opened,
Will record some relevant information in the transaction log (recording the physical location or offset of the data modification to the file data);
Function: When the system crashes and restarts, the transaction is re-made, and when the system is normal, each checkpoint point in time will write the transaction
Applied to the data file.
Introduce a problem: in M/s environment, InnoDB after writing ib_logfile, the service is closed unexpectedly, will the main library recover data with ib_logfile, and
Binlog not write causes less of this transaction when synchronizing from the library? Thus causing the Lord to never agree;
Redo Log Write mode:
1.ib_logfile writes the current transaction update data and marks the transaction preparation Trx_prepare
2. Write Bin-log
3.ib_logfile current transaction Commit commit Trx_commit
Recovery method:
If Ib_logfile has already written transaction preparation, the recovery process will be based on whether the transaction exists in Bin-log to recover data.
Assume:
1) After the exception, because there is no write bin-log, from the library will not synchronize this transaction, on the main library, restart, in the recovery log in this
A transaction has no commit, that is, rollback the transaction.
2) After the end of the exception, this will Bin-log has been written, and the transaction will be synchronized from the library. The main library also resumes this transaction, depending on the recovery log and Bin-log
Comprehensive Description: Bin-log write completion, master and slave will normally complete the transaction, Bin-log is not written, master-slave library ROLLBACK TRANSACTION, there will be no master-slave library inconsistency problem.
Related parameters (Global & Static):
Innodb_log_buffer_size
Innodb_log_file_size
Innodb_log_files_in_group
Innodb_log_group_home_dir
Innodb_flush_log_at_trx_commit
Innodb_log_buffer_size: Transaction log buffer, can be set 1m~8m, default 8M, delay transaction log write disk,
The transaction log buffers are imagined as "funnel", and the cached log records are kept to the disk, and when the parameters are written
Innodb_flush_log_at_trx_commit control, explained later, enables large transaction log caches, which can be fully operational events
Service log, temporarily stored in the transaction buffer, do not have to write (before the transaction commit) to disk storage, but also to save disk space consumption;
Innodb_log_file_size: Control the size of the transaction log ib_logfile, scope 5mb~4g; all transaction logs ib_logfile0+
ib_logfile1+. The cumulative size cannot exceed 4G, the transaction log is large, checkpoint will be less, save disk IO, but the large transaction day
Log means that the database crash when it recovers slowly.
Introduction issue: Modifying this parameter size causes the size of the Ib_logfile file to not match the file size that existed before
Solution: In the case of Clean Shutdown database, delete ib_logfile, and then restart the database, the file will be created by itself;
Set up a few sets of transaction logs in Innodb_log_files_in_group:db, default is 2;
Innodb_log_group_home_dir: Transaction log storage directory, not set, Ib_logfile0 ... exists in the Data files directory
Innodb_flush_log_at_trx_commit: Control transaction log when writing disk and brush disk, safe increment: 0,2,1
Transaction buffers: Log_buffer;
0: One transaction buffer per second is flushed to the file system, while the file system to disk synchronization, but the transaction commits, does not trigger Log_buffer to file system synchronization;
2: The transaction buffer log is flushed to the file system each time the transaction commits, and the file system to disk is synchronized every second;
1: Flush to disk each time the transaction commits, the most secure;
Applicable environment:
0: Disk IO capacity is limited, security and convenience is poor, no replication or replication delay can be accepted, such as log business, mysql corruption lost 1s transaction data;
2: Data security requirements, you can lose a bit of transaction log, replication delay can also be accepted, OS corruption can only lose data;
1: Data security requirements are very high, and disk IO ability to support business, such as recharge consumption, sensitive business;
MySQL InnoDB in the transaction log Ib_logfile
