mysql 日志审计 mysql各种日志的作用

一、简介

1. 分类

Mysql有不同的日志文件，用来存储不同类型和功能的日志，分为二进制日志、错误日志、通用查询日志、慢查询日志。Mysql8.0又新增两种日志：中继日志和数据定义语句日志。

2. 弊端

• 日志会降低mysql数据库的性能，需要花费额外时间记录日志。
• 日志会占用大量磁盘空间。

---

二、通用查询日志(general query log)

1. 介绍

正如他的名字一样，该日志会记录用户的所有操作，包括启动和关闭mysql服务，所有用户的连接开始时间和结束时间、发给mysql的所有sql指令。

2. 相关变量

show variables like '%general%'

set GLOBAL general_log=on;
set GLOBAL general_log_file='/path/filename'

如果开启了以后，可以直接通过vim查看通用查询日志。

3.备份

如果删除或者改名了原来的日志文件，使用如何命令重新生成查询日志文件：

mysqldadmin -uroot -p flush-logs

---

三、错误日志（error log）

1. 介绍

错误日志记录了mysql服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。
该日志默认是开启的，而且无法被禁止。
默认情况下，错误日志存储/var/log下，名称默认为mysqld.log

2. 备份

#mysql5.5.7以前不用执行第一句
install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log    
mysqldadmin -uroot -p flush-logs

---

四、二进制日志（bin log）

1. 介绍

它记录了数据库所有执行的ddl和dml等数据库更新事件语句，但是不包括没有修改数据的语句（select、show等）。
它的主要应用场景有两个：

• 用于数据恢复，通过查看用户执行了哪些操作，可以实现数据的增量恢复。
• 用于数据复制，master把二进制文件传递给slaves来达到master-slave数据一致的目的。

2. 相关变量

show variables like '%log_bin%'

log_bin_trust_function_creators：表示是否信任存储函数

log_bin_use_v1_row_events: on表示使用版本1二进制日志行，off表示使用版本2二进制日志行

show variables like '%binlog_format%'

binglog有如下3种格式：

• statement： 每一条修改的sql都会记录在binlog中。好处是不需要记录每行的变化，减少日志量，节约io。
• row： 保存那条记录被修改。可以避免存储过程或函数无法正确复制的问题。
• mixed：5.1.8以后提供，是statement和row的结合。

3. 永久性设置

修改mysql的my.cnf或my.ini文件：

log_bin = xxxx # 日志名
binlog_expire_logs_seconds=600 # 日志保存时间，s
max_binlog_size=100M # 单个日志大小，当前日志文件大小超过此变量，执行切换动作。默认大小为1GB。该设置不是严格遵守的。

4. 查看日志

mysql每重启一次，就会生成一个bin log文件。查看当前binlog列表及其大小：

show binary logs

所有对数据库的修改都会记录在binlog中，单binlog是二进制文件，无法直接查看，想要查看需要借助mysqlbinlog命令工具。

mysqlbinlog "日志文件路径/名字"

默认打开后，并没有具体的sql语句，而是记录了一些事件：

• Query事件，负责开始一个事务。
• Table_map事件，负责映射需要的表。
• Update_rows事件，复制写入数据。
• Xid事件，复制结束事务。

mysqlbinlog -v "日志文件路径/名字" # 将行事件以伪sql形式表示出来

5. 恢复数据

可以使用mysqlbinlog工具从指定时间点开始到另外一个时间点的日志中恢复数据。

mysqlbinlog [option] filename | mysql -uroot -p

option有几个重要参数：

• --start-date 和--stop-date :可以指定数据库恢复的起始时间和结束时间
• --start-position 和--stop-position :可以指定恢复数据的开始位置和结束为止
• --database=“”:指定数据库

如果是用位置恢复，需要使用如下命令来获取为止：

show binlog events in 'binlog 文件名'

6. 删除二进制日志

mysql的二进制文件可以配置自动删除，也可以安全的手动删除的方法。
PURGE MASTER LOGS 删除指定部分：

PURGE {MASTER | BINARY} LOGS TO '指定文件名' # 删除创建时间早于xxx的日志
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期' # 删除xxxx时间前创建的所有日志。

RESET BINARYLOGS 删除所有：

7. 两阶段提交

binglog的写入策略由参数sync_binlog控制，默认是0。表示每次提交都只写到操作系统缓存中，由系统判断什么时候执行刷盘。为1时，表示每次提交都会刷盘。当大于1时，表示累计到n个事务时才刷盘。

innodb更新一条指定数据的过程如下：

edo log与binlog都写一次的话，也就是存在以下两种情况：

1. 先写binlog，再写redo log：当前事务提交后，写入binlog成功，之后主节点崩溃。在主节点重启后，由于没有写入redo log，因此不会恢复该条数据。而从节点依据binlog在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致。
2. 先写redo log，再写binlog：当前事务提交后，写入redo log成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。

此时，如果发生崩溃，就可以根据redolog和binlog进行恢复。
在写入redo log时，会顺便记录XID，即当前事务id。在写入binlog时，也会写入XID。因此存在以下三种情况：

1. 如果在写入redo log之前崩溃，那么此时redo log与binlog中都没有，是一致的情况，崩溃也无所谓。
2. 如果在写入redo log prepare阶段后立马崩溃，之后会在崩恢复时，由于redo log没有被标记为commit。于是拿着redo log中的XID去bin log中查找，此时肯定是找不到的，那么执行回滚操作。
3. 如果在写入bin log后立马崩溃，在恢复时，由redo log中的XID可以找到对应的bin log，这个时候直接提交即可。

总的来说，在崩溃恢复后，只要redo log不是处于commit阶段，那么就拿着redo log中的XID去binlog中寻找，找得到就提交，否则就回滚。在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，来确保redo log与binlog的数据一致性。

---