MySQL日志管理（十一）

熬夜泡枸杞

已于 2023-02-19 11:51:42 修改

阅读量552

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-12-07 12:03:17 首次发布

本文链接：https://blog.csdn.net/weixin_46818279/article/details/128217276

版权

MySQL 专栏收录该内容

16 篇文章 5 订阅

订阅专栏

1. binlog文件内容查看及数据恢复

1.1 binlog事件查看

# (1) 查看最新mysql正在使用最新的日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      885 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# (2) 查看最新日志内容
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                    |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.30-log, Binlog ver: 4   |
| mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                         |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000001 | 219 | Query          |         6 |         338 | create database dongkun charset utf8mb4 |
| mysql-bin.000001 | 338 | Anonymous_Gtid |         6 |         403 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000001 | 403 | Query          |         6 |         476 | BEGIN                                   |
| mysql-bin.000001 | 476 | Table_map      |         6 |         534 | table_id: 108 (world.city)              |
| mysql-bin.000001 | 534 | Delete_rows    |         6 |         854 | table_id: 108 flags: STMT_END_F         |
| mysql-bin.000001 | 854 | Xid            |         6 |         885 | COMMIT /* xid=40 */                     |
+------------------+-----+----------------+-----------+-------------+----------------------------------------

(3) 查看DDL语句，用的频率比较高
[root@db01 ~]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000001'" | grep DROP

1.2 binlog内容查看

# (1) binlog日志不能使用vim进行查看，mysqlbinlog专门用来查看binlog日志文件内容，重定向普通文件后才可以查看
[root@db01 binlog]# mysqlbinlog /data/binlog/mysql-bin.000001 > /tmp/a.sql

# (2) 查看重定向后的binlog DDL日志内容，DDL直接可以看到sql语句  
## at是每个事件的开头位置。下一个at的开始，就是上一个at的结束点。at下面的set命令不用关注，重点看的是sql语句
[root@db01 binlog]# vim /tmp/a.sql       
# at 219
#221205 23:11:45 server id 6  end_log_pos 338 
create database dongkun charset utf8mb4
/*!*/;
# at 338

# (3) 查看重定向后的binlog DML日志内容，begin开始的一个事务
[root@db01 binlog]# vim /tmp/a.sql     
# at 403
#221205 23:19:58 server id 6  end_log_pos 476 
BEGIN
# at 476
#221205 23:19:58 server id 6  end_log_pos 534 
# at 534
#221205 23:19:58 server id 6  end_log_pos 854 CRC32 0xff40056c  
BINLOG '
HgyOYxMGAAAAOgAAABYCAAAAAGwAAAAAAAEABXdvcmxkAARjaXR5AAUD/v7+Awb+I/4D/hQAw3TN
lA==
HgyOYyAGAAAAQAEAAFYDAAAAAGwAAAAAAAEAAgAF/+ABAAAABUthYnVsA0FGRwVLYWJvbCApGwDg
AgAAAAhRYW5kYWhhcgNBRkcIUWFuZGFoYXK8nwMA4AMAAAAFSGVyYXQDQUZHBUhlcmF0sNkCAOAE
AAAADk1hemFyLWUtU2hhcmlmA0FGRwVCYWxraDjzAQDgBQAAAAlBbXN0ZXJkYW0DTkxEDU5vb3Jk
LUhvbGxhbmRAKAsA4AYAAAAJUm90dGVyZGFtA05MRAxadWlkLUhvbGxhbmSpDQkA4AcAAAAESGFh
ZwNOTEQMWnVpZC1Ib2xsYW5kRLoGAOAIAAAAB1V0cmVjaHQDTkxEB1V0cmVjaHRTkwMA4AkAAAAJ
RWluZGhvdmVuA05MRA1Ob29yZC1CcmFiYW50cxQDAGwFQP8=
'/*!*/;
# at 854
#221205 23:20:15 server id 6  end_log_pos 885 
COMMIT/*!*/;

# (4) 因为binlog使用的是row模式，DML人类不可读，可以使用--base64-output=decode-rows -vvv可以看个大致内容
## mysqlbinlog --base64-output=decode-rows -vvv 这条命令仅用于查看，如果要截取日志的话不能加这个--base64-output=decode-rows -vvv参数
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001 > /tmp/b.sql
[root@db01 binlog]# vim /tmp/b.sql
# at 534
#221205 23:19:58 server id 6  end_log_pos 854 CRC32 0xff40056c  Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `world`.`city`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Kabul' /* STRING(35) meta=65059 nullable=0 is_null=0 */
###   @3='AFG' /* STRING(3) meta=65027 nullable=0 is_null=0 */
###   @4='Kabol' /* STRING(20) meta=65044 nullable=0 is_null=0 */
###   @5=1780000 /* INT meta=0 nullable=0 is_null=0 */
### DELETE FROM `world`.`city`

# (5) 可以看mysqlbinlog命令的使用帮助，命令参数记不住可以使用--help来查询
[root@db01 binlog]# mysqlbinlog --help
  --base64-output=name 
          Determine when the output statements should be
          base64-encoded BINLOG statements: 'never' disables it and
          works only for binlogs without row-based events;
          'decode-rows' decodes row events into commented
          pseudo-SQL statements if the --verbose option is also
          given; 'auto' prints base64 only when necessary (i.e.,
          for row-based events and format description events).  If
          no --base64-output[=name] option is given at all, the
          default is 'auto'.

1.3 binlog日志截取恢复

不管是事件查看还是内容查看都是为了后面做铺垫的，大部分事件会用来数据库故障恢复数据，想要恢复一个数据的时候，可以通过日志的截取工作，然后在恢复这个日志，然后达到数据恢复的目的
看binlog日志，然后在截取位置点，去恢复数据。

日志恢复案例：

# 显示全部的二进制日志文件
show binary logs;
# (1) 滚动一个新的日志 (会生成一个新的日志文件，内容会在这个新的日志中写入，老的日志文件不会在写入内容)
flush logs;
# (2) 模拟数据环境
mysql> create database bindb charset utf8mb4;
mysql> use bindb;
mysql> create table t1 (id int);
mysql> begin;
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> begin;
mysql> insert into t1 values(11),(22),(33);
mysql> commit
mysql> begin;
mysql> insert into t1 values(111),(222),(333);
mysql> commit
mysql> drop database bindb;
# (3) 数据恢复
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      497 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql> show binlog events in 'mysql-bin.000002';
 mysql-bin.000002 |  219 |   332 | create database bindb charset utf8mb4
 mysql-bin.000002  | 1357  |   1452 | drop database bindb

# 截取日志: 
[root@db01 binlog]# mysqlbinlog --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000002 > /tmp/bin.sql

# 恢复日志: 
## 在此会话里面临时关闭binlog，临时不记录binlog，不会影响到别人。
mysql> set sql_log_bin=0;
## binlog日志进行恢复
mysql> source /tmp/bin.sql;
## 恢复完成后，打开binlog
mysql> set sql_log_bin=1;

# 验证数据:
mysql> select * from bindb.t1;

思考问题：如果是生产环境中，此种恢复手段会有什么弊端

（1） binlog记录不单单是一个数据库的操作，可能对其他数据库也有操作，在binlog日志中如何去精确的截取对某一个库的操作？

# -d bindb 只截取跟这个bindb库相关的日志, 从mysql-bin.000002文件截取出只对bindb数据库的操作
[root@db01 binlog]# mysqlbinlog -d bindb --start-position=219 --stop-positon=1357 /data/binlog/mysql-bin.000002;

（2）需要的日志在多个文件中分布

# 起点: 假如 mysql-bin.000001  起始号：4600
# 终点: 一般是最后一个文件，假设mysql-bin.000002 终点号：980
# 小提示: position号，单文件是连续的，多文件是不连续的

# (1) 通过时间点来确认。跨文件的截取内容，恢复的话，知道起始位置号后，找出时间点，然后就行截取
mysqlbinlog --start-datetime= --stop-datetime=  mysql-bin.000001  mysql-bin.000002

# (2) 通过时间和位置点来确认。 因为每秒钟执行了多个sql，时间有可能不太准确，参数可以是一个position,一个为时间
mysqlbinlog --start-position=    --stop-datatime=   mysql-bin.000001  mysql-bin.000002

# (3) 如果时间都不准确，用笨办法，把文件分开弄

（3）创建了几年，期间一直在用的数据库，插入数据的操作从bin_log.000001到bin_log.000123之中都有的库，被删了，如何恢复？
假设：每周六做全备份23：00，binlog每天备份23：00
故障点：周三 10点 drop操作。

全备+binlog 进行恢复
binlog实际上是我们数据恢复时配合备份一起恢复数据的手段。

2. binlog维护操作

2.1 binlog日志滚动

# (1) 滚动一个新的日志
mysql> flush logs;
# (2) 滚动一个新的日志
[root@db01 binlog]# mysqladmin -uroot -p123456 flush-logs
# (3) 一个binlog日志文件大小默认结果是一个T，如果日志文件达到一个T，自动滚动一次，可以设置小一些（建议设置一个binlog日志大小为100~200M）
mysql> select @@max_binlog_size; 
# (4) 自动滚动日志，可用可不用 
mysqldump -F
# (5) 重启数据库自动滚动

2.2 binlog日志删除

注意：不要用rm去删除binlog日志文件，会造成不好的影响，比如主从会受到影响。

2.2.1 自动删除机制

# (1) 默认是0，代表永不删除。单位是天
mysql> select @@expire_logs_days;
# (2) 设置多少天何时？阈值？
一次全备周期。 7+1天，生产一般建议最少2个全备周期+1

2.2.2 手工删除

# 如果忘记了命令，可以使用help帮助
mysql> help purge；
mysql> help purge binary logs;
# (1) 以区间进行删除。 例如下面的栗子会删除mysql-bin.000001~mysql-bin.000005的文件
PURGE BINARY LOGS TO 'mysql-bin.000006';
# (2) 以日期进行删除。 删除到2019-04-02 22:46:26日期之前的日志
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'

2.2.3 全部清空

# 危险命令，在主库执行此操作，主从必宕机
mysql> reset master

3. binlog的GTID模式管理

3.1 GTID介绍

5.6 版本新加的特性，5.7，8.0中做了加强
5.6 中不开启，没有这个功能

# 5.7中GTID，即使不开也会有自动生成,匿名的GTID，内置的，类似于聚簇索引，隐藏存在的，是由自己维护的
SET @@SESSION.GTID_NEXT='ANONYMOUS'

小提示： GTID主要对主从复制来说的，对单机来说给的功能还是比较少的

3.2 GTID(Global Transaction ID)

是对于一个已提交事务的标号，并且是一个全局唯一的编号。
他的官方定义如下：

GTID = server_uuid : transaction_id
638fec45-e734-11ec-bcd2-000c29d09be0:1

小提示：
（1）GTID的事务ID和innodb的事务ID还是不一样的
，innodb的事务ID主要是针对DML语句的，GTID的事务ID主要是binlog中的事件来命名的，像DDL、DCL 也算一个事务id
（2）对于DML传统事务来讲，一个事务一个GTID的号码

3.3 GTID重要参数介绍

# (1) 开启GITD
[root@db01 ~]# vim /etc/my.cnf
gtid-mode=on   # 打开gtid的功能
enforce-gtid-consistency=true # 强制GTID的一致性，在开启gtid之前或者启动gtid之前，启动数据库之前，都要校验所有GTID的一致性问题，GTID主要保证主从之间的一致性的，而不是单机的，单机的比较弱

# (2) 重启mysql生效
[root@db01 ~]# /etc/init.d/mysqld restart

# (3) 查看GTID的开启状态，ON是开启
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+
1 row in set (0.00 sec)

# (4) mysql实例的唯一标识uuid，638fec45-e734-11ec-bcd2-000c29d09be0:1
mysql>  select @@server_uuid; 
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 638fec45-e734-11ec-bcd2-000c29d09be0 |
+--------------------------------------+
1 row in set (0.00 sec)

3.4 GTID操作实战演练

# (1) Executed_Gtid_Set 被执行过的GTID的集合
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# (2) 开启gitd之后，创建一个数据库
mysql> create database gtid_text;
Query OK, 1 row affected (0.00 sec)

# (3) Executed_Gtid_Set生成gtid的信息
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 |      328 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)


# (4) 在没开gtid之前， SET @@SESSION.GTID_NEXT= 'ANONYMOUS'不用管，这个匿名的GITD是由系统自己管理的，个人是使用不了的。
# 一旦开启了GTID，在每一个事件之前都会有一个GTID的号码，有了它之后不需要使用position号，直接用GTID就可以管理二进制日志，截取日志恢复。
mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                             |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000003 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' |
| mysql-bin.000003 | 219 | Query          |         6 |         328 | create database gtid_text                                         |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+


# (5) 继续创建数据库，每个事件都会有一个GTID号
# SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1'，  638fec45-e734-11ec-bcd2-000c29d09be0,每一个数据库实例初始化完成之后，自动生成的一个uuid，能唯一代表一个实例。
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 638fec45-e734-11ec-bcd2-000c29d09be0 |
+--------------------------------------+

mysql> create database gtid_text1;
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                             |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000003 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' |
| mysql-bin.000003 | 219 | Query          |         6 |         328 | create database gtid_text                                         |
| mysql-bin.000003 | 328 | Gtid           |         6 |         393 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:2' |
| mysql-bin.000003 | 393 | Query          |         6 |         505 | create database gtid_text1                                        |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> create database gtid_text2;
Query OK, 1 row affected (0.03 sec)

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                             |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000003 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' |
| mysql-bin.000003 | 219 | Query          |         6 |         328 | create database gtid_text                                         |
| mysql-bin.000003 | 328 | Gtid           |         6 |         393 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:2' |
| mysql-bin.000003 | 393 | Query          |         6 |         505 | create database gtid_text1                                        |
| mysql-bin.000003 | 505 | Gtid           |         6 |         570 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:3' |
| mysql-bin.000003 | 570 | Query          |         6 |         682 | create database gtid_text2                                        |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
8 rows in set (0.00 sec)


# (6) 对DDL(创建数据库)来讲一个事件就是一个GTID。 对于DML来说，一个事务就是一个GTID，从begin到commit算一个GTID信息，SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:4'，第四号GTID信息
mysql> use world;
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from city where id=4078;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                             |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000003 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' |
| mysql-bin.000003 | 219 | Query          |         6 |         328 | create database gtid_text                                         |
| mysql-bin.000003 | 328 | Gtid           |         6 |         393 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:2' |
| mysql-bin.000003 | 393 | Query          |         6 |         505 | create database gtid_text1                                        |
| mysql-bin.000003 | 505 | Gtid           |         6 |         570 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:3' |
| mysql-bin.000003 | 570 | Query          |         6 |         682 | create database gtid_text2                                        |
| mysql-bin.000003 | 682 | Gtid           |         6 |         747 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:4' |
| mysql-bin.000003 | 747 | Query          |         6 |         820 | BEGIN                                                             |
| mysql-bin.000003 | 820 | Table_map      |         6 |         878 | table_id: 108 (world.city)                                        |
| mysql-bin.000003 | 878 | Delete_rows    |         6 |         940 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000003 | 940 | Xid            |         6 |         971 | COMMIT /* xid=41 */                                               |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)

3.5 基于GTID进行查看binlog

具备GTID后，截取查看某些事务日志：

# 包含要执行的gtid
--include-gtids

# 排除不执行的gtid
--exclude-gtids

# 如果要用gtid恢复数据，mysqlbinlog截取日志的时候必须要加此参数。具体原因看GTID的幂等性。
--skip-gtids

GTID的应用：

GTID号是递增的，在多binlog日志文件之间也是递增的，直接根据gtid号进行数据恢复。因为还没有主从复制，单机带来的便利之处就是方便了数据恢复（这不同于position号，在单个binlog日志文件是递增的，恢复数据的时候不好确定position号和时间区间，所以有gtid就不用管position号了）。

3.6 GTID数据恢复实战演练

用gtid来进行数据恢复，多个binlog日志文件进行恢复。跨多文件获取我们想要的日志。

3.6.1 录入测试数据

# (1) 第一波命令
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      971 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql> create database gtdb charset utf8mb4;
mysql> use gtdb;
mysql> create table t1(id int);
mysql> begin;
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |     1571 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)


# (2) 第二波命令
## flush logs新起一个binlog日志，后面的日志内容都打到这个新的binlog日志里面
mysql> flush logs;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      194 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql> create table t2(id int);
mysql> begin;
mysql> insert into t2 values(1),(2),(3);
mysql> commit;


# (3) 第三波命令
mysql> flush logs;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      194 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql> create table t3(id int);
mysql> begin;
mysql> insert into t3 values(1),(2),(3);
mysql> commit;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000005 |      619 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-11 |
+------------------+----------+--------------+------------------+-------------------------------------------+
mysql> drop database gtdb;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000005 |      776 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-12 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

3.6.2 恢复测试数据

截取日志

# 起点（确定起点，看binlog日志比较麻烦，要结合备份）
mysql> show binlog events in 'mysql-bin.000003';
SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:5'
create database gtdb charset utf8mb4    

# 终点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000005 |      776 |              |                  | 638fec45-e734-11ec-bcd2-000c29d09be0:1-12 |
+------------------+----------+--------------+------------------+------------------------------------------
mysql> show binlog events in 'mysql-bin.000005';
SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:12' |
drop database gtdb

起点的GITD是5，终点的GTID是12，又因为12删库操作，那么要恢复的终点是11。
所以GTID的范围是：5-11
涉及到的binlog日志文件：mysql-bin.000003 mysql-bin.000004 mysql-bin.000005

开始恢复：(错误演示)

[root@db01 ~]# cd /data/binlog/
# 截取日志(下面为了演示报错，没有加--skip-gtids。实际上必须加上--skip-gtids)
[root@db01 binlog]# mysqlbinlog --include-gtids='638fec45-e734-11ec-bcd2-000c29d09be0:5-11' mysql-bin.000003  mysql-bin.000004  mysql-bin.000005 > /tmp/gtid.sql
[root@db01 binlog]# mysql -uroot -p123456
# 数据恢复失败，因为需要在截取日志的时候加上--skip-gtids。具体原因看GTID的幂等性
mysql> source /tmp/gtid.sql;

3.6.3 GTID幂等性

开启GTID后，MySQL恢复Binlog时，重复GTID的事务不会在执行了，如果先要执行需要加--skip-gtids。

理解什么是幂等性：同一个操作在幂等性的机制下，是不能重复运行的。比如ansible的剧本，有十个步骤，有可能运行到第五步出现问题了，修复好了在跑一次这个剧本，1-4步运行过的就不会在运行了，这就是幂等性的原则。

GITD幂等性：在这个数据库里面有执行过gtid的事件，在去运行同一个gtid的操作的时候会直接跳过5-11的这个几个gtid的事务，在这个数据库里面已经执行过了。show master status;已经存在5-12，包含5-11了，有一个自动检测的功能，如果已经重复了，就不再操作了。所以加上–skip-gtids, 用mysqlbinlog导出的sql去掉了gtid的信息（去掉了SET *关于GTID的信息），防止幂等性的产生。vimdiff /tmp/gtid.sql(加–skip-gtids) /tmp/gtid1.sql(没有加) 对比两个文件的不同。

开始恢复数据： (正确演示)

# (1) 截取日志(下面为了演示报错，没有加--skip-gtids。实际上必须加上--skip-gtids)
[root@db01 ~]# cd /data/binlog/
# 如果指定截取具体库的操作加参数 [-d gtdb]
[root@db01 binlog]# mysqlbinlog --skip-gtids --include-gtids='638fec45-e734-11ec-bcd2-000c29d09be0:5-11' mysql-bin.000003  mysql-bin.000004  mysql-bin.000005 > /tmp/gtid.sql

# (2) 执行导出的日志
[root@db01 binlog]# mysql -uroot -p123456
#暂时设置为0，恢复数据时不写入binlog，恢复完之后在设置成1
mysql> set sql_log_bin=0;
# 执行成功
mysql> source /tmp/gtid.sql;
mysql> set sql_log_bin=1;

# (3) 开始验证数据，恢复成功
mysql> use gtdb;
Database changed
mysql> show tables;
+----------------+
| Tables_in_gtdb |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.37 sec)

小提示：

(1) 如果使用mysqlbinlog截取日志具体库的操作需要加参数 [-d 数据库名称]

(2) 查看gtid的server_uuid的两种方式

mysql> select @@server_uuid
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 638fec45-e734-11ec-bcd2-000c29d09be0 |
+--------------------------------------+

[root@db01 ~]# cat /data/3306/auto.cnf
[auto]
server-uuid=638fec45-e734-11ec-bcd2-000c29d09be0

4. slowlog慢日志

4.1 slowlog作用

记录MySQL运行过程中较慢的语句，通过一个文本的文件记录下来。帮助我们进行语句优化的工具日志。

4.2 slowlog配置

默认慢日志没有开启。

日志参数查看：

# (1) 慢日志是否开启
mysql> select @@slow_query_log; 
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+

# (2) 慢日志文件存放位置
mysql> select @@slow_query_log_file;  
+--------------------------+
| @@slow_query_log_file    |
+--------------------------+
| /data/3306/db01-slow.log |
+--------------------------+

# (3) 慢语句认定时间阈值(默认10s)
mysql> select @@long_query_time; 
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+

# (4) 不走索引的语句记录
mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+

进行参数永久配置：

# (1) 进行配置 
[root@db01 ~]# vim /etc/my.cnf
slow_query_log=1
# 日志和数据的存放路径一般分开的
slow_query_log_file=/data/3306/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1

# (2) 重启mysql生效
[root@db01 ~]# /etc/init.d/mysqld restart

小提示：
（1）优化是循序渐进的，慢慢的优化
（2）如果超过0.1s或者没有索引的sql，都会记录到慢日志当中，这个两个满足一个条件即可

4.3 模拟慢语句

测试数据：t100w表

use test;

select * from t100w limit 500000,10;
select * from t100w limit 600000,10;
select * from t100w limit 600000,1;
select * from t100w limit 600000,2;

select id,count(num) from t100w group by id limit 10;
select id,count(num) from t100w group by id limit 5;
select id,count(num) from t100w group by id limit 2;
select id,count(num) from t100w group by id limit 2;
select id,count(k1) from t100w group by id limit 1;
select id,count(k2) from t100w group by id limit 1;

select k2,sum(id) from t100w group by k2 limit 1;
select k2,sum(id) from t100w group by k2,k1 limit 1;
select k2,sum(id) from t100w group by k2,k1 limit 1;
select k1,sum(id) from t100w group by k2,k1 limit 1;
select k1,count(id) from t100w group by k1 limit 0;

4.4 慢语句分析

两个问题：
（1）有语句是类似的，能不能把他们聚在一堆？mysqldumpslow命令
（2）需要有个轻重缓急，应该先去处理最影响我们性能的这些语句，如何高效率的去分析
次数和执行时间哪个最影响用户体验的语句？次数比较影响，先优化次数比较多的sql语句，然后把次数多的语句拿出来，看一下执行计划，看一下是索引问题还是语句本身问题

# -s 排序  c 计数  -t 显示前五条，最应该优化的语句
[root@db01 ~]# mysqldumpslow -s c -t 5 /data/3306/db01-slow.log

Reading mysql slow query log from /data/3306/db01-slow.log
Count: 4  Time=0.45s (1s)  Lock=0.00s (0s)  Rows=5.8 (23), root[root]@localhost
  select * from t100w limit N,N

Count: 4  Time=6.89s (27s)  Lock=0.00s (0s)  Rows=4.8 (19), root[root]@localhost
  select id,count(num) from t100w group by id limit N

Count: 2  Time=3.28s (6s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select k2,sum(id) from t100w group by k2,k1 limit N

Count: 1  Time=6.02s (6s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select id,count(k2) from t100w group by id limit N

Count: 1  Time=3.25s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select k1,sum(id) from t100w group by k2,k1 limit N

4.5 慢日志工具扩展

pt-query-digest(企业级分析慢语句的工具) + Amemometer(可视化的展示平台)

目前MySQL配置文件内容：

[root@db01 ~]# cat /etc/my.cnf
[mysqld]
# 慢日志配置
slow_query_log=1
slow_query_log_file=/data/3306/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1
# gtid配置
gtid-mode=on
enforce-gtid-consistency=true
# binlog日志配置
server_id=6 
log_bin=/data/binlog/mysql-bin
sync_binlog=1 
binlog_format=row 
# 表明这个目录是安全的
secure-file-priv=/tmp
# 错误日志路径
log_error=/tmp/mysql.log

user=mysql 
basedir=/app/database/mysql 
datadir=/data/3306/ 
port=3306 
socket=/tmp/mysql.sock 
innodb_data_file_path=ibdata1:12M;ibdata2:128M;ibdata3:128M:autoextend
autocommit=0


[mysql]
socket=/tmp/mysql.sock

5. 日志管理小总结

二进制日志和redo（数据持久化，原子性）非常重要的功能日志

5.1 错误日志

定位MySQL工作过程中的故障

log_error=/data/log/mysql.log
[error] 上下文

5.2 二进制日志

作用：数据恢复、主从复制
配置：

server_id
log_bin
binlog_format: SBR RBR  MRR(不常用)
expire_logs_days
# sync_binlog是为了我们的数据最终一致性，如果数据损坏(物理损坏/逻辑损坏)，依然能保证数据的一致,sync_binlog=1会更加安全
sync_binlog=0/1

双一说明：(5.7版本中默认是双一标准)

innodb_flush_log_at_trx_commit=1  每次事务提交，必然log buffer中redo落到磁盘
sync_binlog=1 每次事务提交，必然保证binlog cache中的日志落到磁盘

涉及的重点命令：

binlog基本操作（查看事件的目的就是日志截取）

show master status;
show binlog events in 'mysql-bin.000001' limit 100;
mysql -e "show binlog events in 'mysql-bin.000001'" grep xxxx

无GTID：

mysqlbinlog --start-position --stop-position mysql-bin.000002 > /tmp/binlog.sql

mysqlbinlog -d [数据库] --start-position --stop-position mysql-bin.000002 > /tmp/binlog.sql

# --start-datetime是以秒为单位，一秒钟可能有多个sql操作，所以截取日志不是很准确
mysqlbinlog --start-datetime --stop-datetime mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql

有GTID：

mysqlbinlog --skip-gtids --include-gtids='xxxxx:1-14' mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql

# gtid=6的sql不截取
mysqlbinlog  --skip-gtids --include-gtids='xxxxx:1-5','xxxxx:7-10' mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql

# gtid=6的sql不截取
mysqlbinlog  --skip-gtids --include-gtids='xxxxx:1-14' --exclude-gtids='xxxxx:6' mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql

恢复日志：

# 临时关闭二进制日志
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=0

滚动日志：

# 滚动日志。重启也会滚动一个新日志。达到一个二进制日志存储最大值也会滚动（可以设置）
flush logs;

删除日志：

purge binary logs to ....
# reset master全部清空，重新记录，谨慎操作，如果有主从使用这个命令必崩
reset master;

小提示：
参数log_bin和sql_log_bin的区别： https://www.cnblogs.com/sdadx/p/7685351.html
sql_log_bin可以动态关闭二进制日志

5.2 慢日志

配置：

slow_query_log
slow_query_log_file
long_query_time
log_queries_not_using_indexes

分析慢日志：

mysqldumpslow -s  c -t xxxx

额外扩展：

pt-query-digest