MySQL数据库日志知识与企业应用实践
为了帮助管理员快速发现数据库的相关运行信息，MySQL为用户提供了几种日志种类，具体见表
默认情况下，以上所有的日志都处于非激活状态（Linux环境）。当激活日志时，所有的日志都默认配置在数据文件的目录下。管理员也可以对上述日志进行轮询切割，实现该功能常见的命令是mysqladmin flush-logs、mysqldump的“-F”或“–master-data”参数等，下面就分别介绍这几种日志知识。
(1)错误日志的介绍与配置
1.错误日志的介绍
MySQL的错误日志用于记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息。
2.错误日志的记录配置
MySQL的错误日志通常由mysqld或mysqld_safe程序产生，前文已经讲解过MySQL的启动原理，因此，可利用如下方法配置记录MySQL错误日志。
方法1：在my.cnf配置文件中调整，注意，是在[mysqld_safe]或[mysqld]模块的下面进行配置。命令如下：
[mysqld]
log-error = /data/mysql/error.err /data/mysql/error.err 都是自己手动创建的，记得修改所属的用户与所属的组为mysql ，或者修改操作权限
chown -R mysql:mysql /data/mysql
chmod -R 755 /data/mysql
方法2：在启动MySQL服务的命令里加入记录错误日志的参数。
示例如下：
log-error=/data/mysql/error/error.err
查看到的最终结果为：
show variables like ‘log_error%’;
修改文件夹的权限
3.错误日志轮询
管理员可以使用命令轮询错误日志，例如可以按天轮询，具体方法如下：
[root@songls~]# cd /data/mysql/error/ #<==切换到日志目录下。
[root@songls mysql]# mv error.err error_$(date +%F).err #<==将错误日志按天
移动改名。
[root@songls data]# mysqladmin flush-logs #<==执行刷新日志命令。
[root@songls data]# ls -l error.err
-rw-rw----. 1 mysql mysql 0 Mar 19 19:34 error.err #<==新的错误日志诞生了。
4.数据库故障排查案例分析
新手安装数据库时，遇到数据库无法启动时的排查方法具体如下。
1）先清空错误日志文件，然后重新启动MySQL服务，再查看日志文件报什么错误，并根据错误日志进行处理。
2）如果无法解决，则删除数据文件，重新初始化数据库。
假设在排查故障时，得到的错误日志提示为：
180321 17:36:26 InnoDB: Completed initialization of buffer pool
180321 17:36:26 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
根据提示可知，该错误是权限问题导致的问题，可对数据目录递归执行权限，然后再重启数据库。命令如下：
[root@songls data]# chown -R mysql:mysql /data/mysql/error
(2)慢查询日志
1.慢查询日志介绍
简单地理解，慢查询日志（slow query log）就是记录执行时间超出指定值（long_query_time）或其他指定条件（例如，没有使用到索引，结果集大于1000行）的SQL语句。
2.慢查询日志相关参数说明
慢查询的参数，对于数据库SQL的优化非常重要，是SQL优化的前提，因此，这里以表的形式进行说明，具体见表
3.慢查询日志重要参数配置
企业中常见的配置慢查询的参数为：
slow-query-log = ON #<==慢查询开启开关
long_query_time = 2 #<==记录大于2秒的SQL语句。
log_queries_not_using_indexes #<==没有使用到索引的SQL语句。
slow-query-log-file = /data/mysql/slow/slow.log #<==记录SQL语句的文件。
min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
可将上述参数配置到my.cnf里，配置完毕重启MySQL服务，并进行检查：
mysql> show variables like ‘slow_query%’;
±--------------------±----------------------------+
| Variable_name | Value |
±--------------------±----------------------------+
| slow_query_log | ON | #<==开关已打开。
| slow_query_log_file | /data/mysql/slow.log | #<==文件路径已生效。
±--------------------±----------------------------+
2 rows in set (0.00 sec)
mysql> show variables like ‘%long_query%’;
±----------------±---------+
| Variable_name | Value |
±----------------±---------+
| long_query_time | 2.000000 | #<==记录大于2秒的查询已生效。
±----------------±---------+
1 row in set (0.01 sec)
mysql> show variables like ‘%log_queries_not%’;
±------------------------------±------+
| Variable_name | Value |
±------------------------------±------+
| log_queries_not_using_indexes | ON | #<==记录没有使用索引的查询已生效。
±------------------------------±------+
1 row in set (0.00 sec)
mysql> show variables like ‘%min_examined_row_limit%’;
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| min_examined_row_limit | 800 | #<==记录查询结果集大于800行的SQL已生效。
±-----------------------±------+
1 row in set (0.00 sec)
到此，就已经设定好记录慢查询SQL语句的条件了，那么，对于每天所产生的大量慢查询，又该如何处理和分析呢？
4.慢查询日志的刷新方法
在工作中，可以利用定时任务按天对慢查询日志进行切割，然后再分析。
示例切割脚本如下：
[root@oldboy data]# mkdir /server/scripts/ -p
[root@oldboy data]# cat /server/scripts/cut_slow_log.sh
export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
cd /data/mysql/slow
mv slow.log slow.log.$(date +%F)
mysqladmin flush-log
将上述脚本放入定时任务，每天0点执行切割任务，配置结果如下：
[root@oldboy data]# tail -2 /var/spool/cron/root
#cut mysql slow log by oldboy at 20180324
00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh >/dev/null 2>&1
5.使用工具分析慢查询日志
实际工作中，慢查询的日志可能非常多，给运维人员的优化工作带来了一定的困难，MySQL官方提供了慢查询的分析工具mysqldumpslow，有兴趣的读者可以参考官方手册。
**
下面为大家介绍一款很不错的第三方分析工具mysqlsla（需要单独安装该工具）。
**
（1）安装mysqlsla
请提前下载好mysqlsla-2.03.tar.gz到指定目录下，然后执行如下命令安装：
yum install perl-devel perl-DBI -y
rpm -qa perl-devel perl-DBI
tar -xf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL make
make install
（2）利用mysqlsla工具分析慢查询
mysqlsla命令的默认路径为：/usr/local/bin/mysqlsla。
简单语法如下：
mysqlsla -lt slow [SlowLogFilePath] >[ResultFilePath]
在实际工作中，通常使用脚本调用mysqlsla工具进行分析，然后每天早晨8点，把分析结果发给企业的核心人员（DBA、运维总监、CTO、研发总监、核心开发），最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。
(3)二进制日志的介绍与配置
1.二进制日志的介绍
二进制日志的作用是记录数据库里的数据被修改的SQL语句，一般为DDL和DML语句，例如含有insert、update、delete、create、drop、alter等关键字的语句。
2.二进制日志的作用
二进制日志最重要的作用有2个，具体如下。
第一个是记录MySQL数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，MySQL的备份将无法完整还原数据。
第二个是实现主从复制功能，具体见MySQL主从复制的相关内容。
3.二进制日志的配置
在/etc/my.cnf
log-bin=/data/mysql/bin/binlog.bin
[root@songls data]# grep log_bin /etc/my.cnf
log_bin #<==默认情况下记录日志前缀为“主机名-bin”。
mysql> show variables like ‘log_bin’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| log_bin | ON | #<==记录binlog开关。
±--------------±------+
1 row in set (0.00 sec)mysql> show variables like ‘%log_bin’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| log_bin | ON | #<==记录binlog开关。
| sql_log_bin | ON | #<==临时不记录binlog开关。
±--------------±------+
2 rows in set (0.00 sec)
有个参数可以实现在开启binlog功能的前提下，临时不记录binlog，示例如下：
mysql> set session sql_log_bin = OFF; #<==临时停止记录binlog，注意是session
级别，不影响其他会话。
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘%log_bin’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| log_bin | ON |
| sql_log_bin | OFF | #<==已关闭。
±--------------±------+
2 rows in set (0.00 sec)
mysql> create database oldgirl; #<==建库测试。
Query OK, 1 row affected (0.00 sec)mysql> show binary logs; #<==查看binlog文件列表及位置点。
±------------------±----------+
| Log_name | File_size |
±------------------±----------+
| oldboy-bin.000001 | 143 |
| oldboy-bin.000002 | 168 |
| oldboy-bin.000003 | 168 |
| oldboy-bin.000004 | 9299 |
| oldboy-bin.000005 | 211 | #<==最新的binlog文件及位置点，也可以通过
“show master status;”来确定。
±------------------±----------+
5 rows in set (0.00 sec)
mysql> system mysqlbinlog oldboy-bin.000005|grep “oldgirl”
#<==过滤binlog文件，没有记录binlog。
mysql> set session sql_log_bin = On; #<==开启开关。
Query OK, 0 rows affected (0.00 sec)
mysql> drop database oldgirl; #<==删除数据库。
Query OK, 0 rows affected (0.00 sec)
mysql> system mysqlbinlog oldboy-bin.000005|grep “oldgirl”
#<==继续过滤，发现记录了binlog。
drop database oldgirl
到这里，读者应该知道sql_log_bin的功能了吧，这个功能通常用于在用户使用mysql恢复数据时不希望恢复的数据SQL记录到binlog里的情况。当然，还有其他的应用场景。
4.二进制日志文件的刷新条件
1）数据库重启会自动刷新binlog为新文件。
2）执行“mysqldump-F”或“mysqladmin flush-logs”会将binlog刷新为新文件。
3）binlog文件达到1GB左右时，会自动刷新binlog为新文件。
4）人为配置切割及调整。
binlog最大值控制参数及默认大小查看方法如下：
mysql> show variables like ‘max_binlog_size’;
±----------------±-----------+
| Variable_name | Value |
±----------------±-----------+
| max_binlog_size | 1073741824 |
±----------------±-----------+
1 row in set (0.00 sec)5.二进制日志索引文件
除了很多按序列生成的binlog文件列表之外，还有一个索引文件，例如下文里的oldboy-bin.index：
[root@oldboy data]# pwd
/application/mysql/data
[root@oldboy data]# ls -l oldboy-bin.*
-rw-rw----. 1 mysql mysql 143 Mar 3 05:50 oldboy-bin.000001
-rw-rw----. 1 mysql mysql 168 Mar 3 05:57 oldboy-bin.000002
-rw-rw----. 1 mysql mysql 168 Mar 3 05:57 oldboy-bin.000003
-rw-rw----. 1 mysql mysql 9299 Mar 19 19:34 oldboy-bin.000004
-rw-rw----. 1 mysql mysql 211 Mar 19 20:15 oldboy-bin.000005
-rw-rw----. 1 mysql mysql 100 Mar 19 19:34 oldboy-bin.index
索引文件的文件名和binlog文件一样，只是扩展名为index，查看索引文件内容的命令如下：
[root@oldboy data]# cat oldboy-bin.index
./oldboy-bin.000001
./oldboy-bin.000002
./oldboy-bin.000003
./oldboy-bin.000004
./oldboy-bin.000005
binlog索引文件的控制参数为：
mysql> show variables like ‘log_bin_index’;
±--------------±------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------+
log_bin_index | /application/mysql-5.6.40/data/oldboy-bin.index |
±--------------±------------------------------------------------+
1 row in set (0.00 sec)6.删除二进制日志的方法
binlog日志很重要，不能随意清除，有些读者看到所维护的服务器空间满了，竟然会直接删除binlog物理文件，这样的操作是错误的，应避免。那么如何正确删除binlog文件呢？
首先，要确定什么时候可以删除binlog。
理论上每天的数据库全备时刻以前的binlog都是无用的，但是工作中我们会根据需要保留3~7天的本地binlog文件。
下面来看看具体的删除方式。
（1）设置参数自动删除binlog
设置参数自动删除binlog是每个管理员都应该做的，参数设置示例如下。
假设参数为：
expire_logs_days = 7 #<==删除7天前的日志
该参数默认是没有配置的，生产中可以同时实现在线更改以及永久更改配置文件：
mysql> show variables like ‘expire_logs_days’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| expire_logs_days | 0 |
±-----------------±------+
1 row in set (0.00 sec)mysql> set global expire_logs_days = 7;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like ‘expire_logs_days’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| expire_logs_days | 7 |
±-----------------±------+
1 row in set (0.00 sec)[root@oldboy data]# grep expir /etc/my.cnf
expire_logs_days = 7
（2）从最开始一直删除到指定的文件位置（不含指定文件）
这种方法一般用于处理临时的需求，操作如下：
[root@oldboy data]# cp oldboy-bin.* /tmp
登录数据库时执行如下命令：
mysql> show binary logs;
±------------------±----------+
| Log_name | File_size |
±------------------±----------+
| oldboy-bin.000001 | 143 |
| oldboy-bin.000002 | 168 |
| oldboy-bin.000003 | 168 |
| oldboy-bin.000004 | 9299 |
| oldboy-bin.000005 | 211 |
±------------------±----------+
4 rows in set (0.00 sec)
mysql> purge binary logs to ‘oldboy-bin.000002’;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
±------------------±----------+
| Log_name | File_size |
±------------------±----------+
| oldboy-bin.000002 | 168 | #<==序列000002以前的就没了。
| oldboy-bin.000003 | 168 |
| oldboy-bin.000004 | 9299 |
| oldboy-bin.000005 | 211 |
±------------------±----------+
4 rows in set (0.00 sec)
（3）按照时间删除binlog日志
这种方法也是用于处理临时的需求，操作如下：
[root@oldboy data]# ls -l --time-style=long-iso oldboy-bin*
-rw-rw----. 1 mysql mysql 168 2018-03-03 05:57 oldboy-bin.000002
-rw-rw----. 1 mysql mysql 168 2018-03-03 05:57 oldboy-bin.000003
-rw-rw----. 1 mysql mysql 9299 2018-03-19 19:34 oldboy-bin.000004
-----------------------±---------------------+
2 rows in set (0.00 sec)
（2）max_binlog_size
该参数用于设置binlog日志的最大大小，默认为1GB，但是该值并不能严格控制binlog的大小。若binlog大小接近1GB，而此时又在执行一个较大的事务，那么为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新。该参数的默认值查询结果为：
mysql> show variables like ‘%max_binlog_size%’;
±----------------±-----------+
| Variable_name | Value |
±----------------±-----------+
| max_binlog_size | 1073741824 |
±----------------±-----------+
1 row in set (0.00 sec)
（3）sync_binlog
这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。
对于“sync_binlog”参数的说明具体如下。
•“sync_binlog=0”表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。
•“sync_binlog=n”表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。
该参数默认的设置是0，示例如下：
mysql> show variables like ‘%sync_binlog%’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| sync_binlog | 0 |
±--------------±------+
1 row in set (0.00 sec)
设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。
