数据库服务日志概述

任何一种数据库中，都会有各种各样的日志，记录这数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件；

主要是针对数据库server层产生的数据信息，主要用于记录和数据库服务运行本身有关的日志、以及SQL语句操作执行相关的日志；

数据库日志常用分类

1
2
#查看日志信息
mysql > show variables like '%log%';

常用日志信息

日志名称 说明
general_log 表示查询日志（通用日志），默认日志状态处于关闭，可以进行在线调整配置
作用：记录了客户端从会话连接开始，执行过的所有SQL语句信息；
常用于 功能测试、语句审计
log_error 表示错误日志（运行日志），默认日志状态处于激活
作用：记录了数据库服务启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息；
log_bin 表示二进制日志（binlog日志），默认日志状态处于激活（8.0之后）
作用：记录了所有的DDL语句和DML语句，但是不包括数据库查询语句；语句以事件的形式保存，描述了数据的更改过程，此日志对于灾难时的数据恢复起着极其重要的作用。
slow_query_log 表示慢查询日志，记录了所有执行时间超过参数long_query_time设置值并且扫描记录数小于min_examined_row_limit的所有SQL语句的日志。

通用日志general_log

1
2
3
4
5
6
7
8
9
10
11
12
13
#默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；
general_log=OFF  
#定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
general_log_file=/app/mysql/data/general.log

#开启通用日志
[root@db01 ~]# vim /etc/my.cnf
general_log=OFF  
general_log_file=/app/mysql/data/general.log


# 修改日志默认状态（激活日志）：
mysql > set global general_log=1;

注意：企业真实环境，由于日志记录量比较大，所以不建议打开此日志记录功能，可以在有需要时打开，支持在线配置调整；

错误日志log_error

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
#默认开启
#默认存储路径 datadir
mysql >show variables like '%err%';
+---------------------+--------------+
| Variable_name | Value 						|
+---------------------+--------------+
| log_error 		| ./db01.err 				|    /app/mysql/data
+---------------------+--------------+

# 修改日志存储路径（永久配置）：
1.编辑配置文件
[root@xiaoq ~]# vim /etc/my.cnf
log_error=/data/db01.err

2.创建错误日志文件
touch /data/db01.err

3.授权文件
chown mysql.mysql /data/db01.err

4.重启数据库
systemctl restart msyqld


# 模拟故障日志应用
[root@oldboyxiaoq ~]# ll /data/3306/data/ibdata1 
-rw-r----- 1 mysql mysql 12582912 Nov 16 17:46 /data/3306/data/ibdata1

[root@oldboyxiaoq ~]# chmod 000 /data/3306/data/ibdata1

[root@oldboyxiaoq ~]# /etc/init.d/mysqld restart

Shutting down MySQL............................... SUCCESS! 
Starting MySQL......................................... ERROR! The server quit without updating PID file (/data/3306/data/oldboyxiaoq.com.pid).
[root@oldboyxiaoq ~]# tail -20 /data/3306/data/oldboyxiaoq.com.err
2022-11-21T01:20:47.735040Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2022-11-21T01:20:47.744091Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2022-11-21T01:20:47.744808Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-11-21T01:20:47.745739Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-11-21T01:20:47.746526Z 0 [ERROR] [MY-010119] [Server] Aborting
-- 根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；

说明：企业真实环境，日志处于默认激活记录状态，可以使用错误日志信息做故障诊断，记录错误信息级别为note warning error；

二进制日志（log_bin)

binlog日志，此日志文件其实就是用于记录对数据库进行操作更改的语句信息的；

并且记录更改的语句信息以事件形式进行记录，但是需要注意的是查询相关的语句是不会被记录的，比如：select、show；

然而作为所有对数据库的改操作事件信息都会被记录，比如：insert、update、create、drop。。。

查看数据库binlog日志配置参数：

进入到数据库服务系统环境中，可以使用命令进行查看binlog日志功能是否开启；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#默认关闭

root@(none) > show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /app/mysql/data/mysql-bin       |
| log_bin_index                   | /app/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

#刷新日志  （企业中不使用）
mysql> flush logs;

刷新后日志记录会生成新的

==查看二进制文件==

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#查看当前使用的二进制文件
root@(none) > show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1252 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

#查看所有的二进制日志文件
root@(none) > show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
| mysql-bin.000002 |       329 |
| mysql-bin.000003 |       317 |
| mysql-bin.000004 |      1252 |
+------------------+-----------+

01日志信息基本配置

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
#修改二进制日志
## 修改配置文件 MySQL5.6
[root@db01 ~]# vim /etc/my.cnf
log-bin=mysql-bin
binlog_format=row

## 修改配置文件 MySQL5.7   (必须得有server -id)
[root@db01 ~]# vim /etc/my.cnf
server_id=1
log-bin=mysql-bin
binlog_format=row


#进行binlog日志目录路径信息修改时，需要创建指定的目录并设置权限信息，最后需要重新启动数据库服务生效；
或者
log_bin=binlog
# 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中；


# 配置文件修改后需要重启数据库服务，加载配置文件改动的信息：
[root@xiaoQ ~]# /etc/init.d/mysqld restart 
[root@xiaoQ xiaoQ]# ll -h /data/3306/data/binlog*
-rw-rw----. 1 mysql mysql 245 6月  24 02:19 /data/3306/data/binlog.00000N
-rw-rw----. 1 mysql mysql   16 6月  24 02:19 /data/3306/data/binlog.index
-- 数据库服务重启后，已经可以在数据库的数据存储目录中，看到binlog日志文件的踪影

1
企业真实环境，日志处于默认激活记录状态，可以使用日志信息进行灾难数据恢复，以及可以用于实现主从复制；

02二进制日志工作模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#查看当前二进制模式
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)


#设置二进制日志模式
vim /etc/my.cnf
[msyqld]
binlog_format=row

#重启
systemctl restart mysqld

  • 语句模式（MySQL5.6）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
优点：简单易读，占用磁盘空间小
缺点：不严谨

#截取日志
[root@db01 data]# mysqlbinlog  mysql-bin.000006
BEGIN
/*!*/;
# at 626
#240820 16:04:25 server id 1  end_log_pos 731 CRC32 0x9e2a3943  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1724141065/*!*/;
insert prod values(1001),(1200)
/*!*/;
# at 731
#240820 16:04:25 server id 1  end_log_pos 762 CRC32 0xf49ea6d8  Xid = 16
COMMIT/*!*/;
DELIMITER ;
# End of log file

  • row 行级模式（MySQL5.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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
优点：严谨，记录原数据和数据的变化过程
缺点：占用磁盘空间大，不易读

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


#对比，加上选项-vvv 就能看到数据变化过程

[root@db01 data]# mysqlbinlog --base64-output=decode-rows mysql-bin.000006
BEGIN
/*!*/;
# at 382
#240820 16:11:19 server id 1  end_log_pos 429 CRC32 0x5544eced  Table_map: `prod`.`prod` mapped to number 70
# at 429
#240820 16:11:19 server id 1  end_log_pos 474 CRC32 0x50d2d755  Write_rows: table id 70 flags: STMT_END_F
# at 474
#240820 16:11:19 server id 1  end_log_pos 505 CRC32 0x6992e02c  Xid = 13
COMMIT/*!*/;
DELIMITER ;
# End of log file


[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000006
BEGIN
/*!*/;
# at 382
#240820 16:11:19 server id 1  end_log_pos 429 CRC32 0x5544eced  Table_map: `prod`.`prod` mapped to number 70
# at 429
#240820 16:11:19 server id 1  end_log_pos 474 CRC32 0x50d2d755  Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `prod`.`prod`
### SET
###   @1=1214 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `prod`.`prod`
### SET
###   @1=2236 /* INT meta=0 nullable=1 is_null=0 */
# at 474
#240820 16:11:19 server id 1  end_log_pos 505 CRC32 0x6992e02c  Xid = 13
COMMIT/*!*/;
DELIMITER ;
# End of log fil
  • mixed 混合模式

03 二进制日志操作

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
#查看所有二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       167 |
| mysql-bin.000004 |       785 |
| mysql-bin.000005 |       167 |
| mysql-bin.000006 |       505 |
+------------------+-----------+
6 rows in set (0.00 sec)

#查看日志事件（日志名，位置点，SQL语句类型，server_id，位置结束点，执行语句信息）
root@(none) > show binlog events in 'mysql-bin.000008';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000008 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.50-log, Binlog ver: 4 |
| mysql-bin.000008 | 120 | Query       |         1 |         211 | create database lzz                   |
| mysql-bin.000008 | 211 | Query       |         1 |         294 | drop database lzz                     |
| mysql-bin.000008 | 294 | Query       |         1 |         386 | drop database luay                    |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+


#查看当前二进制日志所在位置
root@(none) > show master status;
+------------------+----------+--------------+
| File             | Position | Binlog_Do_DB |
+------------------+----------+--------------+
| mysql-bin.000008 |      386 |              |
+------------------+----------+--------------+

==事件==

1）在binlog中最小的记录单元为event

2）一个事务会被拆分成多个事件（event）

==事件特性 binlog特性==

1
2
3
4
5
6
1）每个event都有一个开始位置（start position）和结束位置（stop position）
2）所谓的位置就是event对整个二进制的文件的相对位置(文件大小)
3）对于一个二进制日志中，前120个position是文件格式信息预留空间（MySQL5.6）
3）对于一个二进制日志中，前154个position是文件格式信息预留空间（MySQL5.7）
4）MySQL第一个记录的事件，都是从120开始的（MySQL5.6）
4）MySQL第一个记录的事件，都是从154开始的（MySQL5.7)

04 binlog的刷新和删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
## binlog 刷新
1.重启MySQL数据库
2.flush logs;
3.mysqladmin -uroot -p123 flush-log
4.当binlog大小1G会自动刷新   1G自动切割日志

## binlog删除
1.根据时间删除
#临时生效
SET GLOBAL expire_logs_days = 7;

#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

2.根据时间删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

3.根据文件名删除（常用）  ---仅保留mysql-bin.000020之后的binlog
PURGE BINARY LOGS TO 'mysql-bin.000020';

4.删除所有binlog，从001生成   ❌🔞🈲危险!!!!
reset master;

:dango:

05 日志配置信息扩展

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
# 参数一：sync_binlog 表示刷新日志到磁盘策略
root@(none) > select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

-- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
-- 此参数信息是有三种方式进行配置的：
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
-- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
-- 参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

# 参数二：binlog_format 定义binlog日志的格式信息
root@(none) > select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

-- 在进行主从同步数据恢复时，此参数配置可能会影响数据恢复的一致性问题；
-- 此参数信息是有三种方式进行配置的，确定了主从复制的级别，只针对DML语句的日志才有效；
-- 参数信息配置 statement（SBR）：语句格式记录binlog；

-- DDL DCL语句只能使用statement 表示的就是原原本本的语句信息，即做什么就记录什么；
create database xiaoQ;  

-- 参数信息配置 row（RBR）：行格式记录binlog（默认模式）
update t1 set a=10 where id<10;    
-- 会记录行的变化信息，属于底层的记录信息，可能会有多个变化日志信息记录

-- 参数信息配置 mixed（MBR）：混合格式记录binlog
-- 由数据库服务自行决定，是记录语句信息，还是记录行的变化信息；

慢查询日志

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# 作用：记录执行的比较慢的SQL语句
# 默认是否开启：否
# 存储位置：/app/mysql/data/
root@(none) > show variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | OFF                                  |
| slow_query_log_file       | /app/mysql-5.6.50/data/db01-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)

# 名字：hostname-slow.log
db01-slow.log

# 修改方式
## 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log=1

#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/app/mysql/data/db01-slow.log

#设定慢查询的阀值(默认10s)
long_query_time=0.05

#不使用索引的慢查询语句是否记录到日志
log_queries_not_using_indexes

#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 （很少用）
min_examined_row_limit=100




## 模拟慢查询
mysql >create table world.city1 select * from world.city;
mysql >insert into zls_city select * from zls_city;
[root@db01 ~]# mysqldumpslow
-s：指定排序顺序
c：Count 执行次数
t：Time 执行时间 总时间
r：Rows 结果行数 总行数
l：Lock 锁表时间 总锁表时间
at：平均时间
ar：平均行数
al：平均锁表时间
## 第三方 percona
[root@db01 ~]# yum install -y https://downloads.percona.com/downloads/percona-toolkit/3.6.0/binary/redhat/7/x86_64/percona-toolkit-3.6.0-1.el7.x86_64.rpm


#慢查询扩展
yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

#使用percona公司提供的pt-query-digest工具分析慢查询日志
[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log

成可视化界面：
Anemometer基于pt-query-digest将MySQL慢查询可视化
https://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载
https://github.com/box/Anemometer 可视化代码下载

:dango:percona慢查询可视化

1
2
3
4
5
#安装工具
yum install -y https://downloads.percona.com/downloads/percona-toolkit/3.6.0/binary/redhat/7/x86_64/percona-toolkit-3.6.0-1.el7.x86_64.rpm

#下载可视化工具
$ git clone git://github.com/box/Anemometer.git anemometer

配合binlog恢复数据

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
模拟数据：
# 1.创建binlog库
create database binlog;
use binlog

# 2.创建表
create table binlog_table(id int);

# 3.插入数据
insert into binlog_table values(1);
+------+
| id 	|
+------+
| 1 	|
+------+

# 4.插入数据
insert into binlog_table values(2);
insert into binlog_table values(3);
commit;
select * from binlog_table;
+------+
| id	 |
+------+
| 1 	 |
| 2 	 |
| 3 	 |
+------+

# 5.删除数据
delete from binlog_table where id=1;
commit;
root@localhost:binlog:18: >select * from binlog_table;
+------+
| id   |
+------+
| 2 	 |
| 3    |
+------+

# 6.修改数据
update binlog_table set id=22 where id=2;
commit;
root@localhost:binlog:19: >select * from binlog_table;
+------+
|  id  |
+------+
| 22 	|
|  3 	|
+------+

# 7.删表
drop table binlog_table;

# 8.删库
drop database binlog;

想要恢复数点：
begin：30776
end：42009

mysqlbinlog --start-position=30776 --stop-position=42009 -d binlog /app/mysql/data/mysql-bin.000008 > /tmp/binlog.sql

使用binlog截取数据

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
50
51
52
53
54
55
56
57
#需要如下数据
+------+
| id 	|
+------+
| 1 	|
| 2 	|
| 3 	|
+------+

# 1.准备新环境
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# vim /etc/my.cnf     保持和旧环境一致

[root@db02 ~]# rm -fr /app/mysql-5.6.50/data
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --basedir=/app/mysql --datadir=/app/mysql/data
[root@db02 ~]# /etc/init.d/mysqld start

# 2.将旧数据库数据进行全备
[root@db01 data]# mysqldump -uroot -p123 -A > /tmp/full.sql

# 3.新环境创建一个可以远程连接的用户
mysql> grant all on *.* to root@'%' identified by '123';

# 4.将全备恢复到新环境
[root@db01 data]# mysql -uroot -p123 -h172.16.1.52 < /tmp/full.sql

# 5.旧环境截取binlog
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000030
起始位置点：120
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000030 |grep -iC 10 delete
结束位置点：832
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=832 /app/mysql/data/mysql-bin.000030 > /tmp/binlog.sql

# 6.恢复截取的数据到新环境
[root@db01 data]# mysql -uroot -p123 -h172.16.1.52 < /tmp/binlog.sql
root@localhost:(none):42: >select * from binlog.binlog_table;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
# 7.全备后的新增数据恢复到新环境
# 8.应用割接
- 开发改代码
- 运维导出新环境数据恢复到旧环境

#生产中会一直提供服务，实时在写入数据
每天全备+增备

#产中截取数据时，可能会截取到其他库的数据，怎么解决
#用-d指定恢复的库
mysqlbinlog --start-position=120 --stop-position=832 -d binlog app/mysql/data/mysql-bin.000030 > /tmp/binlog.sql

### binlog只能用来当成是增量数据的备份一定要配合mysqldump逻辑备份的全备一起使用