MySQL之9---日志管理

MySQL之7---日志管理

目录

日志文件 选项 文件名 表名 命令
普通 --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'
  1. 单独过滤某些类型的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'
  1. 生成指定事件回滚语句，数据闪回
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模式)
  1. 故障案例模拟
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;

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

  2. 无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 |
+------+
  1. 有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慢查询可视化

参考文章

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/backups/p/mysql_9.html