首页 / 日志 / MySQL之五:日志管理
MySQL之五:日志管理
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之五:日志管理，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15572字，纯文字阅读大概需要23分钟。
内容图文
事务日志：
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1
日志
事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 relay log
1.普通日志
1.1 介绍
默认是关闭的，可以记录MySQL中发生过的所有操作日志，一般用来调试。
-- 普通日志是否启用，默认0关闭 select @@general_log; -- 普通日志存放路径，默认：datadir/hostname.log select @@general_log_file; -- 关闭普通日志 set global general_log = OFF; -- 启用普通日志 set global general_log = ON; -- 查看普通日志内容 tail -f datadir/hostname.log
1.2 配置
mysql> show variables like '%log%'; general_log=ON #默认是OFF状态 general_log_fiel = /data/3306/data/db01.log [root@db01 data]# tail -f /data/3306/data/db01.log
1.3作用
审计
调试
2.错误日志(log_error)
2.1 介绍
记录数据库从启动以来,状态\报错\警告.
2.2 查询和配置
/数据路径下/hostname.err
mysql> show variables like '%error%'; log_error = ./db01.err log_error_verbosity=3 mysql> set global log_error_verbosity=3; #重启生效 [root@db01 ~]# systemctl restart mysqld mysql> show variables like 'log_error'; -- 日志时间戳，默认UTC，常用SYSTEM select @@log_timestamps;
2.3用法：
每天定时巡检.主要关注 [ERROR] ,[WARNING]
3.二进制日志事件的格式：
# at 328 #151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1446712300/*!*/; CREATE TABLE tb1 (id int, name char(30)) /*!*/; 事件发生的日期和时间：151105 16:31:40 事件发生的服务器标识：server id 1 事件的结束位置：end_log_pos 431 事件的类型：Query 事件发生时所在服务器执行此事件的线程的ID：thread_id=1 语句的时间戳与将其写入二进制文件中的时间差：exec_time=0 错误代码：error_code=0 事件内容： GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
注：二进制只能查找提交后的文件
mysqlbinlog：二进制日志的客户端命令工具
命令格式：
mysqlbinlog [OPTIONS] log_file… --start-position=# 指定开始位置 --stop-position=# --start-datetime= --stop-datetime= 时间格式：YYYY-MM-DD hh:mm:ss --base64-output[=name] -v -vvv
二进制日志
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
彩蛋1: sync_binlog=1 是双一其中一个1.保证事务提交立即刷新binlog到磁盘.
彩蛋2: binlog_format=row /statement/mixed
格式区别:
row (RBR) : 记录每个数据行的真实变化. 日志量会比较大.记录足够准确.
update t1 set num=20 where id<10;
statement(SBR) : 记录发生的是语句.日志量相对少.记录有可能不准确.
mixed 混合模式
binlog_format 只影响DML语句.DDL和DCL都是Statement
数据损坏模拟和恢复
故障模拟:
mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show master status;#查看当前的binlog日志进程 +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000038 | 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 oldboy; Query OK, 1 row affected (0.02 sec)
恢复过程:
mysql> show master status ; mysql> show binlog events in 'binlog.000038'; | binlog.000038 | 233 | Query | 1 | 347 | create database oldboy /* xid=772 */ | binlog.000038 | 1447 | Query | 1 | 1558 | drop database oldboy /* xid=784 */ [root@db01 data]# mysqlbinlog --start-position=233 --stop-position=1447 /data/3306/data/binlog.000038 >/tmp/bin.sql mysql> set sql_log_bin=0;#临时关闭当前窗口； mysql> source /tmp/bin.sql mysql> set sql_log_bin=1; mysql> use oldboy; Database changed mysql> show tables; +------------------+ | Tables_in_oldboy | +------------------+ | t1 | +------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 |
案例:
1. 备份策略每天全备,有全量的二进制日志
2. 业务中一共10个库,其中一个被误drop了
3. 需要在其他9个库正常工作过程中进行数据恢复
使用binlog日志恢复可能遇到的问题：
-
建库时间太久，日志量太多，日志有可能只剩部分了：配合备份
-
binlog 保存多个不同库的日志
-
只需要其中一个库的日志：
mysqlbinlog -d -
只需要其中一个表的日志：
binlog2sql
-
-
大表，小错误：
binlog2sql数据闪回 -
需要的 binlog 跨多个文件：
-
单个文件依次恢复
-
分别截取多个文件日志，然后合并，再恢复
-
GTID (Global Transaction ID)
-
思考问题: 生产中,使用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应用
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 wget -O /opt/binlog2sql-master.zip https://github.com/danfengcao/binlog2sql/archive/master.zip # 或者 rz binlog2sql-master.zip 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='binlog.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='binlog.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='binlog.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='binlog.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='binlog.000038'
--sql-type=delete --start-position=223 --stop-position=1147 -B [root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='binlog.000038'
--sql-type=delete --start-position=223 --stop-position=1147 -B>/tmp/flashback.sql
3.6 GTID应用
3.6.1 gtid介绍
Global Transaction ID . 全局事务ID.不管有多少个binlog,都是连续生成的.具备幂等性.
[root@db01 binlog2sql-master]# cd /data/3306/data
[root@db01 data]# cat auto.cnf
[auto]
server-uuid=4326cb30-23e8-11eb-92ad-000c29f8c9ad
server_uuid : GTID版本号 NO.
select @@server_uuid;
5.7 之后，不开启也会有自动生成隐藏的不能干预的GTID，建议开启
select @@SESSION.GTID_NEXT;
3.6.2 配置
vim /etc/my.cnf [mysqld] # 开启GTID gtid_mode=ON # 强制GTID一致 enforce_gtid_consistency=ON
3.6.3 基于GTID截取日志
# 包含
--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数据恢复
mysqlbinlog --skip-gtids --include-gtids='4326cb30-23e8-11eb-92ad-000c29f8c9ad:1-3' /data/3306/data/binlog.000012 >/tmp/gtid.sql mysqlbinlog --skip-gtids --include-gtids='4326cb30-23e8-11eb-92ad-000c29f8c9ad:1-3'
--exclude-gtids='4326cb30-23e8-11eb-92ad-000c29f8c9ad:2'/data/3306/data/binlog.000012 >/tmp/gtid.sql set sql_log_bin=0; source /tmp/binlog.sql set sql_log_bin=1;
注意: 如果需要截取的日志需要在原库恢复,需要加--skip-gtids 参数.跳过导出文件gtid的记录.
案例
使用二进制日志恢复数据
-
故障环境介绍
创建一个 db 库,导入一个 t1 表,t1 表中录入了很多数据
一个开发人员,drop database db;
没有备份,日志都在.怎么恢复?
思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)
-
故障案例模拟
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;
-
需求：将数据库恢复到以下状态（提示第10步和第14步是误操作，其他都是正常操作）
-
无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 | +------+
-
有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
-
备份时，指定参数
3.7其他管理
3.7.1日志滚动
mysql> flush logs;
mysql> select @@max_binlog_size;
日志文件达到1G大小(max_binlog_size)
备份时,加入参数也可以自动滚动
重启数据库
3.7.2日志的删除
自动清理
-- 二进制日志过期时间，默认一个月
select @@binlog_expire_logs_seconds;
-- 企业建议，至少保留两个全备周期+1的binlog，七天一全备，就是15天
手动清理
mysql> help purge binary logs;
-- 删除 binlog.000010 之前的
PURGE BINARY LOGS TO 'binlog.000010';
-- 删除3天之前的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
注意：不要手工rm binlog文件
全部清理
reset master;
注意：主从关系中，主库执行此操作，主从环境必崩
4.慢日志
作用：记录慢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
配置
mysql> select @@slow_query_time=0.01; mysql>set
分析工具
mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/3306/data/db01-slow.log # -s c 按执行次数排序 # -t 10 打印前10条
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 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 toolkit工具包中的命令: pt-query-diagest /data/mysql/slow.log
基于pt-query-digest将MySQL慢查询可视化
模拟慢语句
mysql> use test; mysql> select * from t100w where id<100000 limit 10; mysql> select count(*) from t100w where id<100000 group by k1; mysql> select count(*) from t100w where id<100000 group by num; mysql> select count(*) from t100w where id<100000 group by k2,k1 limit 10; mysql> select count(*) from t100w where id<100000 group by k2;
分析慢日志
[root@db01 data]# mysqldumpslow -s c -t 3 db01-slow.log Reading mysql slow query log from db01-slow.log Count: 1 Time=0.84s (0s) Lock=0.00s (0s) Rows=3721.0 (3721), root[root]@localhost select count(*) from t100w where id<N group by k1 Count: 1 Time=0.80s (0s) Lock=0.00s (0s) Rows=10.0 (10), root[root]@localhost select count(*) from t100w where id<N group by k2,k1 limit N Count: 1 Time=0.77s (0s) Lock=0.00s (0s) Rows=120242.0 (120242), root[root]@localhost select count(*) from t100w where id<N group by num
内容总结
以上是互联网集市为您收集整理的MySQL之五:日志管理全部内容，希望文章能够帮你解决MySQL之五:日志管理所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】