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

Summary

1. Ib_logfile is similar to oracle's online redo log, including commit and uncommit data.

2. Binary log is similar to oracle's online redo log and archive redo log, but only has commit data.

Binlog in statement format, and finally COMMIT.

Binlog in row format, and finally there will be a XID event

3. Why does MySQL have binlog and redo log? Because MySQL has multiple storage engines, no matter which storage engine you use, there will be binlog, not necessarily redo log. The redo log transaction log ib _ logfile file is generated by the InnoDB storage engine.

4. Ib_logfile is recycled, but binary log is not recycled. After being full or restarted, a new binary log file will be generated.

5. The contents of the two kinds of log records are similar. Both transactions correspond to the information of DML and DDL, but the functions are different, and the contents may be duplicated. For example, a DML is recorded in ib_logfile and recorded in binary log.

6. Ib_logfile is resumed when it is started after an abnormal downtime

7. Binary log is used for data recovery and master-slave replication is used.

8. The trigger points of the two kinds of logs written to disk are different. The binary log is written only once after the transaction commit is completed, and the redo log is written every 1 second after the transaction commit. In order to ensure the data consistency of master and slave, MySQL must ensure the consistency of binlog and InnoDB redo logs (because the standby database replays the transactions committed by the master database through binary logs, if the master database commit is written to binlog before, and once the master database crash is started again, the transaction will be rolled back. However, if the slave database has been executed at this time, the master and backup data will be inconsistent). Therefore, you must ensure that the binary log is written only once after the transaction is committed.

9. In the master-slave replication structure, to ensure the persistence and consistency of transactions, it is most appropriate to set the relevant variables of the two logs as follows: sync_binlog=1 (that is, each transaction committed is synchronously written to disk) and innodb_flush_log_at_trx_commit=1 (that is, each committed transaction is written to disk). The setting of these two variables ensures that each committed transaction is written to the binary log and transaction log and flushed to disk on commit.

10. In innodb, there is only one rule for table data flushing: checkpoint. But there are several situations that trigger checkpoint (1. Reuse redo log files; 2. Dirty pages reach a certain proportion)

11. As a redo log, ib_logfile records "what changes have been made", is a physical log, and records "what changes have been made on a data page"

Binary log records the original logic of this statement, which is divided into two modes, statement format records sql statements, row format records the contents of rows, and records two pieces of data before and after updates.

Use the following methods to view the contents of the ib_logfile

[root@mydb ~] # strings / var/lib/mysql/ib_logfile0

Use the following two ways to view the contents of binary log

Mysqlbinlog mysql-bin.000002

Mysql > show binlog events in 'mysql-bin.000002'

Mysql > show binlog events in 'mysql-bin.00002' from 504769752 limit 301.30

Mysql > show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Option resolution:

IN 'log_name' specifies the name of the binlog file to query (if not specified is the first binlog file)

FROM pos specifies the starting point of pos (if not specified, it is calculated from the first pos point of the whole file)

LIMIT [offset,] offset (0 if not specified)

Total number of row_count queries (all rows are not specified)

Ib_logfile

Official document https://dev.mysql.com/doc/refman/5.7/en/glossary.html

A set of files, typically named ib_logfile0 and ib_logfile1, that form the redo log. Also sometimes referred to as the log group. These files record statements that attempt to change data in InnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.

This data cannot be used for manual recovery; for that type of operation, use the binary log.

A set of files, usually named ib_logfile0 and ib_logfile1, make up the redo log. It is sometimes called a log group. These files record statements that try to change the data in the InnoDB table. When started after a crash, these statements are automatically replayed to correct data written by an incomplete transaction.

This data cannot be used for manual recovery; for this type of operation, use binary logs.

Binary log

Official document https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

The binary log contains "events" that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication

Certain data recovery operations require use of the binary log.After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

The binary log contains "events" that describe database changes, such as table creation operations or changes to table data. It also contains events for statements that may have changed (for example, DELETE that does not match any rows) unless row-based logging is used. The binary log also contains information about how long each statement takes to get updated data.

Binary logs have two important purposes:

For replication

Some data recovery operations require the use of binary logs. After the backup is restored, the events in the binary log logged after the backup are re-performed. These events cause the database to be updated from the backup point.

The binary log is not used for statements such as SELECT or SHOW that do not modify data.

Binary logs are not used for statements such as SELECT or SHOW that do not modify data

Checkpoint

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_checkpoint

As changes are made to data pages that are cached in the buffer pool, those changes are written to the data files sometime later, a process known as flushing. The checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data files.

When changes are made to the data pages cached in the buffer pool, the changes are written to the data file at a later time, a process called refresh. A checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data file.

Sharp checkpoint

The process of flushing to disk all dirty buffer pool pages whose redo entries are contained in certain portion of the redo log. Occurs before InnoDB reuses a portion of a log file; the log files are used in a circular fashion. Typically occurs with write-intensive workloads.

The process of flushing all dirty buffer pool pages that are included in some parts of the redo log to disk. Occurs before InnoDB overrides the reuse of log files; log files are used in a circular manner. Write-intensive workloads usually occur.

Flush

To write changes to the database files, that had been buffered in a memory area ora temporary disk storage area. The InnoDB storage structures that are periodically flushed include the redo log, the undo log, and the buffer pool.

Flushing can happen because a memory area becomes full and the system needs to free some space, because a commit operation means the changes from a transaction can be finalized, or because a slow shutdown operation means that all outstanding work should be finalized. When it is not critical to flush all the buffered data at once, InnoDB can use a technique called fuzzy checkpointing to flush small batches of pages to spread out the I/O overhead.

Writes changes that are buffered in the memory area or temporary disk storage area to the database file. InnoDB storage structures that are periodically refreshed include redo logs, undo logs, and buffer pools.

The refresh may be because the memory area is full and the system needs to free up some space, because the commit operation means that the change to the transaction can be finally determined, or because the slow close operation means that all unfinished work should be finally completed. When it is not important to refresh all buffered data at once, InnoDB can use a technique called fuzzy checkpointing to refresh small batches of pages to spread I / O overhead.

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.