MySQL日志管理

编程 · 02-02 · 140 人浏览

错误日志

记录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

恢复日志:

  1. 临时关闭日志:set sql_log_bin=0;
  2. 导入SQL语句:source /mnt/bin.sql
  3. 恢复日志: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,即返回前面多少条数据
MySQL
Theme Jasmine by Kent Liao