MySQL数据库-日志管理

日志管理

错误日志

作用

MySQL从启动开始，记录状态、警告、错误。辅助排错。

配置方法

查看
mysql> select @@log_error;
默认配置： ./HOSTNAME.err
#配置
[root@db01 ~]# mkdir /data/3306/errlog/ -p
[root@db01 ~]# touch /data/3306/errlog/mysql.log
[root@db01 ~]# chown -R mysql.mysql /data

vim /etc/my.cnf
log_error=/data/3306/errlog/mysql.log

/etc/init.d/mysqld restart

如何查看

查看日志中[error]行。

二进制日志

作用

记录MySQL运行过程中，变更类的语句（DDL，DML，DCL…）
可以用来做数据恢复和主从复制。

配置

# 基本参数
mysql> select @@server_id; # 主机ID，用来主从复制的不同节点
mysql> select @@log_bin; # 二进制日志开关，也可以设置binlog存放位置和名称
mysql> select @@log_bin_basename; # 设置binlog存放位置和名称，可省略
mysql> select @@binlog_format; # 二进制日志记录格式。保持默认row即可。
mysql> select @@sync_binlog; # binlog刷新策略。保持默认为1即可。

#特殊参数 （可选）
mysql> select @@gtid_mode;
mysql> select @@enforce_gtid_consistency;

# 配置开启
vim /etc/my.cnf
server_id=6
log_bin=/data/3306/binlog/mysql-bin

说明： /data/3306/binlog/ 提前创建并授权，mysql-bin是文件名称前缀。

binlog记录什么？

内容介绍
记录MySQL运行过程中，变更类的语句（DDL，DML，DCL）
记录的格式
DDL 、DCL ： statement(语句)格式记录。例如： create database 。。。
DML ： insert、update、delete。
— 1. 只会记录已经提交的事务语句。
— 2. 记录格式受到binlog_format影响。
— binlog_format=格式?
— RBR : 行模式。默认模式。
— SBR ：语句模式
— MBR ：混合模式

binlog 记录单元 - event(事件)
binlog 记录的最小单元。
# 不同语句的事件
DDL ， DCL ： 一条语句就是一个event。
DML ： 一个事务型操作有多个event构成
begin; e1
DML1 e2
DML2 e3
COMMIT; e4

# 事件的主要结构
开始标记 ： position
事件内容
结束标记： position

二进制日志查看

文件查看

[root@db01 binlog]# cd /data/3306/binlog/
[root@db01 binlog]# ll
mysql> show binary logs;
mysql> flush logs;
mysql> flush logs;
mysql> show binary logs;
mysql> show master status ;

事件查看

mysql> create database oldguo charset utf8mb4;
mysql> show master status ;
mysql> show binlog events in 'mysql-bin.000003';

binlog文件内容查看

普通查看
[root@db01 binlog]# mysqlbinlog /data/3306/binlog/mysql-bin.000004

基于row记录的内容查看 
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv  /data/3306/binlog/mysql-bin.000004

日志截取实现数据恢复

模拟环境

mysql> create database test charset utf8mb4;
mysql> use test;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> insert into t1 values(11),(12),(13);
mysql> commit;

模拟删除数据

mysql> drop database test;

截取日志

起点:  create 操作的event起点。
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004';" |grep "create database test"
mysql-bin.000004	1085	Query	6	1195	create database test charset utf8mb4
终点: drop 操作的起点。
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004';" |grep "drop database test"
mysql-bin.000004	1949	Query	6	2041	drop database test
 
截取： 
[root@db01 binlog]# mysqlbinlog --start-position=1085 --stop-position=1949 mysql-bin.000004  >/tmp/bin.sql

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

mysql> select * from test.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+

binlog截取及恢复

步骤

# 查看需要的binlog日志文件
show master status ;
# 确认起点和终点
起点： 
mysql -e  "show binlog events in 'xxxxx'" |grep -i 
POS     end_log_pos      info 
说明： 如果日志文件过大，过多寻找起点很困难。此时可以配合备份工具实现自动记录。

终点： 
mysql -e  "show binlog events in 'xxxxx'" |grep -i 
POS     end_log_pos      info 
# 恢复 
set sql_log_bin=0;
source /tmp/bin.sql 
set sql_log_bin=1;

跨多文件截取

模拟环境

mysql> show master status ;
mysql-bin.000004

mysql> create database oldboy charset utf8mb4;
mysql> use oldboy 
mysql> create table t1 (id int);

mysql> flush logs;
mysql> show master status ;
mysql-bin.000005
mysql> use oldboy
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

mysql> flush logs;
mysql> show master status ;
mysql> insert into t1 values(11),(22),(33);
mysql> commit;
mysql> drop database oldboy;

方法1：分段截取

起点：
mysql> show binlog events in 'mysql-bin.000004';
| mysql-bin.000004 | 2269 | Query          |         6 |        2385 | create database oldboy charset utf8mb4 |
终点：  
mysql> show binlog events in 'mysql-bin.000006';
| mysql-bin.000006 | 486 | Query          |         6 |         584 | drop database oldboy     

mysqlbinlog --start-postion=2269 /data/3306/binlog/mysql-bin.000004 >/tmp/1.sql 
mysqlbinlog  /data/3306/binlog/mysql-bin.000005 >/tmp/2.sql 
mysqlbinlog --stop-postion=486 /data/3306/binlog/mysql-bin.000004 >/tmp/3.sql 

方法2：基于时间点截取

起点：
mysql> show binlog events in 'mysql-bin.000004';
| mysql-bin.000004 | 2269 | Query          |         6 |        2385 | create database oldboy charset utf8mb4 |

[root@db01 binlog]# mysqlbinlog  --start-position=2269 --stop-position=2385  mysql-bin.000004|grep -v 'SET'
#200327 10:30:56 
终点：  
mysql> show binlog events in 'mysql-bin.000006';
| mysql-bin.000006 | 486 | Query          |         6 |         584 | drop database oldboy     
[root@db01 binlog]# mysqlbinlog  --start-position=486 --stop-position=584 mysql-bin.000006|grep -v 'SET'
# 200327 10:34:00

截取格式：
for example: 2004-12-25 11:25:56 

mysqlbinlog --start-datetime='2020-03-27 10:30:56' --stop-datetime='2020-03-27 10:34:00'  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006 >/tmp/binlog.sql


注意： 记录日志的时候，同一秒钟发生多个操作。所以基于时间的截取方式可能导致不精确。
所以截取完成，需要认为查看以下，并进行有必要的处理。

方法3：基于GTID截取跨多文件的binlog

GTID模式下的binlog管理

介绍

Global Transaction ID 全局事务唯一编号。为了主从复制出现的功能。
5.6 版本以后，加入了GTID功能，用来唯一标识事件和事务。
所有GTID号码是从1开始有序生成的。
GTID模式下的日志，是具备幂等性的。

表现方式

server_uuid:ID
09a8c1af-6e45-11ea-a381-000c29248f69 : 1-20
server_uuid介绍：
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 09a8c1af-6e45-11ea-a381-000c29248f69 |
+--------------------------------------+
在第一次初始化，启动数据库时，自动生成的。存在/data/3306/data/auto.cnf文件中。
一旦被删除，被修改，下次重启重置。

ID值的介绍： 
	DDL ，DML ：一个事件记录一个ID 
	DML       ：一个完整的事务，记录一个ID

开启方式

参数： 
select @@gtid_mode;
select @@enforce_gtid_consistency;

基于GTID的binlog管理

# 查看GTID的信息
mysql> show master status ;
# 查看GTID的事件信息
mysql> show binlog events in 'mysql-bin.000007';
mysql-bin.000007 | 154 |         219 | SET @@SESSION.GTID_NEXT= '09a8c1af-6e45-11ea-a381-000c29248f69:1' 
mysql-bin.000007 | 219 |         335 | create database binlog charset 

# 基于GTID方式截取恢复数据
mysqlbinlog --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:1-3' /data/3306/binlog/mysql-bin.000007 >/tmp/gtid1.sql

注意： 开启了GTID模式的数据库，会自动检测GTID幂等性。会自动跳过已有的GTID的操作。
需要添加 --skip-gtids ，在截取日志时，抹掉已有的GTID信息

# 截取时排除某些GTID方法
mysqlbinlog --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:1-10' --exclude-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:4','09a8c1af-6e45-11ea-a381-000c29248f69:7' /data/3306/binlog/mysql-bin.000007 >/tmp/gtid2.sql

# 多文件截取
1-100    003 004 005 
mysqlbinlog --include=1-100 003 004 005>/tmp/gtid.sql

# -d 过滤库的binlog

慢日志-slowlog

作用

记录执行较慢的语句。SQL优化方面的工具日志。

配置

默认没有开启。
mysql> select @@slow_query_log;
mysql> select @@slow_query_log_file;
mysql> select @@long_query_time;
mysql> select @log_queries_not_using_indexes;

vim /etc/my.cnf 
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes

分析

[root@db01 data]# mysqldumpslow  -s c -t 3 db01-slow.log 

推荐工具

pt-query-digest