08MySQL日志管理

08MySQL日志管理

Deng YongJie's blog 295 2022-04-10

第1章 错误⽇志

1.作⽤

记录数据库启动以来，状态、警告、报错。诊断数据库报错问题。

2.配置

默认： 开启状态。存放在数据⽬录下(/data/3306/data),名字：主机名.err

3.查看

mysql> select @@log_error;
+-------------+
| @@log_error |
+-------------+
| ./db-51.err |
+-------------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-------------------+
| @@datadir |
+-------------------+
| /data/mysql_3306/ |
+-------------------+
1 row in set (0.00 sec)

4.⾃定义配置

修改配置：

[root@db-51 ~]# vim /etc/my.cnf
[mysqld]
#新增加参数
log_error=/data/mysql_3306/logs/mysql.err

创建⽇志⽬录并更改授权：

[root@db-51 ~]# mkdir /data/mysql_3306/logs/ -p
[root@db-51 ~]# chown -R mysql:mysql /data/mysql_3306/logs/

重启mysql：

systemctl status mysqld.service

重启后报错：

9⽉ 13 17:18:57 db-51 mysqld[1439]: Starting MySQL.2020-09-
13T09:18:57.124858Z mysqld_safe error: log-error set to
'/data/mysql_3306/logs/mysql.err', however file don't exists. Create
writable for user 'mysq

解决⽅法：

mkdir /data/mysql_3306/logs/ -p
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306/logs/
systemctl restart mysqld.service

第2章 慢⽇志

1.作⽤

1.记录MySQL⼯作过程中较慢的语句
2.默认没有开启,按需求打开。

2.配置

在线配置:

mysql> select @@slow_query_log; # 开关
mysql> set global slow_query_log=1; # 在线改
mysql> select @@slow_query_log_file; # ⽂件位置。离线改。
mysql> select @@long_query_time; # 慢查询时间设定。
mysql> set global long_query_time=0.1; # 在线设置，最低微秒级别。
mysql> select @@log_queries_not_using_indexes #如果没⾛索引会被记录
mysql> set global log_queries_not_using_indexes=1; #在线设置

永久⽣效:

vim /etc/my.cnf
slow_query_log=1 #是否启⽤慢查询⽇志，1为启⽤，0为禁⽤
slow_query_log_file=/data/mysql_3306/logs/slow.log #慢⽇志路径
long_query_time=0.1 #SQL语句运⾏时间阈值，执⾏时间⼤于参数值的语句才会被记录下来
log_queries_not_using_indexes=1 #将没有使⽤索引的语句记录到慢查询⽇志

3.模拟慢语句

select * from t100w as a join t100w as b limit N;
select k1,count(*) from t100w where id<N group by k1 having count(*)>N;
select k1,count(*) from t100w where num<N group by k1,k2;
select * from t100w where id<N order by num desc;
select k1,count(*) from t100w where id<N group by k1,k2;

4.慢⽇志分析

[root@db01 logs]# mysqldumpslow -s c -t 5 slow.log
Reading mysql slow query log from slow.log
Count: 7 Time=0.01s (0s) Lock=0.00s (0s) Rows=177.1 (1240),
root[root]@localhost
 select * from t100w as a join t100w as b limit N
Count: 6 Time=0.57s (3s) Lock=0.00s (0s) Rows=33.7 (202),
root[root]@localhost
 select k1,count(*) from t100w where id<N group by k1 having
count(*)>N
Count: 5 Time=0.59s (2s) Lock=0.00s (0s) Rows=893.0 (4465),
root[root]@localhost
 select k1,count(*) from t100w where num<N group by k1,k2
Count: 5 Time=0.61s (3s) Lock=0.00s (0s) Rows=37.0 (185),
root[root]@localhost
 select * from t100w where id<N order by num desc
Count: 4 Time=0.61s (2s) Lock=0.00s (0s) Rows=496.0 (1984),
root[root]@localhost
 select k1,count(*) from t100w where id<N group by k1,k2

5.拓展

⼯具: pt-query-digest

第3章 ⼆进制⽇志binlog

1.作⽤

1.数据恢复
2.主从复制

2.记录的内容

记录修改类操作(逻辑⽇志，类似于SQL记录)
DML: insert update delete
DDL: create drop alter trucate
DCL: grant revoke

3.配置⽅法

3.1 基础参数查看

mysql> select @@log_bin;
mysql> select @@log_bin_basename;
mysql> select @@server_id;

3.2 设置基础参数

vim /etc/my.cnf
[mysqld]
#新增加参数
server_id=51 #主机ID,在主从复制会使⽤
log_bin=/data/mysql_3306/logs/mysql-bin #开关+⽂件路径+⽂件名前缀,最终格式:
mysql-bin.000001

3.3 重启并查看

[root@db-51 ~]# systemctl restart mysqld
[root@db-51 ~]# ll /data/mysql_3306/logs/
总⽤量 20
-rw-r----- 1 mysql mysql 154 9⽉ 13 17:29 mysql-bin.000001
-rw-r----- 1 mysql mysql 39 9⽉ 13 17:29 mysql-bin.index
-rw-r--r-- 1 mysql mysql 11256 9⽉ 13 17:29 mysql.err

4.binlog内容的记录格式

4.1 事件(event)的记录⽅式

每个事件:

1.事件描述: 时间戳、server_id、加密⽅式、开始的位置（start_pos）、结束位置点
（end_pos）
2.事件内容: 修改类的操作：SQL 语句 或者 数据⾏变化

重点关注:

开始的位置（start_pos）
结束位置点（end_pos）
事件内容

4.2 ⼆进制⽇志事件内容格式

查看⽇志格式

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+

作⽤:

对于DDL、DCL语句，直接将SQL本身记录到binlog中
对于DML ： insert、update、delete 受到binlog_format参数控制。
SBR ： Statement : 语句模式。之前版本，默认模式
RBR ： ROW ： ⾏记录模式。5.7以后，默认模式
MBR ： mixed : 混合模式。

区别:

Statement、ROW区别：
update t1 set name='zhangsan' where id<100;
Statement: 记录SQL本身 。
ROW: 100个数据⾏的变化。
Statement⽇志量少
ROW⽇志量⼤
Statement记录不够准确
ROW记录够准确。
例如函数操作：
now()
rand()

5.binlong查询

5.1 ⽇志⽂件情况查询

查看所有的⽇志⽂件信息

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+

刷新新⽇志

mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2074265 |
| mysql-bin.000002 | 1181 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+

当前数据库使⽤的⼆进制⽇志

mysql> show master status;
+------------------+----------+--------------+------------------+-------
------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------
------------+
| mysql-bin.000005 | 154 | | |
 |
+------------------+----------+--------------+------------------+-------
------------+

5.2 内容查询

数据模拟

mysql> create database ku charset utf8mb4;
mysql> use ku
mysql> create table biao (id int);
mysql> insert into biao values(1);
mysql> commit;

查看⽇志事件

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+--
-------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos |
Info |
+------------------+-----+----------------+-----------+-------------+--
-------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 |
Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 |
 |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 6 | 219 |
SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 6 | 323 |
create database ku charset utf8mb4 |
| mysql-bin.000005 | 323 | Anonymous_Gtid | 6 | 388 |
SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 388 | Query | 6 | 484 |
use `ku`; create table biao (id int) |
| mysql-bin.000005 | 484 | Anonymous_Gtid | 6 | 549 |
SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 549 | Query | 6 | 619 |
BEGIN |
| mysql-bin.000005 | 619 | Table_map | 6 | 664 |
table_id: 111 (ku.biao) |
| mysql-bin.000005 | 664 | Write_rows | 6 | 704 |
table_id: 111 flags: STMT_END_F |
| mysql-bin.000005 | 704 | Xid | 6 | 735 |
COMMIT /* xid=88 */ |
+------------------+-----+----------------+-----------+-------------+--
-------------------------------------+

5.3 查看⽇志内容

查看⽇志内容

[root@db01 logs]# mysqlbinlog /data/mysql_3306/logs/mysql-bin.000005

Create database⽇志内容

# at 219
...略
create database ku charset utf8mb4

create table ⽇志内容

# at 388
...略
create table biao (id int)

insert 操作的⽇志内容

# at 664
#200914 8:15:15 server id 6 end_log_pos 704 CRC32 0x0a91b6f8
Write_rows: table id 111 flags: STMT_END_F
BINLOG '
E7ZeXxMGAAAALQAAAJgCAAAAAG8AAAAAAAEAAmt1AARiaWFvAAEDAAGlD2wp
E7ZeXx4GAAAAKAAAAMACAAAAAG8AAAAAAAEAAgAB//4BAAAA+LaRCg==
'/*!*/;
# at 704
#200914 8:15:15 server id 6 end_log_pos 735 CRC32 0x1e620e90 Xid =
88
COMMIT/*!*/;

查看解密后的insert

[root@db-51 ~]# mysqlbinlog --base64-output=decode-rows -vv
/data/mysql_3306/logs/mysql-bin.000005
.......略
# at 664
#200914 8:15:15 server id 6 end_log_pos 704 CRC32 0x0a91b6f8
Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `ku`.`biao`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */

6.binlog⽇志截取及恢复演练

6.1 前提说明

创建或导⼊数据库之前就配置并开启了binlog

6.2 故障说明

模拟误删库，要求恢复到删库之前

6.3 模拟故障

1.创建库
create database linux5;
2.创建表
use linux5;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
 `name` varchar(10) NOT NULL COMMENT 'name',
 `age` tinyint(4) NOT NULL COMMENT 'age',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.写⼊数据
insert into user(name,age)
values
('z3',22),
('l4',22),
('w5',22);
4.查看数据
mysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | z3 | 18 |
| 2 | l4 | 20 |
| 3 | w5 | 21 |
+----+------+-----+
5.模拟删除
drop database linux5;

6.4 恢复思路

binlog临时开关，重启数据库就会恢复启动状态

mysql>  show variables like '%log_bin%';
 2 +---------------------------------+---------------------------------------+
 3 | Variable_name                   | Value                                |
 4 +---------------------------------+---------------------------------------+
 5 | log_bin                         | ON                                  |记录binlog开关
 6 | log_bin_basename                | /usr/local/mysql/data/mysql-bin      |
 7 | log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
 8 | log_bin_trust_function_creators | OFF                                  |
 9 | log_bin_use_v1_row_events       | OFF                                  |
10 | sql_log_bin                     | ON                                  |为OFF时，临时不记录binlog开关（增量恢复）某个时间点某些语句不记录binlog
11 +---------------------------------+----------------------------------

第⼀步：截取从建库以来到删库之前的所有binlog

查看当前处于什么哪个binlog：

mysql> show master status;
+------------------+----------+--------------+------------------+-------
------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------
------------+
| mysql-bin.000002 | 1134 | | |
 |
+------------------+----------+--------------+------------------+-------
------------+

第⼆步: 找到起点,建库的位置点（position）

mysql> show binlog events in 'mysql-bin.000002';
....略
| mysql-bin.000002 | 219 | Query | 6 | 319 |
create database linux5

第三步: 找到终点

.....略
| mysql-bin.000002 | 1036 | Query | 6 | 1134 |
drop database linux5

导出数据：

mysqlbinlog --start-position=219 --stop-position=1036
/data/mysql_3306/logs/mysql-bin.000002 >/tmp/bin.sql

将截取的⽇志进⾏回放

mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql;
mysql> set sql_log_bin=1;

7.⽣产中⽇志在多个⽂件中,如何截取？

7.1 场景模拟

flush logs;
#mysql-bin.000005
show master status ;
create database tongdian charset=utf8mb4;
use tongdian
create table t1 (id int);
flush logs;
#mysql-bin.000006
show master status ;
insert into t1 values(1),(2),(3);
commit;
flush logs;
#mysql-bin.000007
show master status ;
create table t2(id int);
insert into t2 values(1),(2),(3);
commit;
flush logs;
#mysql-bin.000008
show master status ;
insert into t2 values(11),(22),(33);
commit;
drop database tongdian;

7.2 恢复⽅法

⽅法1:分段截取

--start-position --stop-position

⽅法2:时间戳截取

a.找起点: 建库的时间戳

起点posting号

show binlog events in 'mysql-bin.000005';

通过position过滤时间戳

mysqlbinlog --start-position=951 --stop-position=1073 mysql-bin.000005
|grep -A 1 '^\#\ at\ 951'

b.找终点

mysql -e "show binlog events in 'mysql-bin.000008'"

c.截取⽇志

mysqlbinlog --start-datetime="2020-05-09 17:11:23" --stopdatetime="2020-05-09 17:14:01" mysql-bin.000005 mysql-bin.000006
mysql-bin.000007 mysql-bin.000008 >/tmp/data.sql

7.3 binlog其他注意

binlog属于全局⽇志，⽇志中有其他库的操作，怎么排除掉？

mysqlbinlog -d oldboy mysql-bin.000008 > /tmp/bin.sql

binlog中100w个事件，怎么快速找到drop database的位置点？

[root@db01 ~]# mysql -e "show binlog events in 'mysql-bin.000014'" |less
[root@db01 ~]# mysql -e "show binlog events in 'mysql-bin.000014'" |grep

⽐如删除的库，建库是在2年前操作的。这种情况怎么办？

每天全备，binlog完好的。
可以使⽤ 全备+binlog⽅式实现恢复数据故障之前。

8.基于GTID的binlog应⽤

8.1 GTID全局事务ID

对每个事务，进⾏单独编号。连续不断进⾏增⻓

8.2 表示⽅式

server_uuid:N

8.3 GTID配置

查看参数:

mysql> show variables like '%GTID%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+

设置参数:

vim /etc/my.cnf
[mysqld]
gtid_mode=ON #开关
enforce_gtid_consistency=ON #强制GTID⼀致性
log_slave_updates=ON #强制从库更新binlog

重启服务:

systemctl restart mysqld

建议:

5.7版本以后，都开启GTID。最好是搭建环境就开启。

8.4 GTID应⽤

模拟环境:

a.创建库并查看gtid

mysql> create database gtdb charset utf8mb4;
mysql> show master status ;
+------------------+----------+--------------+------------------+-------
---------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------
---------------------------------+
| mysql-bin.000006 | 329 | | |
9b52b744-eb82-11ea-986c-000c294983f8:1 |
+------------------+----------+--------------+------------------+-------
---------------------------------+

b.创建表并查看gtid

mysql> use gtdb;
mysql> create table t1(id int);
mysql> show master status ;
+------------------+----------+--------------+------------------+-------
-----------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------
-----------------------------------+
| mysql-bin.000006 | 491 | | |
9b52b744-eb82-11ea-986c-000c294983f8:1-2 |
+------------------+----------+--------------+------------------+-------
-----------------------------------+

c.插⼊数据并查看

begin;
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
commit;
show master status ;
+------------------+----------+--------------+------------------+------
------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------
------------------------------------+
| mysql-bin.000006 | 914 | | |
9b52b744-eb82-11ea-986c-000c294983f8:1-3 |
+------------------+----------+--------------+------------------+------
------------------------------------+

d.查看事件

mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+----------------+-----------+-------------+--
-----------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos |
Info |
+------------------+-----+----------------+-----------+-------------+--
-----------------------------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 6 | 123 |
Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000006 | 123 | Previous_gtids | 6 | 154 |
 |
| mysql-bin.000006 | 154 | Gtid | 6 | 219 |
SET @@SESSION.GTID_NEXT= '9b52b744-eb82-11ea-986c-000c294983f8:1' |
| mysql-bin.000006 | 219 | Query | 6 | 329 |
create database gtdb charset utf8mb4 |
| mysql-bin.000006 | 329 | Gtid | 6 | 394 |
SET @@SESSION.GTID_NEXT= '9b52b744-eb82-11ea-986c-000c294983f8:2' |
| mysql-bin.000006 | 394 | Query | 6 | 491 |
use `gtdb`; create table t1(id int) |
| mysql-bin.000006 | 491 | Gtid | 6 | 556 |
SET @@SESSION.GTID_NEXT= '9b52b744-eb82-11ea-986c-000c294983f8:3' |
| mysql-bin.000006 | 556 | Query | 6 | 628 |
BEGIN |
| mysql-bin.000006 | 628 | Table_map | 6 | 673 |
table_id: 108 (gtdb.t1) |
| mysql-bin.000006 | 673 | Write_rows | 6 | 713 |
table_id: 108 flags: STMT_END_F |
| mysql-bin.000006 | 713 | Table_map | 6 | 758 |
table_id: 108 (gtdb.t1) |
| mysql-bin.000006 | 758 | Write_rows | 6 | 798 |
table_id: 108 flags: STMT_END_F |
| mysql-bin.000006 | 798 | Table_map | 6 | 843 |
table_id: 108 (gtdb.t1) |
| mysql-bin.000006 | 843 | Write_rows | 6 | 883 |
table_id: 108 flags: STMT_END_F |
| mysql-bin.000006 | 883 | Xid | 6 | 914 |
COMMIT /* xid=12 */ |
+------------------+-----+----------------+-----------+-------------+--
-----------------------------------------------------------------+

8.5 通过GTID⽅式截取⽇志

错误的截取命令:

mysqlbinlog --include-gtids='9b52b744-eb82-11ea-986c-000c294983f8:1-3'
/data/mysql_3306/logs/mysql-bin.000006 >/tmp/gt.sql

为什么恢复报错？

gtid有“幂等性”检查。GTID的⽣成，通过Set gtid_next命令实现的。
例如：
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:1'
执⾏Set命令时，⾃动检查当前系统是否包含这个GTID信息，如果有就跳过。

正确的⽅式：

mysqlbinlog --skip-gtids --include-gtids='2ddd7a11-4747-11eb-b274-
000c29116b18:1-3' /data/mysql_3306/binlog/mysql-bin.000009 >/tmp/gt.sql

8.6 恢复操作

set sql_log_bin=0;
source /tmp/gt.sql;
set sql_log_bin=1;

9.⽇志滚动

命令触发：

mysql> flush logs;
shell# mysqladmin flush-logs
shell# mysql -e "flush logs"
shell# mysqldump -F

⾃动触发：

mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1043018380 |
+-------------------+
重启数据库，会触发刷新

10.⽇志删除

10.1 默认⽅式

不⾃动清理。直到空间写满

10.2 配置⾃动清理

mysql> select @@expire_logs_days;
最少设置多少天合适？
参考全备时间周期。
例如： 全备周期是7天。可以保留8天。⼀般⽣产中保留两轮备份周期的⽇志，15天。
设置命令：
set GLOBAL expire_logs_days=7;

10.3 ⼿⼯清理

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
MySQL