本文详细介绍了MySQL的日志系统，包括errorlog的作用、配置和查看，binlog的记录、恢复与维护，GTID模式的介绍、查看与幂等性，以及slowlog的用途和分析方法。重点讨论了binlog在数据库恢复中的应用及其挑战，以及如何利用GTID进行精准的日志恢复。
1. errorlog
1.1 作用
记录MySQL启动、关闭、日常运行过程中,状态信息,警告,错误
1.2 配置方法
默认为开启状态 位置：datadir/hostname.err
mysql> select @@datadir;
+-------------+
| @@datadir |
+-------------+
| /data/3306/ |
+-------------+
1 row in set (0.00 sec)
mysql> select @@log_error;
+-------------+
| @@log_error |
+-------------+
| ./db01.err |
+-------------+
1 row in set (0.00 sec)
-- 通过配置文件修改
-- 说明：日志目录必须提前创建，并且mysql有权限写入
vim /etc/my.cnf
log_error=/tmp/mysql.log
-- 通过启动参数指定
mysqld restart --log-error=[file_name]
1.3 错误日志查看
主要关注[ERROR]块，看上下文
2. binlog
2.1 作用
主要记录数据库变化（DDL、DCL、DML）性质的日志(逻辑层)
(1) 备份恢复必须依赖二进制日志
(2) 主从环境必须依赖二进制日志
2.2 配置方法
默认不开启(8.0以前)，建议生产环境开启
-- 通过配置文件设置
vim /etc/my.cnf
server_id=6 -- 主机编号，在主从中使用，5.7以后开binlog要加此参数，5.6单机使用可以不用
log_bin=/data/binlog/mysql-bin -- 日志存放目录+日志名前缀，如mysql-bin.000001，本质上要和数据盘分开
binlog_format=row -- binlog的记录格式为row模式
sync_binlog=1 -- binlog日志刷盘策略，双一中的第二个1，每次事务提交立即刷写binlog到磁盘
-- 查看状态
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
-- 查看日志路径
mysql> select @@log_bin_basename;
+------------------------+
| @@log_bin_basename |
+------------------------+
| /data/binlog/mysql-bin |
+------------------------+
1 row in set (0.00 sec)
-- 查看服务ID号
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
-- 查看二进制日志格式
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
-- 查看二进制日志刷新策略
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
2.3 binlog记录
2.3.0 引入
bilog是SQL层的功能，记录的是变更类型的SQL语句，不记录查询语句
2.3.1 记录SQL语句种类
DDL：原封不动的记录当前DDL(statement语句方式)
DCL：原封不动的记录当前DCL(statement语句方式)
DML：只记录已提交事务的DML
2.3.2 DML的记录格式
参数：binlog_format
(1) statement：5.6默认，SBR(statment based replication)，语句模式原封不动的记录当前DML
(2) row：5.7默认，RBR(row baesd replication)，记录数据行的变化(直接打开看不懂，需要借助工具分析)
(3) mixed：MBR(mixed based replication)，以上两种的混合
2.3.3 SBR与RBR的区别
statement： 可读性较高(直接存储语句)，日志量少，但是不够严谨
row： 可读性低(需要借助工具分析)，日志量大，足够严谨
何为严谨?
如：insert into t1 values(1,now());
当使用了now()这类与操作时所处环境有关的函数时
由于statement模式会原封不动的记录语句，在恢复时会更新该值
而row模式直接记录了解析后的值，在恢复时仍能保持原来的值不变
2.4 event
2.4.1 事件的简介
事件是binlog的最小记录单元
对于DDL、DCL来说，一条语句就是一个事件(也是一个事务)
对于DML来说：只记录已提交的事务
如以下例子，就被分为了4个event：
begin; 120 - 340
DML1; 340 - 460
DML2; 460 - 550
commit; 550 - 760
2.4.2 event的组成
从上表可以看到，在每个event后面还有两个数字，事实上event由三部分构成：
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
后面的数字对应的就是开始标识和结束标识
标识记录了某个事件在binlog中的相对位置号
有了这些标识就可以方便我们在后续的操作中截取某一部分的事件
2.5 binlog的查看
2.5.1 查看相关参数
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON | -- 开启状态
| log_bin_basename | /data/binlog/mysql-bin | -- 文件存放位置
| log_bin_index | /data/binlog/mysql-bin.index | -- 文件索引
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)
2.5.2 文件查看
[root@db01 ~]# ll /data/binlog/
总用量 8
-rw-r-----. 1 mysql mysql 154 6月 23 16:32 mysql-bin.000001
-rw-r-----. 1 mysql mysql 30 6月 23 16:32 mysql-bin.index
[root@db01 ~]# file /data/binlog/mysql-bin.000001
/data/binlog/mysql-bin.000001: MySQL replication log
2.5.3 二进制内置查看命令
-- 查看所有二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
-- 查看当前在用的binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 查看二进制日志事件，其中前两行每个文件都一样
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.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
-- 做一些操作后再查看
mysql> drop database awei;
mysql> create database awei charset utf8mb4;
mysql> create table database.t1 (id int);
mysql> begin;
mysql> insert into awei.t1 values(1);
mysql> commit;
-- 再次查看二进制日志事件
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.20-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 | 311 | drop database awei |
| mysql-bin.000001 | 311 | Anonymous_Gtid | 6 | 376 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 376 | Query | 6 | 486 | create database awei charset utf8mb4 |
| mysql-bin.000001 | 486 | Anonymous_Gtid | 6 | 551 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 551 | Query | 6 | 650 | create table awei.t1 (id int) |
| mysql-bin.000001 | 650 | Anonymous_Gtid | 6 | 715 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 715 | Query | 6 | 783 | BEGIN |
| mysql-bin.000001 | 783 | Table_map | 6 | 828 | table_id: 230 (awei.t1) |
| mysql-bin.000001 | 828 | Write_rows | 6 | 868 | table_id: 230 flags: STMT_END_F |
| mysql-bin.000001 | 868 | Xid | 6 | 899 | COMMIT /* xid=23 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)
2.5.4 文件内容查看
二进制日志可以使用 mysqlbinlog 命令查看
mysqlbinlog [option] log-file1 log-file2...
以下是常用的几个选项
-d,--database=name：只查看指定数据库的日志操作
-o,--offset=#：忽略掉日志中的前n个操作命令
-r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。
-s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用
--set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上set names char_name
--start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息
--start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息
-v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看
使用mysqlbinlog命令直接查看
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200623 16:32:32 server id 6 end_log_pos 123 CRC32 0x024bbd1f Start: binlog v 4, server v 5.7.20-
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IL7xXg8GAAAAdwAAAHsAAAABAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAgvvFeEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AR+9SwI=
'/*!*/;
# at 123
#200623 16:32:32 server id 6 end_log_pos 154 CRC32 0xd4caf80b Previous-GTIDs
# [empty]
# at 154
#200623 19:25:45 server id 6 end_log_pos 219 CRC32 0x46014e8c Anonymous_GTID last_committed=0
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200623 19:25:45 server id 6 end_log_pos 311 CRC32 0xd170f1fd Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911545/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@sess
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop database awei
/*!*/;
# at 311
#200623 19:25:47 server id 6 end_log_pos 376 CRC32 0xb02af897 Anonymous_GTID last_committed=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 376
#200623 19:25:47 server id 6 end_log_pos 486 CRC32 0xbe721e70 Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911547/*!*/;
create database awei charset utf8mb4
/*!*/;
# at 486
#200623 19:26:25 server id 6 end_log_pos 551 CRC32 0xd2846109 Anonymous_GTID last_committed=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 551
#200623 19:26:25 server id 6 end_log_pos 650 CRC32 0x9f8d31bc Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911585/*!*/;
create table awei.t1 (id int)
/*!*/;
# at 650
#200623 19:29:11 server id 6 end_log_pos 715 CRC32 0x74dbbd17 Anonymous_GTID last_committed=3
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 715
#200623 19:26:38 server id 6 end_log_pos 783 CRC32 0xa520d71e Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911598/*!*/;
BEGIN
/*!*/;
# at 783
#200623 19:26:38 server id 6 end_log_pos 828 CRC32 0xbb789fa4 Table_map: `awei`.`t1` mapped to nu
# at 828
#200623 19:26:38 server id 6 end_log_pos 868 CRC32 0x9e651240 Write_rows: table id 230 flags: STM
BINLOG '
7ubxXhMGAAAALQAAADwDAAAAAOYAAAAAAAEABGF3ZWkAAnQxAAEDAAGkn3i7
7ubxXh4GAAAAKAAAAGQDAAAAAOYAAAAAAAEAAgAB//4BAAAAQBJlng==
'/*!*/;
# at 868
#200623 19:29:11 server id 6 end_log_pos 899 CRC32 0x685b2204 Xid = 23
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
通过重定向或-r选项将日志导出(文本编辑器无法直接查看二进制日志)
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000001 > /tmp/a.sql
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000001 -r /tmp/a.sql
[root@db01 ~]# cat /tmp/a.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200623 16:32:32 server id 6 end_log_pos 123 CRC32 0x024bbd1f Start: binlog v 4, server v 5.7.20-
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IL7xXg8GAAAAdwAAAHsAAAABAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAgvvFeEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AR+9SwI=
'/*!*/;
# at 123
#200623 16:32:32 server id 6 end_log_pos 154 CRC32 0xd4caf80b Previous-GTIDs
# [empty]
# at 154
#200623 19:25:45 server id 6 end_log_pos 219 CRC32 0x46014e8c Anonymous_GTID last_committed=0
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200623 19:25:45 server id 6 end_log_pos 311 CRC32 0xd170f1fd Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911545/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@sess
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop database awei
/*!*/;
# at 311
#200623 19:25:47 server id 6 end_log_pos 376 CRC32 0xb02af897 Anonymous_GTID last_committed=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 376
#200623 19:25:47 server id 6 end_log_pos 486 CRC32 0xbe721e70 Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911547/*!*/;
create database awei charset utf8mb4
/*!*/;
# at 486
#200623 19:26:25 server id 6 end_log_pos 551 CRC32 0xd2846109 Anonymous_GTID last_committed=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 551
#200623 19:26:25 server id 6 end_log_pos 650 CRC32 0x9f8d31bc Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911585/*!*/;
create table awei.t1 (id int)
/*!*/;
# at 650
#200623 19:29:11 server id 6 end_log_pos 715 CRC32 0x74dbbd17 Anonymous_GTID last_committed=3
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 715
#200623 19:26:38 server id 6 end_log_pos 783 CRC32 0xa520d71e Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911598/*!*/;
BEGIN
/*!*/;
# at 783
#200623 19:26:38 server id 6 end_log_pos 828 CRC32 0xbb789fa4 Table_map: `awei`.`t1` mapped to nu
# at 828
#200623 19:26:38 server id 6 end_log_pos 868 CRC32 0x9e651240 Write_rows: table id 230 flags: STM
BINLOG '
7ubxXhMGAAAALQAAADwDAAAAAOYAAAAAAAEABGF3ZWkAAnQxAAEDAAGkn3i7
7ubxXh4GAAAAKAAAAGQDAAAAAOYAAAAAAAEAAgAB//4BAAAAQBJlng==
'/*!*/;
# at 868
#200623 19:29:11 server id 6 end_log_pos 899 CRC32 0x685b2204 Xid = 23
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
其中715~899为DML事务操作，无法直接看出做了什么
# at 715
#200623 19:26:38 server id 6 end_log_pos 783 CRC32 0xa520d71e Query thread_id=5 exec_time=0
SET TIMESTAMP=1592911598/*!*/;
BEGIN
/*!*/;
# at 783
#200623 19:26:38 server id 6 end_log_pos 828 CRC32 0xbb789fa4 Table_map: `awei`.`t1` mapped to nu
# at 828
#200623 19:26:38 server id 6 end_log_pos 868 CRC32 0x9e651240 Write_rows: table id 230 flags: STM
BINLOG '
7ubxXhMGAAAALQAAADwDAAAAAOYAAAAAAAEABGF3ZWkAAnQxAAEDAAGkn3i7
7ubxXh4GAAAAKAAAAGQDAAAAAOYAAAAAAAEAAgAB//4BAAAAQBJlng==
'/*!*/;
# at 868
#200623 19:29:11 server id 6 end_log_pos 899 CRC32 0x685b2204 Xid = 23
COMMIT/*!*/;
在导出时可以指定选项解码(仅适用于范围查看内容，不适用于生产操作中导出)
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000001 --base64-output=decode-rows -vvv > /tmp/b.sql
[root@db01 ~]# sed -n '/# at 783/,/COMMIT/p' /tmp/b.sql
# at 783
#200623 19:26:38 server id 6 end_log_pos 828 CRC32 0xbb789fa4 Table_map: `awei`.`t1` mapped to number 230
# at 828
#200623 19:26:38 server id 6 end_log_pos 868 CRC32 0x9e651240 Write_rows: table id 230 flags: STMT_END_F
### INSERT INTO `awei`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 868
#200623 19:29:11 server id 6 end_log_pos 899 CRC32 0x685b2204 Xid = 23
COMMIT/*!*/;
2.6 日志截取恢复案例
1. 滚动一个新的日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
2. 模拟数据环境
mysql> create database bindb charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use bindb
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
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;
3. 删库跑路
mysql> drop database bindb;
Query OK, 1 row affected (0.01 sec)
mysql> use bindb
ERROR 1049 (42000): Unknown database 'bindb'
4. 数据恢复
-- 思路：
---- 分析binlog，找到起点终点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1452 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 6 | 332 | create database bindb charset utf8mb4 |
| mysql-bin.000002 | 332 | Anonymous_Gtid | 6 | 397 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 397 | Query | 6 | 497 | use `bindb`; create table t1 (id int) |
| mysql-bin.000002 | 497 | Anonymous_Gtid | 6 | 562 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 562 | Query | 6 | 635 | BEGIN |
| mysql-bin.000002 | 635 | Table_map | 6 | 681 | table_id: 231 (bindb.t1) |
| mysql-bin.000002 | 681 | Write_rows | 6 | 731 | table_id: 231 flags: STMT_END_F |
| mysql-bin.000002 | 731 | Xid | 6 | 762 | COMMIT /* xid=53 */ |
| mysql-bin.000002 | 762 | Anonymous_Gtid | 6 | 827 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 827 | Query | 6 | 900 | BEGIN |
| mysql-bin.000002 | 900 | Table_map | 6 | 946 | table_id: 231 (bindb.t1) |
| mysql-bin.000002 | 946 | Write_rows | 6 | 996 | table_id: 231 flags: STMT_END_F |
| mysql-bin.000002 | 996 | Xid | 6 | 1027 | COMMIT /* xid=56 */ |
| mysql-bin.000002 | 1027 | Anonymous_Gtid | 6 | 1092 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1092 | Query | 6 | 1165 | BEGIN |
| mysql-bin.000002 | 1165 | Table_map | 6 | 1211 | table_id: 231 (bindb.t1) |
| mysql-bin.000002 | 1211 | Write_rows | 6 | 1261 | table_id: 231 flags: STMT_END_F |
| mysql-bin.000002 | 1261 | Xid | 6 | 1292 | COMMIT /* xid=59 */ |
| mysql-bin.000002 | 1292 | Anonymous_Gtid | 6 | 1357 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1357 | Query | 6 | 1452 | drop database bindb |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
23 rows in set (0.00 sec)
---- 起点：| mysql-bin.000002 | 219 | Query | 6 | 332 | create database bindb charset utf8mb4 |
---- 重点：| mysql-bin.000002 | 1357 | Query | 6 | 1452 | drop database bindb |
---- 截取日志
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000002 > /tmp/bin.sql
---- 导入日志
---- 需要先临时关闭二进制日志(只针对当前会话,因为已经存在这些操作的二进制日志，不需要再记录)
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql;
mysql> set sql_log_bin=1;
---- 验证数据
mysql> select * from bindb.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
| 111 |
| 222 |
| 333 |
+------+
9 rows in set (0.00 sec)
2.7 binlog日志恢复弊端
- binlog日志记录所有库内容，单只针对单库恢复
-- 解决：使用-d选项指定数据库
[root@db01 ~]# mysqlbinlog -d bindb --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000002 > /tmp/bin.sql
- 需要的日志文件分布在多个文件中
-- 解决：使用时间来截取文件
[root@db01 ~]# mysqlbinlog --start-datetime=xxx --stop-datetime=xxx /data/binlog/mysql-bin.* > /tmp/bin.sql
- 数据库创建了几年，涉及文件太多
-- 解决：全备恢复+binlog配合使用
2.8 binlog维护
2.8.1 日志滚动
-- 使用内置命令
mysql> flush logs;
-- 内置参数指定binlog文件大小，超过将自动滚动
mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
1 row in set (0.01 sec)
-- 使用命令行工具
[root@db01 ~]# mysqladmin -uroot -p flush-logs
[root@db01 ~]# mysqldump -F
-- 数据库重启
[root@db01 ~]# mysqld restart
2.8.2 日志删除
不要直接使用rm命令删除
-- 自动删除机制
-- 默认为0，单位是天，代表永不删除
-- 至少设为一个全备份周期+1，一般生产建议最少两个全备份周期
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
-- 手工删除：使用内置purge命令
-- Examples:
-- PURGE BINARY LOGS TO 'mysql-bin.010';
-- PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
-- 全部清零(上一种方法只会删除之前的日志，序号不会重置)
-- 比较危险，在主库上执行此操作，主从必定宕机
mysql> reset master;
3. binlog的GTID模式
3.1 GTID介绍
GTID(Global Transaction ID)
是对一个已提交事务的编号，并且是一个全局唯一的编号
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
5.6版本新加的特性，在5.7、8.0中做了加强
5.6中默认不开启，且不开启就没有该功能
5.7中默认不开启，但即使不开启也会自动生成匿名的
mysql> select @@session.gtid_next;
+---------------------+
| @@session.gtid_next |
+---------------------+
| AUTOMATIC |
+---------------------+
1 row in set (0.00 sec)
3.2 相关参数
-- 开启gtid模式
vim /etc/my.cnf
# 启用gtid模式
gtid_mode=on
# 强制gtid一致性校验(主从中使用，单机体现不大)
enforce-gtid-consistency=true
-- 重启数据库
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
1 row in set (0.00 sec)
-- 开启gtid模式后，每个事务都会有专用的事务标记id
mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000004 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:1' |
| mysql-bin.000004 | 219 | Query | 6 | 325 | create database gtidtest |
| mysql-bin.000004 | 325 | Gtid | 6 | 390 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:2' |
| mysql-bin.000004 | 390 | Query | 6 | 496 | use `gtidtest`; create table t1 (id int) |
| mysql-bin.000004 | 496 | Gtid | 6 | 561 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:3' |
| mysql-bin.000004 | 561 | Query | 6 | 637 | BEGIN |
| mysql-bin.000004 | 637 | Table_map | 6 | 686 | table_id: 231 (gtidtest.t1) |
| mysql-bin.000004 | 686 | Write_rows | 6 | 736 | table_id: 231 flags: STMT_END_F |
| mysql-bin.000004 | 736 | Xid | 6 | 767 | COMMIT /* xid=14 */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)
3.3 基于GTID进行查看binlog
开启GTID模式后，可以使用GTID截取查看某些事务日志
GTID在日志滚动后仍会继续递增，而不是重置
选项：
–include-gtids–exclude-gtids–skip_gtids
基于GTID恢复案例：
-- 数据准备
create database gtdb charset utf8mb4;
use gtdb
create table t1 (id int);
begin;
insert into t1 values(1),(2),(3);
commit;
flush logs;
show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 194 | | | 432e4da1-abba-11ea-99d1-000c29c8dc7e:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
-- 数据再准备
create table t2 (id int);
begin;
insert into t2 values(1),(2),(3);
commit;
flush logs;
show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 194 | | | 432e4da1-abba-11ea-99d1-000c29c8dc7e:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
-- 数据还准备
create table t3 (id int);
begin;
insert into t3 values(1),(2),(3);
commit;
show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 620 | | | 432e4da1-abba-11ea-99d1-000c29c8dc7e:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
drop database gtbd;
-- 查看binlog
show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:1' |
| mysql-bin.000002 | 219 | Query | 6 | 329 | create database gtdb charset utf8mb4 |
| mysql-bin.000002 | 329 | Gtid | 6 | 394 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:2' |
| mysql-bin.000002 | 394 | Query | 6 | 492 | use `gtdb`; create table t1 (id int) |
| mysql-bin.000002 | 492 | Gtid | 6 | 557 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:3' |
| mysql-bin.000002 | 557 | Query | 6 | 629 | BEGIN |
| mysql-bin.000002 | 629 | Table_map | 6 | 674 | table_id: 236 (gtdb.t1) |
| mysql-bin.000002 | 674 | Write_rows | 6 | 724 | table_id: 236 flags: STMT_END_F |
| mysql-bin.000002 | 724 | Xid | 6 | 755 | COMMIT /* xid=36 */ |
| mysql-bin.000002 | 755 | Rotate | 6 | 802 | mysql-bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
12 rows in set (0.00 sec)
show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 194 | 432e4da1-abba-11ea-99d1-000c29c8dc7e:1-5 |
| mysql-bin.000004 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:6' |
| mysql-bin.000004 | 259 | Query | 6 | 357 | use `gtdb`; create table t3 (id int) |
| mysql-bin.000004 | 357 | Gtid | 6 | 422 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:7' |
| mysql-bin.000004 | 422 | Query | 6 | 494 | BEGIN |
| mysql-bin.000004 | 494 | Table_map | 6 | 539 | table_id: 238 (gtdb.t3) |
| mysql-bin.000004 | 539 | Write_rows | 6 | 589 | table_id: 238 flags: STMT_END_F |
| mysql-bin.000004 | 589 | Xid | 6 | 620 | COMMIT /* xid=49 */ |
| mysql-bin.000004 | 620 | Gtid | 6 | 685 | SET @@SESSION.GTID_NEXT= '432e4da1-abba-11ea-99d1-000c29c8dc7e:8' |
| mysql-bin.000004 | 685 | Query | 6 | 777 | drop database gtdb |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)
可以看到建库起点GTID为1，删库GTID为8，截取的范围应该为1~7，涉及文件为 mysql-bin.000002、mysql-bin.000003、mysql-bin.000004
-- 使用GTID截取
[root@db01 ~]# mysqlbinlog --include-gtids='432e4da1-abba-11ea-99d1-000c29c8dc7e:1-7' \
> /data/binlog/mysql-bin.000002 /data/binlog/mysql-bin.000003 /data/binlog/mysql-bin.000004 \
> > /tmp/gtdb.sql
-- 尝试恢复
mysql> source /tmp/gtdb.sql
ERROR 1049 (42000): Unknown database 'gtdb'
-- 发现恢复失败
3.4 GTID的幂等性
开启GTID后，MySQL恢复binlog时，重复的GTID事务是不会再执行的
若想恢复的话，需要使用 –skip-gtids 来指定输出时不记录GTID信息
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='432e4da1-abba-11ea-99d1-000c29c8dc7e:1-7' /data/binlog/mysql-bin.000002 /data/binlog/mysql-bin.000003 /data/binlog/mysql-bin.000004 > /tmp/gtdb.sql
-- 再次尝试恢复
-- 先把binlog关了
set sql_log_bin=0;
source /tmp/gtdb.sql;
set sql_log_bin=1;
use gtdb;
Database changed
show tables;
+----------------+
| Tables_in_gtdb |
+----------------+
| t1 |
| t2 |
| t3 |
+----------------+
3 rows in set (0.00 sec)
4. slowlog
4.1 作用
记录MySQL运行过程中较慢的语句，通过一个文本文件记录下来
帮助我们进行语句优化的工具日志
默认不开启
4.2 相关参数
-- 查看开启状态
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
-- 查看日志存放位置
mysql> select @@slow_query_log_file;
+--------------------------+
| @@slow_query_log_file |
+--------------------------+
| /data/3306/db01-slow.log |
+--------------------------+
1 row in set (0.00 sec)
-- 慢语句评估时间阈值(秒)
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
-- 记录不走索引的语句
mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
-- 开启slowlog
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes=1
4.3 模拟慢查询
-- 导入t100w.sql，最好先把binlog关了
source t100w.sql
-- 查！
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(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 k2, sum(id) from t100w group by k2,k1 limit 1;
select k1, count(id) from t100w group by k1 limit 1;
-- 查看慢日志文件
cat /data/3306/db01-slow.log
# Time: 2020-06-23T18:34:27.388055Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.204820 Lock_time: 0.000096 Rows_sent: 10 Rows_examined: 500010
SET timestamp=1592937267;
select * from t100w limit 500000,10;
# Time: 2020-06-23T18:34:27.625294Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.236712 Lock_time: 0.000088 Rows_sent: 10 Rows_examined: 600010
SET timestamp=1592937267;
select * from t100w limit 600000,10;
# Time: 2020-06-23T18:34:27.863342Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.237455 Lock_time: 0.000203 Rows_sent: 1 Rows_examined: 600001
SET timestamp=1592937267;
select * from t100w limit 600000,1;
# Time: 2020-06-23T18:34:28.098549Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.234461 Lock_time: 0.000075 Rows_sent: 2 Rows_examined: 600002
SET timestamp=1592937268;
select * from t100w limit 600000,2;
# Time: 2020-06-23T18:34:31.428819Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 3.329676 Lock_time: 0.000184 Rows_sent: 10 Rows_examined: 2000010
SET timestamp=1592937271;
select id, count(num) from t100w group by id limit 10;
# Time: 2020-06-23T18:34:34.693040Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 3.263621 Lock_time: 0.000082 Rows_sent: 5 Rows_examined: 2000005
SET timestamp=1592937274;
select id, count(num) from t100w group by id limit 5;
# Time: 2020-06-23T18:34:37.895233Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 3.201568 Lock_time: 0.000084 Rows_sent: 2 Rows_examined: 2000002
SET timestamp=1592937277;
select id, count(num) from t100w group by id limit 2;
# Time: 2020-06-23T18:34:41.087478Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 3.191679 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 2000001
SET timestamp=1592937281;
select id, count(k1) from t100w group by id limit 1;
# Time: 2020-06-23T18:34:44.290580Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 3.202587 Lock_time: 0.000138 Rows_sent: 1 Rows_examined: 2000001
SET timestamp=1592937284;
select id, count(k2) from t100w group by id limit 1;
# Time: 2020-06-23T18:34:45.468768Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 1.177682 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1001297
SET timestamp=1592937285;
select k2, sum(id) from t100w group by k2 limit 1;
# Time: 2020-06-23T18:34:47.502993Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 2.033497 Lock_time: 0.000109 Rows_sent: 1 Rows_examined: 1161899
SET timestamp=1592937287;
select k2, sum(id) from t100w group by k2,k1 limit 1;
# Time: 2020-06-23T18:34:49.512775Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 2.008855 Lock_time: 0.000311 Rows_sent: 1 Rows_examined: 1161899
SET timestamp=1592937289;
select k2, sum(id) from t100w group by k2,k1 limit 1;
# Time: 2020-06-23T18:34:51.509200Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 1.995785 Lock_time: 0.000093 Rows_sent: 1 Rows_examined: 1161899
SET timestamp=1592937291;
select k2, sum(id) from t100w group by k2,k1 limit 1;
# Time: 2020-06-23T18:35:16.319715Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.813123 Lock_time: 0.000117 Rows_sent: 1 Rows_examined: 1001226
SET timestamp=1592937316;
select k1, count(id) from t100w group by k1 limit 1;
直接查看并不方便，所有慢语句都会展示出来
相似的慢语句应该合并统计一下才好，并且应该有个优化的优先顺序
MySQL提供了一个工具来帮助分析
-s：排序
c：指定次数为判断基准
-t 5：输出排序前五的
[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.23s (0s) Lock=0.00s (0s) Rows=5.8 (23), root[root]@localhost
select * from t100w limit N,N
Count: 3 Time=2.01s (6s) Lock=0.00s (0s) Rows=1.0 (3), root[root]@localhost
select k2, sum(id) from t100w group by k2,k1 limit N
Count: 3 Time=3.26s (9s) Lock=0.00s (0s) Rows=5.7 (17), root[root]@localhost
select id, count(num) from t100w group by id limit N
Count: 1 Time=1.18s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select k2, sum(id) from t100w group by k2 limit N
Count: 1 Time=3.19s (3s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select id, count(k1) from t100w group by id limit N
慢日志配合 desc 或 explain 来分析语句的执行过程，最终达到优化语句
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
