[toc]

MySQL日志简介

image-20230702163029651

错误日志

  • 作用：

    • 记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      [ERROR] Could not use /application/mysql/data/slow.log for logging
      (error 2 - No such file or directory). Turning logging off for the
      server process. To turn it on again: fix the cause, then either
      restart the query logging by using "SET GLOBAL SLOW_QUERY_LOG=ON"
      or restart the MySQL server.

      # 服务没有启动
      ps -ef|grep mysql
      netstat -lntup

      # 启动并生成了socket但是位置不对
      [mysqld]
      socket=/xxx
      [mysql]
      socket=/xxx

  • 默认位置：

    • 源码安装 二进制安装

      1
      $DATADIR

    • yum安装

      1
      /var/log/

  • 文件名

    • 源码安装 二进制安装

      1
      $hostname.err

    • yum安装

      1
      mysqld.log

  • 默认是否开启

    • 开启

  • 开启方式:

    • （MySQL安装完后默认开启)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      # 命令行启动：
      mysqld --log-error=/tmp/a.log

      # 配置文件开启
      vim /etc/my.cnf
      [mysqld]
      log_error=/application/mysql/data/$hostname.err

      # 注意：只要不将放在带有setuid权限的目录下，那就需要先创建再授权，然后才能启动MySQL
      touch /opt/a.log
      chown mysql.mysql /opt/a.log

      # 查看方式
      show variables like 'log_error';

一般查询日志

  • 作用

    • 记录mysql所有执行成功的SQL语句信息，可以做审计用，因为会占用大量磁盘空间所以很少开启。

  • 默认位置

    • 源码安装 二进制安装

      1
      $DATADIR

    • yum安装

      1
      /var/log/mysql/

  • 文件名

    • 源码安装 二进制安装 yum安装

      1
      $hostname.log

  • 默认是否开启

    • 不开启

  • 开启方式

    • （MySQL安装完之后默认不开启）

      1
      2
      3
      4
      5
      6
      7
      8
      # 编辑配置文件开启
      vim /etc/my.cnf
      [mysqld]
      general_log=on
      general_log_file=/application/mysql/data/$hostnamel.log

      # 查看方式
      show variables like '%gen%';

慢查询日志

慢查询日志解析

  • 作用

    • 是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
    • 通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

  • 默认位置

    • 源码安装 二进制安装 yum安装

      1
      $DATADIR

  • 文件名

    • 源码安装 二进制安装 yum安装

      1
      $hostname-slow.log

  • 默认是否开启

    • 不开启

  • 开启方式

    • （MySQL安装完之后默认不开启）

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      # 编辑配置文件开启
      vim /etc/my.cnf
      [mysqld]
      slow_query_log=1
      long_query_time=0.05 # 设置SQL语句查询时间阈值
      log_queries_not_using_indexes

      # 配置文件解析
      ## 指定是否开启慢查询日志
      slow_query_log = 1
      ## 设定慢查询的阀值(默认10s)
      long_query_time=0.05
      ## 不使用索引的慢查询日志是否记录到索引
      log_queries_not_using_indexes
      ## 指定慢日志文件存放位置（默认在data）
      slow_query_log_file=/application/mysql/data/slow.log
      ## 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
      min_examined_row_limit=100

慢查询日志查看工具

使用mysqldumpslow命令来分析慢查询日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 输出记录次数最多的10条SQL语句
mysqldumpslow -s c -t 10 /database/mysql/slow-log

# 参数说明:
-s     # 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t     # 是top n的意思，即为返回前面多少条的数据；
-g     # 后边可以写一个正则匹配模式，大小写不敏感的；

# 示例
mysqldumpslow -s t -t 3 db01-slow.log
mysqldumpslow -s c -t 3 db01-slow.log
mysqldumpslow -s r -t 3 db01-slow.log
mysqldumpslow -s l -t 3 db01-slow.log

mysqldumpslow -s at -t 3 db01-slow.log
mysqldumpslow -s ac -t 3 db01-slow.log
mysqldumpslow -s ar -t 3 db01-slow.log
mysqldumpslow -s al -t 3 db01-slow.log

使用percona公司提供的pt-query-digest工具分析慢查询日志

https://downloads.percona.com/downloads/percona-toolkit/3.5.2/binary/redhat/7/x86_64/percona-toolkit-3.5.2-2.el7.x86_64.rpm

image-20230413174419928

1
2
3
4
5
# yum安装
yum localinstall -y percona-toolkit-3.5.2-2.el7.x86_64.rpm

# 使用percona公司提供的pt-query-digest工具分析慢查询日志
pt-query-digest /var/log/mysql/db01-slow.log

可以二次开发做成可视化界面

Anemometer基于pt-query-digest将MySQL慢查询可视化

慢日志分析工具下载

可视化代码下载

image-20230702163150778

二进制日志

二进制日志解析

  • 作用

    • 记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
    • 记录所有DDL、DCL等语句
    • 二进制日志会记录所有对数据库发生修改的操作

  • binlog的作用

    • 如果我拥有数据库搭建开始所有的二进制日志，那么我可以把数据恢复到任意时刻
    • 数据的备份恢复
    • 数据的复制

  • 默认位置

    • 源码安装 二进制安装 yum安装

      1
      $DATADIR

  • 文件名

    • 源码安装 二进制安装 yum安装

      1
      2
      3
      4
      mysql-bin.000001
      mysql-bin.000002
      mysql-bin.000003
      ...

  • 默认是否开启

    • 开启

  • 开启方式

    • mysql5.6

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      # 编辑配置文件开启
      vim /etc/my.cnf
      [mysqld]
      log-bin=/app/mysql/data/zls-bin
      ## 二进制日志存放位置
      binlog_format=row
      ## binglog的工作模式

      # 查看是否开启
      show master status;

    • mysql5.7

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      # 编辑配置文件开启
      vim /etc/my.cnf
      [mysqld]
      log-bin=mysql-bin
      ## 二进制日志存放位置
      binlog_format=row
      ## binglog的工作模式
      server_id=1
      ## 注意:在mysql5.7中开启binlog必须要加上server-id。

      # 查看是否开启
      show master status;

binlog的工作模式

  • statement：语句模式（MySQL5.6默认）

    • 只记录执行的SQL语句
    • 优点：通俗易懂、占用磁盘空间小。
    • 缺点：记录不够严谨。
    1
    2
    3
    4
    5
    6
    show variables like 'binlog_format';
    +---------------+-----------+
    | Variable_name | Value     |
    +---------------+-----------+
    | binlog_format | STATEMENT |
    +---------------+-----------+

  • row：行级模式（MySQL5.7默认）

    • 记录执行的SQL语句，并且还要记录变化过程
    • 优点：记录更加严谨。
    • 缺点：不易读、占用磁盘空间大。
    1
    2
    3
    4
    5
    6
    show variables like 'binlog_format';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | binlog_format | ROW   |
    +---------------+-------+

  • mixed：混合模式

二进制日志的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 查看二进制日志的工具
mysqlbinlog
mysqlbinlog --base64-output=decode-rows -vvv /var/lib/mysql/mysqlbin.000002

# 命令行查看二进制日志
show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 789       |
+------------------+-----------+

# 命令行查看位置点
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set  |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 789      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 查看binlog事件
show binlog events in 'mysql-bin.000007';

事件介绍

  1. 在binlog中最小的记录单元为event
  2. 一个事务会被拆分成多个事件（event）

事件特性

1
2
3
4
5
6
7
8
9
10
11
# mysql5.6
1）每个event都有一个开始位置（start position）和结束位置（stopposition）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。

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

row模式下二进制日志分析及数据恢复

准备数据误删环境

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
# 刷新二进制日志
flush logs;

# 创建一个binlog库
create database binlog;

# 查看binlog信息
show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000002 | 319      |
+------------------+----------+

# 进入binlog库
use binlog

# 创建binglog表
create table binlog(id int);

# 查看binlog信息
show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000002 | 489      |
+------------------+----------+

# 插入数据1
insert into binlog values(1);
insert into binlog values(1);

# 提交
commit;

# 查看binlog信息
show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000002 | 841      |
+------------------+----------+

# 插入数据2
insert into binlog values(2);

# 提交
commit;

# 查看binlog信息
show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000002 | 1102     |
+------------------+----------+

# 更改数据
update binlog set id=3 where id=1;

# 提交
commit;

# 查看binlog信息
show master status;

# 删除字段
delete from binlog where id=3;

# 提交
commit;

# 插入数据1
insert into binlog values(1);

# 提交
commit;

# 删除表
drop table binlog;

# 删除库
drop database binlog;



# 查询表
select * from binlog;
+------+
| id   |
+------+
| 1    |
| 1    |
+------+

# 查询表
select * from binlog;
+------+
| id   |
+------+
| 1    |
| 1    |
| 2    |
+------+

# 查询表
select * from binlog;
+------+
| id   |
+------+
| 3    |
| 3    |
| 2    |
+------+

# 查询表
select * from binlog;
+------+
| id   |
+------+
| 2    |
+------+

# 查询表
select * from binlog;
+------+
| id   |
+------+
| 2    |
| 1    |
+------+

恢复数据步骤

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
# 1.查看binlog内容
mysqlbinlog --base64-output=decode-rows -vvv /var/lib/mysql/mysqlbin.000002

# at 1569
#230414 10:00:23 server id 1 end_log_pos 1614 CRC32 0xa634b644
Delete_rows: table id 111 flags: STMT_END_F
### DELETE FROM `binlog`.`binlog`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `binlog`.`binlog`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */

起始位置点：154
结束位置点：1444

# 2.截取binlog
mysqlbinlog --start-position=154 --stop-position=1444 /var/lib/mysql/mysql-bin.000002 > /tmp/binlog.sql

# 3.先临时关闭记录binlog
mysql> set sql_log_bin=0;

# 4.恢复数据
mysql> source /tmp/binlog.sql

# 5.开启记录binlog
mysql> set sql_log_bin=1;

Database changed
show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| binlog           |
+------------------+

# 6.查看表内容
mysql> select * from binlog;
+------+
| id   |
+------+
| 3    |
| 3    |
| 2    |
+------+

binlog存在问题

  • 恢复成本很高

    • 数据库或表被误删除的是很久之前创建的（一年前）

    • 解决方案：结合mysqldump全备恢复数据，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
      # 查找binlog中的内容两种方式
      ## statement
      mysqlbinlog /var/lib/mysql/xxx.000001
      ## row
      mysqlbinlog --base64-output=decode-rows -vvv /var/lib/mysql/xxx.000001
      ## 进入数据库查看binlog事件
      mysql> show binlog events in 'mysql-bin.000002';

      # 多个库的二进制日志穿插示例
      mysql> insert into db2.tb2 values(6);
      mysql> insert into db2.tb2 values(7);
      mysql> commit;
      mysql> insert into db2.tb2 values(8);
      mysql> commit;
      mysql> insert into db1.tb1 values(5);
      mysql> insert into db1.tb1 values(6);
      mysql> commit;

      mysql> select * from db1.tb1;
      +------+
      | id   |
      +------+
      | 1    |
      | 2    | 
      | 3    |
      | 4    |
      | 5    |
      | 6    |
      +------+

      mysql> select * from db2.tb2;
      +------+
      | id   |
      +------+
      | 1    |
      | 2    |
      | 3    |
      | 4    |
      | 5    |
      | 6    |
      | 7    |
      | 8    |
      +------+

      起始位置点：2437
      结束位置点：3289

  • 如果只想恢复某一个库的数据

    • 过滤数据，不能使用’grep’

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      # 指定查看某个库相关的二进制日志
      -d '库名'
      mysqlbinlog --start-position=154 --stop-position=1779 -d 'db1' /var/lib/mysql/mysql-bin.000003 >/tmp/db1.sql

      起始位置点：154
      结束位置点：1779

      # 恢复数据
      mysql> set sql_log_bin=0;
      mysql> source /tmp/db1.sql
      mysql> set sql_log_bin=1;

binlog刷新机制

  • 自动刷新
    • 重启MySQL之后会刷新
    • 当binlog大小达到1G的时候，会自动刷新
  • 手动刷新
    • mysqladmin -uroot -p123@qqdianCOM flush-log
    • mysql> flush logs;
    • 做备份时，可以加刷新binlog的参数
      • mysqldump -F
      • 此参数在备份时使用，会刷新出基于当前库数量的二进制日志文件

删除binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 1.根据时间删除
mysql> set global expire_logs_days = 7;
vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

# 2.使用purge命令按时间删除
purge binary logs before now() - interval 7 day;

# 3.使用purge命令根据文件名删除（删除指定文件名及之前的二进制日志，推荐！！）
purge binary logs to 'mysql-bin.000010';

# 4.重置binlog(binlog全部重置，清空)
mysql> reset master;

模拟生产环境误删除库并恢复数据

操作流程

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
# 模拟用户写入数据
vim a.sh
#!/bin/bash
mysql -uroot -pHelp9090 -e 'drop database chenlin;'
mysql -uroot -pHelp9090 -e 'create database if not exists chenlin;'
mysql -uroot -pHelp9090 -e 'use chenlin;create table if not exists chenlin1(id int);'
num=0
while true;do
	((num ++))
	mysql -uroot -pHelp9090 -e "insert into chenlin.chenlin1
values($num);commit;"
	sleep 1
done

# 创建库1
mysql> create database test1;

# 创建表1
mysql> create table test_a(id int);

# 表1插入数据
mysql> insert into test1.test_a values(1);
mysql> insert into test1.test_a values(2);
mysql> insert into test1.test_a values(3);
mysql> insert into test1.test_a values(4);
mysql> insert into test1.test_a values(5);

# 创建库2
mysql> create database test2;

# 创建表2
mysql> create table test2.test_b(id int);

# 表2插入数据
mysql> insert into test2.test_b values(1);
mysql> insert into test2.test_b values(2);
mysql> insert into test2.test_b values(3);
mysql> insert into test2.test_b values(4);


# 查看表1内容
mysql> select * from test1.test_a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

# 查看表2内容
mysql> select * from test2.test_b;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

# 删除表1
mysql> drop table test1.test_a;

# 删除表2
mysql> drop table test2.test_b;


create1
create2
DML
DML
DML
drop table1
DML
DML
DML
drop table2
DML
DML

# 截取点1
mysqlbinlog --base64-output=decode-rows -vvv mysql_bin.000001 |grep create -C 10
起始位置点：24485
mysqlbinlog --base64-output=decode-rows -vvv mysql_bin.000001 |grep DROP -C 10
结束位置点：29469

# 截取点2
mysqlbinlog --base64-output=decode-rows -vvv mysql_bin.000001 |grep DROP -C 10
起始位置点：29593
mysqlbinlog --base64-output=decode-rows -vvv mysql_bin.000001 |grep DROP -C 10
结束位置点：31722

# 截取点3
mysqlbinlog --base64-output=decode-rows -vvv mysql_bin.000001 |grep DROP -C 10
起始位置点：31846
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001|tail
结束位置点：32878

# 截取binlog
mysqlbinlog --start-position=24485 --stop-position=29469 mysql_bin.000001 > /tmp/1.sql
mysqlbinlog --start-position=29593 --stop-position=31722 mysql_bin.000001 > /tmp/2.sql
mysqlbinlog --start-position=31846 --stop-position=32878 mysql_bin.000001 > /tmp/3.sql

# 恢复数据之前，关闭binlog记录
mysql> set sql_log_bin=0;

# 恢复数据
mysql> source /tmp/1.sql
mysql> source /tmp/2.sql
mysql> source /tmp/3.sql

# 恢复数据之前，打开binlog记录
mysql> set sql_log_bin=1;

# 查看表1内容
mysql> select * from test1.test_a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

# 查看表2内容
mysql> select * from test2.test_b;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

注意：因为binlog存在问题，结合mysqldump全备恢复数据，binlog对于我们来说，只能
当增量数据

解决方案：MySQL全备 + binlog增量备份