2019-05-13 MySQL数据库日志与企业应用实践

作者: 阿丧小威 | 来源:发表于2019-05-23 22:57 被阅读0次

1. MySQL常用日志文件知识

MySQL日志种类

MySQL常用日志种类

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

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

1. 错误日志的介绍

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

2. 错误日志的记录配置

MySQL的错误日志通常由mysqld或mysqld_safe程序产生。可以利用如下方法配置记录MySQL错误日志。
方法1：在my.cnf配置文件中调整，注意，是在[mysqld_safe]模块的下面进行配置：

[mysqld_safe]
log_error = /application/mysql-5.6.41/data/oldboy.err

方法2：在启动MySQL服务的命令里加入记录错误日志的参数：

mysql_safe --log-error=/application/mysql-5.6.41/data/oldboy.err &

注意：没有该文件的话需要自行创建并赋予mysql权限
查看到的最终结果为：

mysql> show variables like '%log_error%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| binlog_error_action | IGNORE_ERROR                              |
| log_error           | /application/mysql-5.6.41/data/oldboy.err |
+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)
[root@oldboy ~]# tail -4 /application/mysql/data/oldboy.err 
2019-05-05 04:23:24 13047 [Note] Server socket created on IP: '::'.
2019-05-05 04:23:24 13047 [Note] Event Scheduler: Loaded 0 events
2019-05-05 04:23:24 13047 [Note] /application/mysql/bin/mysqld: ready for connections.
Version: '5.6.41-log'  socket: '/application/mysql-5.6.41/tmp/mysql.sock'  port: 3306  Source distribution

3. 错误日志轮询

管理员可以使用命令轮询错误日志，例如可以按天轮询：

[root@oldboy ~]# cd /application/mysql/data    ---切换到日志目录下
[root@oldboy data]# mv oldboy.err oldboy_$(date +%F).err    ---将错误日志按天移动改名
[root@oldboy data]# mysqladmin flush-logs    ---执行刷新日志命令
[root@oldboy data]# ll oldboy.err     ---新的错误日志诞生了
-rw-rw----. 1 mysql mysql 0 May  5 04:30 oldboy.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@oldboy data]# chown -R mysql.mysql /application/mysql/data/

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

1. 普通查询日志的介绍

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

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

可能官方考虑到普通查询日志的重要性比较低，因此默认情况下普通查询日志是关闭状态：

mysql> show variables like 'general_log%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | OFF                                |
| general_log_file | /application/mysql/data/oldboy.log |
+------------------+------------------------------------+
2 rows in set (0.00 sec)

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

mysql> set global general_log = on;
Query OK, 0 rows affected (0.03 sec)
mysql> show variables like 'general_log%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | ON                                 |
| general_log_file | /application/mysql/data/oldboy.log |
+------------------+------------------------------------+
2 rows in set (0.03 sec)

如果希望永久生效，则可以把参数写入my.cnf的配置文件里的[mysqld]模块下：

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

3. 普通查询日志示例

可在执行几个操作后，观察查询日志文件的变化：

[root@oldboy data]# tail oldboy.log 
/application/mysql/bin/mysqld, Version: 5.6.41-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /application/mysql-5.6.41/tmp/mysql.sock
Time                 Id Command    Argument
190505  5:09:38     3 Query show variables like 'general_log%'
190505  5:10:28     3 Query show variables like 'log_error%'
190505  5:12:52     3 Quit

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

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

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

1. 二进制日志的介绍

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

2. 二进制日志的作用

二进制日志最重要的作用有2个，具体如下：
第一个是记录MySQL数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，MySQL的备份将无法完整还原数据。
第二个是实现主从复制功能。

3. 二进制日志的配置

[root@oldboy data]# grep log_bin /etc/my.cnf
log_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)

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

1）数据库重启会自动刷新binlog为新文件
2）执行“mysqldump -F”或“mysqldump 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.03 sec)

5. 二进制日志索引文件

除了很多按序列生成的binlog文件列表之外，还有一个索引文件，例如下文里的oldboy-bin.index：

[root@oldboy logs]# pwd
/application/mysql/logs
[root@oldboy logs]# ll oldboy-bin.*
-rw-rw----. 1 mysql mysql    2409 May  4 11:42 oldboy-bin.000003
-rw-rw----. 1 mysql mysql     611 May  4 13:15 oldboy-bin.000004
-rw-rw----. 1 mysql mysql   69414 May  4 13:17 oldboy-bin.000005
-rw-rw----. 1 mysql mysql 1396515 May  4 13:17 oldboy-bin.000006
-rw-rw----. 1 mysql mysql     143 May  4 13:27 oldboy-bin.000007
-rw-rw----. 1 mysql mysql     120 May  4 13:28 oldboy-bin.000008
-rw-rw----. 1 mysql mysql     143 May  5 04:22 oldboy-bin.000009
-rw-rw----. 1 mysql mysql     168 May  5 04:30 oldboy-bin.000010
-rw-rw----. 1 mysql mysql     211 May  5 13:00 oldboy-bin.000011
-rw-rw----. 1 mysql mysql     378 May  5 04:30 oldboy-bin.index

索引文件的文件名和binlog文件一样，只是扩展名为index，查看索引文件内容的命令如下：

[root@oldboy data_ori]# cat oldboy-bin.index 
./oldboy-bin.000001
./oldboy-bin.000002
./oldboy-bin.000003

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

mysql> show variables like 'log_bin_index';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| log_bin_index | /application/mysql/logs/oldboy-bin.index |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

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

binlog日志很重要，不能随意清除。那么如何正确删除binlog文件呢？
首先，要确定什么时候可以删除binlog。理论上每天的数据库全备时刻以前的binlog都是无用的，但是工作中我们会根据需要保留3~7天的本地binlog文件。
以下是具体的删除方式：
（1）设置参数自动删除binlog
设置参数自动删除binlog是每个管理员都应该做的。设置示例如下：

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 logs]# grep expir /etc/my.cnf
expire_logs_days = 7

（2）从最开始一直删除到指定的文件位置（不含指定文件）
这种方法一般用于处理临时的需求，操作如下：

[root@oldboy logs]# cp oldboy-bin.* /tmp/

登录数据库时执行如下命令：

mysql> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| oldboy-bin.000003 |      2409 |
| oldboy-bin.000004 |       611 |
| oldboy-bin.000005 |     69414 |
| oldboy-bin.000006 |   1396515 |
| oldboy-bin.000007 |       143 |
| oldboy-bin.000008 |       120 |
| oldboy-bin.000009 |       143 |
| oldboy-bin.000010 |       168 |
| oldboy-bin.000011 |       211 |
+-------------------+-----------+
9 rows in set (0.00 sec)
mysql> purge binary logs to 'oldboy-bin.000004';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| oldboy-bin.000004 |       611 |    ---序列000004以前的就没了
| oldboy-bin.000005 |     69414 |
| oldboy-bin.000006 |   1396515 |
| oldboy-bin.000007 |       143 |
| oldboy-bin.000008 |       120 |
| oldboy-bin.000009 |       143 |
| oldboy-bin.000010 |       168 |
| oldboy-bin.000011 |       211 |
+-------------------+-----------+
8 rows in set (0.00 sec)

（3）按照时间删除binlog日志
这种方法也是用于临时的需求，操作如下：

[root@oldboy logs]# ls -l --time-style=long-iso oldboy-bin*
-rw-rw----. 1 mysql mysql     611 2019-05-04 13:15 oldboy-bin.000004
-rw-rw----. 1 mysql mysql   69414 2019-05-04 13:17 oldboy-bin.000005
-rw-rw----. 1 mysql mysql 1396515 2019-05-04 13:17 oldboy-bin.000006
-rw-rw----. 1 mysql mysql     143 2019-05-04 13:27 oldboy-bin.000007
-rw-rw----. 1 mysql mysql     120 2019-05-04 13:28 oldboy-bin.000008
-rw-rw----. 1 mysql mysql     143 2019-05-05 04:22 oldboy-bin.000009
-rw-rw----. 1 mysql mysql     168 2019-05-05 04:30 oldboy-bin.000010
-rw-rw----. 1 mysql mysql     211 2019-05-05 13:00 oldboy-bin.000011
-rw-rw----. 1 mysql mysql     336 2019-05-05 13:35 oldboy-bin.index

下面删除“2019-05-04 13:27”以前的binlog文件：

mysql> PURGE MASTER LOGS BEFORE '2019-05-04 13:27';
Query OK, 0 rows affected (0.08 sec)
mysql> system ls -l --time-style=long-iso oldboy-bin*
-rw-rw----. 1 mysql mysql 143 2019-05-04 13:27 oldboy-bin.000007
-rw-rw----. 1 mysql mysql 120 2019-05-04 13:28 oldboy-bin.000008
-rw-rw----. 1 mysql mysql 143 2019-05-05 04:22 oldboy-bin.000009
-rw-rw----. 1 mysql mysql 168 2019-05-05 04:30 oldboy-bin.000010
-rw-rw----. 1 mysql mysql 211 2019-05-05 13:00 oldboy-bin.000011
-rw-rw----. 1 mysql mysql 210 2019-05-05 13:42 oldboy-bin.index

（4）清除所有的binlog，并从000001开始重新记录

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> system ls -l --time-style=long-iso oldboy-bin*
-rw-rw----. 1 mysql mysql 120 2019-05-05 13:44 oldboy-bin.000001
-rw-rw----. 1 mysql mysql  42 2019-05-05 13:44 oldboy-bin.index

下面是binlog相关参数的设置和优化思路
使用如下命令可以查看binlog相关参数：

mysql> show variables like 'binlog_%';
mysql> show variables like '%log_bin%';

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

mysql> show variables like '%binlog_cache%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
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=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记录的内容与数据库的实际内容不一致的风险。

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

（1）语句模式
语句（statement-based）模式是MySQL5.6版本默认的模式，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。
该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。而缺点是由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。
例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题。
（2）行级模式
行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。
优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。
缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。
（3）混合模式
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。

当函数中包含UUID()时
当表中有自增列（AUTO_INCREMENT）被更新时
当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

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

在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。
如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。

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

临时调整命令如下：

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

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

10. 行级模式二进制日志实际读取示例

当在数据库中执行如下语句：

mysql> SET GLOBAL binlog_format = 'ROW';

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

[root@oldboy ~]# mysqlbinlog --base64-output=decode-rows -v oldboy-bin.000001
--- --base64-output=decode-rows -v以行级模式解析binlog日志

5. 慢查询日志

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 = ON    ---没有使用到索引的SQL语句
slow-query-log-file = /application/mysql/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 | /application/mysql/logs/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.00 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)

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

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

[root@oldboy scripts]# cat cut_slow_log.sh 
#!/bin/bash
export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
cd /application/mysql/logs &&\
mv slow.log slow.log.$(date +%F)&&\
mysqladmin flush-log

将上述脚本放入定时任务，每天0点执行切割任务，配置结果如下：

[root@oldboy scripts]# tail -2 /var/spool/cron/root 
#cut mysql slow log by oldboy at 20190522
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 perl-DBD-MySQL -y
rpm -qa perl-devel perl-DBI perl-DBD-MySQL
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工具进行分析，然后每天早晨8点，把分析结果发给企业的核心人员（DBA、运维总监、CTO、研发总监、核心开发），最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。
简单的案例脚本如下，注意切割日志和分析合并为一个脚本了：

[root@oldboy mysqlsla]# cat /server/scripts/slow_log_analyze.sh 
#!/bin/bash
export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
Date=`date +%F -d -1day`

#cut slow log
cd /application/mysql/logs &&\
mv slow.log slow.log_$Date &&\
mysqladmin flush-log

#analyze slow log
Time=`date +%F`
Path=/usr/local/bin
cd /application/mysql &&\
$Path/mysqlsla -lt slow slow.log_$Date > analyzed_slow_$Date.log 2>&1

#rsync analyzed_slow to backup server    ---省略了此步
#send analyzed slow log to administator on backup server by mail.    ---省略了此步