MySQL日志管理
MySQL日志管理
一、MySQL日志管理
1.1.1 MySQL日志管理介绍
- 错误日志
mysql> select @@log_error;
+-----------------------------------+
| @@log_error |
+-----------------------------------+
| /application/mysql/data/mysql.err |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%error';
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| log_error | /application/mysql/data/mysql.err |
+---------------+-----------------------------------+
1 row in set (0.04 sec)
配置方式
vim /etc/my.cnf
log_error=/application/mysql/data/mysql.err
- binglog(二进制文件)
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> show global variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /application/mysql/log_bin/mysql-bin |
| log_bin_index | /application/mysql/log_bin/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------------------+
5 rows in set (0.00 sec)
配置方式
vim /etc/my.cnf
/application/mysql/log_bin/mysql-bin (生产要求：日志要和数据分开存放不同的物理磁盘)
server_id=6
binlog记录了数据库中所有变更类的操作
DDL
DCL
DML
（1）
对于DDL和DCL语句，记录发生过的语句
（2）DML(IUD)
前提： 已经提交的事务IUD
关于记录格式：
ROW ：RBR 行记录模式，记录的是行的变化
STATEMENT ：SBR 语句记录模式，记录操作语句
MIXED ：MBR 混合记录模式
电话面试的题目：
delete from city where id>1000
RBR,逐行记录日志,日志量很大，可读性差。但是够严谨，不会出现记录错误
SBR,只记录语句本身，日志量很少，可读性较强。对于函数类的操作，将来恢复时会造错误。
5.7 版本 默认是RBR，是企业建议模式
binlog记录模式查看
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
mysql> show variables like '%log_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
- 二进制日志事件（event）
简介
二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
position号码
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
event的组成
三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
二进制日志位置的查看
mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /application/mysql/log_bin/mysql-bin |
| log_bin_index | /application/mysql/log_bin/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------------------+
5 rows in set (0.00 sec)
[root@db01 ~]# ll /application/mysql/log_bin/
total 44020
-rw-r----- 1 mysql mysql 177 Jun 3 18:47 mysql-bin.000001
-rw-r----- 1 mysql mysql 9128 Jun 5 19:26 mysql-bin.000002
-rw-r----- 1 mysql mysql 6233 Jun 6 19:01 mysql-bin.000003
-rw-r----- 1 mysql mysql 695165 Jun 10 00:01 mysql-bin.000004
-rw-r----- 1 mysql mysql 177 Jun 11 13:44 mysql-bin.000005
查看使用过的二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 9128 |
| mysql-bin.000003 | 6233 |
| mysql-bin.000004 | 695165 |
| mysql-bin.000005 | 177 |
| mysql-bin.000006 | 1382573 |
| mysql-bin.000007 | 21102987 |
| mysql-bin.000008 | 154 |
| mysql-bin.000009 | 154 |
| mysql-bin.000010 | 154 |
| mysql-bin.000011 | 154 |
| mysql-bin.000012 | 154 |
| mysql-bin.000013 | 154 |
| mysql-bin.000014 | 154 |
| mysql-bin.000015 | 154 |
| mysql-bin.000016 | 21795116 |
| mysql-bin.000017 | 154 |
| mysql-bin.000018 | 1026 |
| mysql-bin.000019 | 154 |
| mysql-bin.000020 | 154 |
| mysql-bin.000021 | 154 |
| mysql-bin.000022 | 154 |
+------------------+-----------+
22 rows in set (0.00 sec)
#在用是22
log_name : 目前MySQL存在的二进制日志名字
file_size: 目前mysql用到哪个position号
当前正在使用binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000022 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看当前enevt日志事件
mysql> show binlog events in 'mysql-bin.000022';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
创建一个库测试一下
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> show binlog events in 'mysql-bin.000022';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000022 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 219 | Query | 1 | 316 | create database test2 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
注释：每一行都是一个事件
Log_name ：日志名
Pos ：事件开始的position *****
Event_type ：事件类型
Server_id ：发生在哪台机器的事件
End_log_pos：事件结束的位置号 *****
Info ：事件内容 *****
查看二进制日志内容
[root@db01 ~]# mysqlbinlog /application/mysql/log_bin/mysql-bin.000022 |grep -v SET
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/log_bin/mysql-bin.000022 （查看插入，修改等加密数据）
- 基于二进制日志数据恢复案例
基于position号的截取
--start-position
--stop-position
mysql> show binlog events in 'mysql-bin.000022';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000022 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 219 | Query | 1 | 316 | create database test2 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=316 /application/mysql/log_bin/mysql-bin.000022
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=316 /application/mysql/log_bin/mysql-bin.000022 >/tmp/test.sql
[root@db01 ~]# ll /tmp/test.sql
-rw-r--r-- 1 root root 1413 Jun 22 01:15 /tmp/test.sql (将来库被删了，可以用来恢复)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenhj |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| test2 |
| wordpress |
| world |
+--------------------+
10 rows in set (0.09 sec)
mysql> drop database test2;
Query OK, 0 rows affected (0.30 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenhj |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| wordpress |
| world |
+--------------------+
9 rows in set (0.00 sec)
恢复第一步
mysql> set sql_log_bin=0; （重要） （在当前会话数据恢复不记录binlog日志，因为你的恢复的数据就是基于binlog的，这些数据在binlog中有，不需要重复记录）
Query OK, 0 rows affected (0.00 sec)
开始恢复
mysql> source /tmp/test.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenhj |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| test2 |
| wordpress |
| world |
+--------------------+
10 rows in set (0.00 sec)
恢复成功
基于时间点的截取(了解)
--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56
- 简单案例恢复
mysql> create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use binlog;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.31 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.06 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database binlog;
Query OK, 1 row affected (0.06 sec)
第一步、查看正在使用的binlog日志事件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000022 | 1730 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
第二步、查看event日志事件
mysql> show binlog events in 'mysql-bin.000022';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000022 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 219 | Query | 1 | 316 | create database test2 |
| mysql-bin.000022 | 316 | Anonymous_Gtid | 1 | 381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 381 | Query | 1 | 468 | drop database test2 |
| mysql-bin.000022 | 468 | Anonymous_Gtid | 1 | 533 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 533 | Query | 1 | 630 | create database test2 |
| mysql-bin.000022 | 630 | Anonymous_Gtid | 1 | 695 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 695 | Query | 1 | 796 | use `binlog`; create table t1(id int) |
| mysql-bin.000022 | 796 | Anonymous_Gtid | 1 | 861 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 861 | Query | 1 | 935 | BEGIN |
| mysql-bin.000022 | 935 | Table_map | 1 | 982 | table_id: 111 (binlog.t1) |
| mysql-bin.000022 | 982 | Write_rows | 1 | 1022 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000022 | 1022 | Xid | 1 | 1053 | COMMIT /* xid=111 */ |
| mysql-bin.000022 | 1053 | Anonymous_Gtid | 1 | 1118 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 1118 | Query | 1 | 1192 | BEGIN |
| mysql-bin.000022 | 1192 | Table_map | 1 | 1239 | table_id: 111 (binlog.t1) |
| mysql-bin.000022 | 1239 | Write_rows | 1 | 1279 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000022 | 1279 | Xid | 1 | 1310 | COMMIT /* xid=113 */ |
| mysql-bin.000022 | 1310 | Anonymous_Gtid | 1 | 1375 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 1375 | Query | 1 | 1449 | BEGIN |
| mysql-bin.000022 | 1449 | Table_map | 1 | 1496 | table_id: 111 (binlog.t1) |
| mysql-bin.000022 | 1496 | Write_rows | 1 | 1536 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000022 | 1536 | Xid | 1 | 1567 | COMMIT /* xid=115 */ |
| mysql-bin.000022 | 1567 | Anonymous_Gtid | 1 | 1632 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 1632 | Query | 1 | 1730 | drop database binlog |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
27 rows in set (0.00 sec)
第三步、截取日志
[root@db01 ~]# mysqlbinlog --start-position=1795 --stop-position=2913 /application/mysql/log_bin/mysql-bin.000022 >/tmp/bin.sql
[root@db01 ~]# ll /tmp/bin.sql
-rw-r--r-- 1 root root 4539 Jun 22 02:22 /tmp/bin.sql
第四步、不记录binlog恢复数据
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/bin.sql
检查：恢复成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| chenhj |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| test2 |
| wordpress |
| world |
+--------------------+
11 rows in set (0.00 sec)
mysql> use binlog
Database changed
mysql> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
1.1.2 开启GTIO功能的二进制日志管理
- 什么是GTID
GTID：全局事务编号
如果binlog中数据记录混乱，基于position号恢复需要多次截取，找起点和终点过程很复杂。
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
说明：
DDL DCL，一条语句（事件）就是一个事务，占一个GTID号
DML：一个完整的事务(begin--》commit)，是一个事务，占一个GTID号
配置GTID
mysql> show global variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like '%enforce%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF |
+--------------------------+-------+
1 row in set (0.00 sec)
开启GTID
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld
查看开启GTID的事务uuid（前半部分）
[root@db01 ~]# cat /application/mysql/data/auto.cnf
[auto]
server-uuid=5cabcecd-95d4-11ea-928e-000c290e8d03
mysql> create database ll;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000024 | 307 | | | 5cabcecd-95d4-11ea-928e-000c290e8d03:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
创建库后出现的GTID的uuid与上面的一致
实践
mysql> use ll
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.30 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.13 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database ll;
Query OK, 1 row affected (0.05 sec)
数据恢复第一步
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000024 | 1364 | | | 5cabcecd-95d4-11ea-928e-000c290e8d03:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
数据恢复第二步
mysql> show binlog events in 'mysql-bin.000024';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000024 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000024 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000024 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:1' |
| mysql-bin.000024 | 219 | Query | 1 | 307 | create database ll |
| mysql-bin.000024 | 307 | Gtid | 1 | 372 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:2' |
| mysql-bin.000024 | 372 | Query | 1 | 466 | use `ll`; create table t1 (id int) |
| mysql-bin.000024 | 466 | Gtid | 1 | 531 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:3' |
| mysql-bin.000024 | 531 | Query | 1 | 601 | BEGIN |
| mysql-bin.000024 | 601 | Table_map | 1 | 644 | table_id: 108 (ll.t1) |
| mysql-bin.000024 | 644 | Write_rows | 1 | 684 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000024 | 684 | Xid | 1 | 715 | COMMIT /* xid=17 */ |
| mysql-bin.000024 | 715 | Gtid | 1 | 780 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:4' |
| mysql-bin.000024 | 780 | Query | 1 | 850 | BEGIN |
| mysql-bin.000024 | 850 | Table_map | 1 | 893 | table_id: 108 (ll.t1) |
| mysql-bin.000024 | 893 | Write_rows | 1 | 933 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000024 | 933 | Xid | 1 | 964 | COMMIT /* xid=19 */ |
| mysql-bin.000024 | 964 | Gtid | 1 | 1029 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:5' |
| mysql-bin.000024 | 1029 | Query | 1 | 1099 | BEGIN |
| mysql-bin.000024 | 1099 | Table_map | 1 | 1142 | table_id: 108 (ll.t1) |
| mysql-bin.000024 | 1142 | Write_rows | 1 | 1182 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000024 | 1182 | Xid | 1 | 1213 | COMMIT /* xid=21 */ |
| mysql-bin.000024 | 1213 | Gtid | 1 | 1278 | SET @@SESSION.GTID_NEXT= '5cabcecd-95d4-11ea-928e-000c290e8d03:6' |
| mysql-bin.000024 | 1278 | Query | 1 | 1364 | drop database ll |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
23 rows in set (0.00 sec)
基于GTID截取日志
本地恢复方法：（--skip-gtids） 要加上忽略GTID，否则恢复时会去检查GTID，如果检查有相同的GTID的UUID，数据就恢复不了
mysqlbinlog --skip-gtids --include-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:1-5' /application/mysql/log_bin/mysql-bin.000024 >/tmp/ll.sql
其它机器： （--skip-gtids）可以不忽略GITD
mysqlbinlog --include-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:1-5' /application/mysql/log_bin/mysql-bin.000024 >/tmp/ll.sql
所以在（本机）做基于GTID数据恢复就要加上--skip-gtids(忽略GTID)
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:1-5' /application/mysql/log_bin/mysql-bin.000024 >/tmp/ll.sql
[root@db01 ~]# ll /tmp/ll.sql
-rw-r--r-- 1 root root 3514 Jun 22 13:22 /tmp/ll.sql
临时关闭二进制日志记录（数据恢复完成之后记得开启，切记切记。）
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
开始恢复数据
mysql> source /tmp/ll.sql
mysql> select * from ll.t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
恢复成功 开启二进制日志记录
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
扩展跳过某个uuid号不截取（--exclude-gtids）
连续的跳过
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:1-5' --exclude-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:3-4' /application/mysql/log_bin/mysql-bin.000024 >/tmp/ll2.sql
非连续的跳过
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:1-5' --exclude-gtids='5cabcecd-95d4-11ea-928e-000c290e8d03:2,5cabcecd-95d4-11ea-928e-000c290e8d03:4' /application/mysql/log_bin/mysql-bin.000024 >/tmp/ll1.sql
[root@db01 ~]# ll /tmp/*.sql
-rw-r--r-- 1 root root 2743 Jun 22 13:48 /tmp/ll1.sql
-rw-r--r-- 1 root root 2342 Jun 22 13:48 /tmp/ll2.sql
-rw-r--r-- 1 root root 3514 Jun 22 13:22 /tmp/ll.sql
- 二进制日志其它操作
1、临时关闭二进制日志记录
set sql_log_bin=0;
2、自动清理二进制日志文件
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
mysql> show variables like '%expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)
配置自动清理
至少是一个全备周期+1，企业建议至少2个全备周期+1
vim /etc/my.cnf
expire_logs_days=15
手工清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000003';
实践
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 9128 |
| mysql-bin.000003 | 6233 |
| mysql-bin.000004 | 695165 |
| mysql-bin.000005 | 177 |
| mysql-bin.000006 | 1382573 |
| mysql-bin.000007 | 21102987 |
| mysql-bin.000008 | 154 |
| mysql-bin.000009 | 154 |
| mysql-bin.000010 | 154 |
| mysql-bin.000011 | 154 |
| mysql-bin.000012 | 154 |
| mysql-bin.000013 | 154 |
| mysql-bin.000014 | 154 |
| mysql-bin.000015 | 154 |
| mysql-bin.000016 | 21795116 |
| mysql-bin.000017 | 154 |
| mysql-bin.000018 | 1026 |
| mysql-bin.000019 | 154 |
| mysql-bin.000020 | 154 |
| mysql-bin.000021 | 154 |
| mysql-bin.000022 | 3034 |
| mysql-bin.000023 | 154 |
| mysql-bin.000024 | 1364 |
+------------------+-----------+
24 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS TO 'mysql-bin.000010';
Query OK, 0 rows affected (0.04 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 154 |
| mysql-bin.000011 | 154 |
| mysql-bin.000012 | 154 |
| mysql-bin.000013 | 154 |
| mysql-bin.000014 | 154 |
| mysql-bin.000015 | 154 |
| mysql-bin.000016 | 21795116 |
| mysql-bin.000017 | 154 |
| mysql-bin.000018 | 1026 |
| mysql-bin.000019 | 154 |
| mysql-bin.000020 | 154 |
| mysql-bin.000021 | 154 |
| mysql-bin.000022 | 3034 |
| mysql-bin.000023 | 154 |
| mysql-bin.000024 | 1364 |
+------------------+-----------+
15 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS TO 'mysql-bin.000016';
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000016 | 21795116 |
| mysql-bin.000017 | 154 |
| mysql-bin.000018 | 1026 |
| mysql-bin.000019 | 154 |
| mysql-bin.000020 | 154 |
| mysql-bin.000021 | 154 |
| mysql-bin.000022 | 3034 |
| mysql-bin.000023 | 154 |
| mysql-bin.000024 | 1364 |
+------------------+-----------+
9 rows in set (0.00 sec)
注意:不要手工 rm binlog文件
删除所有binlog，从000001开始（危险！！！！）
mysql> reset master;
- 日志滚动
重启数据库
mysql> flush logs
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000016 | 21795116 |
| mysql-bin.000017 | 154 |
| mysql-bin.000018 | 1026 |
| mysql-bin.000019 | 154 |
| mysql-bin.000020 | 154 |
| mysql-bin.000021 | 154 |
| mysql-bin.000022 | 3034 |
| mysql-bin.000023 | 154 |
| mysql-bin.000024 | 1364 |
+------------------+-----------+
9 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000016 | 21795116 |
| mysql-bin.000017 | 154 |
| mysql-bin.000018 | 1026 |
| mysql-bin.000019 | 154 |
| mysql-bin.000020 | 154 |
| mysql-bin.000021 | 154 |
| mysql-bin.000022 | 3034 |
| mysql-bin.000023 | 154 |
| mysql-bin.000024 | 1411 |
| mysql-bin.000025 | 194 |
+------------------+-----------+
10 rows in set (0.00 sec)
mysql> mysqladmin -uroot -p flush-logs
mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
mysql> select sum(1073741824/1024/1024);
+---------------------------+
| sum(1073741824/1024/1024) |
+---------------------------+
| 1024.00000000 |
+---------------------------+
1 row in set (0.00 sec)
默认是一个G可以修改
备份加一些参数，会触发滚动日志
1.1.3 优化相关日志-slowlog
- 作用
记录慢SQL语句的日志,定位低效SQL语句的工具日志
- 开启slowlog
slow_query_log=1
文件位置及名字
slow_query_log_file= /application/mysql/slow/slow.log
设定慢查询时间:
long_query_time=0.1
没走索引的语句也记录:
log_queries_not_using_indexes
slow_query_log=1
slow_query_log_file=/application/mysql/slow/slow.log
long_query_time=0.1
log_queries_not_using_indexes
systemctl restart mysqld
mysql> show variables like '%slow%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /application/mysql/slow/slow.log |
+---------------------------+----------------------------------+
5 rows in set (0.00 sec)
3.mysqldumpslow 分析慢日志
[root@db01 ~]# mysqldumpslow -s c -t 10 /application/mysql/slow/slow.log
Reading mysql slow query log from /application/mysql/slow/slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
Died at /application/mysql/bin/mysqldumpslow line 161, <> chunk 1.
-----你努力获得的，都是自己的，不容易忘掉。
星辰和大海都需要门票，诗和远方的路费都很贵。来人间一次，努力成为最好的自己。人间值得，未来可期。
