12.MySQL日志管理
孙富阳， 江湖人称没人称。多年互联网运维工作经验，曾负责过孙布斯大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型博客网站运维工作。
1.普通日志（general log）
1.介绍
默认是关闭的，可以记录MySQL中发生过的所有操作日志，一般用来调试。
2.查询并配置
mysql> show variables like '%log%';
general_log OFF
general_log_file /data/3316/data/db01.log
开启：mysql> set global general_log=ON;3.作用
记录所有操作，可用于审计，和调试
2.错误日志（error log）
1.介绍
错误日志默认开启，记录数据库从启动以来的状态、报错、警告
2.查询并配置
mysql> show variables like '%error%';
日志路径：log_error /db01.err
日志级别：log_error_verbosity 2
修改日志级别
mysql> set global log_error_verbosity=3;
最高级别3，记录的最详细的错误日志
3.作用
定时巡检，关注error和warning信息
3.二进制日志（binlog）
1.介绍
以event形式，记录MySQL数据库中变更类的操作日志（DDL DCL DML）,可用于数据恢复和主从复制
2.查询并配置
mysql> show variables like '%bin%';
log_bin ON
log_bin_basename /data/3316/data/binlog
sync_binlog 1
binlog_format ROW
是否开启日志：log_bin=1
文件前缀：log_bin_basename=/data/3316/data/binlog
每次事务提交立即刷新事务日志：sync_binlog=1
日志格式：binlog_format=ROW
ps:
1.Sync_binlog=1是双一期中一个1.保证事务提交立即刷新binlog到磁盘
2. binlog_format=ROW/statement/mixed
格式区别：
1.row（RBR）：记录每个数据行的真实变化。日志量会比较大，但足够准确
2.statement（SBR）：记录当前发生的语句本身，日志量会比较少。
3.mixed混合模式
binlog_format只影响DML语句，DDL和DCL都是statement
3.binlog应用
1.查看和分析binlog
看所有的：mysql> show binary logs;
看当前在用的：mysql> show master status;
看日志的事件信息：mysql> show binlog events in 'binlog.000009';
查看binlog日志文件内容：[root@db01 ~]# mysqlbinlog /data/3316/data/binlog.000007
查看binlog日志文件详细内容：[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3316/data/binlog.000007
只看某个库里的日志：[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv -d test /data/3316/data/binlog.000007
2.数据损坏模拟和恢复
mysql> flush logs；###生成新的binlog文件
mysql> show master status;##查看一下当前的日志文件
#创建数据库
mysql> create database sfy;
mysql> use sfy
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
##删除数据库
mysql> drop database sfy;
##查看一下事件的编号
mysql> show binlog events in 'binlog.000011';
###根据编号截取对应的binlog日志文件
[root@db01 ~]# mysqlbinlog --start-position=233 --stop-position=1448 /data/3316/data/binlog.000011 > /tmp/bin.sql
###因为是恢复所以不需要记录日志，临时关闭
mysql> set sql_log_bin=0;
##导入数据，数据恢复成功
mysql> source /tmp/bin.sql;
ps思考问题:日志恢复数据的痛点
生产中，使用binlog日志恢复数据会有什么痛点?
1．建库时间太久，日志量太多，日志有可能只剩部分了．怎么破?
备份+binlog可以恢复到．
2. binlog保存了多个不同库的日志．只需要期中一个库的日志.怎么办?
mysqlbinlog -d
只需要期中一个表的日志.怎么办?
binlog2sql
3.一张表10亿行，误删除10行数据．怎么办?
binlog2sql做数据闪回
4．我需要的日志磅了多个文件,怎么办?
a.单独截取多个文件日志，然后合并
binlog.000005 1080
binlog.000006
binlog.000007 789
b. gtid 日志记录模式
binlog.000005 1-3
binlog.000006 4-10
binlog.000007 11-12
4.binlog2sql应用数据闪回
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
2.解析日志事件SQL
a.单独过滤某张表的binlog
[ root@db01 binlog2sq1-master]# python3 binlog2sql.py -h 10.0.0.51 -P3366 -uroot -p123 -d test1 -t tl --start-file='myspl-bin.000003'
INSERT INTO ‘test1 .'t1' ('id ) VALUES (1);#start 1647 end 1891 time 2820-09-18 08:46:53 gtid
INSERT INTO ‘test1'. 't1' ( 'id') VALUES (2); #start 1922 end 2166 time 2820-09-18 08:46:54 gtidINSERT INTO 'test1' . 't1' ( 'id') VALUES (3);#start 3125 end 3369 time 2020-09-18 08:47:50 gtidINSERT INTO 'test1 . 't1 ( 'id') VALUES (4);#start 3400 end 3644 time 2820-09-18 08:47:53 gtid
b.单独过滤某些类型的binlog
[rootpah01 binlog2sql-master]# python3 binlog2sol.py -h 10.0.0.51 -P3306 -uroot -pl123 -d testl -t tl --sql-type=deletel --start-fi1e='myspl-bin.000003'
DELETE FROM 'test1 . 't1'MHERE'id'=3 LIMIT i;#start 5172 end 5416 time 2020-09-18 09:17:48 gtid
[rootpah01 binlog2sql-master]# python3 binlog2sol.py -h 10.0.0.51 -P3306 -uroot -pl123 -d testl -t tl --sql-type=update --start-fi1e='myspl-bin.000003'
[rootpah01 binlog2sql-master]# python3 binlog2sol.py -h 10.0.0.51 -P3306 -uroot -pl123 -d testl -t tl --sql-type=insert --start-fi1e='myspl-bin.000003'
c.生成指定事件回滚语句
应用场景: 3000万数据,误删10行数据，怎么恢复?
[ root@db01 binlog2sql]# python3 binlog2sol.py -h 10.0.0.51 -P3306 -uroot -pl123 -d testl -t tl --sql-type=deletel --start-fi1e='myspl-bin.000003' --start-position=932 --stop-position=1198 -B >/tmp/flashback.sql
5.GTID应用
1.介绍
Global Transaction ID ，全局事务ID.不管有多少个binlog,都是连续生成的.具备幂等性.
1ced0886-23d4-11eb-9768-000c29f4772b: 1
server_uuid : NO.
2.配置
set global gtid_mode=ON;
set global enforce_gtid_consistency=ON ;
3.基于GTID截取日志
截取1-3：mysqlbinlog --skip-gtids --include-gtids='1ced0886-23d4-11eb-9768-000c294772b:1-3’/data/306/data/binlog.00012 >/tmp/gtid.sql
截取1-3排除2：mysqlbinlog --skip-gtids --include-gtids='1ced0886-23d4-11eb-9768-000c29f4772b:1-3' --exclude-gtids='1ced0886-23d4-11eb~9768-000c29f4772b:2'/data/3306/data/binlog.0012 >/tmp/gtid.sql
注意:如果需要截取的日志需要在原库恢复,需要加--skip-gtids 参数.跳过导出文件gtid的记录．
6.其他管理
1.日志滚动
mysql> flush logs;
mysql> select @@max_binlog_size;
重启数据数据库2.日志的删除
a.设置自动过期时间
binlog_expire_logs_seconds过期时间.默认是一个月．
b.手工清理
PURGE BINARY LOGS To 'mysql-bin.010' ;#010之前的全删除
PURGE BINARY LoGS BEFORE '2022-04-0222:46:26';#事件之前的全删除
c .全部重置
reset master;
4.慢日志（binlog）
1.介绍
记录执行较慢的日志，可以帮助优化语句
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;
3.模拟慢语句
select * from t100w where num<N limit N；自己多写几个group_by等语句模拟即可
4.分析慢日志
##-s排序c按次数排序 -t显示top3,然后根据查询的结果分析语句
[ root@db01 data]# mysqldumpslow -s c -t 3 db01-slow.log
Reading mysql slow query log from db01-slow.log
Count: 4Time=0.00s (0s)Lock=0.00s (0s) Rows=10.0 (40)，root[root]@localhostselect * from t100w where num<N limit N
Count: 3 Time=0.55s (1s) Lock=0.00s (0s) Rows=20.0 (60)，root[root]@localhostselect count(*)from t100w where num<Ngroup by k2,k1 limit N
Count: 3 Time=0.53s (1s) Lock=0.00s (0s)Rows=13.3(40)，root[root]@localhostselect count(*)from t100w where num<Ngroup by num, k1 limit N
未经允许不得转载：孙某某的运维之路 » 12.MySQL日志管理
评论已关闭