MySQL日志管理

错误日志

记录MySQL数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志

配置方式

[mysqld]
log_error=err.log

然后重启会报错

[root@localhost data]# mysqld
2019-01-18T22:18:00.613170Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-01-18T22:18:00.628904Z 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2019-01-18T22:18:00.628972Z 0 [Note] mysqld (mysqld 5.7.24-log) starting as process 11463 ...
2019-01-18T22:18:00.636455Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2019-01-18T22:18:00.636488Z 0 [ERROR] Aborting

2019-01-18T22:18:00.636500Z 0 [Note] Binlog end
2019-01-18T22:18:00.636533Z 0 [Note] mysqld: Shutdown complete

大概意思就是不能用root用户运行，所以多添加一项,设置启动用户为mysql,再次重启就可以了。

[mysqld]
user=mysql

查看配置方式

mysql> SHOW VARIABLES LIKE 'log_error';

一般查询日志

记录mysql所有执行成功的SQL语句信息，可以做审计用，但是很少开启

配置方式

[mysqld]
general_log=on
general_log_file=/data/server.log

查看配置方式

mysql> SHOW VARIABLES LIKE '%gen%';

二进制日志

记录了已提交的数据，以event形式记录到二进制文件中，可以用来做数据恢复，主从复制

配置方式

[mysqld]
log_bin=/data/mysql-bin

在恢复的时候有时候会有需求吧binlog日志暂时关闭。不然恢复的数据又会再次写入binlog日志这个显得很没必要

mysql> SET sql_log_bin=0;  # 当前会话不记录到binlog日志中

二进制日志的记录格式

row：行模式，记录的日志精准，但是日志量可能很大（推荐）
statement： 语句模式，记录的数据变化可能有错误，特别是用到函数的比如now(),无法保证恢复的时刻这个函数执行结果与当时的结果一致，所以不是很安全，但是因为是基于语句的，所以记录的数据量比较少
mixed:混合模式 （一般不用）

设置方式

[mysqld]
binlog_format=row

命令行修改

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

查看binlog设置

mysql> SHOW VARIABLES LIKE '%binlog%';

查看二进制日志

二进制日志是二进制格式的，需要用mysqlbinlog命令才能看到,基于语句的很容易看出，但是如果是行模式记录的就回得到一些加密的数据，很难看懂比如下面

#190118 15:50:30 server id 129  end_log_pos 1277 CRC32 0xb0910eb1 	Write_rows: table id 122 flags: STMT_END_F

BINLOG '
FjxCXBOBAAAALAAAAMsEAAAAAHoAAAAAAAEAA2RiMgACdDIAAQMAAa8ifhQ=
FjxCXB6BAAAAMgAAAP0EAAAAAHoAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAALEOkbA=

这些就是加密后的很难看懂,可以使用选项--base64-output=decode-rows -vvv

#190118 15:50:30 server id 129  end_log_pos 1277 CRC32 0xb0910eb1 	Write_rows: table id 122 flags: STMT_END_F
### INSERT INTO `db2`.`t2`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db2`.`t2`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `db2`.`t2`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */

但是当导入数据的时候 不需要使用这个选项，MySQL自己能识别行格式的二进制文件，只是方便用户查看

常见的用法

# 如果记录的格式是行格式，则用这个命令可以查看到详细信息
mysqlbinlog --base64-output=decode-rows   -vvv  mysql-bin.000001
mysqlbinlog --stop-position=414   mysql-bin.000001 
# 指定起始位置和结束位置，这里的位置对整个二进制文件的相对位置(前120个字节都是格式信息文件预留的，所以基本都是从120开始)
mysqlbinlog --start-position=120 --stop-position=323 mysql-bin.000001 
# 显示指定数据库的相关二进制日志信息
mysqlbinlog  -d db1  mysql-bin.000004

也可以在数据库的内部查看相关信息

# 查询所有二进制日志信息
mysql> SHOW BINARY LOGS;
# 查看当前在使用的二进制日志
mysql> SHOW MASTER STATUS;
# 查看二进制日志文件中具体事件
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000007';

滚动二进制日志

mysql> FLUSH LOGS;

删除二进制日志

# 根据存在时间删除日志,0 表示永不过期
mysql> SET GLOBAL expire_logs_days = 7;
# 删除3天前的二进制日志
mysql> PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
# 根据文件名删除日志
mysql> PURGE BINARY LOGS TO 'mysql-bin.000001';

慢查询日志

将MySQL服务器中影响数据库性能的相关SQL语句记录到日志文件,通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的，这个日志和二进制日志不同，可以直接vim查看的文本文件，但是也有命令工具mysqldumpslow可以帮助统计,这个工具输出的信息比较简陋，可以用percona公司提供的一个pt-query-diagest工具，还能pt-query-diagest + Anemometer建立web可视化页面可视化页面

percona 工具包下载地址
https://www.percona.com/downloads/percona-toolkit/3.0.7/

Anemometer下载地址
https://github.com/box/Anemometer

相关变量

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

mysqldumpslow

mysqldumpslow -s c -t 10 /data/mysql/slow.log
这会输出记录次数最多的10条SQL语句，其中：
-s
	是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t
 	是top n的意思，即为返回前面多少条的数据；
-g, 
	后边可以写一个正则匹配模式，大小写不敏感的；
例子：
	mysqldumpslow -s r -t 10 /database/mysql/slow-log
	得到返回记录集最多的10个查询。
    mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log
    得到按照时间排序的前10条里面含有左连接的查询语句。