13 Mysql之日志管理

```sql
Mysql共有六种日志:
重做日志（redo log):CR提交事务
回滚日志（undo log):CR回滚事务
二进制日志（binlog）:记录DML语句及数据变化
错误日志（errorlog）:记录mysql实例运行错误
慢查询日志（slow query log）：记录慢SQL
一般查询日志（general log）：所有行为记录
中继日志（relay log）：主从复制日志
```

# 1.General log

```sql
1.概念:开启 general log 将所有到达MySQL Server的SQL语句记录下来。
2.使用:一般不会开启开功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供排障使用。
3.相关参数:general_log、log_output、general_log_file

show variables like 'general_log';  -- 查看日志是否开启
set global general_log=on; -- 开启日志功能

show variables like 'general_log_file';  -- 看看日志文件保存位置
set global general_log_file='tmp/general.log'; -- 设置日志文件保存位置

show variables like 'log_output';  -- 看看日志输出类型  table或file,基本用不上
set global log_output='table'; --设置输出类型为 table日志信息就会被写入到mysql.slow_log表中.
set global log_output='file';   -- 设置输出类型为file。
set global log_output='table,file'; --更多IO资源,基本用不上

4.开启步骤
临时:set global general_log = ON;
永久:my.cnf-->[mysqld]-->general_log = 1
```

**实战案例：**

- 查看general log文件位置
mysql> show variables like 'general_log_file'; 
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log_file | /data/mysql3306/data/db01.log |
+------------------+-------------------------------+
1 row in set (0.01 sec)

- 开启
mysql> set GLOBAL general_log=on; 
Query OK, 0 rows affected (0.01 sec)

- 命令测试2
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

- 命令测试3
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

- 查看记录的日志
root@db01:~# cat /data/mysql3306/data/db01.log 
/opt/mysql-8.0.36/bin/mysqld, Version: 8.0.36 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2024-03-28T03:24:09.745628Z         8 Query     show databases
2024-03-28T03:24:45.356966Z         8 Query     SELECT DATABASE()
2024-03-28T03:24:45.357838Z         8 Init DB   test
2024-03-28T03:24:45.360889Z         8 Query     show databases
2024-03-28T03:24:45.362483Z         8 Query     show tables
2024-03-28T03:24:45.365726Z         8 Field List        t1 
2024-03-28T03:25:18.224415Z         8 Query     show tables

mysql> set GLOBAL general_log=off; # 关闭
Query OK, 0 rows affected (0.01 sec)
```

# 2.Binlog详解

## 2.1.binlog介绍

```sql
1.介绍:
 - 最重要Mysql日志.
 - 记录了所有的DDL和DML语句(对数据库的改动)
 - 数据库的查询select或show等不会被binlog日志记录
 - 以事件形式记录(包含语句所执行的消耗的时间，事务安全型的)
 - 性能损耗:1%的性能损耗。
 - 数据库重启，flushlogs，达到指定大小.

2.作用
 - 主从复制:Master端开启binlog==>slaves来达到master-slave数据一致的目的。
 - 数据恢复:mysqlbinlog,binlog2sql达到备份+binlog完成任一事务之前的数据恢复
 - 数据管道:canle，tapdata

3.种类:
 - 二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件；
 - 二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

4.binlog日志
show variables like '%log_bin%'; or select @@log_bin  #是否开启
select @@log_bin_basename;  #路径  
select @@server_id;         #服务ID号
select @@binlog_format;     #binlog格式
select @@sync_binlog;       #binlog是否落盘
- 0,MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新,性能最好,风险最大,Crash则binlog_cache中所有binlog信息丢失。
- 1,最安全,每次事务提交,mysql刷新binlog，最安全但是性能损耗最大
- >1,每过多少个事务刷新一次Binlog,比如与业务沟通可以设置100,牺牲一定一致性,获取更高并发和性能.

5.binlog格式（5.7.7之前默认STATEMENT,之后默认ROW),
(1)STATEMENT:基于SQL语句复制(statement-based replication, SBR),每条修改数据SQL会被记录
	- 优点:不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；
  - 缺点:在某些情况下会导致主从数据不一致，比如执行sysdate()、sleep()等。
(2) ROW:基于行的复制(row-based replication, RBR),仅记录哪条数据被修改了。
  - 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
  - 产生大量的日志，尤其是alter table的时候会让日志暴涨
(3) MIXED:基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。
  - 生产环境中使用ROW,因为一般我们需要binlog来做更多的操作(数据同步,日志解析),Mixed因为混合模式binlog日志处理会变得很困难(很多同步工具无法使用)

6.binlog记录的内容
binlog 什么时候会被刷新
1)重启数据库
2)执行flush logs;
3)超过max binlog的参数
#事务不可以跨binlog

mysql>flush logs;
mysql>insert into app_user values(1,'zhangsan','2939858@qq.com',12338563,1,'siehv3',23,now(),now());
shell>mysqlbinlog  binlog.000015 >/tmp/mysql3306.log

###binlog记录内容
# at 235
#211030 22:19:04 server id 1  end_log_pos 318 CRC32 0x149ef4ad  Query   thread_id=1213168       exec_time=0     error_code=0
SET TIMESTAMP=1635603544/*!*/;

#上述内容得知
- position: 位于文件中的位置，即第一行的（# at 211030）,说明该事件记录从文件第211030个字节开始
timestamp: 事件发生的时间戳，即第二行的（211030 22:19:04）
server id: 服务器标识（1）
end_log_pos 表示下一个事件开始的位置（即当前事件的结束位置+1）
thread_id: 执行该事件的线程id （thread_id=1213168）
exec_time: 事件执行的花费时间
error_code: 错误码，0意味着没有发生错误
type:事件类型Query

7.参数设置
[mysqld]
expire_logs_days = 15/30           #设置binlog清理时间
max_binlog_size = 300m         #binlog每个日志文件大小
binlog_cache_size = 4m         #binlog缓存大小
max_binlog_cache_size = 512m   #最大binlog缓存大小
```

**案例:** binlog基础

描述数据库修改的语句，如create table（DDL）、update（dml）等数据库变更语句。不会记录select show等语句。
```sql
mysql> create database aa;
Query OK, 1 row affected (0.02 sec)

mysql> use aa;
Database changed

mysql> create table a1 (id int);
Query OK, 0 rows affected (0.05 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 521
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)

mysql> show binlog events in 'binlog.000001' from 521;
Empty set (0.00 sec)

mysql> show binlog events in 'binlog.000001' from 9;
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                            |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------+
| binlog.000001 | 126 | Previous_gtids |         1 |         157 |                                                 |
| binlog.000001 | 157 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| binlog.000001 | 234 | Query          |         1 |         336 | create database aa /* xid=15 */                 |
| binlog.000001 | 336 | Anonymous_Gtid |         1 |         413 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| binlog.000001 | 413 | Query          |         1 |         521 | use `aa`; create table a1 (id int) /* xid=21 */ |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------+
5 rows in set (0.01 sec)
```
如何开启和关闭Binlog
```sql
mysql> show global VARIABLES like '%log%bin%';
| log_bin | OFF

[mysqld]
datadir = /var/lib/mysql/data
basedir = /opt/rh/rh-mysql57/root/usr
plugin-dir = /opt/rh/rh-mysql57/root/usr/lib64/mysql/plugin
log_bin=/var/lib/mysql/data/binlog/mysql-binlon
binlog_format=row
server-id=1
修改配置文件 mysql5.7
如果是myqsl8.0 则默认开启

生产环境：
创建MySQL的时候 手动创建目录，建议MySQL data 平级目录

bash-4.2$ mkdir -p /var/lib/mysql/binlog/
bash-4.2$ chown mysql:mysql /var/lib/mysql/binlog/
比如
/export/servers//port/data
/export/servers/port/binlog
/export/servers/port/log
/export/servers/port/redolog
/export/servers/port/undolog
/export/servers/port/tmp

关闭binlog
配置文件里添加 永久关闭
skip_log_bin
disable_log_bin
临时关闭
set session sql_log_bin=0

dba维护的时候，比如说 批量创建“dba账户”时，或者批量执行dba任务时。
```
**binlog的作用**
复制：主库的变更先写入binlog，然后传到从库进行恢复，主从的具体原理，下周会讲
灾备：当误操作后，可以先把全备份导入某个新的实例中。

| 日志格式 | 优点 | 缺点 |
| --- | --- | --- |
| STATEMENT | 日志量少，节约IO，性能高 | 在主从复制中可能导致主从数据不一致，如果使用了不确定的函数，类似UUID等函数 |
| ROW | 主从数据基本一致，支持闪回 | 日志量多 |
| MIXEND | 日志量少，节约IO，性能高。解决了STATEMENT格式部分数据不一致的情况 | 不支持闪回，部分高可用架构不支持改日志格式，不方便将数据同步到其他类型的数据库 |

## 2.2.binlog常用操作

### 2.2.1.基本命令

```sql
# 是否启用binlog日志
show variables like 'log_bin';

# 查看详细的日志配置信息
show global variables like '%log%';

# mysql数据存储目录
show variables like '%dir%';

# 查看binlog的目录
show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看最新一个binlog日志文件名称和Position
show master status;

#事件查询;
##语法:
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
show binlog events in 'binlog.000015' from 393;

# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3;
vim my.cnf

# 删除当前的binlog文件
reset master;  #生产环境谨慎操作，切记

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件之前的binlog
purge master logs to 'mysql-bin.000003';

# 清除3天前binlog日志BEFORE。
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  ####重要
```

### 2.2.2.mysqlbinlog工具使用

```sql
1.数据准备
create table `t1` (
  `id` int(11) default null,
  `name` varchar(20) default null
) engine=innodb default charset=utf8mb4;

insert into t1(id,name) select 101,'tome101';
insert into t1(id,name) select 102,'tome102';
insert into t1(id,name) select 103,'tome103';
insert into t1(id,name) select 104,'tome104';
insert into t1(id,name) select 105,'tome105';
insert into t1(id,name) select 106,'tome106';
insert into t1(id,name) select 107,'tome107';
insert into t1(id,name) select 108,'tome108';

update t1 set name='jack101' where id=101;
update t1 set name='jack103' where id=103;
update t1 set name='jack105' where id=105;

delete from t1 where id=102;
delete from t1 where id=104;
delete from t1 where id=106;

2.查看binlog内容
mysqlbinlog --no-defaults --database=test  --start-datetime='2021-10-31 11:37:00'   mysql-bin.000007 | less

#加上--base64-output=decode-rows –v 选项解析
##base64-output，可以控制输出语句输出base64编码的BINLOG语句;decode-rows：选项将把基于行的事件解码成一个SQL语句

#按照时间读取
mysqlbinlog --no-defaults --database=test --base64-output=decode-rows -v    binlog.000017 --start-datetime='2021-10-30 22:45:00'   --stop-datetime='2021-10-30 23:45:00'| less

#按照点位读取（4425可以 4481不行，Table_map: `test`.`t1`定位）
mysqlbinlog --no-defaults --database=test --base64-output=decode-rows -v    binlog.000017 --start-position=4425   --stop-position=4529| less

--start-position 开始位置点
--stop-position 结束位置点
binlog的开始位置点 一定是begin开始的 结束位置点 是commit之后的

#查看特定时间
mysqlbinlog --no-defaults --database=test --base64-output=decode-rows -v   mysql-bin.000007 --start-position=3780   --stop-position=4044|grep insert

#简单数据恢复
mysqlbinlog --no-defaults --database=test mysql-bin.000006|mysql -uqianlong -p123456 -h127.0.0.1 test

shell>mysqlbinlog --no-defaults --database=test --base64-output=decode-rows -v   mysql-bin.000006 >/tmp/binlog.sql
mysql>source /tmp/binlog.sql;

--skip-gtids=true

#binlog库重写恢复
##应用场景:重写库名,某一时刻全备(导入指定新库)+mysqlbinlog增量(rewrite-db指定新库,业务写-->一直追)
mysqlbinlog --no-defaults --rewrite-db='test->test1' --base64-output=decode-rows -vv     mysql-bin.000007 --start-datetime='2021-10-30 22:45:00'
```

1.远程备份binlog日志

```sql
场景:本地磁盘备份容易丢失binlog,正常binlog备份(压缩后传输到远程服务器),为了防止周期备份宕机。
功能:Mysql5.6开始.mysqlbinlog支持将远程服务器上的binlog实时复制到本地服务器上
机制:通过MySQLReplication API实时获取二进制事件。本质上，就相当于MySQL的从服务器。与普通服务器类似，主服务器发生事件后，一般都会在0.5~1秒内进行备份。（事件立马发生,宕机在事件发生的0.1s,并且本次磁盘损坏,0.1秒有并发）

#步骤
1.账号准备
create user  'binlogrepl'@'%' IDENTIFIED BY 'repl123';
GRANT REPLICATION SLAVE ON *.* TO 'binlogrepl'@'%';
mysql -ubinlogrepl -prepl123 -h 172.21.188.37 -P3306

2.命令备份
mysqlbinlog --read-from-remote-server --raw --host=172.21.188.37 --port=3306 --user=binlogrepl --password=repl123 --stop-never mysql-bin.000007
#参数详解
read-from-remote-server：备份远程服务器的binlog。不指定查找本地的binlog。
raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。
user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。
stop-never：远程服务器不关闭或者连接未断开一直复制binlog。
mysql-bin.0000017：代表从哪个binlog开始复制。

除了以上选项外，还有以下几个选项需要注意：
stop-never-slave-server-id：制定备份从服务器的server-id
to-last-log：获取其后生成的binlog,stop-never选项则会隐式打开to-last-log选项。
result-file：用于设置远程服务器的binlog，保存到本地的前缀。譬如对于mysql-bin.000001，如果指定--result-file=/test/backup-，则保存到本地后的文件名为/test/backup-mysql-bin.000001。注意：如果将--result-file设置为目录，则一定要带上目录分隔符“/”。譬如--result-file=/test/，而不是--result-file=/test，不然保存到本地的文件名为/testmysql-bin.000001。
```

### 两阶段提交

Mysql大体可以分为Server层和存储引擎层，其中binlog在Server层，redo log在存储引擎层

```sql
作用:redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
```

![107](https://img.sunrisenan.com/img/2024/03/20/133113054.png)

主要过程

```sql
1）执行器调用存储引擎接口，存储引擎将修改更新到内存中后，将修改操作写到redo log里面，此时redo log处于prepare状态；
2）存储引擎告知执行器执行完毕，执行器开始将操作写入到bin log中，写完后调用存储引擎的接口提交事务；
3）存储引擎将redo log的状态置为commit。

两阶段提交机制的必要性
1.没有prepare,先写redo log再写binlog:写完commit。如果CR，redo log 恢复数据比binlog多.
2.先写 binlog 后写 redo log:binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效.binlog恢复数据会多出数据,与数据页不一致
```

binlog 和redo的一致性总结：
```sql
redolog 是InnoDB 层的,binlog是MySQL 层的；
redolog是物理逻辑日志,binlog是逻辑日志；
InnoDB 层负责写prepare log，写的还是 redo file，写入xid，xid 写在undo 页里面，
这时候的 innodb redo prepare log 写入成功了但是还没有commit的标志（未封口）。

MySQL层负责写binlog, 当MySQL层binlog写成功了就会把binlog filename，position 往
InnoDB层写commit log.（也就是说InnoDB层负责写commit log）

redo log file 里面记录到了这个position位置，可以理解成redo file 就有了commit的标志，说明redo也commit了。

因此binlog和redo 保证一致性的是通过内部事务xid 的。事物在commit的时候一般有有几个步骤：

1 InnoDB 层写 prepare log
2 MySQL 层写binlog
3 InnoDB 层写commit log
```

MySQL没有开启Binary log的情况下？
```sql
没有binlog，那innodb 里面prepare状态的事务都会回滚

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此确保重做日志都已经写
入磁盘。当数据库发生宕机时，可以通过重做日志进行恢复。

InnoDB存储引擎通过redo和undo日志可以safe crash recovery数据库，当数据库crash recovery时，通过redo日志将所有已经在存储引擎内部提交的事务应用重做日志恢复，
所有已经prepared但是没有commit的事务将会应用undo log做rollback。
然后客户端连接时就能看到已经提交的数据存在数据库内，未提交被回滚地数据需要重新执行。
```

扩展：http://mysql.taobao.org/monthly/2015/06/01/

![108](https://img.sunrisenan.com/img/2024/03/20/133215578.png)

### 2.2.3.bin2sql工具恢复勿删除数据

参考地址：https://www.cnblogs.com/lkj371/p/15177266.html

```sql
#介绍:
binlog2sql解析出你要的SQL。可得到原始SQL，回滚SQL，移除主键的INSERT SQL等

#用途:
  - 数据快速回滚（闪回）
  - 主从切换后新master丢数据的修复
  - 从binlog生成标准SQL，带来的衍生功能
#环境需求
  - Python 2.7、3.4+
  - MySQL 5.6、5.7
  - 开启binlog ，binlog格式为ROW格式
  - binlog_row_image = full #5.6,5.7.默认参数是FULL,代表Binlog日志记录所有镜像

#准备
创建用户
CREATE USER 'olddba'@'%'IDENTIFIED BY "olddba123O";
GRANT all ON *.* TO 'olddba'@'%' ;
创建表
create table a1(id int,name varchar(10));
写入数据
insert into a1 values (1,'a');

#安装
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip3 install -r requirements.txt 
shell> pip3 install  pymysql

注：如果是mysql 8.0需要修改以下配置
 - **binlog2sq1路径下的requirements.txt文件需要调整**
 - cat requirements.txt
 - PyMySQL==0.7.11
 - whee1==0.29.0
 - mysq1-replication==0.13
 - 需要将PyMySQL由0.7.11调整为0.9.3
 - she11>pip install-r requirements.txt

#使用
2.标准用法-解析出执行过的sql
shell> python3 binlog2sql.py -h127.0.0.1 -P3306 -uqianlong -p123456 -dtest -t t1 --start-file='mysql-bin.000008'

3.解析回滚SQL
shell> python3 binlog2sql.py   -h127.0.0.1 -P3306 -uqianlong  -p123456  --databases='test' --tables='t1'    --start-file='mysql-bin.000002'  --start-datetime  '2021-10-31 00:00:00' -B

案例：
root@db01:/data/mysql3306/data# python3 /root/binlog2sql/binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p  -dtest -t t1 --start-file='binlog.000012'
Password: 
USE b'sunrise';
create database sunrise;
USE b'sunrise';
create table `t1` (
  `id` int(11) default null,
  `name` varchar(20) default null
) engine=innodb default charset=utf8mb4;
USE b'sunrise';
DROP TABLE `t1` /* generated by server */;
USE b'sunrise';
drop database sunrise;
USE b'test';
create database test;
USE b'test';
create table `t1` (
  `id` int(11) default null,
  `name` varchar(20) default null
) engine=innodb default charset=utf8mb4;
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (101, 'tome101'); #start 5719 end 5977 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (102, 'tome102'); #start 6008 end 6266 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (103, 'tome103'); #start 6297 end 6555 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (104, 'tome104'); #start 6586 end 6844 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (105, 'tome105'); #start 6875 end 7133 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (106, 'tome106'); #start 7164 end 7422 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (107, 'tome107'); #start 7453 end 7711 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (108, 'tome108'); #start 7742 end 8000 time 2024-03-26 13:26:14
UPDATE `test`.`t1` SET `id`=101, `name`='jack101' WHERE `id`=101 AND `name`='tome101' LIMIT 1; #start 8031 end 8312 time 2024-03-26 13:26:14
UPDATE `test`.`t1` SET `id`=103, `name`='jack103' WHERE `id`=103 AND `name`='tome103' LIMIT 1; #start 8343 end 8624 time 2024-03-26 13:26:14
UPDATE `test`.`t1` SET `id`=105, `name`='jack105' WHERE `id`=105 AND `name`='tome105' LIMIT 1; #start 8655 end 8936 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=102 AND `name`='tome102' LIMIT 1; #start 8967 end 9225 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=104 AND `name`='tome104' LIMIT 1; #start 9256 end 9514 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=106 AND `name`='tome106' LIMIT 1; #start 9545 end 9803 time 2024-03-26 13:26:15
root@db01:/data/mysql3306/data# python3 /root/binlog2sql/binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p  -dtest -t t1 --start-file='binlog.000012' -B
Password: 
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (106, 'tome106'); #start 9545 end 9803 time 2024-03-26 13:26:15
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (104, 'tome104'); #start 9256 end 9514 time 2024-03-26 13:26:14
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (102, 'tome102'); #start 8967 end 9225 time 2024-03-26 13:26:14
UPDATE `test`.`t1` SET `id`=105, `name`='tome105' WHERE `id`=105 AND `name`='jack105' LIMIT 1; #start 8655 end 8936 time 2024-03-26 13:26:14
UPDATE `test`.`t1` SET `id`=103, `name`='tome103' WHERE `id`=103 AND `name`='jack103' LIMIT 1; #start 8343 end 8624 time 2024-03-26 13:26:14
UPDATE `test`.`t1` SET `id`=101, `name`='tome101' WHERE `id`=101 AND `name`='jack101' LIMIT 1; #start 8031 end 8312 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=108 AND `name`='tome108' LIMIT 1; #start 7742 end 8000 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=107 AND `name`='tome107' LIMIT 1; #start 7453 end 7711 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=106 AND `name`='tome106' LIMIT 1; #start 7164 end 7422 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=105 AND `name`='tome105' LIMIT 1; #start 6875 end 7133 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=104 AND `name`='tome104' LIMIT 1; #start 6586 end 6844 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=103 AND `name`='tome103' LIMIT 1; #start 6297 end 6555 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=102 AND `name`='tome102' LIMIT 1; #start 6008 end 6266 time 2024-03-26 13:26:14
DELETE FROM `test`.`t1` WHERE `id`=101 AND `name`='tome101' LIMIT 1; #start 5719 end 5977 time 2024-03-26 13:26:14

#参数使用
1.连接配置
[mysqld]
server_id = 8563
log_bin = /var/log/mysql/mysql-bin
max_binlog_size = 300M
binlog_format = row
binlog_row_image = full #5.6,5.7.默认参数是FULL,代表Binlog日志记录所有镜像

2.解析模式
stop-never #持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。

-K, --no-primary-key #对INSERT语句去除主键。可选。默认False,这样可以插入重复数据

-B,--flashback #生成回滚SQL，可解析大文件，不受内存限制,与stop-never或no-primary-key不能同时添加

back-interval #-B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。

3.解析控制范围
--start-file #起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos  #起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file   #终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos  #终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime #起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤
--stop-datetime #终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

4.对象过滤
-d, --databases #只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables      #只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml       #只解析dml，忽略ddl。可选。默认False。

--sql-type        #只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。
```

- 闪回实战(项目,flag标记)

```sql
TIPS：
- 恢复数据的流程。整体流程
1.需求方联系DBA，(如果是你误操作，你就是需求方)
2.库设置只读 或者表级别只读，避免产生脏数据。
3.要和需求方确认， 库 表 信息 还有时间信息
4.生成会滚sql，在执行之前，dba和需求方要共同确认。避免 有脏数据，或者存在危险sql

- 闪回的目标：快速筛选出真正需要回滚的数据。
- 先根据库、表、时间做一次过滤，再根据位置做更准确的过滤。
- 由于数据一直在写入，（发生数据错乱和覆盖场景设置主库read_only）,要确保回滚sql中不包含其他数据。可根据是否是同一事务、误操作行数、字段值的特征等等来帮助判断。
- 执行回滚sql时如有报错，需要查实具体原因，一般是因为对应的数据已发生变化。由于是严格的行模式，只要有唯一键(包括主键)存在，就只会报某条数据不存在的错，不必担心会更新不该操作的数据。业务如果有特殊逻辑，数据回滚可能会带来影响。
- 如果只回滚某张表，并且该表有关联表，关联表并不会被回滚，需与业务方沟通清楚。
```

- 闪回工具发展

MySQL闪回特性最早由阿里彭立勋开发，彭在2012年给官方提交了一个patch，并对[闪回设计思路](http://www.penglixun.com/tech/database/mysql_flashback_feature.html)做了说明(设计思路很有启发性，强烈推荐阅读)。但是因为种种原因，业内安装这个patch的团队至今还是少数，真正应用到线上的更是少之又少。彭之后，又有多位人员针对不同mysql版本不同语言开发了闪回工具，原理用的都是彭的思路。

我将这些闪回工具按实现方式分成了三类。

- 第一类是以patch形式集成到官方工具mysqlbinlog中。以彭提交的patch为代表。

> 优点 mysqlbinlog
> 
> - 上手成本低。mysqlbinlog原有的选项都能直接利用，只是多加了一个闪回选项。闪回特性未来有可能被官方收录。
> - 支持离线解析。
> 
> 缺点
> 
> - 兼容性差、项目活跃度不高。由于binlog格式的变动，如果闪回工具作者不及时对补丁升级，则闪回工具将无法使用。目前已有多位人员分别针对mysql5.5，5.6，5.7开发了patch，部分项目代码公开，但总体上活跃度都不高。
> - 难以添加新功能，实战效果欠佳。在实战中，经常会遇到现有patch不满足需求的情况，比如要加个表过滤，很简单的一个需求，代码改动也不会大，但对大部分DBA来说，改mysql源码还是很困难的事。
> - 安装稍显麻烦。需要对mysql源码打补丁再编译生成。
> 这些缺点，可能都是闪回没有流行开来的原因。
> 
- 第二类是独立工具，通过伪装成slave拉取binlog来进行处理。以binlog2sql为代表。

> 优点
> 
> - 兼容性好。伪装成slave拉binlog这项技术在业界应用的非常广泛，多个开发语言都有这样的活跃项目，MySQL版本的兼容性由这些项目搞定，闪回工具的兼容问题不再突出。
> - 添加新功能的难度小。更容易被改造成DBA自己喜欢的形式。更适合实战。
> - 安装和使用简单。
> 
> 缺点
> 
> - 必须开启MySQL server。
- 第三类是简单脚本。先用mysqlbinlog解析出文本格式的binlog，再根据回滚原理用正则进行匹配并替换。

> 优点
> 
> - 脚本写起来方便，往往能快速搞定某个特定问题。
> - 安装和使用简单。
> - 支持离线解析。
> 
> 缺点
> 
> - 通用性不好。
> - 可靠性不好。

就目前的闪回工具而言，线上环境的闪回，建议使用binlog2sql，离线解析使用mysqlbinlog。

## 2.3.GTID

### 2.3.0.基础

先了解复制的基本原理：

**1. MySQL复制方式：**

```sql
master用户写入数据，生成event记到binary log中. slave接收master上传来的binlog，然后按顺序应用，重现master上的操作。

传统的复制基于(file,pos)，当主从发生宕机，切换的时候有问题

slave保存的是原master上的(file,pos)，无法直接指向新master上的(file,pos)
```

![109](https://img.sunrisenan.com/img/2024/03/20/133759677.png)

**2. 日志记录上position方式和GTID方式区别**

![111](https://img.sunrisenan.com/img/2024/03/20/134934484.png)

```sql
1.5.6 版本新加的特性
2.介绍:GTID (global transaction identifier)是已提交事务的唯一表示, 不单在它产生的originating server是唯一的, 在复制拓扑中的所有server范围内都是唯一的, 该特性当gtid_mode=ON时才生效.
- GTID的格式:source_id:transaction_id
	- sourceid就是产生该事务所在server的server-uuid,
	- transactionid与事务提交顺序有关, 取值从1开始, 之后在同一个originating server的提交的事务, 它的transactionid逐一递增.

比如,在 UUID为 3e11fa47-71ca-11e1-9e33-c80aa9429562 的server上提交的第58个事务的 GTID是:
- 3e11fa47-71ca-11e1-9e33-c80aa9429562:58

3.主从复制应用：
- 通过pos复制(postion)方式,将用户进行的每一项操作都进行编号(pos),每一个event都有一个起始编号,一个终止编号。
- GTID就是类似于pos的一个作用,全局通用并且日志文件里事件的GTID值是一致的。 pos与GTID在日志里是一个标识符，在slave 里已不同的方式展现。  
- GTID的生成受gtid_next控制。 在Master上，gtid_next是默认的AUTOMATIC,即GTID在每次事务提交时自动生成。它从当前已执行的GTID集合(即gtid_executed)中，找一个大于0的未使用的最小值作为下个事务GTID。同时将GTID写入到binlog(set gtid_next记录)，在实际的更新事务记录之前。 在Slave上，从binlog先读取到主库的GTID(即set gtid_next记录)，而后执行的事务采用该GTID。
```

![112](https://img.sunrisenan.com/img/2024/03/20/135042564.png)

GTID变量:

![113](https://img.sunrisenan.com/img/2024/03/20/135131115.png)

### 2.3.1. GTID优势与特点

```sql
- 更简单的实现failover，不像传统方式那样在需要找log_file和log_Pos。
- 更简单的搭建主从复制。
- 复制集群有一个统一的方式识别复制位置，给集群管理带来了便利。
- 正常情况下，GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。
- MySQL5.7.6版本开始可以在线升级gtid模式
- 查看当前GTID信息:SELECT * FROM  mysql.gtid_executed;

5.6
- 必须配置参数log_slave_updates的最重要原因在于当slave重启后，无法得知当前slave已经运行到的GTID位置，因为变量gtid_executed是一个内存值

5.7,8.0
- gtid_executed这个值给持久化,保存在mysql.gtid_executed表中
- 表mysql.gtid_executed的更新仅在二进制rotation时发生,重启扫描binlog
	
触发条件:（5.6是个内存表.5.7持久化）
- 在binlog发生rotate(flush binary logs/达到max_binlog_size)或者关闭服务时，会把所有写入到binlog中的Gtid信息写入到mysql.gtid_executed表。
- 从库：如果没有开启log_bin或者没有开启log_slave_updates，从库在应用relay-log中的每个事务会执行一个insert mysql.gtid_executed操作。
```

### 2.3.2. 常用命令

```sql
1.gtid设置 my.cnf 
gtid_mode=ON #必选
enforce-gtid-consistency=true #必选
log-bin=mysql #5.6必选 5.7.5和它之后可选，为了高可用，最好设置
server-id=1842  #开启log-bin的必须设置 
log-slave-updates=ON # 5.6必选 5.7.5和它之后可选，为了高可用切换，最好设置ON

2.gtid跳过gtid_next
stop slave;
set gtid_next='d74faa2d-5819-11e8-b248-ac853db70398:10603';
begin;commit;
set gtid_next='automatic';
start slave;

3.gtid清除gtid_pureged
- 命令的实际意义：因没有binlog信息（expire_logs_days），不考虑这些gtid确认和回滚。 常用备份恢复，搭建从库的时候使用。
- 自动触发机制：flush，服务器重新启动
- 使用场景
  - set @@GLOBAL.gtid_purged显式添加到集合中的gtid。(之前事务代表执行完毕)

tips:mysqldump --set-gtid-purged=off/on 参数;
是否将GTID_PURGED’添加到输出中
```

![114](https://img.sunrisenan.com/img/2024/03/20/135252982.png)

```sql
4.gtid升级
- pos升级gtid方式，条件允许建议重新搭建从库的方式。以下方式存在风险。
- gtid_mode可选值
   - ON：完全打开GTID，如果打开状态的备库接受到不带GTID的事务，则复制中断
   - ON_PERMISSIV：可以认为是打开gtid前的过渡阶段，主库在设置成该值后会产生GTID，同时备库依然容忍带GTID和不带GTID的事务
   - OFF_PERMISSIVE：可以认为是关闭GTID前的过渡阶段，主库在设置成该值后不再生成GTID,备库在接受到带GTID和不带GTID事务都可以容忍。主库在关闭GTID时，执行事务会产生一个Anonymous_Gtid事件，会在备库执行：set @@session.gtid_next=‘anonymous’
   - OFF：彻底关闭GTID，如果关闭状态的备库收到带GTID的事务，则复制中断

#从position模式切换到GTID模式
mysql>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
mysql>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
mysql>SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
mysql>SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
mysql>SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
mysql>SET @@GLOBAL.GTID_MODE = ON;
shell>vim my.cnf
gtid-mode=ON
ENFORCE_GTID_CONSISTENCY = ON

5.gtid 压缩 gtid_executed_compression_period
- 启用GTID时，服务器会定期在mysql.gtid_executed表上执行此类压缩。
- 变量控制压缩表之前允许的事务数.默认值1000
mysql> select thread_id,thread_os_id,name, processlist_command, processlist_state   from `performance_schema`.threads where name like '%compress%';
```

![115](https://img.sunrisenan.com/img/2024/03/20/135346864.png)

### 2.3.3. 限制

```sql
1.create table xxx as select： #8.0.21已经解决了
拆分成两部分：
create table xxxx like table;
insert into xxxx select * from table;

2.临时表的限制
使用GTID复制模式：
1）.不支持create temporary table 和 drop temporary table。
2）.在autocommit=1的情况下可以创建临时表，
3）.Master端创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致，主从中断.

3.mysql_upgrade.
在启用全局事务标识符(GTIDs)的情况下运行时，不要通过mysql_upgrade(——write binlog选项)启用二进制日志记录。

4.sql_slave_skip_counter
传统模式的跳过postion方式gtid模式下不支持。
```

配合mysqlbinlog应用

```sql
mysql> show master status;  #获取gtid
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000006 |     2266 |              |                  | ddd2aab7-24ae-11ec-8406-fa202013137b:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+

#截取gtid的binlog日志
mysqlbinlog  --include-gtids='ddd2aab7-24ae-11ec-8406-fa202013137b:1-8' --base64-output=decode-rows -v  mysql-bin.000006

mysqlbinlog  --include-gtids='ddd2aab7-24ae-11ec-8406-fa202013137b:1-8' --exclude-gtids='ddd2aab7-24ae-11ec-8406-fa202013137b:7' --base64-output=decode-rows -v  mysql-bin.000006

#恢复数据且不记录binlog
mysqlbinlog  --include-gtids='ddd2aab7-24ae-11ec-8406-fa202013137b:1-8'    --skip-gtids=true  mysql-bin.000006>/tmp/binlog.sql
set sql_log_bin=0; 
source /tmp/binlog.sql 
set sql_log_bin=1;

参数:
--skip-gtids=true   #一个gtid在一个实例只执行一次,不加会被跳过
--include-gtids     #包含gtid的事务
--exclude-gtids     #排除gtid事务
```

## 2.4.Binlog 的落盘

binlog的同步到磁盘的频率由sync_binlog参数控制

| 列名 | 列名 |
| --- | --- |
| sync_binlog=0 | 禁用MySQL服务器将binlog同步磁盘的功 能，是由操作系统控制binlog刷盘。性能会好，但是操作系统奔溃可能会丢失事务 |
| sync_binlog=1 | 每个事务都会同步到磁盘，这个是最安全的， 但磁盘写入次数会增加，性能会下降  |
| sync_binlog=N | 表示每N个事务binlog同步一次到磁盘，当操 作系统奔溃时，服务器提交的事务可能被刷新 到binlog中，可能会丢失部分事务。 |

生产建议：
主库必须是 sync_binlog=1。
什么情况下，可以配置 sync_binlog=N/sync_binlog=0 主从有延迟，从库性延迟较大，从库io有瓶颈

## 2.5.MySQL8.0 binlog加密

```bash
MySQL8.0.14开始，可以对binlog文件和中继日志文件进行加密，从而保护敏感数据
```
https://dev.mysql.com/doc/refman/8.0/en/replication-security.html

# 3.slowlog

## 3.1.简介

```sql
1.介绍:MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL,生产0.1
2.配置
vim /etc/my.cnf
slow_query_log=1   #开关
slow_query_log_file=/chj/class/data/mysql57/log/slow.log #目录文件
long_query_time=0.1  #查询时间慢SQL阈值 ,调成0记录所有SQL语句
log_queries_not_using_indexes=on  #未使用索引的查询
log_output   #默认值是'FILE'。
log_slow_admin_statements  #系统变量log_slow_admin_statements  表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

3.自导工具解析
mysqldumpslow -s c -t 10 /chj/class/data/mysql57/log/tidb_slow_query.log  #基本不用

开启慢查询日志
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.02 sec)

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

root@db01:/data/mysql3306/data# cat db01-slow.log
/opt/mysql-8.0.36/bin/mysqld, Version: 8.0.36 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2024-03-28T07:12:18.274841Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 10.002478  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1711609928;
select sleep(10);

- Query_time 语句执行时间 单位是秒
- Lock_time 获取所的时间，单位是秒
- Rows_sent 发送给客户端的行数
- Rows_examined MySQL server层检查的行数

mysql> set global log_slow_extra=on;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

root@db01:/data/mysql3306/data# cat db01-slow.log
# Time: 2024-03-28T07:18:03.845077Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 10.001040  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1 Thread_id: 8 Errno: 0 Killed: 0 Bytes_received: 23 Bytes_sent: 57 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-03-28T07:17:53.844037Z End: 2024-03-28T07:18:03.845077Z
SET timestamp=1711610273;
select sleep(10);

- Thread_id: 语句线程id
- Errno: 语句错误号
- Killed: 如果该语句终止，则错误号指示原因，如果该语句正常终止，则返回0
- start 语句开始执行时间
- end 语句结束时间
```

## 3.2.**pt-query-digest分析慢日志**

### 3.2.1.安装与介绍

> Percona Toolkit简称pt工具，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等，我们以后主要依赖的Mysql运维工具包

```sql
1.安装
shell>wget -c  install https://www.percona.com/downloads/percona-toolkit/3.0.1/binary/redhat/7/x86_64/percona-toolkit-3.0.1-1.el7.x86_64.rpm
shell>yum localinstall percona-toolkit-3.0.1-1.el7.x86_64.rpm

2.使用,定位到文件
pt-query-digest   /chj/class/data/mysql57/log/tidb_slow_query.log >/tmp/slow.log

案例：
root@db01:~# wget https://downloads.percona.com/downloads/percona-toolkit/3.5.7/binary/debian/jammy/x86_64/percona-toolkit_3.5.7-1.jammy_amd64.deb
root@db01:~# dpkg -i percona-toolkit_3.5.7-1.jammy_amd64.deb

root@db01:/data/mysql3306/data# pt-query-digest db01-slow.log

#能分析的类别
1.慢日志(默认)
2.tcpdump
3.general log
4.binlog
5.show processlist

3.结果信息分析
3.1.第一部分：输出结果的总体信息
# 15.3s user time, 210ms system time, 142.12M rss, 340.63M vsz
说明：
执行过程中在用户中所花费的所有时间
执行过程中内核空间中所花费的所有时间
pt-query-digest进程所分配的内存大小
pt-query-digest进程所分配的虚拟内存大小

# Current date: Sun Oct 31 08:07:21 2021
说明：当前日期

# Hostname: xxx
说明：执行pt-query-digest的主机名

# Files: mysql-slow.log
说明：被分析的文件名

# Overall: 6.00k total, 556 unique, 0.11 QPS, 1.05x concurrency __________
说明：语句总数量，唯一语句数量，每秒查询量，查询的并发

# Time range: 2021-10-30T17:20:11 to 2021-10-31T07:50:33
说明：执行过程中日志记录的时间范围

# Attribute          total     min     max     avg     95%  stddev  median
说明：属性              总计     最小值  最大值    平均值   95%  标准差   中位数
# Exec time         54654s      1s    494s      9s     32s     23s      3s
说明：执行时间
# Query size 
说明 查询字符数
# Rows sent        182.14k       0     100    4.98    0.99   21.06       0
说明：发送到客户端的行数
# Rows examine     491.83k       0   1.02k   13.45   97.36   56.85       0
说明：扫描的语句行数
# Query size        19.82M       5 511.96k  554.80   72.65  16.25k    5.75 
说明：查询的字符数

3.2.输出队列组的统计信息
# Profile
# Rank Query ID           Response time   Calls R/Call   V/M   Item
# ==== ================== =============== ===== ======== ===== ===========
#    1 0x3C134E75C54D2D71 5603.2644 10.3%    19 294.9087  1.09 SELECT da_dmp_tidb.nr_sales_leads_detail_table saos_tur.dcc_follow_up mall_vehicle.vehicle_order
#    2 0x7A3E587FE5FFE5D0 5481.6923 10.0%   238  23.0323  0.31 SELECT saos_op.base_sys_day saos_tur.dcc_follow_up
说明:
Rank:所有语句排名,按照时间查询顺序
Query ID:查询语句ID信息
Response:响应时间
time:时间占比
Calls:执行次数
R/Call:平均响应时间
V/M：Variance-to-mean的比率
Item:SQL语句样本

3.3.输出每列查询的详细信息
# Query 1: 1.02k QPS, 10.94x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 26111916
说明：查询队列1：每秒查询量，查询的并发，队列1的ID值，26111916：表示文中偏移量（查看方法在下面‘偏1’中）

# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-02T11:21:59 to 2020-09-02T11:22:29
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         81   30520
# Exec time     90    328s     1ms   129ms    11ms    23ms     8ms     9ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0 178.83k       6       6       6       6       0       6
说明：查询的详细说明，在第一部分/第二部分有相关参数说明
# String:
# Databases    test
说明：使用的数据库名称
# Hosts        10.186.60.147
说明：使用的主机IP
# Users        root
说明：使用的用户名
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ###########################################################
# 100ms  #
#    1s
#  10s+
说明：查询时间分布
COMMIT\G

#应用场景
- 查询次数多且每次查询占用时间长的sql:通常为pt-query-digest分析的前几个查询，该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL
- IO大的sql:注意pt-query-digest分析中的Rows examine项，扫描的行数越大，IO越大
- 未命中的索引的SQL:pt-query-digest分析中的Rows examine 和 Rows Send的对比。说明该SQL的索引命中率不高，对于这种SQL，我们要重点进行关注。
```

### 3.2.2.参数解析与应用

```sql
1.参数解析
pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table   当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit    限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

2.用法实例

2.1.直接分析慢查询文件:
pt-query-digest    slowquery.log   >/tmp/slow.log

2.2.分析最近12小时内的查询：
pt-query-digest  --since=48h  slowquery.log > slow_report2.log

2.3.分析指定时间范围内的查询：
pt-query-digest  --since '2021-09-01 09:30:00' --until '2021-09-30 10:00:00' slowquery.log>  slow_report3.log

2.4.分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slowquery.log> slow_report4.log

2.5.针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^dbarepl/i' slowquery.log> slow_report5.log

2.6.查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slowquery.log> slow_report6.log

2.7.把查询保存到query_review表(常用)
pt-query-digest --user=qianlong –password=123456 --review  h=127.0.0.1,D=test,t=query_review --create-review-table  slowquery.log

mysql -uqianlong -p123456 -h127.0.0.1

2.8.把查询保存到query_history表(常用)
pt-query-digest  --user=root –password=abc123 --history  h=localhost,D=test,t=query_history --create-review-table  slow.log_0001

通过tcpdump抓取mysql的tcp协议数据，然后再分析（基本不用）
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

分析binlog（基本不用）
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog  mysql-bin000017.sql > slow_report10.log

分析general log（基本不用）
pt-query-digest  --type=genlog  localhost.log > slow_report11.log

#实际业务中slowlog的使用通常是
- 无CMDB平台
   1. 推荐ELK,能够提供实时日志搜索(可视化)
   2. 保存到库中使用query_history和query_review,写脚本推送给自己和业务。
- 有CMDB平台
   1.pt工具创建review和history两张表
   2.按照10.小时,天创建趋势图
   3.联动前后端查询指定时间慢SQL

select user,client,db,ts_min,ts_max,Query_time_median,Lock_time_max from mysql_slow_query_review_history  where ts_min>'2021-10-29 03:55:07' and ts_min<='2021-10-29 10:55:07'  ;
```

```sql
#基本SQL信息
select * from mysql_slow_query_review limit 1\G
*************************** 1. row ***************************
       checksum: 533FEA8B9A553E2A76C1B71D646E99C0
    fingerprint: select count(*) from (select `end_lat`, `end_lon`, `num` from (select round(end_lat, ?) end_lat, round(end_lon, ?) end_lon, count(?) num from (select a.end_lat as end_lat, a.end_lon as end_lon from (select end_lat, end_lon, day_type, end_city_code, dt from saos_bs.user_driving_location where end_city_code = ? and ord(mid((select ifnull(cast(schema_name as nchar), ?) from (select distinct (schema_name) from information_schema.schemata limit ?) as owse), ?, ?)) > ? and ? = ?) a right join (select max(dt) as dt from saos_bs.user_driving_location where case when ? not in(?+) then ? else ? end = day_type) b on a.dt = b.dt union all select c.end_lat as end_lat, c.end_lon as end_lon from (select end_lat, end_lon, day_type, end_city_code, dt from saos_bs.user_driving_location where end_city_code = ? and ord(mid((select ifnull(cast(schema_name as nchar), ?) from (select distinct (schema_name) from information_schema.schemata limit ?, ?) as owse), ?, ?)) > ? and ? = ?) c right join (select max(dt) as dt from saos_bs.user_driving_location where case when ? not in(?+) then ? else ? end = day_type) d on c.dt = d.dt) a group by ?, ?) t) ct
         sample: SELECT COUNT(*) FROM (SELECT `end_lat`, `end_lon`, `num` FROM (SELECT round(end_lat, 3) end_lat, round(end_lon, 3) end_lon, count(1) num FROM (SELECT a.end_lat AS end_lat, a.end_lon AS end_lon FROM (SELECT end_lat, end_lon, day_type, end_city_code, dt FROM saos_bs.user_driving_location WHERE end_city_code = '110100' AND ORD(MID((SELECT IFNULL(CAST(schema_name AS NCHAR), 0x20) FROM (SELECT DISTINCT (schema_name) FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 1, 1) AS owse), 1, 1)) > 64 AND 'WXpV' = 'WXpV') a RIGHT JOIN (SELECT max(dt) AS dt FROM saos_bs.user_driving_location WHERE CASE WHEN 2 NOT IN (1, 2) THEN 2 ELSE 2 END = day_type) b ON a.dt = b.dt UNION ALL SELECT c.end_lat AS end_lat, c.end_lon AS end_lon FROM (SELECT end_lat, end_lon, day_type, end_city_code, dt FROM saos_bs.user_driving_location WHERE end_city_code = '110100' AND ORD(MID((SELECT IFNULL(CAST(schema_name AS NCHAR), 0x20) FROM (SELECT DISTINCT (schema_name) FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 1, 1) AS owse), 1, 1)) > 64 AND 'WXpV' = 'WXpV') c RIGHT JOIN (SELECT max(dt) AS dt FROM saos_bs.user_driving_location WHERE CASE WHEN 2 NOT IN (1, 2) THEN 1 ELSE 2 END = day_type) d ON c.dt = d.dt) a GROUP BY 1, 2) T) CT
     first_seen: 2021-09-06 11:04:55.000000
      last_seen: 2021-09-06 11:07:32.000000
    reviewed_by: NULL
    reviewed_on: NULL
       comments: NULL
reviewed_status: NULL

#详细SQL信息
select * from mysql_slow_query_review_history limit 1\G
*************************** 1. row ***************************
                          id: 140598105
                    type_max: mysql
                     env_max: prod
                hostname_max: devops:3306
                  client_max: 172.21.244.154
                    user_max: availability_rw
                      db_max: op_availability
                    checksum: 4EE68DE204007D3D07644F44CEFDB71B
                      sample: select sum(success)/(sum(success)+ sum(error)) * 100.000 as percent
        from (select success,error from gateway_log_min_number
        where
        app='api-gateway-app'
        and time >= 1609430400
         order by id desc limit 525599) as t
                      ts_min: 2021-07-29 03:55:07.000000
                      ts_max: 2021-07-29 04:05:07.000000
                      ts_cnt: 31
              Query_time_sum: 37.035316
              Query_time_min: 1.01506
              Query_time_max: 3.113844
           Query_time_pct_95: 1.2059487
           Query_time_stddev: 0.3565949
           Query_time_median: 1.0938311
               Lock_time_sum: 0.003196
               Lock_time_min: 0.000074
               Lock_time_max: 0.000155
            Lock_time_pct_95: 0.0001252393
            Lock_time_stddev: 0.000017858318
            Lock_time_median: 0.000098128265
               Rows_sent_sum: 31
               Rows_sent_min: 1
               Rows_sent_max: 1
            Rows_sent_pct_95: 1
            Rows_sent_stddev: 0
            Rows_sent_median: 1
           Rows_examined_sum: 27963112
           Rows_examined_min: 901977
           Rows_examined_max: 902076
        Rows_examined_pct_95: 892821.56
        Rows_examined_stddev: 0
        Rows_examined_median: 892821.56
           Rows_affected_sum: NULL
           Rows_affected_min: NULL
           Rows_affected_max: NULL
        Rows_affected_pct_95: NULL
        Rows_affected_stddev: NULL
        Rows_affected_median: NULL
               Rows_read_sum: NULL
               Rows_read_min: NULL
               Rows_read_max: NULL
            Rows_read_pct_95: NULL
            Rows_read_stddev: NULL
            Rows_read_median: NULL
            Merge_passes_sum: NULL
            Merge_passes_min: NULL
            Merge_passes_max: NULL
         Merge_passes_pct_95: NULL
         Merge_passes_stddev: NULL
         Merge_passes_median: NULL
         InnoDB_IO_r_ops_min: NULL
         InnoDB_IO_r_ops_max: NULL
      InnoDB_IO_r_ops_pct_95: NULL
      InnoDB_IO_r_ops_stddev: NULL
      InnoDB_IO_r_ops_median: NULL
       InnoDB_IO_r_bytes_min: NULL
       InnoDB_IO_r_bytes_max: NULL
    InnoDB_IO_r_bytes_pct_95: NULL
    InnoDB_IO_r_bytes_stddev: NULL
    InnoDB_IO_r_bytes_median: NULL
        InnoDB_IO_r_wait_min: NULL
        InnoDB_IO_r_wait_max: NULL
     InnoDB_IO_r_wait_pct_95: NULL
     InnoDB_IO_r_wait_stddev: NULL
     InnoDB_IO_r_wait_median: NULL
    InnoDB_rec_lock_wait_min: NULL
    InnoDB_rec_lock_wait_max: NULL
 InnoDB_rec_lock_wait_pct_95: NULL
 InnoDB_rec_lock_wait_stddev: NULL
 InnoDB_rec_lock_wait_median: NULL
       InnoDB_queue_wait_min: NULL
       InnoDB_queue_wait_max: NULL
    InnoDB_queue_wait_pct_95: NULL
    InnoDB_queue_wait_stddev: NULL
    InnoDB_queue_wait_median: NULL
   InnoDB_pages_distinct_min: NULL
   InnoDB_pages_distinct_max: NULL
InnoDB_pages_distinct_pct_95: NULL
InnoDB_pages_distinct_stddev: NULL
InnoDB_pages_distinct_median: NULL
                  QC_Hit_cnt: NULL
                  QC_Hit_sum: NULL
               Full_scan_cnt: NULL
               Full_scan_sum: NULL
               Full_join_cnt: NULL
               Full_join_sum: NULL
               Tmp_table_cnt: NULL
               Tmp_table_sum: NULL
       Tmp_table_on_disk_cnt: NULL
       Tmp_table_on_disk_sum: NULL
                Filesort_cnt: NULL
                Filesort_sum: NULL
        Filesort_on_disk_cnt: NULL
        Filesort_on_disk_sum: NULL
                   Bytes_sum: 7794
                   Bytes_min: 250
                   Bytes_max: 254
                Bytes_pct_95: 246.01547
                Bytes_stddev: 0
                Bytes_median: 246.01547
```

# 4.error log
MySQL的error log不仅包含错误信息，还包含启动和关闭的一些记录。
排查一些问题，第一时间应该看error log

```sql
log_error = /data/mysql3306/log/mysql.err

借助error log 定位问题
```
**开启general log 一定要关闭**

# 5.Redo log
MySQL 采用的是WAL技术，先写日志在写磁盘。如果有修改操作，则先操作记录在redo log
buffer中，然后将redo log buffer中的数据刷新到磁盘日志文件中，最后写入数据文件中。

# 6.undo log

前面讲了redo log记录了事务操作的变化，但是事务有时可能会回滚，此时undo就发挥作用。

redo和undo 不做过多介绍，上节课同学们已经学过了

**对redo undo做个总结:**

binlog 和redo 区别
- redo log是innodb层产生的，binlog不单单记录innodb的修改也记录其他任何存储引擎的修改。
- redo log 是物理逻辑格式日志，记录的是每页的修改，binlog是一种逻辑日志，记录的是对应的变更sql
- 在事务进行中会不断地写入redo log，而binlog只在事务提交时写入一次。

undo和 redo 的区别
- 从作用方面来说，redo log 用来保证事务的持久性，undo log用来帮助事务回滚以及MVCC实现。
- 从写入顺序来说，redo log是顺序写的，undo log是随机读、写

# 7.relay log

从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致

重要参数
relay_log_purge
是否自动清空不再需要中继日志时。默认值为1(启用)。
relay_log_recovery
当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

![image-20240328161808355](https://img.sunrisenan.com/img/2024/03/28/161810467.png)