目录
- 前言
- 一、bin log（二进制日志）
- 1、启用配置
- 2、bin log配置参数
- 3、bin log常用命令
- ⑴、查看binlog文件列表
- ⑵、查看master所有binlog日志
- ⑶、查看master 正在使用的binlog日志信息
- ⑷、刷新log日志
- ⑸、重置(清空)所有binlog日志
- ⑹、读取binlog日志内容
- ①、mysqlbinlog 命令查看
- ②、show binlog events 查询命令
- ③、通过解析工具 binlog2sql 解析SQL语句
- 4、复制模式
- ⑴、STATEMENT模式（SBR）
- ⑵、ROW模式（RBR）
- ⑶、MIXED模式（MBR）
- 5、日志文件
- 6、数据恢复
- ⑴、创建误删场景
- ⑵、恢复数据
- 二、redo log（重做日志）
- 1、redo log概念
- 2、redo log相关配置
- 3、redo log的刷盘策略
- ⑴、Buffer Pool（缓冲池）
- ⑵、redo log 的好处
- ⑶、redo log 的组成
- ⑷、redo log 刷盘时机
- ⑸、redo log 的整体流程
- ⑹、redo log 的刷盘策略
- ⑺、redo log 的不同刷盘策略演示
- ①、innodb_flush_log_at_trx_commit = 1时
- ②、innodb_flush_log_at_trx_commit = 2时
- ③、innodb_flush_log_at_trx_commit = 0时
- 4、两阶段提交
- 三、undo log（回滚日志）
- 1、undo log的功能
- 2、undo log的存储结构
- ⑴、InnoDB数据文件存储结构
- ⑵、undo log存储及相关配置
- ⑶、回滚段与事务
- ⑷、回滚段中的数据分类
- ⑸、undo页的重用
- ⑹、undo log日志的存储机制
- 3、undo log的工作原理
- 4、undo log的类型
- 5、清空(purge)操作
- 6、undo log生命周期
- 四、error log（错误日志）
- 启用配置
- 五、slow query log（慢查询日志）
- 启用配置
- 六、general log（一般查询日志）
- 启用配置
- 七、relay log（中继日志）
前言
MySQL 中有七种日志文件，分别是：redo log（重做日志）、undo log（回滚日志）、bin log（二进制日志）、error log（错误日志）、slow query log（慢查询日志）、general log（一般查询日志），relay log（中继日志）。
其中有三个非常重要的核心日志：bin log（二进制日志） 、 redo log（重做日志） 、undo log（回滚日志）。
本篇博客就简单了解一下各种日志的记录方式及相关配置，更多底层知识还是需要去看官方文档，本多处都是转载整合，用于个人学习使用。
对于研发人员来说 最重要的还是bin log，其他的仅需了解即可。
一、bin log（二进制日志）
bin log 就是 binary log，二进制日志文件，记录了mysql所有的 DDL 和 DML (除了数据查询语句)操作，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。通过 bin log 日志我们可以做数据恢复，增量备份，主主复制和主从复制等等。
1、启用配置
MySQL 5.7这个版本默认是不开启 bin log 日志的，下面是开启 bin log 过程：
# 查看 bin log 相关日志， 可以看到还未开启 bin log 日志
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------+
6 rows in set (0.04 sec)
登录服务器查找MySQL配置文件（服务器首先读取的是/etc/mysql/myf文件，如果前一个文件不存在则继续读/etc/myf文件，如若还不存在便会去读~/.myf文件），在 [mysqld] 后加上以下配置（参考下面配置说明），重启MySQL。
[root@VM-0-12-centos ~]# which mysql # 找到mysql位置
/usr/bin/mysql
[root@VM-0-12-centos ~]# /usr/bin/mysql --verbose --help | grep -A 1 'Default options' # 查找mysql配置文件
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
[root@VM-0-12-centos ~]# find / -name my.cnf
/usr/local/mysql-5.7.35/my.cnf
/usr/local/mysql/my.cnf
/etc/my.cnf
[root@VM-0-12-centos ~]# vim /etc/my.cnf
[mysqld]
log_bin=ON
binlog_format=ROW
server-id=1
[root@VM-0-12-centos ~]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[root@VM-0-12-centos ~]#
回到MySQL命令行，可以看到 bin log 已经开启：
mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/var/ON |
| log_bin_index | /usr/local/mysql/var/ON.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------------------------------+
5 rows in set (0.04 sec)
mysql>
2、bin log配置参数
这里只写了比较基础的配置，里面还有设及主从同步的配置参数，还没有测试过这里就不写了。
[mysqld]
# 配置与其它server不同的id，主从、主主复制需要。必须为1到232–1之间的一个正整数值。
server-id=1
# 任意名称均可（这里是名称），只要配置了名称即开启 bin log。
log_bin=ON
# 设置binlog模式为row（基于行的复制），binlog有三种模式下面会讲到。
binlog-format=row
# log_bin_basename和log_bin_index会根据配置的log_bin 自动生成，如果需要可以指定文件。
# binlog日志的基本文件名，后面会追加标识来表示每一个文件
# log_bin_basename=/usr/local/mysql/var/ON
# 是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
# log_bin_index=/usr/local/mysql/var/ON.index
# binlog日志文件大小超过 max_binlog_size 配置的上限时，也会生成新的日志文件入下一个日志文件，mysql会在重启时生成一个新的日志文件。
max_binlog_size=100M
#对指定的数据库开启binlog，这里是对test数据库开启binlog服务
binlog-do-db=test
# binlog过期清理时间（天）
expire-logs-days=7
# binlog缓存大小
binlog_cache_size=4m
# 最大binlog缓存大小
max_binlog_cache_size=512m
# 表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。
# 如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。
# 最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。
sync_binlog=0
3、bin log常用命令
⑴、查看binlog文件列表
show binary logs;
⑵、查看master所有binlog日志
mysql> show master logs;
+-------------+-----------+
| Log_name | File_size |
+-------------+-----------+
| MYON.000001 | 120 |
+-------------+-----------+
1 row in set (0.04 sec)
⑶、查看master 正在使用的binlog日志信息
即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| MYON.000001 | 120 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)
⑷、刷新log日志
自此刻开始产生一个新编号的binlog日志文件。
mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| MYON.000002 | 120 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
mysql>
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
⑸、重置(清空)所有binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| MYON.000001 | 120 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
mysql>
⑹、读取binlog日志内容
注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看。
①、mysqlbinlog 命令查看
语法：/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/MYON.000001 -r /home/wwwroot/output.sql
参数： -d -database
-h -host
-p -password
-P -port
--no-defaults # 解决 unknown variable 'default-character-set=utf8mb4' 错误
--start-datetime="2022-10-01 00:00:00" # 开始时间
--stop-datetime="2022-11-09 23:59:59" # 结束时间
--start-position=4 # POS点 开始位置
--stop-position=120 # POS点 截止位置 (结束的POS点是不包含在内)
--base64-output=decode-rows # 在使用mysqlbinlog查看混合模式的二进制文件时，基于行的部分默认是以base64编码显示的,加上版参数对其进行解码。
-vv 显示sql
注意：这里导出的依然是二进制文件内容。
[root@VM-0-12-centos var]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/MYON.000001
/usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8mb4'
# 加上--no-defaults 或者注释my.cnf中的default-character-set=utf8mb4
[root@VM-0-12-centos var]#
[root@VM-0-12-centos var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/var/MYON.000001
[root@VM-0-12-centos var]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/MYON.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
#221109 14:14:02 server id 1 end_log_pos 120 CRC32 0xdf04edf4 Start: binlog v 4, server v 5.6.40-log created 221109 14:14:02 at startup
ROLLBACK/*!*/;
BINLOG '
KkVrYw8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAqRWtjEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfTt
BN8=
'/*!*/;
# at 120
#221109 14:22:01 server id 1 end_log_pos 162 CRC32 0xe9622405 Rotate to MYON.000002 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@VM-0-12-centos var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --start-datetime="2022-10-01 00:00:00" --stop-datetime="2022-11-09 23:59:59" /usr/local/mysql/var/MYON.000001 -r /home/wwwroot/output.sql
[root@VM-0-12-centos var]# cd /home/wwwroot/
[root@VM-0-12-centos var]# ls
default face face.tar.gz output.sql
[root@VM-0-12-centos wwwroot]# vim output.sql
/*!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
#221109 14:14:02 server id 1 end_log_pos 120 CRC32 0xdf04edf4 Start: binlog v 4, server v 5.6.40-log created 221109 14:14:02 at startup
ROLLBACK/*!*/;
BINLOG '
KkVrYw8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAqRWtjEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfTt
BN8=
'/*!*/;
# at 120
#221109 14:22:01 server id 1 end_log_pos 162 CRC32 0xe9622405 Rotate to MYON.000002 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@VM-0-12-centos wwwroot]#
②、show binlog events 查询命令
语法如下：
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
查询第一个(最早)的binlog日志：
mysql> show binlog events;
+-------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+-----+-------------+-----------+-------------+---------------------------------------+
| MYON.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| MYON.000001 | 120 | Rotate | 1 | 162 | MYON.000002;pos=4 |
+-------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.06 sec)
在这期间创建一张数据表，并刷新 binlog 日志：
mysql> show master logs;
+-------------+-----------+
| Log_name | File_size |
+-------------+-----------+
| MYON.000001 | 162 |
| MYON.000002 | 319 |
| MYON.000003 | 1574 |
| MYON.000004 | 120 |
+-------------+-----------+
4 rows in set (0.06 sec)
指定查询 MYON.000003 这个文件，从查询结果我们可以看出，一个语句即一个事务：
mysql> show binlog events in 'MYON.000003';
+-------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| MYON.000003 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| MYON.000003 | 120 | Query | 1 | 333 | use `test`; CREATE TABLE `test`.`user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) |
| MYON.000003 | 333 | Query | 1 | 405 | BEGIN |
| MYON.000003 | 405 | Table_map | 1 | 455 | table_id: 90 (test.user) |
| MYON.000003 | 455 | Write_rows | 1 | 500 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 500 | Xid | 1 | 531 | COMMIT /* xid=494 */ |
| MYON.000003 | 531 | Query | 1 | 603 | BEGIN |
| MYON.000003 | 603 | Table_map | 1 | 653 | table_id: 90 (test.user) |
| MYON.000003 | 653 | Write_rows | 1 | 698 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 698 | Xid | 1 | 729 | COMMIT /* xid=497 */ |
| MYON.000003 | 729 | Query | 1 | 801 | BEGIN |
| MYON.000003 | 801 | Table_map | 1 | 851 | table_id: 90 (test.user) |
| MYON.000003 | 851 | Write_rows | 1 | 896 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 896 | Xid | 1 | 927 | COMMIT /* xid=500 */ |
| MYON.000003 | 927 | Query | 1 | 999 | BEGIN |
| MYON.000003 | 999 | Table_map | 1 | 1049 | table_id: 90 (test.user) |
| MYON.000003 | 1049 | Update_rows | 1 | 1105 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 1105 | Xid | 1 | 1136 | COMMIT /* xid=503 */ |
| MYON.000003 | 1136 | Query | 1 | 1208 | BEGIN |
| MYON.000003 | 1208 | Table_map | 1 | 1258 | table_id: 90 (test.user) |
| MYON.000003 | 1258 | Write_rows | 1 | 1303 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 1303 | Xid | 1 | 1334 | COMMIT /* xid=506 */ |
| MYON.000003 | 1334 | Query | 1 | 1406 | BEGIN |
| MYON.000003 | 1406 | Table_map | 1 | 1456 | table_id: 90 (test.user) |
| MYON.000003 | 1456 | Delete_rows | 1 | 1501 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 1501 | Xid | 1 | 1532 | COMMIT /* xid=509 */ |
| MYON.000003 | 1532 | Rotate | 1 | 1574 | MYON.000004;pos=4 |
+-------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
27 rows in set (0.08 sec)
指定查询 MYON.000003 这个文件，从pos点:500开始查起：
mysql> show binlog events in 'MYON.000003' from 500;
+-------------+------+-------------+-----------+-------------+--------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+------+-------------+-----------+-------------+--------------------------------+
| MYON.000003 | 500 | Xid | 1 | 531 | COMMIT /* xid=494 */ |
| MYON.000003 | 531 | Query | 1 | 603 | BEGIN |
| MYON.000003 | 603 | Table_map | 1 | 653 | table_id: 90 (test.user) |
| MYON.000003 | 653 | Write_rows | 1 | 698 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 698 | Xid | 1 | 729 | COMMIT /* xid=497 */ |
| MYON.000003 | 729 | Query | 1 | 801 | BEGIN |
| MYON.000003 | 801 | Table_map | 1 | 851 | table_id: 90 (test.user) |
| MYON.000003 | 851 | Write_rows | 1 | 896 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 896 | Xid | 1 | 927 | COMMIT /* xid=500 */ |
| MYON.000003 | 927 | Query | 1 | 999 | BEGIN |
| MYON.000003 | 999 | Table_map | 1 | 1049 | table_id: 90 (test.user) |
| MYON.000003 | 1049 | Update_rows | 1 | 1105 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 1105 | Xid | 1 | 1136 | COMMIT /* xid=503 */ |
| MYON.000003 | 1136 | Query | 1 | 1208 | BEGIN |
| MYON.000003 | 1208 | Table_map | 1 | 1258 | table_id: 90 (test.user) |
| MYON.000003 | 1258 | Write_rows | 1 | 1303 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 1303 | Xid | 1 | 1334 | COMMIT /* xid=506 */ |
| MYON.000003 | 1334 | Query | 1 | 1406 | BEGIN |
| MYON.000003 | 1406 | Table_map | 1 | 1456 | table_id: 90 (test.user) |
| MYON.000003 | 1456 | Delete_rows | 1 | 1501 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 1501 | Xid | 1 | 1532 | COMMIT /* xid=509 */ |
| MYON.000003 | 1532 | Rotate | 1 | 1574 | MYON.000004;pos=4 |
+-------------+------+-------------+-----------+-------------+--------------------------------+
22 rows in set (0.09 sec)
指定查询 MYON.000003 这个文件，从pos点:500开始查起，查询5条：
mysql> show binlog events in 'MYON.000003' from 500 limit 5;
show binlog events in 'MYON.000003' from 500 limit 5;
+-------------+-----+------------+-----------+-------------+--------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+-----+------------+-----------+-------------+--------------------------------+
| MYON.000003 | 500 | Xid | 1 | 531 | COMMIT /* xid=494 */ |
| MYON.000003 | 531 | Query | 1 | 603 | BEGIN |
| MYON.000003 | 603 | Table_map | 1 | 653 | table_id: 90 (test.user) |
| MYON.000003 | 653 | Write_rows | 1 | 698 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 698 | Xid | 1 | 729 | COMMIT /* xid=497 */ |
+-------------+-----+------------+-----------+-------------+--------------------------------+
5 rows in set (0.07 sec)
指定查询 MYON.000003 这个文件，从pos点:500 开始查起，偏移2行，查询5条：
mysql> show binlog events in 'MYON.000003' from 500 limit 2,5;
+-------------+------+-------------+-----------+-------------+--------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+------+-------------+-----------+-------------+--------------------------------+
| MYON.000003 | 603 | Table_map | 1 | 653 | table_id: 90 (test.user) |
| MYON.000003 | 653 | Write_rows | 1 | 698 | table_id: 90 flags: STMT_END_F |
| MYON.000003 | 698 | Xid | 1 | 729 | COMMIT /* xid=497 */ |
| MYON.000003 | 729 | Query | 1 | 801 | BEGIN |
| MYON.000003 | 801 | Table_map | 1 | 851 | table_id: 90 (test.user) |
+-------------+------+-------------+-----------+-------------+--------------------------------+
5 rows in set (0.06 sec)
③、通过解析工具 binlog2sql 解析SQL语句
binlog2log是解析binlog文件的一个工具，应该有许多做DBA的使用过这个工具，在使用前需要先进行安装，这里不做多说，可自行百度安装使用。
4、复制模式
修改 myf 文件中的配置参数 binlog-format 就可以改变 bin log 日的复制模式，MySQL复制主要有三种方式：
- STATEMENT：基于SQL语句的复制(statement-based replication, SBR)
- ROW：基于行的复制(row-based replication, RBR)
- MIXED：混合模式复制(mixed-based replication, MBR)。
⑴、STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到 bin log 中。
优点 ：并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
缺点 ：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
⑵、ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。
优点：而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
⑶、MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
5、日志文件
在上面的配置我们知道 log_bin_basename 和 log_bin_index 会根据配置的 log_bin 配置的名称自动生成日志索引文件和二进制日志文件。
bin log日志索引文件用于记录所有的二进制文件，文件名后缀为.index。二进制日志文件记录数据库所有的DDL和DML(除了数据查询语句)语句事件，文件名后缀为.00000*。
当 MySQL 停止或重启时，会在重启时生成一个新的二进制日志文件，文件序号依次递增。如果日志文件超过 max_binlog_size（在myf文件中配置）系统变量配置的上限时，也会生成新的日志文件。除此之外，如果在 MySQL 中执行 flush logs 命令也会生成新的日志文件。
STATEMENT模式 和 ROW模式（RBR） 在文件中存储内容格式是不同的，下面把两种格式的binlog文件内容取出来了简单对比一下，把读取 binlog文件内容这种事一般都是DBA来做，我觉得简单了解即可。
上面配置的是 ROW模式（RBR） 先来看一下它的文件内容（这里查看MYON.000003的文件内容）：
[root@VM-0-12-centos var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -vv /usr/local/mysql/var/MYON.000003
/*!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
#221109 15:58:17 server id 1 end_log_pos 120 CRC32 0xf9dce47d Start: binlog v 4, server v 5.6.40-log created 221109 15:58:17
BINLOG '
mV1rYw8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAX3k
3Pk=
'/*!*/;
# at 120
#221109 16:02:06 server id 1 end_log_pos 333 CRC32 0xfcf2db8d Query thread_id=1623 exec_time=1 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1667980926/*!*/;
SET @@session.pseudo_thread_id=1623/*!*/;
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 utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `test`.`user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
)
/*!*/;
# at 333
#221109 16:02:24 server id 1 end_log_pos 405 CRC32 0xa1f4e7a7 Query thread_id=1623 exec_time=0 error_code=0
SET TIMESTAMP=1667980944/*!*/;
BEGIN
/*!*/;
# at 405
#221109 16:02:24 server id 1 end_log_pos 455 CRC32 0x57e5973e Table_map: `test`.`user` mapped to number 90
# at 455
#221109 16:02:24 server id 1 end_log_pos 500 CRC32 0xafa4d17e Write_rows: table id 90 flags: STMT_END_F
BINLOG '
kF5rYxMBAAAAMgAAAMcBAAAAAFoAAAAAAAEABHRlc3QABHVzZXIAAgMPAvwDAD6X5Vc=
kF5rYx4BAAAALQAAAPQBAAAAAFoAAAAAAAEAAgAC//wBAAAAAwBBQUF+0aSv
'/*!*/;
### INSERT INTO `test`.`user`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='AAA' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
# at 500
#221109 16:02:24 server id 1 end_log_pos 531 CRC32 0xc892b21a Xid = 494
COMMIT/*!*/;
# at 531
#221109 16:02:27 server id 1 end_log_pos 603 CRC32 0xe63abc2c Query thread_id=1623 exec_time=0 error_code=0
SET TIMESTAMP=1667980947/*!*/;
BEGIN
/*!*/;
# at 603
#221109 16:02:27 server id 1 end_log_pos 653 CRC32 0xce6e9141 Table_map: `test`.`user` mapped to number 90
# at 653
#221109 16:02:27 server id 1 end_log_pos 698 CRC32 0x13fa6a1c Write_rows: table id 90 flags: STMT_END_F
BINLOG '
k15rYxMBAAAAMgAAAI0CAAAAAFoAAAAAAAEABHRlc3QABHVzZXIAAgMPAvwDAEGRbs4=
k15rYx4BAAAALQAAALoCAAAAAFoAAAAAAAEAAgAC//wCAAAAAwBCQkIcavoT
'/*!*/;
### INSERT INTO `test`.`user`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='BBB' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
# at 698
#221109 16:02:27 server id 1 end_log_pos 729 CRC32 0xce607ed6 Xid = 497
COMMIT/*!*/;
# at 729
#221109 16:02:33 server id 1 end_log_pos 801 CRC32 0x06a8b5a5 Query thread_id=1623 exec_time=0 error_code=0
SET TIMESTAMP=1667980953/*!*/;
BEGIN
/*!*/;
# at 801
#221109 16:02:33 server id 1 end_log_pos 851 CRC32 0x22ab9309 Table_map: `test`.`user` mapped to number 90
# at 851
#221109 16:02:33 server id 1 end_log_pos 896 CRC32 0x00d1d553 Write_rows: table id 90 flags: STMT_END_F
BINLOG '
mV5rYxMBAAAAMgAAAFMDAAAAAFoAAAAAAAEABHRlc3QABHVzZXIAAgMPAvwDAAmTqyI=
mV5rYx4BAAAALQAAAIADAAAAAFoAAAAAAAEAAgAC//wDAAAAAwBDQ0NT1dEA
'/*!*/;
### INSERT INTO `test`.`user`
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='CCC' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
# at 896
#221109 16:02:33 server id 1 end_log_pos 927 CRC32 0xfdf7f578 Xid = 500
COMMIT/*!*/;
# at 927
#221109 16:02:43 server id 1 end_log_pos 999 CRC32 0x86137e6b Query thread_id=1623 exec_time=0 error_code=0
SET TIMESTAMP=1667980963/*!*/;
BEGIN
/*!*/;
# at 999
#221109 16:02:43 server id 1 end_log_pos 1049 CRC32 0x955a22c8 Table_map: `test`.`user` mapped to number 90
# at 1049
#221109 16:02:43 server id 1 end_log_pos 1105 CRC32 0x77b7d593 Update_rows: table id 90 flags: STMT_END_F
BINLOG '
o15rYxMBAAAAMgAAABkEAAAAAFoAAAAAAAEABHRlc3QABHVzZXIAAgMPAvwDAMgiWpU=
o15rYx8BAAAAOAAAAFEEAAAAAFoAAAAAAAEAAgAC///8AwAAAAMAQ0ND/AMAAAADAENDRJPVt3c=
'/*!*/;
### UPDATE `test`.`user`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='CCC' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='CCD' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
# at 1105
#221109 16:02:43 server id 1 end_log_pos 1136 CRC32 0xaa363d09 Xid = 503
COMMIT/*!*/;
# at 1136
#221109 16:02:46 server id 1 end_log_pos 1208 CRC32 0x2dce89bd Query thread_id=1623 exec_time=0 error_code=0
SET TIMESTAMP=1667980966/*!*/;
BEGIN
/*!*/;
# at 1208
#221109 16:02:46 server id 1 end_log_pos 1258 CRC32 0x8ddc624b Table_map: `test`.`user` mapped to number 90
# at 1258
#221109 16:02:46 server id 1 end_log_pos 1303 CRC32 0xfeb5713f Write_rows: table id 90 flags: STMT_END_F
BINLOG '
pl5rYxMBAAAAMgAAAOoEAAAAAFoAAAAAAAEABHRlc3QABHVzZXIAAgMPAvwDAEti3I0=
pl5rYx4BAAAALQAAABcFAAAAAFoAAAAAAAEAAgAC//wEAAAAAwBEREQ/cbX+
'/*!*/;
### INSERT INTO `test`.`user`
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='DDD' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
# at 1303
#221109 16:02:46 server id 1 end_log_pos 1334 CRC32 0x5a133d1e Xid = 506
COMMIT/*!*/;
# at 1334
#221109 16:02:52 server id 1 end_log_pos 1406 CRC32 0x58144a2b Query thread_id=1623 exec_time=0 error_code=0
SET TIMESTAMP=1667980972/*!*/;
BEGIN
/*!*/;
# at 1406
#221109 16:02:52 server id 1 end_log_pos 1456 CRC32 0xc23e211a Table_map: `test`.`user` mapped to number 90
# at 1456
#221109 16:02:52 server id 1 end_log_pos 1501 CRC32 0xaff6275b Delete_rows: table id 90 flags: STMT_END_F
BINLOG '
rF5rYxMBAAAAMgAAALAFAAAAAFoAAAAAAAEABHRlc3QABHVzZXIAAgMPAvwDABohPsI=
rF5rYyABAAAALQAAAN0FAAAAAFoAAAAAAAEAAgAC//wEAAAAAwBERERbJ/av
'/*!*/;
### DELETE FROM `test`.`user`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='DDD' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
# at 1501
#221109 16:02:52 server id 1 end_log_pos 1532 CRC32 0x5fb11687 Xid = 509
COMMIT/*!*/;
# at 1532
#221109 16:03:26 server id 1 end_log_pos 1574 CRC32 0xd801fc00 Rotate to MYON.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
接下来修改配置文件改变模式为STATEMENT模式：
[root@VM-0-12-centos wwwroot]# vim /etc/my.cnf
binlog-format=STATEMENT
[root@VM-0-12-centos wwwroot]# service mysql restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[root@VM-0-12-centos wwwroot]#
# 并查看 MYON.000005 文件
mysql> show master logs;
+-------------+-----------+
| Log_name | File_size |
+-------------+-----------+
| MYON.000001 | 162 |
| MYON.000002 | 319 |
| MYON.000003 | 1574 |
| MYON.000004 | 143 |
| MYON.000005 | 120 |
+-------------+-----------+
5 rows in set (0.03 sec)
继续对user表做几次 CURD 操作，然后再来看看 MYON.000005 文件内容：
[root@VM-0-12-centos var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/var/MYON.000005
/*!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
#221109 16:28:29 server id 1 end_log_pos 120 CRC32 0x252a2074 Start: binlog v 4, server v 5.6.40-log created 221109 16:28:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
rWRrYw8BAAAAdAAAAHgAAAABAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACtZGtjEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXQg
KiU=
'/*!*/;
# at 120
#221109 16:36:27 server id 1 end_log_pos 199 CRC32 0x5ce7a69d Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1667982987/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
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 utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
# at 231
#221109 16:36:27 server id 1 end_log_pos 231 CRC32 0x4da339f8 Intvar
SET INSERT_ID=4/*!*/;
#221109 16:36:27 server id 1 end_log_pos 353 CRC32 0x3aaf8929 Query thread_id=2 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1667982987/*!*/;
INSERT INTO `test`.`user`(`name`) VALUES ('DDD')
/*!*/;
# at 353
#221109 16:36:27 server id 1 end_log_pos 384 CRC32 0xe0eda80f Xid = 25
COMMIT/*!*/;
# at 384
#221109 16:36:30 server id 1 end_log_pos 463 CRC32 0x2a652e8e Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1667982990/*!*/;
BEGIN
/*!*/;
# at 463
# at 495
#221109 16:36:30 server id 1 end_log_pos 495 CRC32 0xd4ed1dfe Intvar
SET INSERT_ID=5/*!*/;
#221109 16:36:30 server id 1 end_log_pos 618 CRC32 0xffde758c Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1667982990/*!*/;
INSERT INTO `test`.`user`(`name`) VALUES ('EEEE')
/*!*/;
# at 618
#221109 16:36:30 server id 1 end_log_pos 649 CRC32 0xb45c45e1 Xid = 28
COMMIT/*!*/;
# at 649
#221109 16:36:36 server id 1 end_log_pos 728 CRC32 0xcb1993dc Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1667982996/*!*/;
BEGIN
/*!*/;
# at 728
#221109 16:36:36 server id 1 end_log_pos 857 CRC32 0x0390a3a6 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1667982996/*!*/;
UPDATE `test`.`user` SET `name` = 'EEee' WHERE `id` = 5
/*!*/;
# at 857
#221109 16:36:36 server id 1 end_log_pos 888 CRC32 0x52b86d0b Xid = 31
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
6、数据恢复
我们知道binlog主要的功能就是主从复制和数据恢复，这里对主从复制就不讲了，还没操作过。接下来就讲讲怎么通过mysqlbinlog来进程数据恢复。
误删场景：创建一个user表，添加两条记录，然后误删了user表，现在我们要去恢复user表。
⑴、创建误删场景
虽然我这里知道 user表创建和删除操作在哪个binlog文件中，但是实际上一般只记得删除时间而不知道创建时间，所以需要先去找到创建操作在哪个binlog文件中及在文件中的位置。
grep 参数：
-A<显示行数> 或 --after-context=<显示行数> 除了显示符合范本样式的那一列之外，并显示该行之后的内容。
-B<显示行数> 或 --before-context=<显示行数> 除了显示符合样式的那一行之外，并显示该行之前的内容。
创建：
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
mysql> show binlog events;
+-------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+-----+-------------+-----------+-------------+---------------------------------------+
| MYON.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
+-------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.03 sec)
mysql> DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO `user` VALUES (1, 'AAA');
INSERT INTO `user` VALUES (2, 'BBB');
INSERT INTO `user` VALUES (3, 'CCC');
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
+----+------+
3 rows in set (0.04 sec)
mysql>
mysql> drop table user;
Query OK, 0 rows affected (0.04 sec)
mysql> show binlog events;
+-------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MYON.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 |
| MYON.000001 | 120 | Query | 1 | 247 | use `test`; DROP TABLE IF EXISTS `user` /* generated by server */ |
| MYON.000001 | 247 | Query | 1 | 623 | use `test`; CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact |
| MYON.000001 | 623 | Query | 1 | 702 | BEGIN |
| MYON.000001 | 702 | Query | 1 | 812 | use `test`; INSERT INTO `user` VALUES (1, 'AAA') |
| MYON.000001 | 812 | Xid | 1 | 843 | COMMIT /* xid=109 */ |
| MYON.000001 | 843 | Query | 1 | 922 | BEGIN |
| MYON.000001 | 922 | Query | 1 | 1032 | use `test`; INSERT INTO `user` VALUES (2, 'BBB') |
| MYON.000001 | 1032 | Xid | 1 | 1063 | COMMIT /* xid=111 */ |
| MYON.000001 | 1063 | Query | 1 | 1142 | BEGIN |
| MYON.000001 | 1142 | Query | 1 | 1252 | use `test`; INSERT INTO `user` VALUES (3, 'CCC') |
| MYON.000001 | 1252 | Xid | 1 | 1283 | COMMIT /* xid=113 */ |
| MYON.000001 | 1283 | Query | 1 | 1400 | use `test`; DROP TABLE `user` /* generated by server */ |
+-------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.09 sec)
由上面 show binlog events 命令执行结果可以看到 创建时pos点：120 ；删除是pos点：1283 （mysqlbinlog 查询记录只查询结束POS点之前的操作，不包含结束POS点操作）；
⑵、恢复数据
恢复之前为了避免产生没有用的 binlog 日志，可以关闭 binlog 日志的记录，当然这里根据自己的业务场景决定是否需要关闭。
SET SESSION sql_log_bin=0;
恢复完成，启用记录二进制日志
SET SESSION sql_log_bin=1;
如果记录少单次就可以执行完，可直接导入mysql。如果数据较多并且分布在多个binlog日志文件中，而且还掺杂其他的数据表操作时，需要把每个binlog日志文件中关于user表的记录按顺序提取出来再导入数据库。
[root@VM-0-12-centos ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -d test -vv --start-position=120 --stop-position=1283 /usr/local/mysql/var/MYON.000001 | mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 rows in set (0.08 sec)
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
+----+------+
3 rows in set (0.08 sec)
mysql>
下面简单讲一下多个日志文件去恢复数据：
场景：需要删除user222表，但是不小心把user表也删除了。
操作记录 ：从MYON.000001到MYON.000003一共有三个 binlog日志文件，现在需要从三个文件中提取关于user表的操作记录，其中夹杂了user222表的操作记录。
| 日志文件 | user表 | user222表 |
|---|---|---|
| MYON.000001 | 创建user表 | |
| MYON.000002 | user表插入一条数据 | |
| MYON.000002 | 创建user222表 | |
| MYON.000003 | user表插入两条数据 | |
| MYON.000003 | user222表插入一条数据 | |
| MYON.000003 | 删除user表 | |
| MYON.000003 | 删除user222表 |
开始恢复数据：下面就不把日志内容在粘贴出来了，内容比较多。因为我不是专业的DBA，没有专业去解析binlog日志文件的工具，所以接下来我使用 grep 命令去提取user表的操作记录。
注意：这里使用 STATEMENT模式 测试，ROW模式 insert和update日志行数不一样，所以grep命令不好去提取sql执行记录， STATEMENT模式 可以直接提取一行数据方便操作，如果可以的话还是用专业的解析工具去操作。
关于test 数据库下的操作提取出来放入test.sql
[root@VM-0-12-centos ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -d test -vv /usr/local/mysql/var/MYON.000001 > /home/wwwroot/test.sql
[root@VM-0-12-centos ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -d test -vv /usr/local/mysql/var/MYON.000002 >> /home/wwwroot/test.sql
[root@VM-0-12-centos ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -d test -vv /usr/local/mysql/var/MYON.000003 >> /home/wwwroot/test.sql
再从 test数据库操作记录中提取 user表操作
[root@VM-0-12-centos ~]# cat /home/wwwroot/test.sql | grep -A5 -B5 -E 'CREATE TABLE `user`' > /home/wwwroot/user.sql
[root@VM-0-12-centos ~]# cat /home/wwwroot/test.sql | grep -A1 -B3 -i -E '^insert|^update|^delete|^replace|^alter' | grep -A1 -B3 '`user`' >> /home/wwwroot/user.sql
导入sql文件
mysql> use test;
Database changed
mysql> source /home/wwwroot/user.sql;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| chat_word |
| noun |
| noun1 |
| sheet |
| user |
+----------------+
5 rows in set (0.00 sec)
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
+----+------+
3 rows in set (0.00 sec)
mysql>
二、redo log（重做日志）
1、redo log概念
redo log 是属于引擎层(innodb)的日志，它的设计目标是支持innodb的“事务”的特性，事务ACID特性分别是原子性、一致性、隔离性、持久性， 一致性是事务的最终追求的目标，隔离性、原子性、持久性是达成一致性目标的手段，隔离性是通过 锁机制+MVCC 来实现的。 而事务的原子性和持久性则是通过redo log 和 undo log 来保障的。
由于事务 commit 成功后，更新磁盘数据需要一段时间，若此时系统宕机崩溃，redo log 能保证在下一次MySQL服务正常进行时，对于已经 commit 的事务产生的数据变更进行数据恢复，达到数据的一致性，这也就是事务持久性的特征。所以解决异常、宕机而可能造成数据错误或丢失就是 redo log 的核心职责，也称为重做日志。
redo log是在事务begin时就开始记录，并不是事务commit时才记录，因为整个事务做的操作可能很多，如果在commit的时候才写redo log，此时一旦发生异常，redo log还没写，这就太晚了，无法确保事务的持久性，不管事务是否提交都会记录下来，在异常发生时，InnoDB会使用redo log恢复到发生异常前的时刻，保证数据的完整性。
2、redo log相关配置
在控制台查看 redo log 相关配置：
mysql> show variables like 'innodb_log%';
+-----------------------------+----------------------+
| Variable_name | Value |
+-----------------------------+----------------------+
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 33554432 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /usr/local/mysql/var |
+-----------------------------+----------------------+
5 rows in set (0.03 sec)
mysql> show variables like 'innodb_buffer_%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
8 rows in set (0.04 sec)
mysql>
默认情况下，redo log 在磁盘上由名为 ib_logfile0 和 ib_logfile1 的两个文件，从上面的 innodb_log_group_home_dir 参数可以看到，我的服务器中 redo log 文件位置是在 /usr/local/mysql/var。redo log 相关参数简单介绍如下：
innodb_log_buffer_size # redo log buffer 大小，默认16M。延迟事务日志写入磁盘，把 redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit 参数的设置，再把日志从 buffer 中 flush 到磁盘中。
innodb_log_compressed_pages # 判定在压缩page时，是否在redo中存储压缩页数据。
innodb_log_file_size # 单个 redo log 文件设置大小，默认值为 48M，所有redo log 系列文件大小之和最大值为512G，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。
innodb_log_files_in_group # 指定 redo log 文件的个数，默认2，最大100；命名方式如：ib_logfile0，iblogfile1… iblogfilen。
innodb_log_group_home_dir # 指定 redo log 文件组所在的路径，默认./ ，表示在数据库的数据目录下。
MySQL InnoDB缓冲池相关配置：
innodb_buffer_pool_dump_at_shutdown # 默认OFF。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
innodb_buffer_pool_dump_now # 默认是OFF，立即记录在缓冲池中缓存的页面。
innodb_buffer_pool_filename # 指定保存由innodb_buffer_pool_dump_at_shutdown或innodb_buffer_pool_dump_now生成的表空间ID和页面ID列表的文件的名称。
innodb_buffer_pool_instances # 将缓冲池划分为用户指定数量的单独区域，每个区域都有自己的LRU列表和相关数据结构，以减少并发内存读取和写入操作期间的争用。 只有将innodb_buffer_pool_size设置为1GB或更大的值时，此选项才会生效。 您指定的总大小被分配到所有缓冲池中。 为了获得最佳效率，请指定innodb_buffer_pool_instances和innodb_buffer_pool_size的组合，以便每个缓冲池实例至少为1 GB。
innodb_buffer_pool_load_abort # 默认是OFF，中断由innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now触发的缓冲池内容恢复过程。
innodb_buffer_pool_load_at_startup # 默认OFF。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。
innodb_buffer_pool_load_now # 默认是OFF，中断由innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now触发的缓冲池内容恢复过程。
innodb_buffer_pool_size # 默认大小为128M，指定缓冲池的大小。如果缓冲池很小并且有足够的内存，那么通过减少查询访问InnoDB表所需的磁盘I / O量可以提高缓冲池的性能，从而提高性能。 innodb_buffer_pool_size选项是动态的，允许在不重新启动服务器的情况下配置缓冲池大小。
更多的 redo log 配置参数可参考：[玩转MySQL之十]InnoDB Buffer Pool详解。
3、redo log的刷盘策略
在讲redolog的刷盘策略之前，我们需要去了解什么是 Buffer Pool？
此部分转载于：redo log的刷盘策略。
⑴、Buffer Pool（缓冲池）
首先，我们需要知道，页（Pages） 是 InnoDB 中管理数据的最小单元，以页作为磁盘与内存交互的基本单位，一般页的大小为16KB，往 MySQL 插入的数据最终都是存在页中的。在 InnoDB 中的设计中，页与页之间是通过一个双向链表连接起来，而存储在页中的一行一行的数据则是通过单链表连接起来的。
InnoDB存储引擎在处理客户端的请求时，如果需要访问某个页的数据，就会把完整的页中的数据全部加载到内存中，即使只访问页中的一条记录，也需要先把整个页的数据加载到内存中。将整个页加载到内存后就可以进行读写访问了，而且在读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以剩下磁盘IO的开销了。
为了缓存磁盘中的页，Innodb在MySQL服务器启动时就像操作系统申请了一片连续的内存，即 Buffer Pool（缓冲池）。默认情况下，Buffer Pool的大小为 128M。
Buffer Pool对应的一片连续的内存被划分为若干个页，页大小与Innodb表空间用的页大小一致，默认都是16kb，为了与磁盘中的页面区分开来，我们把这些Buffer Pool中的页面称为 缓冲页。
当我们修改了Buffer Pool中某个缓冲页的数据，它就与磁盘上的页不一致了，这样的缓冲页称为 脏页。当然，我们可以每当修改完某个数据页时，就立即将其刷新到磁盘中对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能，所以每次修改缓冲页后，我们并不着急立即将修改刷新到磁盘上，而是在某个时间点进行刷新。
后台有专门的线程负责每隔一段时间就把脏页刷新到磁盘，这样就可以不影响用户线程处理正常的请求。
总结：InnoDB存储引擎是以页为单位来管理存储空间的，在真正访问页面之前，需要先把磁盘中的页加载到内存中的Buffer Pool中，之后才可以访问，所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页以一定的频率刷新到磁盘（checkpoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就能保证整体的性能不会下降的太快。
checkpoint机制 可参考 14.Mysql之redo log -checkpoint浅谈01。
⑵、redo log 的好处
相较于在事务提交时将所有修改过的页刷新到磁盘中，只将该事务执行过程中产生的redo日志刷次年到磁盘，有下面的好处：
- (1) redo日志降低了刷盘频率。
- (2) redo日志占用的空间非常小。
- (3) redo日志是顺序写入磁盘的。在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是顺序IO。
InnoDB存储引擎的事务采用了WAL技术（Write-Ahead Logging），这种技术就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷新到磁盘的时候，可以通过redo log恢复过来，保证事务的持久性。
redo log跟bin log的区别：redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做了10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入bin log文件中。
⑶、redo log 的组成
重做日志缓冲区（redo log buffer）：保存在内存中，是易丢失的。InnoDB为了解决磁盘速度过慢的问题而引入了Buffer Pool，同理，写入redo log日志时，也不能直接写到磁盘中，实际上在服务器启动时就向操作系统申请了一片称为 redo log buffer 的连续存储空间。
重做日志文件 (redo log file) ：保存在硬盘中，是持久的。redo日志总存放在内存 redo log buffer 中待着也不是个办法，在一些情况下他们会被刷新到磁盘中。mysql的数据目录下默认有名为ib_logfile0和 ib_logfile1 两个文件，redo log buffer中的日志在默认情况下就是刷新到这两个磁盘文件中，即redo log file。
⑷、redo log 刷盘时机
-
log buffer空间不足时： log buffer的大小是有限的，如果不停的向这个有限大小的log buffer中塞入日志，很快就会将它填满，设计InnoDB的大叔认为，如果当前写入log buffer的redo日志量已经占满了log buffer总容量的50%左右，就需要将这些日志刷新到磁盘中。
-
事务提交时： 之所以提出redo log的概念，主要是因为它占用的空间少，而且可以将其顺序写入磁盘，引入redo日志之后，虽然在事务提交时可以不把修改过的buffer pool页面立即刷新到磁盘，但是为了保持持久性，必须要把页面修改时所对应的redo日志刷新到磁盘，否则系统崩溃后，无法将该事务对页面所做的修改恢复过来。
-
将某个脏页刷新到磁盘前，会先保证该脏页对应的redo日志刷新到磁盘中： redo日志是顺序写入的，因此在将某个脏页对应的redo日志从redo log buffer刷新到磁盘中时，也会保证将在其之前产生的redo日志也刷新到磁盘中。
-
后台有一个线程，大约以每秒一次的频率将redo log buffer中的redo日志刷新到磁盘中。
-
做checkpoint时。
⑸、redo log 的整体流程
以一个更新事务为例，redo log 流转过程，如下图所示：
- 第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝 。
- 第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值 。
- 第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式 。
- 第4步：定期将内存中修改的数据刷新到磁盘中。
Write-Ahead Log(预先日志持久化)：在持久化一个数据页之前，先将内存中相应的日志页持久化。
⑹、redo log 的刷盘策略
redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以 一 定的频率刷入到真正的redo log file 中。这里的一定频率怎么看待呢？这就是我们要说的刷盘策略。
redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存 （page cache）中去，真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同 步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。
针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务时，如何将 redo log buffer中的日志刷新到 redo log file 中。它支持三种策略：
- （1）设置为0 ：表示每次事务提交时不进行刷盘操作。默认master thread每隔1s进行一次重做日志的同步。
- （2）设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）。
- （3）设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。
InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存（page cache）,然后调用刷盘操作。
一个没有提交事务的redo log日志，也可能会刷盘，因为在事务执行过程redo log记录是会写入redo log buffer中，这些redo log记录会被后台线程刷盘。
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.07 sec)
mysql>
可以看到刷盘策略默认为1。
⑺、redo log 的不同刷盘策略演示
①、innodb_flush_log_at_trx_commit = 1时
当 innodb_flush_log_at_trx_commit = 1时，redo log记录就一定在硬盘里，不会有任何数据丢失，如果在事务执行期间MySQL挂了或者宕机了，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失，可以保证ACID的D，数据绝对不会丢失，但是效率是最差的。
建议使用默认值，虽然操作系统宕机的概率理论小于数据库宕机的概率，但是一般既然使用了事务，那么树的安全性相对来说就更重要些。
②、innodb_flush_log_at_trx_commit = 2时
innodb_flush_log_at_trx_commit = 2 时，只要事务提交成功，redo log buffer中的内容只写入文件系统缓存，如果只是MySQL挂了不会有任何数据丢失，但是操作系统宕机可能会有1秒数据的丢失，这种情况下无法满足ACID中的D，但是数值2是效率最高的。
③、innodb_flush_log_at_trx_commit = 0时
innodb_flush_log_at_trx_commit = 0 时，后台线程每隔1秒进行一次重做日志的fsync操作，因此示例crash最多丢失1秒钟内的事务。后台线程是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。
4、两阶段提交
如果系统成功的把数据成功的存储到了redo log中，然后再去存到bin log的时候发生了异常，那么就会造成数据的不一致性。
为了解决上述的问题，mysql采用两段式提交的方法，具体的方法是：
- （1）首先在写入到redo log的时候先进行一个prepare准备的阶段，还没有完全成功此时。
- （2）然后再把数据写入到bin log中。
- （3）最后再进行最后的确认，如果没有写入bin log没有问题，就commit，完成写入redo log的操作。
使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。
三、undo log（回滚日志）
undo log 是 MySQL 用来记录事务操作的 反方向逻辑日志，顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。
有关undo log 内容基本上都转载于:图文结合带你搞定MySQL日志之Undo log(回滚日志)。
1、undo log的功能
- 提供数据回滚： 当事务回滚时或者数据库崩溃时，可以利用 undo log来进行数据回滚。
- 多个行版本控制(MVCC)： 即在InnoDB存储引擎中MVCC的实现是通过 undo 来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo 读取之前的行版本信息，以此实现非锁定读取。
此外，undo log会产生 redo Log，也就是undo log的产生会伴随着 redo log的产生，这是因为undo log也需要持久性的保护。
2、undo log的存储结构
⑴、InnoDB数据文件存储结构
InnoDB数据文件存储结构：
分为一个ibd数据文件-->Segment（段）-->Extent（区）-->Page（页）-->Row（行）
-
Tablesapce 表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。
-
Segment 段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf nodesegment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。
-
Extent 区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会一页一页分，直接分配一个区。
-
Page 页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。
-
Row 行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息。
⑵、undo log存储及相关配置
undo log的存储由InnoDB存储引擎实现，数据保存在InnoDB的数据文件中，innodb存储引擎对undo的管理采用 段（segment）的方式，具体来说是一种命名为 回滚段（rollback segment） 的数据结构。每个回滚段中有1024个 undo log segment，而在每个Undo Log segment段中进行Undo页的申请。
mysql> show variables like '%undo%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+-------------------------+-------+
3 rows in set (0.06 sec)
innodb_undo_directory：设置rollback segment文件所在的路径。这意味着rollback segment可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为“.”，表示当前InnoDB存储引擎的目录。
innodb_undo_logs：设置rollback segment的个数，默认值为128。在InnoDB1.2版本中，该参数用来替换之前版本的参数innodb_rollback_segments。
innodb_undo_tablespaces：设置构成rollback segment文件的数量，这样rollback segment可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件，该文件就代表rollback segment文件。
注意： 所有的 undo log 均存放在表空间中，即 ibdata1文件（系统表空间） 或 undo 表空间（ 以 undo 为前缀的文件）。
innodb_undo_tablespaces = 0时，回滚段（rollback segment）默认记录到ibdata1文件（即InnoDB的共享表空间，或者系统表空间）中。ibdata1文件存放数据，索引和缓存等，是MYSQL的最主要的数据。
innodb_undo_tablespaces > 0时，回滚段（rollback segment）会平均分布到各个undo 表空间（ 以 undo 为前缀的文件）中。
虽然undo log单独拆出来后文件变小了，但随着时间推移一样会越来越大。MySQL 5.7引入了两个新的参数，开启后可在线收缩拆分出来的undo表空间。在满足以下2个条件下，undo表空间文件可在线收缩：
innodb_max_undo_log_size ：默认1024M，表示限制undo log的最大值，如果超过临界值，当启用了 innodb_undo_log_truncate的话，undo会被标记为可truncate。即可开启在线收缩undo日志。
innodb_undo_log_truncate：默认OFF，开启在线回收（收缩）undo log日志文件，支持动态设置，默认是关闭的
- innodb_undo_tablespaces>=2。因为truncate undo表空间时，该文件处于inactive状态，如果只有1个undo表空间，那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响，建议将该参数最少设置为3；
- innodb_undo_logs>=35(默认128)。因为在MySQL 5.7中，第一个undo log永远在系统表空间中，另外32个undo log分配给了临时表空间，即ibtmp1，至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log；
- 即：
N个ibtmp1（临时表空间）+ innodb_undo_tablespaces + ibdata1 = innodb_undo_logs；
- 即：
满足以上2个条件后，即可修改参数 innodb_undo_log_truncate 和 innodb_max_undo_log_size 配置，开启undo表空间的自动truncate。
⑶、回滚段与事务
-
①、每个事务只会使用一个回滚段（rollback segment），一个回滚段在同一时刻可能会服务于多个事务。
-
②、当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。
-
③、在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用。
-
④、回滚段存在于 undo 表空间中，在数据库中可以存在多个 undo 表空间，但同一时刻只能使用一个 undo 表空间。
-
⑤、当事务提交时，InnoDB存储引擎会做以下两件事情：
-
将undo log放入列表中，以供之后的
purge(清洗、清除)操作。 -
判断undo Log所在的页是否可以重用(低于3/4可以重用)，若可以分配给下个事务使用。
-
⑷、回滚段中的数据分类
未提交的回滚数据(uncommitted undo information)：该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖。
已经提交但未过期的回滚数据(committed undo information)：该数据关联的事务已经提交，但是仍受到undo retention参数的保持时间的影响。
事务已经提交并过期的数据(expired undo information)：事务已经提交，而且数据保存时间已经超过undo retention参数指定的时间，属于已经过期的数据。当回滚段满了之后，会优先覆盖"事务已经提交并过期的数据"。
⑸、undo页的重用
当我们开启一个事务需要写undo log的时候，就得先去undo log segment中去找到一个空闲的位置，当有空位的时候，就去申请 undo页，在这个申请到的undo页中进行undo log的写入。我们知道MySQL默认一页的大小是16k。
为每一个事务分配一个页，是非常浪费的(除非你的事务非常长)，假设你的应用的TPS(每秒处理的事务数目)为1000，那么1s就需要1000个页，大概需要16M的存储，1分钟大概需要1G的存储。如果照这样下去除非MySQL清理的非常勤快，否则随着时间的推移，磁盘空间会增长的非常快，而且很多空间都是浪费的。
于是 undo 页就被设计的可以重用了，当事务提交时，并不会立刻删除undo页。因为重用，所以这个undo页可能混杂着其他事务的undo log。undo log在commit后，会被放到一个链表中，然后判断undo页的使用空间是否小于3/4，如果小于3/4的话，则表示当前的undo页可以被重用，那么它就不会被回收，其他事务的undo log可以记录在当前undo页的后面。由于undo Log是离散的，所以清理对应的磁盘空间时，效率不高。
⑹、undo log日志的存储机制
如上图，可以看到，Undo Log日志里面不仅存放着 数据更新前的记录，还记录着 RowID、事务ID、回滚指针。其中事务ID每次递增，回滚指针第一次如果是INSERT语句的话，回滚指针为NULL，第二次UPDATE之后的Undo Log的回滚指针就会指向刚刚那一条Undo Log日志，以此类推，就会形成一条Undo Log的回滚链，方便找到该条记录的历史版本。
3、undo log的工作原理
在更新数据之前，MySQL会提前生成Undo Log日志，当事务提交的时候，并不会立即删除Undo Log，因为后面可能需要进行回滚操作，要执行回滚（ROLLBACK）操作时，从缓存中读取数据。Undo Log日志的删除是通过通过后台purge线程进行回收处理的。
- 1、事务A执行UPDATE操作，此时事务还没提交，会将数据进行备份到对应的Undo Buffer，然后由Undo Buffer持久化到磁盘中的Undo Log文件中，此时Undo Log保存了未提交之前的操作日志，接着将操作的数据，也就是test表的数据持久保存到InnoDB的数据文件IBD。
- 2、此时事务B进行查询操作，直接从Undo Buffer缓存中进行读取，这时事务A还没提交事务，如果要回滚（ROLLBACK）事务，是不读磁盘的，先直接从Undo Buffer缓存读取。
4、undo log的类型
在InnoDB存储引擎中，Undo Log分为：
-
insert Undo Log是指在insert操作中产生的Undo Log。因为insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该Undo Log可以在事务提交后直接删除。不需要进行purge操作。 -
update Undo Log记录的是对delete和update操作产生的Undo Log。该Undo Log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入Undo Log链表，等待purge线程进行最后的删除。
5、清空(purge)操作
purge 到底做了些什么？其实它就相当于一个垃圾收集器。取个例子，当用户下一个命令，如 “DELETE FROM t WHERE c = 1;”,
InnoDB 不会马上删除对应的记录，它会做如下三件事情:
-
它标记此记录为删除(通过删除标记位)
-
存储原始的记录到UNDO log
-
更新记录列DB_TRX_ID和DB_ROLL_PTR(这些列是Innodb在原记录列上增加的)。
-
DB_TRX_ID记录了最后操作记录的事务ID。
-
DB_ROLL_PTR也叫回滚指针(rollback pointer)，指向UNDO log 记录，此UNDO Log记录了原始记录的信息，这些信息可以用来重建原始记录(如发生了rollback的情况)。
-
如果操作是插入，还会有一个DB_ROW_ID，这个指明了新记录的行号.
-
当事务提交后，那些标记了删除的记录，以及UNDOLog中的记录并不会马上清除，这些记录信息可以被其它事务所重用，或是共享。只有当没有任何事务共享这些记录的时候，这些记录才会被清除(purge)。这就是所谓purge操作。而为了提高数据库的操作效率，purge操作是由另外的线程异步完成。
6、undo log生命周期
以下是Undo+Redo事务的简化过程:
假设有2个数值，分别为A=1和B=2，然后将A修改为3，B修改为4。
1. start transaction;
2. 记录A=1到Undo Log;
3. update A = 3;
4．记录A=3 到Redo Log;
5．记录B=2到Undo Log;
6. update B = 4;
7．记录B = 4到Redo Log;
8．将Redo Log刷新到磁盘;
9. commit
在1-8步骤的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。
如果在8-9之间宕机：Redo Log 进行恢复，Undo Log 发现有事务没完成进行回滚。
若在9之后系统宕机：内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据Redo Log把数据刷回磁盘。
四、error log（错误日志）
错误日志（Error Log）是 MySQL 中最常用的一种日志，主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。
在 MySQL 数据库中，默认开启错误日志功能。一般情况下，错误日志存储在 MySQL 数据库的数据文件夹下，通常名称为 hostname.err。其中，hostname 表示 MySQL 服务器的主机名。
启用配置
在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义，其中，log-err 定义是否启用错误日志功能和错误日志的存储位置，log-warnings 定义是否将警告信息也记录到错误日志中。
将 log_error 选项加入到 MySQL 配置文件 myf 的 [mysqld] 组中，形式如下：
[mysqld]
log-error=dir/{filename}
mysql> show variables like 'log_error%';
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| log_error | ./VM-0-12-centos.err |
+---------------+----------------------+
1 row in set (0.06 sec)
错误日志以文本文件的形式存储，直接使用普通文本工具就可以查看。
在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，也可以使用手工方式来删除（删除之后需要重新启动 MySQL 服务，重启之后就会生成新的错误日志），以保证 MySQL 服务器上的硬盘空间。mysqladmin 命令的语法如下：
mysqladmin -uroot -p flush-logs
执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old。
MySQL 服务器发生异常时，管理员可以在错误日志中找到发生异常的时间、原因，然后根据这些信息来解决异常。对于很久之前的错误日志，查看的可能性不大，可以直接将这些错误日志删除。
五、slow query log（慢查询日志）
慢查询日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。
通俗的说，MySQL 慢查询日志是 排查问题的 SQL 语句，以及 检查当前 MySQL 性能 的一个重要功能。如果不是调优需要，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
更详细的优化步骤可参考另一篇博客：MySQL优化。
慢查询日志相关参数：
mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/var/VM-0-12-centos-slow.log |
+---------------------+----------------------------------------------+
2 rows in set (0.07 sec)
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.08 sec)
参数说明如下：
- slow_query_log：慢查询开启状态
- slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录）
- long_query_time：查询超过多少秒才记录
启用配置
可以通过 log-slow-queries 选项开启慢查询日志。通过 long_query_time 选项来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。
将 log_slow_queries 选项和 long_query_time 选项加入到配置文件的 [mysqld] 组中。格式如下：
[mysqld]
log-slow-queries=dir\filename
long_query_time=n
其中：
- dir 参数指定慢查询日志的存储路径，如果不指定存储路径，慢查询日志将默认存储到 MySQL 数据库的数据文件夹下。
- filename 参数指定日志的文件名，生成日志文件的完整名称为 filename-slow.log。 如果不指定文件名，默认文件名为 hostname-slow.log，hostname 是 MySQL 服务器的主机名。
“n”参数是设定的时间值，该值的单位是秒。如果不设置 long_query_time 选项，默认时间为 10 秒。
还可以通过以下命令启动慢查询日志、设置指定时间：
SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;
删除慢查询日志和通用日志的删除方法是一样的。可以使用 mysqladmin 命令来删除。也可以使用手工方式来删除。
六、general log（一般查询日志）
通用查询日志（General Query Log）用来记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。
如果希望了解用户最近的操作，可以查看通用查询日志。通用查询日志以文本文件的形式存储，可以使用普通文本文件查看该类型日志内容。
启用配置
在 MySQL 中，可以通过在 MySQL 配置文件添加 log 选项来开启通用查询日志，格式如下：
[mysqld]
log=dir/filename
其中，
- dir 参数指定通用查询日志的存储路径；
- filename 参数指定日志的文件名。如果不指定存储路径，通用查询日志将默认存储到 MySQL 数据库的数据文件夹下。如果不指定文件名，默认文件名为 hostname.log，其中 hostname 表示主机名。
mysql> show variables like '%general%';
+------------------+-----------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/mysql/var/VM-0-12-centos.log |
+------------------+-----------------------------------------+
2 rows in set (0.07 sec)
mysql>
删除方式同上。
七、relay log（中继日志）
详细配置可参考：MySQL的日志 - relay log。
relay log（中继日志）一般情况下它在MySQL主从同步读写分离集群的从节点才开启。主节点一般不需要这个日志。它是依赖于 bin log 的日志，格式也和 bin log 一样。是MySQL复制进程把 别的实例的binlog 复制到本地后，就叫做 relay log。
作用是为了MySQL高可用复制服务的一种日志。
普通研发人员不需要深入研究只需要知道relay log是用于主从同步读写分离的。
更多推荐
MySQL的核心日志
发布评论