MySql运维篇——日志：错误日志、二进制日志、查询日志、慢查询日志_mysql 错误日志

目录

一、日志

1. 日志分类

2. 错误日志

3. 二进制日志

3.1 格式

3.2 查看

3.3 删除

4. 查询日志

5.  慢查询日志

---

一、日志

1. 日志分类

• 错误日志 Error log 

启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqd.log

• 通用查询日志

所有的查询都记下来

• 二进制日志 binary log

实现备份，增量备份。值记录改变数据，除了select都记

• 中继日志

读取猪服务器的binlog,在本地回放，保持一致

• 慢查询日志 slow log

指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优建议给开发人员

• DDL log

定义语句的标记

2. 错误日志

• 错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
• 该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。

查看日志位置：先登录MySQL,通过系统变量查看日志位置

mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)

或者进入配置文件查看位置

vim /etc/my.cnf 
 
 28 #log_bin
 29 #server-id=2
 30 
 31 datadir=/var/lib/mysql
 32 socket=/var/lib/mysql/mysql.sock
 33 
 34 log-error=/var/log/mysqld.log          // 错误日志位置
 35 pid-file=/var/run/mysqld/mysqld.pid

通过tail指令查看文件尾部的50行日志 t

tail -n 50 /var/log/mysqld.log

3. 二进制日志

前言：二进制日志（BINLOG）记录了所有的 DDL（数据定义语言：创建数据库…）语句和 DML（数据操纵语言：增删改）语句，但不包括数据查询（SELECT、SHOW）语句。

作用：

① 灾难时的数据恢复

因为二进制日志中记录了数据库、表、以及数据的变更。只需要把这里面的语句再次执行就可以恢复数据了。

② MySQL的主从复制。

在MySQL8版本中，主从复制底层原理就是基于二进制日志的

mysql8.0版本二进制日志默认是开启着的，涉及到的参数如下：

• 通过此系统变量来查看二进制日志相关的参数配置，主要看前三行参数

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

• 参数说明：

log_bin：on代表二进制日志是开着的。

log_bin_basename：最终生成的二进制日志文件就在/var/lib/mysql目录下，文件名叫做      binlog，但是日志文件有可能有很多，binlog只是它的前缀。

      ①当前数据库服务器的binlog是日志的基础名称(前缀)，具体的binlog文件名需要再该                    basename的基础上加上编号(编号从000001开始往上自增)。

      ②第一个日志文件写满了或者日志的格式变更了之后，它会再次开启一个新的文件来写              日志。
log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。

### mysql 5.7版本中，默认没有开启二进制日志，需要进入配置文件/vim/mysql/my.cnf，添加以下字段：

log_bin        // 指启动二进制日志 
server-id=2    // 群集问题，必须指定该主机的序号。数字随意，但不能相同

3.1 格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

举例：如果我们执行了一条update语句，这条update语句影响的行数是5行

• STATEMENT：记录的就是这条update语句
• ROW ：它记录的是update语句所影响的这五行，每一行的数据内容在变更之前怎么样，在变更之后是什么样。

通过此系统变量，查看当前mysql的版本中默认的日志格式是那个 

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

如果我们需要配置二进制日志的格式，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。

vim /etc/my.cnf 
 
#在这个文件中添加一行内容
binlog_format=STATEMENT
 
#重新启动mysql服务
systemctl restart mysqld.service
 
cd /var/lib/mysql
 
#可以看到此时重新生成了一个日志文件binlog.000005，原先是1~4。
#因为它的二进制日志格式改了，他不会再往原来的二进制日志文件写入了，而是写到一个新的日志文件中。

3.2 查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法：

#logfilename：二进制文件名
mysqlbinlog [ 参数选项 ] logfilename
 
 
参数选项：
 
	-d 		指定数据库名称，只列出指定的数据库相关操作。
	-o 		忽略掉日志中的前n行命令。
	-v 		将行事件(数据变更)重构为SQL语句
	-vv 	将行事件(数据变更)重构为SQL语句，并输出注释信息

测试：接下来呢我们就来设置一下这两种日志格式，来看一下它们之间的区别是什么样子的。

先准备一个school库、student 1表，内容如下：

• 情况1：当前的日志格式是row, 修改表中数据，然后去查看二进制日志文件

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> update school.student1 set age= age+1 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
 
mysql> exit
 
// 先查看下二进制文件
 
[root@harbor ~]# ls /var/lib/mysql/binlog* -l
-rw-r-----. 1 mysql mysql 7875 3月  17 22:52 /var/lib/mysql/binlog.000001
-rw-r-----. 1 mysql mysql  180 3月  17 22:55 /var/lib/mysql/binlog.000002
-rw-r-----. 1 mysql mysql  180 3月  17 22:56 /var/lib/mysql/binlog.000003
-rw-r-----. 1 mysql mysql  180 3月  17 22:56 /var/lib/mysql/binlog.000004
 
 
#因为二进制日志是第一个日志文件写满了之后会开启一个新的日志文件，所以只需要看最后一个日志文件即可。
#二进制文件不能直接查看使用cat显示的是乱码，需要通过mysqlbinlog 指令来查看
#日志里面是以行的格式显示的，所以看不到sql语句，我们还需要使用-v把它重构为sql语句才能看到
#效果：在日志的最后部分可以看到数据执行前后的变化
[root@harbor ~]# mysqlbinlog  -v /var/lib/mysql/binlog.000004

查询内容如下：

---

情况2：当前的日志格式是STATEMENT

修改日志格式为STATEMENT，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可

vim /etc/my.cnf 
 
#在这个文件中添加一行内容
binlog_format=STATEMENT
 
#重新启动mysql服务
systemctl restart mysqld.service
 
cd /var/lib/mysql
 
#可以看到此时重新生成了一个日志文件binlog.000005，原先是1~4。
#因为它的二进制日志格式改了，他不会再往原来的二进制日志文件写入了，而是写到一个新的日志文件中。

再修改表中数据后，去查看二进制日志内容

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
 
mysql> update school.student1 set age= age+1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from school.student1;
+------+----------+--------+------+
| id   | name     | sex    | age  |
+------+----------+--------+------+
|    2 | zhonghui | female |   27 |
|    3 | wangba   | NULL   | NULL |
|    4 | wangzi   | NULL   | NULL |
+------+----------+--------+------+
3 rows in set (0.00 sec)
 
mysql> exit
 
[root@harbor ~]# mysqlbinlog -v /var/lib/mysql/binlog.000005

查询内容如下：

3.3 删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

#查看系统变量，在mysql命令行中执行
#单位是秒，默认过期时间为30天，到期之后会自动删除
 
mysql> show variables like '%binlog_expire_logs_seconds%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set (0.00 sec)

4. 查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下， 查询日志是未开启的。

#检查参数查看开关是否开启
#可以看到默认是关闭的以及日志文件所处位置和文件名
mysql> show variables like '%general%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /var/lib/mysql/harbor.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)

如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

vim  /etc/my.cnf 
 
#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启
general_log=1
 
#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log
general_log_file=mysql_query.log
 
#重启mysql服务
systemctl restart mysqld.service
 
#查看这个目录下是否会生成此日志文件
ls /var/lib/mysql/ -l 
 

开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现mysql_query.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。所以用不上此日志文件，我们可以把它关上。

5.  慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为10 秒，最小为 0， 精度可以到微秒。

解释：

• 慢查询日志记录了执行效率比较低，执行速度比较慢的sql语句。
• 之前在索引的sql性能分析中讲解过。

如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：

#慢查询日志：1代表开启
slow_query_log=1
 
#执行时间参数：表示执行时间超过2秒就是慢查询日志，此时慢查询日志文件就会记录这条sql.
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下所述。

解释：

• 通过在vim /etc/my.cnf 配置文件中配置这2个参数，可以改变它的默认行为。
• 如果添加了log_slow_admin_statements =1：表示当我们执行比较慢的管理语句的时候，也会记录在慢查询日志当中。
• 如果添加了log_queries_not_using_indexes = 1：表示如果某一条sql语句，它没有使用索引而造成执行效率比较慢的话，也会记录在慢查询日志当中。
• 通过慢查询日志就可以定位出那些sql执行效率低，从而对这类的sql进行优化。

#记录执行较慢的管理语句
log_slow_admin_statements =1
 
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

测试：

客户端1：

mysql -uroot -p1234
 
#db01数据库下有tb_sku表，存放了1000万条记录
#电脑太卡，所以我没有创建tb_sku表,这里不在演示，只显示最终结果
use db01;
 
#不会记录
select * from tb_user limit 0,10; -- 这条SQL执行效率比较高, 执行耗时 0.01sec
 
#前面学习过SQL优化，分页查询越向后效率越低，此时超过2秒，会记录在慢查询日志中
select * from tb_user limit 1000000,10; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时4.71sec（秒）

客户端2:

#配置慢查询日志
vim /etc/my.cnf 
 
#配置的内容
slow_query_log=1
 
long_query_time=2
 
# 重启Mysql服务器
systemctl restart mysqld
 
# 进入到此目录，发现会有一个后缀是-slow.log的日志文件
ls /var/lib/mysql/ -l
 
#实时刷新文件尾部的位置发现：
#记录了什么时间哪一个用户在哪一个主机上执行了什么样的sql语句
tail -f mysql8-slow.log