文章 标签 分类 系列 About

Mysql 日志管理

0x5c0f  收录于  类别 Linux 运维记事 和 系列 运维记事 Mysql
     约 1439 字  预计阅读 3 分钟    次阅读     条评论    CC BY-NC-SA 4.0   
警告
本文最后更新于 2022-06-28，文中内容可能已过时。

1. Mysql 日志管理

日志文件 选项 文件名(表名称) 程序
错误 --log-error host_name.err N/A
常规 --general_log host_name.log
general_log		 N/A
慢(速)查询 --slow_query_log
--long_query_time		 host_name-show.log
show_log		 mysqldumpslow
二进制 --log-bin
--expire-logs-days		 host_name-bin.000001 mysqlbinlog
审计 --audit_log
--audit_log_file		 audit.log N/A
  • 配置方法: 
    1
2

    [mysqld]
log-error=/var/log/mysql/mysql.log
  • 查看方法
    • mysql> show variables like '%log_error%
  • 作用
    • 记录mysql数据库的一般状态及报错信息,是我们对于数据库常规报错处理的常用日志
  • 配置方法
1
2
3

[mysqld]
general_log=on
general_log_file=/var/log/mysql/server2.log
  • 查看方法
    • show variables like '%gen%'
  • 作用
    • 记录mysql所有执行成功的语句,可以作审计用,但很少开启

  • 二进制日志会记录已提交的数据,以event的形式记录到二进制文件中,其常用的记录格式有:

    • row: 行模式,即数据行的变化过程,将某一个值修改到另一个值的过程(建议及常用模式) TODO: mysql 配置文件中是否区分大小写(这个需要根据官方建议核查)

    • statement: 语句模式,直接记录执行过的语句,其优点是记录的数据好分析,数据量级小,比如批量修改,缺点就是记录函数(如:now())类操作不是特别准确(默认模式show variables like '%binlog_format%' );

    • mixed: 以上两种的混合模式

  • 开启、关闭及记录格式

1
2
3
4
5
6
7
8
9


[mysqld]
# 开启
log-bin = /data/mysql56/3307/data/mysql-bin/mysql-bin
binlog_format = row

# 关闭注释上面两个配置即可 
# 临时关闭 set sql_log_bin=0
# 命令行修改 set global binlog_format = 'row'
  • sync_binlog 值为1时，每次事务提交时就向磁盘进行写入

pos: 开始位置号 End_log_pos: 结束位置号

  • 查看当前所有二进制日志可用信息: show binary logs;
  • 当前正在使用的binlog日志: show master status
  • 查看二进制日志中记录的事件: show binlog events in 'mysql-bin.000002';
1
2
3
4
5
6
7
8
9

+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 | 4   | Format_desc | 10        | 120         | Server ver: 5.6.50-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query       | 10        | 192         | BEGIN                                 |
| mysql-bin.000004 | 192 | Table_map   | 10        | 248         | table_id: 72 (test.test_table)        |
| mysql-bin.000004 | 248 | Write_rows  | 10        | 292         | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000004 | 292 | Xid         | 10        | 323         | COMMIT /* xid=36 */                   |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
  • 查看二进制文件内容(mysqlbinlog可能不会识别default-character-set=utf8这个指令,报错为unknown variable,解决指定参数--no-defaults) TODO: unknown variable ‘default-character-set=utf8’(这个不识别就不识别吧,具体不是很清楚,也没有咨询到解决方案)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 查看binlog内容(仅包含DDL操作) 
$> /opt/mysql56/bin/mysqlbinlog --no-defaults /data/mysql56/3307/data/mysql-bin/mysql-bin/mysql-bin.000004
# 查看binlog详细内容(注释中包含大概的详细语句)  
$> /opt/mysql56/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 
# @1: 代表第一列 @2: 代表第二列 
### INSERT INTO `test`.`test_table`
### SET
###   @1=6
###   @2='333'
# at 292

# 范围截取 
$> /opt/mysql56/bin/mysqlbinlog --no-defaults --start-position=192 --stop-position=323 --base64-output=decode-rows -v ./data/mysql-bin/mysql-bin.000004

# 导出为数据库可恢复文件(恢复执行 source ./binlog.sql) 
$> /opt/mysql56/bin/mysqlbinlog --no-defaults --start-position=192 --stop-position=323 ./data/mysql-bin/mysql-bin.000004 > ./binlog.sql 


# 刷新日志(重新生成一个binlog日志)  
mysql> flush logs;

# 设置二进制日志保存天数,默认永久保留(建议永久保留) 
mysql> set global expire_logs_days = 90;

# 手动删除(删除3天前)
mysql> purge binary logs before now() - interval 3 day;

# 删除到那个日志文件 
mysql> purge binary logs to 'mysql-bin.000020';

1.6. 慢日志管理 (my.cnf 中配置无顺序要求)

  • show variables like '%slow%'

  • show variables like '%long%

  • show variables like '%indexes%'

  • 查询

    1. 是将mysql服务中影响数据库性能的相关sql语句记录到日志文件中
    2. 通过对这些特殊的sql语句分析,改进以达到提高数据库性能的目的

  • 设置

    1. long_query_time: 设定慢查询的阀值,超出设定值的sql即被记录到慢查询日志,缺省值为10s
    2. show_query_log : 指定是否开启慢查询日志
    3. slow_query_log_file: 指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log
    4. min_examined_row_limit: 查询检查返回少于改参数指定行的sql不会记录到慢查询日志
    5. log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引

  • mysqldumpslow(扩展命令 mysqlsla、pt-query-diagest percona-toolkit)
    导出host_name-slow.log日志中执行次数最多的前10条数据 mysqldumpslow -s c -t 10 host_name-slow.log

  • 导出host_name-slow.log日志中平均执行时间的前10条数据 mysqldumpslow -s at -t 10 host_name-slow.log

更新于 2022-06-28
CC BY-NC-SA 4.0
阅读原始文档
 Linux, Mysql
返回 | 主页