MySQL-日志

前言
一、错误日志（error log）
二、慢查询日志(slow query log)
三、一般查询日志(general log)
四、事务日志
- 重做日志（redo log）
- 回滚日志（undo log）
五、二进制日志(bin log)/归档日志 => 数据同步和数据恢复
总结

前言

日志用来做什么？

记录了很多关于程序运行状态的信息（正常、出错…）

用于排错
了解MySQL性能（速度）运行情况…
数据的备份和恢复

一、错误日志（error log）

记录MySQL启动、关闭、运行过程中的错误信息
配置方式
my.cnf文件

[mysqld]
log-error=/var/log/mysqld.log

查看错误日志路径

root@test 15: 38>show variables like 'log_error';
±--------------±---------------------------+
| Variable_name | Value |
±--------------±---------------------------+
| log_error | /data/mysql/mysqld3308.err |
±--------------±---------------------------+
1 row in set (0.00 sec)

二、慢查询日志(slow query log)

记录MySQL中响应时间超过阈值的SQL语句信息

作用：记录消耗时间较长的SQL语句，为数据库性能提升提供了线索（DBA/开发人员）
配置方式

my.cnf

[mysqld]
slow_query_log=1 # on
slow_query_log_file=/data/mysql/mysqld_query.log
long_query_time=10 # 默认10s，如果sql语句执行超过10s，将会记录下来

分析慢日志：mysqldumpslow

查看与慢查询时间阈值

root@test 15: 48>show variables like '%long_query%';
±----------------±----------+
| Variable_name | Value |
±----------------±----------+
| long_query_time | 10.000000 |
±----------------±----------+

慢查询日志开关及路径

root@test 15: 48>show variables like '%slow_query%';
±--------------------±-----------------------------+
| Variable_name | Value |
±--------------------±-----------------------------+
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/mysqld_query.log |
±--------------------±-----------------------------+

三、一般查询日志(general log)

记录客户端连接服务端的信息以及执行SQL语句的信息
执行的SQL命令，执行结果（成功、失败原因）
从性能考虑，默认没有开启

[mysqld]
general_log=1
general_log_file=/data/mysql/mysqld_general.log

查看一般查询日志开关及路径

root@test 15: 48>show variables like '%general_log%';
±-----------------±-------------------+
| Variable_name | Value |
±-----------------±-------------------+
| general_log | OFF |
| general_log_file | /data/mysql/db.log |
±-----------------±-------------------+

四、事务日志

重做日志（redo log）

是MySQL存储引擎InnoDB的事务日志
可以让MySQL拥有崩溃恢复的能力
比如MySQL实例挂了、宕机了，重启时InnoDB使用redo log恢复数据保持数据的一致性

redo log时当做更新操作时，就会写的日志。

查看 InnoDB 存储引擎中与刷盘策略相关的配置参数，默认为1

show variables like '%innodb_flush%';
innodb_flush_log_at_trx_commit= 1

当值为1时表示，只要事务提交成功，将redo log写入磁盘
即使系统崩溃，也不会丢失已提交的事务

当值为2时表示，当事务提交时，只把redo log buffer写入page cache
定期刷盘，若操作系统崩溃，可能丢失缓存中的数据

当值为0时表示，每秒刷写一次 redo log 到磁盘，事务提交时不主动刷盘。若系统崩溃，可能丢失最后 1 秒内已提交的事务

redo log 文件的大小？
大小固定，循环写入格式

为什么这么设计？
因为redo log记录的数据页上的修改，如果buffer pool中数据已经刷盘（写入磁盘），日志失效了。因此可以将其进行覆盖

回滚日志（undo log）

是MySQL存储引擎InnoDB的
起到回滚的作用，保证事务的原子性

1->2
当执行rollback时，2->1

当修改操作时，记录下对应所需要的信息
插入一条记录：至少记录这条记录主键 -> 回滚：找到主键删除
删除一条记录：记录下这条记录的所有信息 -> 回滚：将信息整理成INSERT SQL
修改一条记录：主键，旧值 -> 回滚：Update为旧值

五、二进制日志(bin log)/归档日志 => 数据同步和数据恢复

数据以二进制方式存储在磁盘上逻辑日志，记录了用户对数据库写操作

二进制日志能完整记录数据变更历史，默认没有开启
表结构/数据增、删、改存储过程、函数

二进制日志的作用

备份恢复
主从复制
日志审计场景（被攻击）

查询是否开启了二进制日志

root@test 16: 57>select @@log_bin;
±----------+
| @@log_bin |
±----------+
| 1 |
±----------+

二进制日志配置

[mysqld]
log_bin=/data/mysql/mysqld_binlog
binlog_format=ROW

STATEMENT:记录SQL语句
ROW: 记录每一行数据更改
MIXED:以上两者的混合

查看二进制日志的记录格式

root@test 17: 00>show variables like 'binlog_format';
±--------------±------+
| Variable_name | Value |
±--------------±------+
| binlog_format | ROW |
±--------------±------+

二进制日志很大，日志切割

查看与二进制日志大小限制相关的配置参数

root@test 17: 01>show variables like '%max_binlog%';
±---------------------------±---------------------+
| Variable_name | Value |
±---------------------------±---------------------+
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 | 1G
| max_binlog_stmt_cache_size | 18446744073709547520 |
±---------------------------±---------------------+

xxx.index => binlog目录
xxx.00001 => binlog文件

-rw-r----- 1 mysql mysql 157 8月 9 17:00 mysqld_binlog.000001
-rw-r----- 1 mysql mysql 33 8月 9 17:00 mysqld_binlog.index

查看有哪些binlog文件

root@test 17: 04>show master logs;
±---------------------±----------±----------+
| Log_name | File_size | Encrypted |
±---------------------±----------±----------+
| mysqld_binlog.000001 | 180 | No |
| mysqld_binlog.000002 | 157 | No |
±---------------------±----------±----------+
2 rows in set (0.00 sec)

root@test 17: 05>show binary logs;
±---------------------±----------±----------+
| Log_name | File_size | Encrypted |
±---------------------±----------±----------+
| mysqld_binlog.000001 | 180 | No |
| mysqld_binlog.000002 | 157 | No |
±---------------------±----------±----------+
2 rows in set (0.00 sec)

查看当前binlog文件

root@test 17: 05>show master status\G
*************************** 1. row ***************************
File: mysqld_binlog.000002
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

查看二进制日志

show binlog events in 'mysqld_binlog.000002' \G

*************************** 17. row ***************************Log_name: mysqld_binlog.000002          日志文件名 Pos: 1085                          pos起始位置Event_type: Xid                           事件类型Server_id: 1                             哪台服务在操作
End_log_pos: 1116                          pos结束位置Info: COMMIT /* xid=80 */           操作的内容

查看指定二进制日志文件

root@test 17: 09>show binlog events in 'mysqld_binlog.000002' from 924\G

解析和查看二进制二进制日志文件

mysqlbinlog -vv /data/mysql/mysqld_binlog.000002

-vv 选项表示以详细模式输出，会显示更完整的日志内容（包括行级变更细节）

自动清除策略

root@test 17: 18> show variables like '%expire_logs_days%';
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| expire_logs_days | 0 |
±-----------------±------+

设置为自动保留 7 天
[mysqld]
expire_logs_days=7

删除所有二进制日志

reset master

二进制如何写入磁盘

什么写二进制日志（INSERT/DELETE/UPDATE）
当执行以上操作 -> binlog_buffer -> 磁盘
sync_binlog=0：由操作系统来决定什么时候写入磁盘
sync_binlog=1：每次事务提交时都会调用fsync，刷新binlog写入磁盘
sync_binlog=N：每N次事务提交时都会调用fsync，刷新binlog写入磁盘

查看二进制日志的同步策略配置

root@test 17: 20>show variables like "sync_binlog";
±--------------±------+
| Variable_name | Value |
±--------------±------+
| sync_binlog | 1 |
±--------------±------+
1 row in set (0.00 sec)