成都个人学做网站/网络热词2023

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 慢查询可视化