# 日志
mysqld 可以产生多种日志，不过默认只启用了错误日志。
# Error Log
：错误日志。用于记录 mysqld 启动、停止、报错的日志。
- 相关配置：
[mysqld] log_error = /var/lib/mysql/mysql-error.log # 错误日志的保存路径。默认为 log_error=stderr ，即输出到终端 - 日志内容示例：
2020-01-12T09:09:17.376824Z 0 [Note] Server socket created on IP: '0.0.0.0'. # 创建 Socket 2020-01-12T09:09:17.385746Z 0 [Note] Event Scheduler: Loaded 0 events 2020-01-12T09:09:17.385880Z 0 [Note] mysqld: ready for connections. # mysqld 启动成功，允许被连接 Version: '5.7.26-29-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 29, Revision 11ad961 2020-01-12T09:34:51.166819Z 5 [Note] Access denied for user root@'localhost' (using password: YES) # 客户端连接出错（连接成功则不会在该日志中记录） - 相关命令：
flush logs; -- 关闭并重新打开所有类型的日志文件，这会将内存中的日志缓冲立即写入磁盘 flush binary logs; -- 关闭当前打开的 binlog 文件，并创建一个新的 binlog 文件供写入 flush engine logs; flush error logs; flush general logs; flush slow logs; flush replay logs;
# General Query Log
：通用查询日志。用于记录 mysqld 执行过的所有 SQL 命令。
- 相关配置：
[mysqld] general_log = on # 是否启用。默认禁用，因为会拖慢 MySQL 的处理速度 general_log_file = /var/lib/mysql/mysql.log - 日志内容示例：
Time Id Command Argument 2020-01-12T09:10:00.371447Z 2 Connect root@localhost on using Socket # 客户端建立连接 2020-01-12T09:10:00.371642Z 2 Query SELECT @@version_comment LIMIT 1 2020-01-12T09:10:24.049055Z 2 Query SHOW variables LIKE '%time_zone%' # 执行一条 SQL 命令 2020-01-12T09:34:34.644737Z 2 Query SHOW tables 2020-01-12T09:34:43.278005Z 2 Quit # 客户端断开连接- Id 表示这是 mysql 启动之后，第几次建立的客户端连接。
# Slow Query Log
：慢查询日志，简称为慢日志。用于记录耗时较久的查询操作，方便用户排查问题、进行优化。
相关配置：
[mysqld] slow_query_log=on # 是否启用慢日志。默认禁用 long_query_time=10 # 慢查询的阈值，默认为 10 秒，超过该时长的查询操作才会被记录 log_queries_not_using_indexes=off # 是否记录未使用索引的查询操作 slow_query_log_file='/var/lib/mysql/mysql-slow.log' # 日志文件的保存路径，默认为 <hostname>-slow.log- long_query_time 的最小值为 0 ，表示记录所有查询操作，但记录大量慢日志会降低 MySQL 的性能。
也可以在 MySQL 终端中配置，当 MySQL 重启时就会失效：
mysql> SET global slow_query_log=on; mysql> SET global long_query_time=3;直接查看慢日志文件比较麻烦，可以用 mysqldumpslow 命令进行筛选：
mysqldumpslow <slow.log> -s # 指定排序方式，默认为 at c # count ，访问次数 l # lock time ，锁定时间 r # rows sent ，返回的记录数 t # query time ，查询时间 al # average rows sent ar # average query time at # average lock time -r # 倒序排列 -t 10 # 只显示前 10 条 -g PATTERN # 通过正则匹配进行筛选例：
mysqldumpslow -s r -t 10 slow.log
# Binary Log
：二进制日志。用于记录 MySQL 执行过的所有 DDL、DML 语句。默认禁用。
- 优点：可用于拷贝数据、备份数据。
- 缺点：会让 MySQL 执行事务时稍微变慢。
- 一个事务可能包含一组事件（event），比如 INSERT、UPDATE 。因此 binlog 记录日志的基础单位是 event 。
- mysqld 会将 binlog 保存为磁盘上的多个文件。
- binlog 文件中，每个 event 有确定的字节偏移量 start-position、stop-position ，而事务也是根据 position 来定位。
# 相关配置
[mysqld]
server_id = 1 # 启用 binlog 时，必须指定服务器的唯一 ID ，以支持主从同步
log_bin = mysql-bin # 启用 binlog ，并指定其保存路径，默认在 datadir 目录下。实际保存时会加上时间字符串作为后缀
# log_bin_index = mysql-bin.index # binlog 的索引文件的保存路径，默认为 ${log_bin}.index
# binlog_format = row # binlog 的记录格式
# binlog_row_image = full # binlog 采用 row 记录格式时，每修改一行数据，记录哪些列。默认为 full ，表示记录所有列。改为 minimal ，则只记录被修改的部分列
# binlog_cache_size = 32k # binlog 的内存缓冲区大小，单位为 bytes 。超出缓冲区的事务会存储到磁盘的临时文件中
sync_binlog = 0 # 每提交几次事务就写入 binlog 文件。默认为 1 ，安全性最高，但性能最差。取值为 0 则由文件系统自动写入，性能最好
expire_logs_days = 7 # binlog 文件的保存天数，过期后会自动删除。默认为 0 ，即不自动删除
# max_binlog_size = 1G # 每个 binlog 文件的最大大小，超过则创建一个新的文件，文件编号递增。不过一个事务总是会整个写入一个 binlog 文件中，因此一个 binlog 文件可能超过最大大小
gtid_mode = on # 是否启用 gtid ，默认为 off
enforce-gtid-consistency = on # 是否让 master 只执行支持 gtid 的事务，默认为 off
# binlog-do-db = db1 # 记录该数据库（其它的默认不记录）
# binlog-do-db = db2 # 可以多次配置该参数
# binlog-ignore-db= db1 # 不记录该数据库（其它的默认记录）
# binlog-ignore-db= db2
- binlog 有三种记录格式：
- statement
- ：记录 MySQL 执行的每条写操作的 SQL 命令.
- 优点：日志量较少。
- 缺点：还原数据时，SQL 命令的执行结果不一定与原来一致。比如重复执行
UPDATE tb1 SET datetime=now() WHERE id=1;时，now() 函数的返回值不同。
- row
- ：记录 MySQL 对每行数据做出的实际修改，记录成 Write_rows、Update_rows、Delete_rows 事件。
- 假设一条 UPDATE 语句修改了 5 条数据，则 statement 格式只会记录一条 SQL 命令，而 row 格式会记录 5 条被修改的数据内容。
- 优点：还原数据时，能保证与原来的数据一致。
- 缺点：日志量较多。且只能记录 INSERT、UPDATE、DELETE 三种 DML 语句修改的数据，而 DDL 语句依然以 statement 格式记录，记录成 create table、alter table、drop table 等日志。
- mixed
- ：一般的操作用 statement 格式记录，状态可能变化的操作用 row 格式记录。
- statement
- binlog 根据 position 定位每个事务，因此主从复制时，各个事务必须按顺序传输。
- 启用 gtid 时，会给每个已提交事务分配一个全局事务 ID（Global Transaction ID ，GTID），格式为
server_uuid:transaction_id。- 此时不需要根据 position 定位事务，因此不需要按顺序传输事务，还可以避免 slave 重复执行同一事务。
- 启用 gtid 时，会给每个已提交事务分配一个全局事务 ID（Global Transaction ID ，GTID），格式为
# 相关命令
显示 binlog 的配置：
mysql> SHOW variables LIKE '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | -- 说明是否启用了 binlog | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | -- 是否为当前 session 启用 binlog +---------------------------------+--------------------------------+显示 binlog 的内容：
mysql> SHOW binlog events in 'mysql-bin.000060' LIMIT 7; +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+ | mysql-bin.000060 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.25-15, Binlog ver: 4 | -- binlog 固定格式的开头 | mysql-bin.000060 | 125 | Previous_gtids | 1 | 196 | f75d3723-1c3e-11ec-a666-0242c0a8f002:1-7754 | | mysql-bin.000060 | 196 | Gtid | 1 | 275 | SET @@SESSION.GTID_NEXT= 'f7343723-8di7-11ec-aj66-0r36c0a8f002:7455' | | mysql-bin.000060 | 275 | Query | 1 | 370 | BEGIN | -- 开始一个事务 | mysql-bin.000060 | 370 | Table_map | 1 | 449 | table_id: 85 (db1.tb1) | | mysql-bin.000060 | 449 | Update_rows | 1 | 619 | table_id: 85 flags: STMT_END_F | | mysql-bin.000060 | 619 | Xid | 1 | 650 | COMMIT /* xid=351 */ | -- 结束一个事务 +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+其它命令：
SHOW binary logs; -- 显示当前所有 binlog 文件 SHOW binlog events in 'mysql-bin.000001' -- 显示一个 binlog 的内容 FROM 0 LIMIT 2 -- 显示从 position=0 开始的最多 2 个 event SHOW master status; -- 显示 master 的状态 reset master; -- 删除所有 binlog 及其索引，重新开始记录 purge master logs to 'mysql-bin.000001'; -- 删除指定的 binlog在 shell 中，可用官方提供的 mysqlbinlog 命令解析 binlog 的内容。如下：
mysqlbinlog mysql-bin.000001 --database=db1 # 只显示指定数据库的记录 --start-position=0 --stop-position=177 --start-datetime='2019-12-01 12:00:00' --stop-datetime='2019-12-02 12:00:00' -vv --base64-output=decode-rows # row 格式的 binlog 只记录了修改的数据内容，可加上该选项，解析成 DML 语句（位于注释中）将 binlog 转换成.sql 文件之后，便可以导入数据库，还原数据。如下：
mysqlbinlog mysql-bin.000001 > tmp.sql mysql -u root -p < tmp.sql
# Transaction Log
- InnoDB 引擎默认启用了事务日志（Transaction Log），分为两种：
- 重做日志（redo log）
- 回滚日志（undo log）
# redo log
：属于预写日志（WAL），用于在数据库崩溃之后重启时，重新提交最近一些尚未保存到磁盘的事务。
InnoDB 引擎每次提交事务时，会用 redo log 记录相应数据页的变化内容。
- 新增的事务先记录到内存的 redo log buffer ，缓冲了一定数量才写入磁盘的 redo log file ，并记录此时的 LSN 。
- redo log file 缓冲了一定数量，才 flush 到磁盘，实际完成事务。
- redo log file 的大小固定，会从头到尾循环写入。
- MySQL 重启时，会恢复 redo log file 中尚未 flush 到磁盘的数据页。
- 新增的事务先记录到内存的 redo log buffer ，缓冲了一定数量才写入磁盘的 redo log file ，并记录此时的 LSN 。
新增一个事务时，InnoDB 引擎通过二阶段提交确保将它同时记录到 redo log 和 binary log 。
- 先将该事务记录到 redo log buffer ，标记为 prepare 状态，表示预提交。
- 然后将该事务记录到 binary log 。
- 最后在 redo log buffer 中将该事务标记为 commit 状态，表示已提交。实际上不一定写入了磁盘。
- 返回响应给客户端。
相关配置：
innodb_log_buffer_size = 16777216 # redo log buffer 的大小，默认为 16M innodb_log_file_size = 50331648 # redo log file 的大小，默认为 48M innodb_log_files_in_group = 2 # redo log file 的数量，默认为 2 个，名为 ib_logfile0、ib_logfile1 innodb_log_group_home_dir = ./ # redo log file 的保存目录，默认在数据目录下 innodb_flush_log_at_trx_commit = 1 # redo log 的 flush 策略，取值如下： # 0 ：每隔一秒，就将 redo log buffer 新增的事务写入 redo log file ，并 flush 到磁盘 # 1 ：默认值。redo log buffer 每次新增事务时，都写入 redo log file ，并 flush 到磁盘，实现严格的事务 # 2 ：redo log buffer 每次新增事务时，都写入 redo log file ，但每隔一秒才 flush 到磁盘关闭 redo log ：
ALTER INSTANCE DISABLE INNODB REDO_LOG; -- 关闭 redo log SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled'; -- 查看是否启用 ALTER INSTANCE ENABLE INNODB REDO_LOG; -- 启用 redo log