1.MySQL常用日志文件知识

MySQL常用日志种类：

默认情况下，以上所有的日志都处于非激活状态（Linux环境）。当激活日志时，所有的日志都默认配置在数据文件的目录下。管理员也可以对上述日志进行轮询切割，实现该功能常见的命令是mysqladmin flush-logs、mysqldump的“-F”或“--master-data”参数等。

2.错误日志的介绍与配置

2.1.错误日志的介绍

MySQL的错误日志用于记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息。

2.2.错误日志的记录配置

MySQL的错误日志通常由mysqld或mysqld_safe程序产生。

记录MySQL错误日志的配置：

1、在my.cnf配置文件中调整，注意，是在[mysqld_safe]模块的下面进行配置。
[mysqld_safe]
log-error = /application/mysql-5.6.40/data/oldboy.err

2、在启动MySQL服务的命令里加入记录错误日志的参数。
mysqld_safe --log-error=/application/mysql-5.6.40/data/oldboy.err &

查看结果
mysql> show variables like ‘log_error%‘;

2.3.错误日志轮询

管理员可以使用命令轮询错误日志：

cd /application/mysql/data/  #切换到日志目录下
mv oldboy.err oldboy_$(date +%F).err  #将错误日志按天移动改名
mysqladmin flush-logs  #执行刷新日志命令

2.4.数据库故障排查

新手安装数据库时，遇到数据库无法启动时的排查方法：

1、先清空错误日志文件，然后重新启动MySQL服务，再查看日志文件报什么错误，并根据错误日志进行处理。
2、如果无法解决，则删除数据文件，重新初始化数据库。

在排查故障时，得到的错误日志是：

The error means mysqld does not have the access rights to
the directory.

该错误是权限问题导致的问题，可对数据目录递归执行权限，然后再重启数据库。

chown -R mysql.mysql /application/mysql/data/

3.普通查询日志的介绍与配置

3.1.普通查询日志的介绍

普通查询日志的作用是记录客户端连接信息，以及执行的SQL语句信息。

3.2.普通查询日志的功能配置

默认情况下普通查询日志是关闭状态：

mysql>show variables like ‘general_log%‘;

可以执行在线修改的命令使其临时生效：

mysql>set global general_log = on;
mysql>show variables like ‘general_log%‘;

可以把参数写入my.cnf的配置文件里的[mysqld]模块下使其永久生效：

general_log = on
general_log_file = /application/mysql-5.6.40/data/oldboy.log

3.3.普通查询日志

tail oldboy.log

3.4.普通查询日志的生产使用建议

在高并发数据库的场景下，普通查询日志应该是关闭状态的（默认也是关闭的），主要是因为查询日志的信息量很大，容易导致磁盘I/O性能问题。当访问量不是很大，而企业又有审计执行的SQL语句的需求时，可以考虑开启该功能。

4.二进制日志的介绍与配置

4.1.二进制日志的介绍

二进制日志的作用是记录数据库里的数据被修改的SQL语句，一般为DDL和DML语句，例如含有insert、update、delete、create、drop、alter等关键字的语句。

4.2.二进制日志的作用

二进制日志最重要的作用有2个。

第一个是记录MySQL数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，MySQL的备份将无法完整还原数据。

第二个是实现主从复制功能。

4.3.二进制日志的配置

vi /etc/my.cnf
[mysqld]
log_bin = mysql-bin

show variables like ‘%log_bin‘;  #查看binlog是否开启

实现在开启binlog功能的前提下，临时不记录binlog

set session sql_log_bin = OFF;  #临时停止记录binlog
create database oldgirl;  #建库测试
show binary logs;  #查看binlog文件列表及位置点
system mysqlbinlog mysql-bin.000005|grep "oldgirl"  #过滤binlog文件，没有记录binlog
set session sql_log_bin = ON;  #开启记录binlog
drop database oldgirl;  #删除数据库
system mysqlbinlog mysql-bin.000005|grep "oldgirl"  #继续过滤，发现记录了binlog

这个功能常用于在用户使用mysql恢复数据时不希望恢复的数据SQL记录到binlog里的情况。

4.4.二进制日志文件的刷新条件

1、数据库重启会自动刷新binlog为新文件。
2、执行“mysqldump -F”或“mysqladmin flush-logs”会将binlog刷新为新文件。
3、binlog文件达到1G左右时，会自动刷新binlog为新文件。
4、人为配置切割及调整。

binlog最大值控制参数及默认大小查看方法：
show variables like ‘max_binlog_size‘;

4.5.二进制日志索引文件

除了很多按序列生产的binlog文件列表之外，还有一个索引文件mysql-bin.index。

索引文件的文件名和binlog文件一样，只是扩展名为index。

binlog索引文件的控制参数为：

show variables like ‘log_bin_index‘;

4.6.删除二进制日志的方法

binlog日志很重要，不能随意清除。

首先，要确定什么时候可以删除binlog。

理论上每天的数据库全备时刻以前的binlog都是无用的，但是工作中我们会根据需要保留3-7天的本地binlog文件。

删除方式：

4.6.1.设置参数自动删除binlog

参数设置为：

show variables like ‘expire_logs_days‘;
set global expire_logs_days = 7;  #设置删除7天前的日志

vi /etc/my.cnf
[mysqld]
expire_logs_days = 7

4.6.2.从最开始一直删除到指定的文件位置（不含指定文件）

这种方法一般用于处理临时的需求：

cp mysql-bin.* /tmp

登录到数据库执行：

show binary logs;
purge binary logs to ‘mysql-bin.000002‘;
show binary logs;

4.6.3.安装时间删除binlog日志

用于处理临时的需求：

ls -l --time-style=long-iso mysql-bin*

删除“2020-05-19 10:24”以前的binlog文件：

PURGE MASTER LOGS BEFORE ‘2020-05-19 10:24‘;
system ls -l --time-style=long-iso mysql-bin*

4.6.4.清楚所有的binlog，并从000001开始重新记录

reset master指令可以清楚数据库所有的binlog文件，并从000001开始重新记录；

reset master;
system ls -l --time-style=long-iso mysql-bin*;

binlog相关参数的设置和优化思路。

查看binlog相关的参数：

show variables like ‘binlog_%‘;
show variables like ‘%log_bin%‘;

4.6.4.1.binlog_cache_size

二进制日志缓存是数据库为每一个客户连接分配的内存空间。对于事务引擎来说，适当调整该参数会获得更好的性能，该参数的默认值为：

show variables like ‘%binlog_cache%‘;

4.6.4.2.max_binlog_size

该参数用于设置binlog日志的最大大小，默认为1G，但是该值并不能严格控制binlog的大小。若binlog大小接近1G，而此时又在执行一个较大的事务，那么为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新。该参数的默认值查询结果为：

show variables like ‘%max_binlog_size%‘;

4.6.4.3.sync_binlog

这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。

sync_binlog参数的具体说明：

1、sync_binlog=0表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。
2、sync_binlog=n表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。

该参数默认的设置是0：
show variables like ‘%sync_binlog%‘;

设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。

4.7.记录二进制日志的三种模式

MySQL使用不同的模式记录二进制日志信息，常见的有三种模式。

4.7.1.语句模式

语句（statement-based）模式是MySQL5.6默认的模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。

该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。

但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。

例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题。

4.7.2.行级模式

简单地说，行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。

优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。

缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量会非常大。

4.7.3.混合模式

混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于这几种情况。

1、当函数中包含UUID()时。
2、当表中有自增列（AUTO_INCREMENT）被更新时。
3、当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
4、当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

4.8.企业中如何选择二进制日志模式

在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。

如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。

4.9.二进制日志的模式配置调整

临时调整命令：

set global binlog_format = ‘statement‘;
set global binlog_format = ‘row‘;
set global binlog_format = ‘mixed‘;

永久调整可以将“binlog_format = ‘模式名‘”写入到my.cnf配置文件中，并重启服务。

4.10.行级模式二进制日志实际读取

当在数据库中执行语句：

set global binlog_format = ‘row‘;

或者在my.cnf中加入binlog_format = ‘row‘配置生效后，此时如果更新或者删除多行数据就会发现binlog日志记录的内容有所不同。

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001  #--base64-output=decode-rows -v以行级模式解析binlog日志。

5.慢查询日志

5.1.慢查询日志介绍

简单地理解，慢查询日志（slow query log）就是记录执行时间超出指定值（long_query_time）或其他指定条件（例如，没有使用到索引，结果集大于1000行）的SQL语句。

5.2.慢查询日志相关参数说明

慢查询的参数，对于数据库SQL的优化非常重要，是SQL优化的前提，因此，这里以表的形式进行说明。

慢查询的参数及说明：

5.3.慢查询日志重要参数配置

企业中常见的配置慢查询的参数：

slow-query-log = ON                                #慢查询开启开关
long_query_time = 2                                #记录大于2秒的SQL语句
log_queries_not_using_indexes = ON                 #没有使用到索引的SQL语句
slow-query-log-file = /application/mysql/slow.log  #记录SQL语句的文件
min_examined_row_limit = 800                       #记录结果大于800行的SQL语句

5.4.慢查询日志的刷新方法

在工作中，可以利用定时任务按天对慢查询日志进行切割，然后再分析。

切割脚本：

vi /server/scripts/cut_slow_log.sh
export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
cd /application/mysql &&mv slow.log slow.log.$(date +%F) &&mysqladmin flush-log

将脚本放入定时任务，每天0点执行切割任务：

00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh >/dev/null 2>&1

MySQL常用日志文件知识