MySQL- InnoDB之二进制日志

1.1 查看二进制日志位置

mysql> show variables like '%log_bin%';

1.2 查看所有已存在的二进制日志

mysql> show binary logs;

mysql> flush logs;

mysql> show binary logs;

1.3 查看正在使用的二进制日志

mysql> show master status ;

1.4 查看二进制日志事件 

mysql> create database binlog charset utf8mb4;

mysql> use binlog

mysql> create table t1(id int);

mysql> insert into t1 values(1);

mysql> show master status ;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 |      501 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';

mysql> show binlog events in 'mysql-bin.000004' limit 5;

1.5 查看二进制日志内容

[root@db01 binlog]# mysqlbinlog mysql-bin.000004

[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004 

[root@db01 binlog]# mysqlbinlog -d haoge mysql-bin.000004 

1.6 截取二进制日志

[root@db01 binlog]# mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql

1.7 通过binlog恢复数据

(1) 模拟数据

mysql> create database haoge charset utf8mb4;

mysql> use haoge;

mysql> create table t1(id int);

mysql> insert into t1 values(1);

mysql> commit;

(2)模拟故障 

mysql> drop database haoge;

(3)分析和截取binlog

mysql> show master status ;    --->确认使用的是哪一个日志

mysql> show binlog events in 'mysql-bin.000004' ;   --->查看事件

说明: 找到起点和终点,进行截取

 mysqlbinlog --start-position=823 --stop-position=1420 data/binlog/mysql-bin.000004 >/tmp/bin.sql

(4)恢复binlog

mysql> set sql_log_bin=0;    --->临时关闭恢复时产生的新日志

mysql> source /tmp/bin.sql

mysql> set sql_log_bin=1; set sql_log_bin=1;    --->改回来

2.1 GTID介绍

对于binlog中的每一个事务,都会生成一个GTID号码

DDL ,DCL 一个event就是一个事务,就会有一个GTID号.

DML语句来讲,begin到commit,是一个事务,就是一个GTID号

2.2 GTID的组成

severi_uuid:TID

severi_uuid? 

[root@db01 data]# cat auto.cnf 

[auto]

server-uuid=d60b549f-9e10-11e9-ab04-000c294a1b3b

TID是一个:自增长的数据,从1开始

d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15

2.3 GTID的幂等性

如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过

会影响到binlog恢复和主从复制.

2.4 GTID的开启和配置

vim /etc/my.cnf

gtid-mode=on

enforce-gtid-consistency=true

2.5 查看GTID信息

mysql> create database gtid charset utf8mb4;

mysql> show master status;

mysql> use gtid;

mysql> create table t1(id int);

mysql> show master status;

mysql> insert into t1 values(1);

mysql> commit;

mysql> show master status;

mysql> drop database gtid;

2.6 基于GTID,binlog恢复

(1) 截取日志

[root@db01 data]# cd /data/binlog/

[root@db01 binlog]# mysqlbinlog --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3' mysql-bin.000005 >/tmp/gtid.sql

(2)恢复 

mysql> set sql_log_bin=0;

mysql> source /tmp/gtid.sql

mysql> set sql_log_bin=1;

(3) 报错

ERROR 1049 (42000): Unknown database 'gtid'

Query OK, 0 rows affected (0.00 sec)

ERROR 1046 (3D000): No database selected

为什么报错?

因为幂等性的检查,1-3事务已经做过了.

(4)正确的做法?

mysqlbinlog --skip-gtids --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3' mysql-bin.000005 >/tmp/gtid.sql

--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息

(5) 恢复

set sql_log_bin=0;

source /tmp/gtid.sql

set sql_log_bin=1;

2.7 GTID相关的参数

--skip-gtids 

--include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

--exclude-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

3.1 作用

记录运行较慢的语句,优化过程中常用的工具日志.

3.2 如何配置

## 开关

slow_query_log=1 

## 文件位置及名字 

slow_query_log_file=/data/mysql/slow.log

## 设定慢查询时间

long_query_time=0.1

## 没走索引的语句也记录

log_queries_not_using_indexes

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

3.3分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log

# 3.4 第三方工具(自己扩展)

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  /data/mysql/slow.log

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