MySQL之9---日志管理

MySQL之7---日志管理

目录

• MySQL之7---日志管理
  • 普通日志
  • 错误日志
  • 二进制日志
    • binlog 参数配置
    • event（事件）
    • binlog 文件查看
    • binlog 内容查看
    • binlog 数据恢复
    • binlog2sql
    • binlog GTID
    • 案例
    • 日志滚动方式
    • 日志清理
  • 慢日志
    • 分析工具

日志文件	选项	文件名	表名	命令
普通	--general_log	hostname.log	general_log	N/A
错误	--log-error	hostname.err	N/A	N/A
二进制	--log-bin、--expire-logs-days	hostname-bin.000001	N/A	mysqlbinlog
慢速查询	--slow_query_log、--long_query_time	hostname-slow.log	slow_log	mysqldumpslow
事务	innodb_log_group_home_dir	ib_logfile0， ib_logfile1	N/A	N/A
中继	relay_log
审计	--audit_log、--audit_log_file、...	audit.log	N/A	N/A

普通日志

记录MySql中发生过的所有操作的日志（包括错误的SQL语句），用于审计，调试，不建议业务繁忙时使用。

-- 普通日志是否启用，默认0关闭
select @@general_log;
-- 普通日志存放路径：datadir/hostname.log
select @@general_log_file;
-- 日志输出到文件/表，默认文件
select @@log_output;
-- 普通日志输出到表
set global log_output='table'
-- 关闭普通日志
set global general_log = OFF;
-- 启用普通日志
set global general_log = ON;
-- 查看普通日志内容
tail -f datadir/hostname.log

错误日志

记录：

• 启动和关闭过程中输出的事件信息

• 运行中产生的错误信息

• event scheduler运行一个event时产生的日志信息

• 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

-- 错误日志存放路径，默认开启，默认 datadir/hostname.err
select @@log_error;
-- 详细级别：1~3
select @@log_error_verbosity;
-- 日志时间戳，默认UTC，常用SYSTEM
select @@log_timestamps;

日志内容每天定时巡检，主要看[ERROR]，[WARNING]上下文

二进制日志

binlog是SQL层的功能。以event的形式，记录MySQL数据库中变更类SQL语句，不记录查询语句。

作用：

• 数据恢复必须依赖二进制日志
• 主从环境必须依赖二进制日志

---

binlog 参数配置

-- 临时关闭当前会话二进制日志记录SQL
select @@sql_log_bin;
-- 二进制日志是否启用，8.0前默认不启用，8.0后默认启用
select @@log_bin;
-- 二进制日志存放路径及前缀，默认 datadir/binlog
select @@log_bin_basename;
-- 保证事务提交立即刷新binlog到磁盘(双一标准之二)
select @@sync_binlog;

变更类SQL语句：

• DDL ：原封不动的记录当前DDL(statement语句模式)。
• DCL ：原封不动的记录当前DCL(statement语句模式)。
• DML ：只记录已经提交的事务DML

-- 二进制日志记录格式，只影响DML语句
select @@binlog_format;
-- ① statement (SBR：statement based replication)：语句模式原封不动的记录当前DML，可读性较高，日志量较少，记录可能不准确，5.6默认
-- ② ROW (RBR：ROW based replication)：记录每个数据行的真实变化(用户看不懂，需要工具分析)，可读性较低，日志量较大，记录足够准确，5.7以后默认
-- ③ mixed（混合）(MBR：mixed based replication)：以上两种模式的混合

-- 服务ID号 (5.7之后在主从复制和开启binlog日志必须配置。默认1)
select @@server_id;

---

event（事件）

event是二进制日志的最小记录单元，对于DDL和DCL，一个语句就是一个event；对于DML语句，只记录已提交的事务。

event组成

• 事件的开始标识（Position：at 194）
• 事件内容
• 事件的结束标识（Position：end_log_pos 254）

例如：一个事务，分为4个event

begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760

---

binlog 文件查看

-- 查看binlog开启情况
show variables like '%log_bin%';
-- 查看所有binlog文件
show binary logs;
-- 刷新当前 log buffer 到日志文件
-- 从新的 binlog 文件开始记录 File_size 156，执行一次 flush logs; 增加44
flush logs;
-- 查看正在使用的binlog文件及Position
show master status;
/* 
File：当前MySQL正在使用的文件名
Position：最后一个事件的结束位置号
*/

---

binlog 内容查看

• 查看event

-- 查看event
SHOW BINLOG EVENTS IN 'binlog.000001';
/*
Log_name：binlog文件名
Pos：事件开始位置号 *****
Event_type：事件类型
Server_id：mysql服务标识号
End_log_pos：事件结束位置号 *****
Info：事件内容*****
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
*/
-- 语法结构
SHOW BINLOG EVENTS
[IN 'binlog_name']
[FROM pos]
[LIMIT [offset,] row_count]
-- 结合shell处理
# mysql -e "show binlog events in 'mysql-bin.000001'" | grep drop

• 查看binlog文件内容

# 查看binlog文件内容
mysqlbinlog /data/3306/data/binlog.000003
# 解码显示DML操作内容
mysqlbinlog --base64-output=decode-rows -vvv /data/3306/data/binlog.000003
# 仅列出指定数据库的本地日志
mysqlbinlog -d test /data/3306/data/binlog.000003
# 查看指定时间段内的binlog文件内容（晚于指定时间的第一个开始或停止读取）
mysqlbinlog --start-datetime='2020-11-02 16:57:00' --stop-datetime='2020-11-02 17:00:00'  /data/3306/data/binlog.000001

---

binlog 数据恢复

基于Position号进行日志截取，核心就是找截取的起点和终点

mysqlbinlog --start-position=219 --stop-position=1347 /data/3306/data/binlog.000003 >/tmp/bin.sql

-- 创建环境:
flush log;
show master status;
create database binlog;
use binlog;
create table t1(id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
-- 删除：
drop database binlog;
-- 恢复：
show master status;
show binlog events in 'mysql-bin.000008';
exit
# mysqlbinlog --start-position=219 --stop-position=1347 /data/3306/data/binlog.000008 >/tmp/bin.sql
# mysql -uroot -p123
set sql_Log_bin=0;
source /tmp/bin.sql
set sql_Log_bin=1;

面试案例:
1. 备份策略每天全备,有全量的二进制日志
2. 业务中一共10个库,其中一个被误drop了
3. 需要在其他9个库正常工作过程中进行数据恢复

---

使用binlog日志恢复可能遇到的问题：

1. 建库时间太久，日志量太多，日志有可能只剩部分了：配合备份
2. binlog 保存多个不同库的日志
   • 只需要其中一个库的日志：mysqlbinlog -d
   • 只需要其中一个表的日志：binlog2sql
3. 大表，小错误：binlog2sql数据闪回
4. 需要的 binlog 跨多个文件：
   • 单个文件依次恢复
   • 分别截取多个文件日志，然后合并，再恢复
   • GTID (Global Transaction ID)

---

binlog2sql

安装

yum -y install python3
cd /opt
cat > requirements8.0.txt << EOF
PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.13
EOF
pip3 install -r requirements8.0.txt
pip3 show pymysql

cat > requirements5.7.txt << EOF
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13
EOF

wget -O /opt/binlog2sql-master.zip https://github.com/danfengcao/binlog2sql/archive/master.zip
# 或者
rz binlog2sql-master.zip
unzip binlog2sql-master.zip

---

1. 单独过滤某张表的binlog

python3 /opt/binlog2sql-master/binlog2sql.py -uroot -p1 -d test -t t1 --start-file='binlog.000008'

2. 单独过滤某些类型的binlog

python3 /opt/binlog2sql-master/binlog2sql.py -uroot -p1 -d test -t t1 --sql-type=delete --start-file='binlog.000008'
python3 /opt/binlog2sql-master/binlog2sql.py -uroot -p1 -d test -t t1 --sql-type=update --start-file='binlog.000008'
python3 /opt/binlog2sql-master/binlog2sql.py -uroot -p1 -d test -t t1 --sql-type=insert --start-file='binlog.000008'

2. 生成指定事件回滚语句，数据闪回

python3 /opt/binlog2sql-master/binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p1 -d test -t t1 --start-file='binlog.000008' --sql-type=delete --start-position=932 --stop-position=1198 -B > /tmp/flashback.sql

注意：执行生成的回滚语句前，请仔细检查，8.0可能会有数据行错位的Bug

---

binlog GTID

GTID：Global Transaction ID。全局事务ID，是对于一个已提交事务的编号，一个全局唯一的编号。不管有多少个binlog，是连续生成的，具备幂等性。

• GTID = server_uuid:transaction_id
  1aa38bc6-1cbc-11eb-a6b8-000c29caebef:1
  

• -- 启动生成，存放在datadir/auto.cnf
  select @@server_uuid;
  

• 5.7 之后，不开启也会有自动生成隐藏的不能干预的GTID，建议开启

  select @@SESSION.GTID_NEXT;
  

---

配置

vim /etc/my.cnf
[mysqld]
# 开启GTID
gtid_mode=ON
# 强制GTID一致
enforce_gtid_consistency=ON

---

基于GTID截取binlog

# 包含
--include-gtids
# 排除
--exclude-gtids
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/3306/data/binlog.000008

注意：开启GTID后，MySQL恢复binlog时，由于GTID的幂等性，重复GTID的事务不会再执行了

# 跳过导出文件中的gtid
--skip-gtids

---

基于GTID数据恢复

mysqlbinlog --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/3306/data/binlog.000008 > /tmp/binlog.sql
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;

---

案例

使用二进制日志恢复数据

1. 故障环境介绍

创建一个 db 库,导入一个 t1 表,t1 表中录入了很多数据
一个开发人员,drop database db;
没有备份,日志都在.怎么恢复?
思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)

2. 故障案例模拟

drop database if exists db;
create database db charset utf8; 
use db;
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=3;
commit;
delete from t1 where id=4;
commit;
insert into t1 values(7),(8),(9);
commit;
drop database db;

3. 需求：将数据库恢复到以下状态（提示第10步和第14步是误操作，其他都是正常操作）

4. 无GTID的恢复

① 查看当前使用的 binlog 文件

show master status;

② 查看事件

SHOW BINLOG EVENTS IN 'binlog.000015';

③ 导出 binlog 日志

mysqlbinlog --skip-gtids --start-position=409 --stop-position=1551 /data/3306/data/binlog.000015 > /tmp/bin1.sql

④ 恢复

set sql_log_bin=0;
source /tmp/bin1.sql
set sql_log_bin=1;
select * from db.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|   30 |
|    4 |
|    5 |
|    6 |
+------+

5. 有GTID的恢复

① 截取

mysqlbinlog --skip-gtids --include-gtids='1aa38bc6-1cbc-11eb-a6b8-000c29caebef:9' /data/3306/data/binlog.000015 > /tmp/bin2.sql

注意：日志中GTID设置在事务之前，指向它之后的事务

② 恢复

set sql_log_bin=0;
source /tmp/bin2.sql
set sql_log_bin=1;
select * from db.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|   30 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+

---

日志滚动方式

• flush logs;
• 日志文件达到最大大小max_binlog_size，默认1G
• 重启mysql
• 备份时，指定参数

---

日志清理

自动清理

-- 二进制日志过期时间，默认一个月
select @@binlog_expire_logs_seconds;
-- 企业建议，至少保留两个全备周期+1的binlog，七天一全备，就是15天

手动清理

-- 删除 binlog.000010 之前的
PURGE BINARY LOGS TO 'binlog.000010';
-- 删除3天之前的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

注意：不要手工rm binlog文件

全部清理

reset master; 

注意：主从关系中，主库执行此操作，主从环境必崩

慢日志

作用：记录慢SQL语句的日志，定位低效SQL语句的工具日志

-- 慢日志是否开启，默认关闭
select @@slow_query_log;
-- 文件位置及名字，默认 datadir/hostname-slow.log
select @@slow_query_log_file;
-- 设定慢查询时间，默认10s,建议0.01~0.1s
select @@long_query_time;
-- 记录没走索引的语句
select @@log_queries_not_using_indexes;

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

---

分析工具

mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/3306/data/db01-slow.log
# -s c     按执行次数排序
# -t 10    打印前10条

---

Percona工具包

yum -y install https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm

percona-toolkit工具包中的命令:

pt-query-diagest /data/mysql/slow.log

---

Anemometer

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

参考文章