文章目录
- 1. binlog文件内容查看及数据恢复
- 1.1 binlog事件查看
- 1.2 binlog内容查看
- 1.3 binlog日志截取恢复
- 2. binlog维护操作
- 2.1 binlog日志滚动
- 2.2 binlog日志删除
- 2.2.1 自动删除机制
- 2.2.2 手工删除
- 2.2.3 全部清空
- 3. binlog的GTID模式管理
- 3.1 GTID介绍
- 3.2 GTID(Global Transaction ID)
- 3.3 GTID重要参数介绍
- 3.4 GTID操作实战演练
- 3.5 基于GTID进行查看binlog
- 3.6 GTID数据恢复实战演练
- 3.6.1 录入测试数据
- 3.6.2 恢复测试数据
- 3.6.3 GTID幂等性
- 4. slowlog慢日志
- 4.1 slowlog作用
- 4.2 slowlog配置
- 4.3 模拟慢语句
- 4.4 慢语句分析
- 4.5 慢日志工具扩展
- 5. 日志管理小总结
- 5.1 错误日志
- 5.2 二进制日志
- 5.2 慢日志
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 DROP1.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个全备周期+12.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 master3. 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-gtidsGTID的应用：
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-000c29d09be04. 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 N4.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.sock5. 日志管理小总结
二进制日志和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的区别：
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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
