MySQL日志管理 - 三毛笔记

错误日志

记录MySQL从启动以来，所有的状态、警告、错误。为我们定位数据库问题，提供帮助。

查看数据库日志路径：select @@log_error;

默认是开启状态，文件位于/datadir/hostname.err，也可以通过my.cnf自定义文件位置：log_error=/tmp/mysql.log，重启MySQL生效。

二进制日志

作用是记录数据库变化（DDL，DCL，DML）性质的日志，是逻辑层性质日志。应用在数据恢复，主从复制中。

8.0版本以前，默认没有开启，建议生产开启，且一定要和数据盘分开。

配置方法：vim /etc/my.cnf

server_id=6 # 主机编号。主从中使用，5.7以后开binlog要加此参数
log_bin=/data/binlog/mysql-bin # 日志存放目录+日志名前缀，例如mysgl-bin.000001、mysq1-bin.000002
sync_binlog=1 # binlog日志刷盘策略，1是每次事务提交立即刷写binlog到磁盘
binlog_format=row # binlog记录格式为row模式

记录内容

bin1og是SQL层的功能，记录变更的SQL语句，不记录查询语句。

记录SQL语句种类

DDL：原封不动记录当前DDL语句
DCL：原封不动记录当前DCL语句
DML：只记录已经提交的事务DML（insert、update、delete）

DML三种记录方式，受binlog_format参数影响，建议使用row记录模式：

statement（statement based replication, SBR）5.6默认：以语句模式原封不动记录当前DML
row（row based replication, RBR）5.7 默认：以行模式记录数据行的变化，用户看不懂，需要工具分析
mixed（mixed based replication, MBR）：混合模式，以上两种模式的混合

🤔SBR与RBR模式对比？

statement：可读性较高，日志量少，不够严谨
row：可读性很低，日志量大，足够严谨

记录单元

事件是二进制日志的最小记录单元。对于DDL、DCL，一个语句就是一个event，对于DML语句来讲，只记录已提交的事务。

例如以下列子，就被分为了4个event：

        position（start-stop）
begin;  120 - 340
DML1    340 - 460
DML2    460 - 550
commit; 550 - 760

位置号（position）的作用是为了方便截取事件。

事件组成：

事件开始标识：at 194
事件内容
事件结束标识：end_log_pos 254

查看日志

查看是否开启：select @@log_bin;

查看文件保存路径：select @@log_bin_basename;

查看目前有几个日志文件：show binary logs;

查看当前使用的binlog：show master status;

查看二进制日志事件：show binlog events in 'binlog文件';

事件查看：mysql -e "show binlog events in 'binlog文件'" | grep DROP

内容查看：mysqlbinlog 'binlog文件' >/tmp/a.sql

翻译数据行：mysqlbinlog --base64-output=decode-rows -vvv 'binlog文件' >/tmp/b.sql

恢复日志

滚动一个新日志：flush logs; 或 mysqladmin flush-logs 或重启数据库

截取二进制日志：mysqlbinlog --start-position=219 --stop-position=335 'binlog文件' >/tmp/c.sql

恢复日志：

临时关闭日志：set sql_log_bin=0;
导入SQL语句：source /mnt/bin.sql
恢复日志：set sql_log_bin=1;

日志删除

自动删除机制：select @expire_logs_days;
默认是0，代表永不删除，单位是天。设置多少天合适：一次全备份周期7+1天，生产环境一般建议，最少2个全备份周期+1，即15天。

手工删除，例如，删除某个日志之前的日志（正在使用的日志文件不能清理）：purge binary logs to 'mysql-bin.000005';

删除某个时间点之前的日志：purge binary logs before '2024-01-01 22:11:11';

全部清空：reset master;，比较危险，在主库执行此操作，主从必宕。

GTID

5.6版本新加特性，5.7 8.0中做了加强。5.6中不开启，没有这个功能，5.7中GTID即使不开也会有自动生成。

GTID（Global Transaction ID）是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID组成：server_uuid:transaction_id。

开启GTID，vim /etc/my.cnf：

gtid-mode=on
enforce-gtid-consistency=true

基于GTID截取日志：mysqlbinlog --skip-gtids --include-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:1-2' mysql-bin.000003 mysql-bin.000004 >/tmp/gtid.sql

GTID幂等性：在基于GTID进行数据恢复时，如果在截取GTID记录时没加--skip-gtids选项，那么，在使用source命令导入sql文件进行数据恢复时，会发现数据并没有恢复成功。这是因为当前系统中已经有了这些GTID记录，而基于幂等性机制，已经有的GTID是不会进行操作的，既然不会进行操作，那自然数据恢复就不会成功了。

慢日志

记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

查看慢日志开关状态：select @@slow_query_log;，默认没有开启，需要修改配置文件/etc/my.cnf：

# 开启慢日志查询开关
slow_query_log=1

# 设置慢查询时间阈值为2秒
long_query_time=2

配置完毕之后，重启MySQL服务器，查看慢日志文件（查看文件存放位置：select @@slow_query_log_file;）中记录的信息。

查看慢日志文件尾部实时输出内容：tail -f localhost-slow.log

查看不走索引的语句记录：select @@log_queries_not_using_indexes;

慢语句分析

命令：mysqldumpslow -s c -t 5 慢日志文件：

-s sort，表示按照何种方式排序，c、t、l、r分别按照记录次数、时间、查询时间、返回记录数来排序，ac、at、al、ar表示相应的倒序
-t top，即返回前面多少条数据