第六章日志管理

本课程，适合具备一定Linux运维或者开发基础的朋友，课程定级中、高级DBA。
只要掌握80%，轻松助力薪资15k-25K。
课程内容均来自与MySQL官网+MySQL源码。
配套精品视频(2021 5月全新录制，版权所有：郭加磊 oldguo。)，获取方法私聊。

1. 普通日志

1.1 介绍

默认是关闭的. 可以记录MySQL中发生过的所有操作日志.一般用来调试.

1.2 如何配置

general_log = ON
general_log_file  = /data/3306/data/db01.log

1.3 作用

审计
调试

2. 错误日志

2.1 介绍

记录数据库从启动以来,状态\报错\警告.

2.2 查询和配置

mysql> show variables like '%error%';

log_error = ./db01.err
log_error_verbosity=3

mysql> set global log_error_verbosity=3;

2.3 怎么用

每天定时巡检.主要关注  [ERROR] ,[WARNING]

3. 二进制日志 (binlog)

3.1 介绍

以event形式,记录MySQL数据库中变更类的操作日志(DDL  DCL  DML).

3.2 作用

数据恢复.
复制

3.3 配置

mysql> show variables like '%bin%';
log_bin=1
log_bin_basename=/data/3306/data/binlog
sync_binlog=1
binlog_format=row/statement/mixed

彩蛋1:

sync_binlog=1 是双一期中一个1.保证事务提交理解刷新binlog到磁盘.

彩蛋2:

 binlog_format=row/statement/mixed
格式区别:  
    1. row (RBR)      : 记录每个数据行的真实变化. 日志量会比较大.记录足够准确.
        update t1 set num=20 where id<10;
    
    2. statement(SBR)  : 记录发生的是语句.日志量相对少.记录有可能不准确.
    
    3.  mixed 混合模式

binlog_format 只影响DML语句.DDL和DCL都是Statement

3.4 binlog 应用

3.4.1 查看和分析binlog

mysql> show binary logs;
mysql> show master status ;
mysql> show binlog events in 'binlog.000007';

[root@db01 data]# mysqlbinlog binlog.000007 
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv    binlog.000007

3.4.2 数据损坏模拟和恢复

故障模拟: 
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status ;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000008 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create database oldboy;
Query OK, 1 row affected (0.01 sec)

mysql> use oldboy;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database oldguo;
Query OK, 1 row affected (0.02 sec)



恢复过程: 
mysql> show master status ;
mysql> show binlog events in 'binlog.000008';

| binlog.000008 |  233 | Query          |         1 |         347 | create database oldguo /* xid=772 */  
| binlog.000008 | 1448 | Query          |         1 |        1558 | drop database oldguo /* xid=784 */    


[root@db01 data]# mysqlbinlog --start-position=233 --stop-position=1448 /data/3306/data/binlog.000008 >/tmp/bin.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;

mysql> use oldguo;
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldguo |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |

彩蛋: 生产中,使用binlog日志恢复数据会有什么痛点?

1. 建库时间太久,日志量太多 , 日志有可能只剩部分了. 怎么破?
备份+binlog可以恢复到.

2. binlog 保存了多个不同库的日志. 只需要期中一个库的日志.怎么办? 

mysqlbinlog -d 

只需要期中一个表的日志.怎么办? 

binlog2sql 

3. 一张表10亿行,误删除10行数据. 怎么办 ? 
binlog2sql 做数据闪回


4. 我需要的日志跨了多个文件,怎么办?

a.  单独截取多个文件日志,然后合并

binlog.000005    1080   1-3
binlog.000006           4-10
binlog.000007    789    11-12

b. gtid 日志记录模式

3.5 扩展内容:binlog2sql应用

3.5.1 安装

[root@db01 opt]# vim requirements.txt 
PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.13

[root@db01 opt]# yum install python3 -y

pip3 install -r requirements.txt
pip3 show pymysql
[root@db01 opt]# unzip binlog2sql-master.zip

3.5.1 解析日志事件SQL

a. 单独过滤某张表的binlog

[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --start-file='mysql-bin.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid 



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

[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=delete  --start-file='mysql-bin.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5172 end 5416 time 2020-09-18 09:17:48 gtid 
[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=update  --start-file='mysql-bin.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4882 end 5141 time 2020-09-18 09:17:35 gtid 
[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=insert  --start-file='mysql-bin.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid 
[root@db01 binlog2sql-master]# 

c. 生成指定事件回滚语句
应用场景： 3000万数据，误删10行数据，怎么恢复？

[root@db01 binlog2sql]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1  --start-file='mysql-bin.000003'   --sql-type=delete --start-position=932 --stop-position=1198 -B

[root@db01 binlog2sql]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1  --start-file='mysql-bin.000003'   --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql

3.6 GTID应用

3.6.1 介绍

Global  Transaction ID . 全局事务ID.不管有多少个binlog,都是连续生成的.具备幂等性.

1ced0886-23d4-11eb-9768-000c29f4772b:1
server_uuid  :   NO.

3.6.2 配置

set global gtid_mode=ON;
set global enforce_gtid_consistency=ON;

3.6.3 基于GTID截取日志

mysqlbinlog --skip-gtids --include-gtids='1ced0886-23d4-11eb-9768-000c29f4772b:1-3' /data/3306/data/binlog.000012 >/tmp/gtid.sql

mysqlbinlog --skip-gtids --include-gtids='1ced0886-23d4-11eb-9768-000c29f4772b:1-3' --exclude-gtids='1ced0886-23d4-11eb-9768-000c29f4772b:2'/data/3306/data/binlog.000012 >/tmp/gtid.sql


注意:  如果需要截取的日志需要在原库恢复,需要加--skip-gtids 参数.跳过导出文件gtid的记录.

3.7 其他管理

3.7.1 日志滚动

mysql> flush logs;
mysql> select @@max_binlog_size;
重启数据数据库与

3.7.2 日志的删除

a . 设置自动过期时间
binlog_expire_logs_seconds  过期时间.默认是一个月.
b . 手工清理
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

c . 全部重置 
reset master;

4. 慢日志

4.1 介绍

记录执行较慢语句.

4.2 配置

mysql> set global slow_query_log=on;
mysql> set global long_query_time=0.01;
mysql> set global log_queries_not_using_indexes=1;

mysql> select @@slow_query_log;
mysql> select @@long_query_time;
mysql> select @@log_queries_not_using_indexes;

4.3 模拟慢语句

略.

4.4 分析慢日志

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

Reading mysql slow query log from db01-slow.log
Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=10.0 (40), root[root]@localhost
  select * from t100w where num<N limit N

Count: 3  Time=0.55s (1s)  Lock=0.00s (0s)  Rows=20.0 (60), root[root]@localhost
  select count(*)  from t100w where num<N  group by k2,k1  limit N

Count: 3  Time=0.53s (1s)  Lock=0.00s (0s)  Rows=13.3 (40), root[root]@localhost
  select count(*)  from t100w where num<N  group by num,k1  limit N

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,547评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,399评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,428评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,599评论 1赞 274
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,612评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,577评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,941评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,603评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,852评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,605评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,693评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,375评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,955评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,936评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,172评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,970评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,414评论 2赞 342

第六章 日志管理

1. 普通日志

1.1 介绍

1.2 如何配置

1.3 作用

2. 错误日志

2.1 介绍

2.2 查询和配置

2.3 怎么用

3. 二进制日志 (binlog)

3.1 介绍

3.2 作用

3.3 配置

彩蛋1:

彩蛋2:

3.4 binlog 应用

3.4.1 查看和分析binlog

3.4.2 数据损坏模拟和恢复

彩蛋: 生产中,使用binlog日志恢复数据会有什么痛点?

3.5 扩展内容:binlog2sql应用

3.5.1 安装

3.5.1 解析日志事件SQL

3.6 GTID应用

3.6.1 介绍

3.6.2 配置

3.6.3 基于GTID截取日志

3.7 其他管理

3.7.1 日志滚动

3.7.2 日志的删除

4. 慢日志

4.1 介绍

4.2 配置

4.3 模拟慢语句

4.4 分析慢日志

推荐阅读更多精彩内容

第六章日志管理