MySQL 日志管理 (二)

binlog日志的 GTID 特性

GTID 介绍

5.6 版本新加的特性, 5.7 中做了加强
5.6 中不开启, 没有这个功能.
5.7 中的GTID, 即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

GTID(Global Transaction ID)

是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

基于GTID进行查看binlog

具备GTID后,截取查看某些事务日志:
--include-gtids
--exclude-gtids

mysqlbinlog --include-gtids='ec4463a1-0e92-11ea-93bf-00155d1f4002:1' --exclude-gtids='ec4463a1-0e92-11ea-93bf-00155d1f4002:2'  /var/lib/mysql/mysql-bin.000002 

GTID的幂等性

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
--skip-gtids

mysqlbinlog --skip-gtids --include-gtids='ec4463a1-0e92-11ea-93bf-00155d1f4002:1-6' /var/lib/mysql/mysql-bin.000002 > /tmp/bin.sql
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;

通过 GTID 恢复数据库的例子

创建模拟数据

create database bbb;
use bbb;
create table t1(id int);
insert into t1 values(1),(2),(3);
update t1 set id=22 where id=2;
delete from t1 where id=1;
insert into t1 values(111),(222),(333);
drop database bbb;

确定日志位置

show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+
| mysql-bin.000022 |     1734 |              |                  | 439e7a53-0e93-11ea-ba2b-00155d1f4003:1, ec4463a1-0e92-11ea-93bf-00155d1f4002:1-22 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+

show binlog events in 'mysql-bin.000022';

恢复数据

mysqlbinlog --skip-gtids --include-gtids='ec4463a1-0e92-11ea-93bf-00155d1f4002:16-22' --exclude-gtids='ec4463a1-0e92-11ea-93bf-00155d1f4002:22,ec4463a1-0e92-11ea-93bf-00155d1f4002:20' /var/lib/mysql/mysql-bin.000022 > /tmp/bin.sql

set sql_log_bin=0;
source /tmp/bin.sql;
set sql_log_bin=1;

select * from bbb.t1;
+------+
| id   |
+------+
|    1 |
|   22 |
|    3 |
|  111 |
|  222 |
|  333 |
+------+

自动清理日志

select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+

# 0 为永不过期
set global expire_logs_days=8;
永久生效:
my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog

日志滚动

flush logs; 

重启mysql也会自动滚动一个新的
日志文件达到1G大小(max_binlog_size)

select @@max_binlog_size ;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+   

备份时,加入参数也可以自动滚动

slow_log 慢日志

记录慢 SQL 语句的日志,定位低效 SQL 语句的工具日志

# 慢查询日志是否开启
select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+

# 慢查询日志位置
select @@slow_query_log_file;
+-----------------------------+
| @@slow_query_log_file       |
+-----------------------------+
| /var/lib/mysql/db1-slow.log |
+-----------------------------+

# 慢查询时间
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+

# 没走索引的语句记录
select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+

开启慢查询日志

vi /etc/my.cnf

slow_query_log=1 
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes

# 重启 mysql 
systemctl restart mysqld

mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 第三方工具(自己扩展)
https://www.percona.com/downloads/percona-toolkit/LATEST/

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5

# toolkit 工具包中的命令:
pt-query-diagest  /var/lib/mysql/slow.log

# Anemometer 基于 pt-query-digest 将 MySQL 慢查询可视化