[转帖]第六章日志管理

第六章,日志,管理 · 浏览次数 : 0

小编点评

4.1 介绍记录执行较慢语句 ```sql select @@slow_query_log; ``` 4.2 配置mysql> set global slow_query_log=on; ```sql set global slow_query_log=on; ``` 4.3 模拟慢语句略 ```sql select * from t100w where num<N limit NCount: 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 NCount: 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; ``` 4.4 分析慢日志 ```sql [root@db01 data]# mysqldumpslow -s c -t 3 db01-slow.log Reading mysql slow query log from db01-slow.logCount: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (40), root[root]@localhost select * from t100w where num<N limit NCount: 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 NCount: 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; ```

正文

第六章日志管理

https://www.jianshu.com/p/e0d95da89fee

本课程，适合具备一定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

[转帖]第六章 日志管理