可以说mysql的多数特性都是围绕日志文件实现,而其中最重要的有以下三种
innodb 为了提高磁盘I/O读写性能,存在一个 buffer pool 的内存空间,数据页读入会缓存到 buffer pool,事务的提交则实时更新到 buffer pool,而不实时同步到磁盘(innodb 是按 16KB 一页同步的,一事务可涉及多个数据页,实时同步会造成浪费,随机I/O)。事务暂存在内存,则存在一致性问题,为了解决系统崩溃,保证事务的持久性,我们只需把事务对应的 redo 日志持久化到磁盘即可(redo 日志占用空间小,顺序写入磁盘,顺序I/O)
MTR 产生的 redo 日志先会被复制到一个 log buffer 里(类似 buffer pool)。而同步到磁盘的时机如下:
事务需要保证原子性,也是说事务中的操作要么全部完成,要么什么也不做。如果事务执行到一半,出错了怎么办-回滚。但是怎么回滚呢,靠 undo 日志。undo 日志就是我们执行sql的逆操作
binlog有三种格式:Statement、Row以及Mixed。
[root@root log]# mysqlbinlog 'log.000001'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181214 14:44:48 server id 1 end_log_pos 120 CRC32 0x79b6cd10 Start: binlog v 4, server v 5.6.40-log created 181214 14:44:48 at startup
ROLLBACK/*!*/;
BINLOG '
YDIUXA8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABgMhRcEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARDN
tnk=
'/*!*/;
# at 120
#181214 14:45:20 server id 1 end_log_pos 199 CRC32 0x10dec193 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827520/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#181214 14:45:20 server id 1 end_log_pos 303 CRC32 0x9ec5f952 Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1544827520/*!*/;
insert into t1 values('8','7')
/*!*/;
# at 303
#181214 14:45:20 server id 1 end_log_pos 334 CRC32 0xfd659542 Xid = 10
COMMIT/*!*/;
# at 334
#181214 14:45:35 server id 1 end_log_pos 413 CRC32 0x43929486 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827535/*!*/;
BEGIN
/*!*/;
# at 413
#181214 14:45:35 server id 1 end_log_pos 517 CRC32 0x4f1284f2 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827535/*!*/;
insert into t1 values('9','7')
/*!*/;
# at 517
#181214 14:45:35 server id 1 end_log_pos 548 CRC32 0x67231f2b Xid = 20
COMMIT/*!*/;
# at 548
#181214 14:45:39 server id 1 end_log_pos 627 CRC32 0x82b39b3e Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1544827539/*!*/;
BEGIN
/*!*/;
# at 627
#181214 15:00:48 server id 1 end_log_pos 1646 CRC32 0x7e89c8dc Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> show binlog events in 'log.000001';
+------------+------+-------------+-----------+-------------+---------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+------+-------------+-----------+-------------+---------------------------------------------+
| log.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| log.000001 | 120 | Query | 1 | 199 | BEGIN |
| log.000001 | 199 | Query | 1 | 303 | use `test`; insert into t1 values('8','7') |
| log.000001 | 303 | Xid | 1 | 334 | COMMIT /* xid=10 */ |
| log.000001 | 334 | Query | 1 | 413 | BEGIN |
| log.000001 | 413 | Query | 1 | 517 | use `test`; insert into t1 values('9','7') |
| log.000001 | 517 | Xid | 1 | 548 | COMMIT /* xid=20 */ |
| log.000001 | 548 | Query | 1 | 627 | BEGIN |
| log.000001 | 627 | Query | 1 | 732 | use `test`; insert into t1 values('10','7') |
| log.000001 | 732 | Xid | 1 | 763 | COMMIT /* xid=30 */ |
| log.000001 | 763 | Query | 1 | 842 | BEGIN |
| log.000001 | 842 | Query | 1 | 947 | use `test`; insert into t1 values('11','7') |
| log.000001 | 947 | Xid | 1 | 978 | COMMIT /* xid=40 */
+------------+------+-------------+-----------+-------------+---------------------------------------------+
23 rows in set (0.00 sec)
image.png
redolog 中的事务如果经历了二阶段提交中的prepare阶段,则会打上 prepare 标识,如果经历commit阶段,则会打上commit标识(此时redolog和binlog均已落盘)。崩溃恢复逻辑如下: