08-mysql-binlog

1 事件查看

 show master status\G 

 mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000002'" 

2.内容查看

第一步  mysqlbinlog mysql-bin.000002 >/tmp/a.sql

第二步  vim /tmp/a.sql

3.详细查看

 mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002 >/tmp/b.sql

vim /tmp/b.sql

4.日志截取恢复

1. 滚动一个新的日志
flush logs ;    
2. 模拟数据环境
mysql> create database bindb charset utf8mb4;
mysql> use bindb
mysql> create table t1 (id int);
mysql> begin;
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),(332);
mysql> commit;
mysql> drop database bindb;
3. 数据恢复
起点： 
mysql> show binlog events in 'mysql-bin.000005';
|  219 |       332 | create database bindb charset utf8mb4 |
终点： 
| 1357 |       1452 | drop database bindb    
截取日志：
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000005 >/tmp/bin.sql
恢复日志：
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;

验证数据： 
select * from bindb.t1;

 5.binlog恢复的弊端

1. binlog记录不单单一个数据库的操作 ，可能对其他数据库重复操作？
mysqlbinlog -d bindb --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000005

2.需要的日志在多个文件中分布

起点： 假如，mysql-bin.000001 , 4600
终点：一般是最后一个文件，假设mysql-bin.000002 980
mysqlbinlog --start-datetime= --stop-datetime= mysql-bin.000001 mysql-bin.000002

3. 创建了几年，期间一直在用的数据库，插入数据的操作从 bin_log.00000001到 bin_log_.0000121345 之中都有的库，被删了，怎么恢复啊，数据多了咋办，数据行多。
假设： 每周六做全备份23：00，binlog每天备份23:00。
故障点 周三 10点 drop操作。

binlog实际上是我们数据恢复时配合备份一起恢复数据的手段。

6.binlog维护操作

日志滚动
mysql> flush logs ;
mysql> select @@max_binlog_size;
mysqladmin -uroot -p123 flush-logs
mysqldump -F
重启数据库自动滚动

日志的删除
注意：不要使用rm命令删除日志。
2.2.1 自动删除机制
mysql> select @@expire_logs_days;
默认是0，单位是天，代表永不删除。
问题： 到底设置多少天合适？阈值？
一次全备份周期。7+1天，一般生产一般建议，最少2个全备周期+1

手工删除
Examples:
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

全部清空
mysql> reset master;
比较危险,在主库执行此操作，主从必宕。

7.binlog的GTID模式管理

GTID 介绍

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

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

重要参数介绍：
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

基于GTID进行查看binlog

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

截取日志：

起点： 
mysql> show binlog events in 'mysql-bin.000002';
SET @@SESSION.GTID_NEXT= '9b8e7056-4d4c-11ea-a231-000c298e182d:5' 
create database gtdb charset utf8mb4                                  
终点：  
mysql> show master status;
mysql> show binlog events in 'mysql-bin.000004';
+++++++++++++++++++++++++++++++++
SET @@SESSION.GTID_NEXT= '9b8e7056-4d4c-11ea-a231-000c298e182d:12' 
drop database gtdb           
+++++++++++++++++++++++++++++++++
gtid : 5-11
文件 ：mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 
# 截取： 
cd /data/binlog/
mysqlbinlog  --include-gtids='9b8e7056-4d4c-11ea-a231-000c298e182d:5-11' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtid.sql

注意:

3.5 GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
就想恢复?怎么办?
--skip-gtids
cd /data/binlog/
mysqlbinlog  --skip-gtids --include-gtids='9b8e7056-4d4c-11ea-a231-000c298e182d:5-11' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtid.sql

8.slowlog 慢日志

作用

记录MySQL运行过程中较慢的语句，通过一个文本的文件记录下来。
帮助我们进行语句优化工具日志。

如何配置

默认慢日志没有开启。
配置参数： 
    mysql> select @@slow_query_log;      # 是否开启
    mysql> select @@slow_query_log_file; # 文件存放位置
    mysql> select @@long_query_time;     # 慢语句认定时间阈值
      mysql> select @@log_queries_not_using_indexes; # 不走索引的语句记录

vim /etc/my.cnf 
slow_query_log=1
slow_query_log_file=/data/3306/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1

模拟慢语句（t100w表）

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(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 k1 ,sum(id) from t100w group by k2,k1 limit 1;
select k1,count(id) from t100w group by k1 limit 10;

慢语句分析

mysqldumpslow -s c -t 5 /data/3306/db01-slow.log 

Reading mysql slow query log from /data/3306/db01-slow.log
Count: 8  Time=0.36s (2s)  Lock=0.00s (0s)  Rows=5.8 (46), root[root]@localhost
  select * from t100w limit N,N
Count: 7  Time=4.55s (31s)  Lock=0.00s (0s)  Rows=5.1 (36), root[root]@localhost
  select id ,count(num) from t100w group by id limit N
Count: 4  Time=2.73s (10s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select k2 ,sum(id) from t100w group by k2,k1 limit N
Count: 3  Time=1.22s (3s)  Lock=0.00s (0s)  Rows=7.0 (21), root[root]@localhost
  select k1,count(id) from t100w group by k1 limit N
Count: 2  Time=4.40s (8s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select id ,count(k2) from t100w group by id limit N