mysql> show binlog events in 'mysql-bin.00002' from 504769752 limit 30,30; mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 選項解析： IN 'log_name' 指定要查詢的binlog文件名(不指定就是第一個binlog文件) FROM pos 指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算) LIMIT [offset,] 偏移量(不指定就是0) row_count 查詢總條數(不指定就是所有行)
ib_logfile
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.
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 is not used for statements such as SELECT or SHOW that do not modify data.
二進制日志不用于不修改數據的SELECT或SHOW等語句
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.
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.
To write changes to the database files , that had been buffered in a memory area or a 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.
