MySQL 日志管理

一、日志分类

二、错误日志

错误日志：记录 MySQL 服务器启动、关闭及运行错误等信息

默认开启，配置方式如下

首次启动会提示错误日志位置，后期查询错误日志存放路径的命令如下
mysql -uroot -p123
mysql> show variables like '%log_error%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/lib/mysql/error.log   |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)


修改默认错误日志文件路径
vim /etc/my.cnf
在[mysqld]中添加：
[mysqld]
log_error=/var/log/mysql.errlog  #错误日志文件路径

touch /var/log/mysql.errlog
chmod 640 /var/log/mysql.errlog 
chown mysql.mysql /var/log/mysql.errlog 
systemctl restart mysqld

查看是否修改成功
mysql -uroot -p123
mysql> show variables like '%log_error%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/log/mysql.errlog   |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

三、一般查询日志（通用日志）

一般查询日志：记录所有sql语句

默认不开启，开启方法如下

方式一：更改my.cnf配置文件（永久添加）

vim /etc/my.cnf

在[mysqld]中添加：
[mysqld]
general_log=on  #开启日志
general_log_file=/var/log/select.log  #日志文件路径

创建日志文件
touch /var/log/select.log
chmod 640 /var/log/select.log
chown mysql.mysql /var/log/select.log
systemctl restart mysqld

查看是否开启，为on则开启
mysql -uroot -p123
mysql> show variables like 'general_%';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log      | ON                             |
| general_log_file | /var/log/select.log            |
+------------------+--------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

方式二：使用mysql控制台开启（临时）

mysql -uroot -p123

查看当前日志输入类型（默认输出到file文件）
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

查看普通日志输出文件地址
mysql> show variables like 'general%';
+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | OFF                                      |
| general_log_file | /usr/local/mysql/data/VM-12-7-centos.log |
+------------------+------------------------------------------+
2 rows in set (0.00 sec)

开启一般日志
mysql> set global general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

修改日志文件路径
mysql> set global general_log_file='/var/log/select.log';
Query OK, 0 rows affected (0.00 sec)


创建日志文件
touch /var/log/select.log
chmod 640 /var/log/select.log
chown mysql.mysql /var/log/select.log
systemctl restart mysqld


查看是否开启
mysql -uroot-p123
mysql> show variables like 'general_%';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log      | ON                             |
| general_log_file | /var/log/select.log            |
+------------------+--------------------------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

四、慢查询日志

慢查询日志：记录在MySQL中响应时间超过阀值的语句

默认不开启，开启方法如下

方式一：更改my.cnf配置文件（永久添加）

vim /etc/my.cnf

在[mysqld]中添加：
[mysqld]
slow_query_log = 1  #指定是否开启慢查询日志
slow_query_log_file=/var/log/slow.log  #指定慢日志文件存放位置（默认在data）
long_query_time=0.05  #设定慢查询的阀值(默认10s)
log_queries_not_using_indexes=ON  #不使用索引的慢查询日志是否记录到日志

创建日志文件
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld

mysql -uroot -p123
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/log/slow.log              |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

方式二：mysql控制台开启（临时添加）

查看是否开启慢查询
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/slow.log        |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

查询超过多少秒才记录
mysql> show variables like 'long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

开启慢查询
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)


更改慢日志文件路径
mysql> set global slow_query_log_file='/var/log/slow.log';
Query OK, 0 rows affected (0.00 sec)

查询超过多少秒才记录的时间
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

创建日志文件
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld

查看是否开启日志
mysql -uroot -p123
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/log/slow.log              |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

五、配置文件开启日志汇总

 vim /etc/my.cnf
在[mysqld]中添加：
[mysqld]
log_error=/var/log/mysql.errlog       #记录错误日志
general_log=on
general_log_file=/var/log/select.log  #记录所有的sql语句
slow_query_log = 1
slow_query_log_file=/var/log/slow.log # 记录在MySQL中响应时间超过阀值的语句
long_query_time=0.05
log_queries_not_using_indexes=ON


touch /var/log/mysql.errlog
chmod 640 /var/log/mysql.errlog 
chown mysql.mysql /var/log/mysql.errlog 

touch /var/log/select.log
chmod 640 /var/log/select.log
chown mysql.mysql /var/log/select.log

touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

六 二进制日志

6.1 二进制日志简介

二进制日志即binlog日志，记录了mysql数据库所有的dml,ddl语句事件（不包含select）。记录增删改，也可以记录SQL语句及行记录变化，还可以记录这些操作事件；总之会记录所有修改操作的SQL语句。

不要混淆以下三种日志：

（1）general log：记录数据库里的所有SQL操作记录

（2）redo log：只记录innodb存储引擎的修改日志

（3）binlog：只记录server层面内部的修改情况。–select /show 不记录

6.2 开启binlog日志的好处

（1）数据恢复：可以基于时间点恢复，以及根据其进行增量与差异备份
（2）mysql主从复制，通过binlog实现数据复制
1
2

6.3 二进制日志的工作模式

（1）语句模式：binlog_format=statement（Mysql5.7.6之前的默认级别）

# 语句模式介绍
记录对数据库做出修改的sql语句,不记录该sql的上下文信息

例如
1、开启binlog日志后，我们在某个库下自定义函数，若想定义成功需要先设置配置项
set global log_bin_trust_function_creators=TRUE;  

2、然后自定义函数
delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;

3、最后执行一条插入语句
insert into t1(name) values(concat("jason",f1(1,2)));

如果使用statement模式，那么这条insert语句将会被记录到二进制日志中,而sql语句中依赖的f1的定义是不会记录下来的，f1只存在于当前库。

# 优点
不需要记录细到每一行数据的更改变化，因此，binlog日志量小，IO压力小，性能较高

例如:
    一条语句修改了100万行，该模式下只需要记录下该条语句即可

# 缺点
日志中记录的sql语句可能有上下文依赖，此时脱离了当前数据库环就无法运行了，因此该模式下容易出现主从不一致的问题。

例如：
	主库记录的某条sql语句引用了主库中的函数、触发器、存储过程等特殊功能
    在从库上接收了该sql之后，可能就无法正确运行，从而主从库数据不一致的问题。

ps：row模式是基于每一行来记录变化的，所以不会出现类似的问题。

# 应用场景
sql语句对mysql内置功能依赖比较少：不使用存储过程/触发器/函数，可以使用该模式，否则还是推荐行级模式
折叠 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

（2）行级模式：binlog_format=row，（mysql5.7.6之后+8.0的默认级别）

# 行级模式介绍
记录每一行数据修改的细节，即哪一条记录被修改了，修改成什么样了

例如：执行语句（f1参照上例，此处略）
insert into t1(name) values(concat("jason",f1(1,2)));

如果使用row模式，那么日志中会记录插入了一条新记录，记录中的name字段值为'jason3'

# 优点
相当于把上下文依赖都记录了下来，可以更方便查看每一条数据修改的细节，并且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题，即该模式下主从复制强一致，数据最安全。

# 缺点
日志量大

例如
    一条语句修改了100万行，语句模式下只需要记录一条语句即可
	而行级模式却修改记录下100万行的修改记录，binlog日志的量可能会大得惊人。
    
# 应用
sql语句对mysql内置功能依赖比较多，希望数据最安全，复制强一致的场景推荐行级模式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（3）混合模式：binlog_format=mixed，一般不用

# 混合模式介绍
结合 row level 与statement level的优点
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
•当函数中包含UUID()时。
•当表中有自增列（AUTO_INCREMENT）被更新时。
•当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
•当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

# 应用场景
看上去这种方式似乎比较美好，但是在生产环境中，为了保险起见，一般会使用row模式。
1
2
3
4
5
6
7
8
9
10

6.4 binlog日志相关概念

二进制日志文件，顾名思义，它是二进制的，所以我们不能直接使用cat命令进行查看，而是需要通过一些别的命令查看其内容，而且，二进制日志文件，有”事件”和”位置”的概念，什么是事件呢？

(1) 事件events

通俗的讲，我们可以把binlog中的每一条记录当做一个”事件”，因为binlog记录了所有对数据库进行的修改，所以，我们可以认为，数据库的修改被记录到二进制日志中，这些记录每一条都可以理解为一个”事件”。

(2) 位置position

由于二进制日志文件是二进制的，所以，我们可以把整个二进制文件想象成一个字节序列。假设，二进制日志文件刚开始是空的，从第1个字节开始记录，假设记录第一个”事件”（第一条记录），需要15个字节，那么第一个事件的开始”位置”就是1，结束”位置”就是15，由于前15个字节已经被第一个事件占用，那么当我们想要通过二进制日志记录第二个事件时，则需要从第15个字节向后开始记录，假设记录第二个”事件”需要20个字节，那么第二个事件在binlog中的起始”位置”就是15，结束”位置”就是35，以此类推。

6.5 二进制日志相关参数

(1) server_id

服务ID,主从库必须不一样，建议数字为:ip+端口，5.7.3以后版本,必须指定
1

(2) log-bin

此变量用于控制是否开启二进制日志，而且这是一个只读变量，默认值为OFF

当我们启动数据库以后，在当前数据库连接中查看此变量的值，此变量值可能为OFF，表示不记录二进制日志，如果想要记录二进制日志，只需将此值设置为二进制日志的文件名即可，但是需要注意的是，我们无法在当前会话中使用set命令设置log_bin的值，因为它是一个只读变量，我们只能通过修改my.cnf的方式，设置log_bin的值，假设，我们编辑my.cnf文件，设置log_bin的值为mybinlog，那么，在mysql的数据目录中，将会自动生成一个以mybinlog为文件名前缀的二进制日志文件，如果想要再次禁用binlog，只需要将log_bin这一行配置从my.cnf文件中注释即可，或者将其删除，重启mysql服务后，再次查看log_bin的值，其值为OFF，注意，不要直接在my.cnf文件中将log_bin的值设置为ON或者OFF，如果这样做，你将会看到以ON或者OFF为文件名前缀的二进制日志文件。换句话说，如果my.cnf配置文件中没有log_bin的配置，则表示未开启二进制日志，如果my.cnf中存在log_bin的配置，那么则表示开启了二进制日志，同时二进制日志文件的名称将会以log_bin对应的值为文件名前缀，同时，二进制日志文件的后缀名会进行自动编号，每次日志滚动后，后缀名编号自动加1。

示例：
log-bin=/var/lib/mysql/mybinlog #绝对路径
# log-bin=mybinlog #也可以用相对路径， 会在$datadir下产生mysqlbinlog-00000N
1
2
3
4
5
6
7

(3) log_bin_index

不设置的话，会根据log_bin值名称自动生成mybinlog.index
log_bin_index=var/lib/mysql/mybinlog.index 
1
2

(4) sql_log_bin

默认为ON
此变量用于标识当前会话中的操作是否会被记录于二进制日志，此变量值设置为ON，则表示在当前数据库连接中，对数据库进行修改的语句将会被记录到binlog中，此变量值设置为OFF，则表示在当前数据库连接中，对数据库进行的修改的语句将不会被记录到binlog中，在主从复制结构中，这些语句由于没有被binlog记录，所以也不会同步到从节点中。换句话说，即使在my.cnf配置文件中设置了log_bin的值，当前会话中，如果sql_log_bin的值设置为OFF，当前会话的操作也不会记录在二进制日志中。而且需要注意的是，sql_log_bin是一个会话界别的变量，只能在当前会话中使用set sql_log_bin命令进行设置，不能使用set global sql_log_bin命令进行设置，因为它是会话级别的变量，而且，sql_log_bin也不能配置在my.cnf文件中，否则可能会无法启动mysql。
1
2

(5) binlog_format

此变量值得含义上文已经解释过，此变量的值决定了二进制日志的记录方式，此变量的值可以设置为statement、row、mixed，分别表示以语句的形式记录二进制日志，以数据修改的形式记录二进制日志，以混合的方式记录二进制日志，安全保险起见，推荐使用row的方式记录。
1

(6) max_binlog_size

设置单个二进制日志文件的最大大小，以字节为单位，超过此值大小，则二进制日志文件会自动滚动，比如设置为500M为524288000。
1

(7) sync_binlog

在存储引擎章节中我们提过：
innodb_flush_log_at_trx_commit控制redo日志的刷盘策略，建议值为1
sync_binlog控制binlog日志的刷盘策略，建议值为1

当我们把innodb_flush_log_at_trx_commit设置为1的时候，表示事务提交时，事务日志立马从内存刷写到磁盘中的事务日志文件中，而sync_binlog对于二进制日志的作用，就像innodb_flush_log_at_trx_commit对于事务日志的作用，由于二进制日志一开始存在于内存（binlog_cache）中，如果将sync_binlog设置为1，则表示每1次事务提交之后，都会立即将内存中的二进制日志立即同步到磁盘中的二进制日志文件中，如果将sync_binlog设置为0，则表示当事务提交之后，mysql不会立即将内存中的binlog刷写到磁盘中的binlog日志文件中，而是由文件系统决定什么时候刷写，这可能取决于文件系统的缓存机制，当此值设置为0时，一旦操作系统宕机，那么将丢失未从内存中同步到磁盘中的binlog，所以，当此值设置为0时，安全性最差，但是性能最高，当此值设置为1时，安全性最高，性能最差，除了将此值设置为0或1，还能设置为N，假设将此值设置为3，则表示每3次事务提交后，将binlog从内存刷写到磁盘一次，值设置的越大，有可能丢失的日志数据将会越多，当然，性能会越好，在追求安全的情况下，推荐设置为1，但是听说，此值设置为0和设置为1时在性能上的差距还是比较明显的，如果设置为0或N，最好为操作系统准备带有备用电源的缓存。
1
2
3
4
5

(8) 其他参数

#打开才能查看详细记录，默认为off
binlog_rows_query_log_events=on 

#表示自动删除10天以前的日志
expire_logs_days=10 

# full,minimal,noblob分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
binlog_row_image=full #（full,minimal,noblob），
1
2
3
4
5
6
7
8

6.5 查看binlog配置项

show variables like '%log_bin%';
show variables like '%binlog%';
show variables like '%binlog_format%';
show variables like '%server%';
show variables like 'expire_logs_days'; -- 过期日志天数

# 或者
[root@db01 ~]# mysqladmin -uroot -pEgon@123 variables |grep -w log_bin
1
2
3
4
5
6
7
8

6.6 开启binlog

1.默认是关闭的

2.配置开启binlog
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/var/lib/mysql/mybinlog 
binlog_format='row' #(row,statement,mixed)，不建议随意去修改binlog工作模式    
binlog_rows_query_log_events=on 
max_binlog_size=100M 
1
2
3
4
5
6
7
8
9
10

6.7 查看binlog日志

查看日志日志名、状态、事件
[rml_read_more]：

show binary logs;
show master logs;
show master status;
show binlog events in 'mybinlog.000002';
show binlog events in 'mybinlog.000002' limit 3;
1
2
3
4
5

查看日志内容

# 1、执行下述sql
create database if not exists test;
use test;
create table user(name varchar(10));
begin;
insert into user values("jason1"),("jason2"),("jason3");
update user set name="EGON" where name="jason1";
commit;
show master status; -- binlog的当前position

# 查看binlog，看看有没有记录，二进制文件我们看不了，有专门的命令
查看全部：
# mysqlbinlog mybinlog.000002

按时间：
# mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56"
# mysqlbinlog mybinlog.000002 --stop-datetime="2022-11-05 11:02:54"
# mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56" --stop-datetime="2022-11-05 11:02:54" 
 
按字节数：
# mysqlbinlog mybinlog.000002 --start-position=337
# mysqlbinlog mybinlog.000002 --stop-position=662
# mysqlbinlog mybinlog.000002 --start-position=337 --stop-position=662

如果是行级模式，想要看懂详细内容则需要加上额外参数，但是仅用于看懂内容，如果要用于还原数据，还是应该去掉额外的参数并将内容定位到文件中
#mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002  # 仅用于查看，不能用于日后的数据恢复

#mysqlbinlog mybinlog.000002  --start-position=100 > /tmp/1.sql # /tmp/1.sql可用于日后的数据恢复

如果查看过程报错
unknown variable 'default-character-set=utf8mb4' 
原因是mysql5.6中，mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个配置项目。

两个方法可以解决这个问题
一是在MySQL的配置/etc/my.cnf中
将default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

二是用mysqlbinlog --no-defaults mysql-bin.000001 命令打开
折叠 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

6.8 binlolg可以用来恢复数据

#修改数据
begin;
update user set name="XXX" where name="jason2";
commit;
#发现自己修改错了
select * from user;
#回滚，回滚不了，已经提交了
rollback;
select * from user;
#一怒之下删表
drop table user;

#恢复数据:查看binlog数据的起始点与要恢复到的位置点，导出成SQL
mysqlbinlog mybinlog.000002 --stop-position=772 > /tmp/binlog.sql

mysql -uroot -pEgon@123 < /tmp/binlog.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6.9 刷新与清除二进制日志

(1) 刷新binlog

#刷新binlog：关闭当前的二进制日志文件并创建一个新文件
1）手动执行命令刷新
flush logs;  
或者
mysqladmin -uroot -p flush-logs;
或者
mysql -uroot -pEgon@123 -e 'flush logs'
 
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）;当binlog达到1G,自动刷新
1
2
3
4
5
6
7
8
9
10

(2) 清除binlog

# 清除二进制日志原则
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除

1) 删除所有binlog，相当于重置
reset master;

2) 删除指定binlog名之前的所有binlog(保留指定的binlog)
purge binary logs to 'mybinlog.00003'; -- mybinlog.00003之前的都删除掉

3）删除日期之前的日志：手动执行
PURGE {MASTER | BINARY} LOGS BEFORE 'date' --用于删除日期之前的日志，BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式

如：（MASTER 和BINARY 在这里都是等效的）
PURGE MASTER LOGS TO 'mybinlog.00003';
purge binary logs before '2021-07-13 19:11:00';

还可以做减法：如只保留3天的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

4）删除日期之前的日志：修改配置参数，让mysql自动执行
删除7天前的binlog
#临时生效
SET GLOBAL expire_logs_days = 7;

#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29