MySQL日志管理

37次阅读
没有评论

共计 2826 个字符,预计需要花费 8 分钟才能阅读完成。

错误日志

记录 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,即返回前面多少条数据

正文完
 0
三毛笔记
版权声明:本站原创文章,由 三毛笔记 于2024-02-02发表,共计2826字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)