mysql使用binlog日志进行数据恢复

create database binlog charset utf8mb4;use binlog;create table t1(id int);insert into t1 values(1);commit;insert into t1 values(2);commit;insert into t1 values(3);commit;drop database binlog;恢复:找到起点和终

众生多匆忙

417人浏览 · 2020-11-18 22:49:19

众生多匆忙 · 2020-11-18 22:49:19 发布

create database binlog charset utf8mb4;
use binlog;
create table t1(id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
drop database binlog;

恢复:

找到起点和终点

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     2196 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看事件

mysql> show binlog events in 'mysql-bin.000002';
| mysql-bin.000002 |  980 | Query          |         6 |        1096 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 2098 | Query          |         6 |        2196 | drop database binlog

截取日志（看日志文件存放位置）

# mysqlbinlog --start-position=980 --stop-position=2098 /data/binlog/mysql-bin.000002 >/tmp/bak.sql

进行恢复

mysql> set sql_log_bin=0;//临时关闭二进制日志记录，退出mysql窗口可以恢复
mysql> source /tmp/bak.sql

验证数据

mysql> use binlog
Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

开启GTID功能的二进制日志管理
下面怎么恢复？

create database binlog charset utf8mb4;

use binlog;
create table t1(id int);

insert into t1 values(1);
commit;
insert into t1 values(2);
commit;

truncate table t1;

insert into t1 values(3);
commit;

drop database binlog;

基于position号恢复需要多次截取，找起点和终点过程很复杂。
什么是GTID （全局事务编号）
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’

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

说明：
DDL DCL，一条语句（事件）就是一个事务，占一个GTID号
DML：一个完整的事务(begin–》commit)，是一个事务，占一个GTID号

开启GTID
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld

查看本机GTID信息
mysql> create database gg;
mysql> show master status;
mysql> use gg;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;
drop database gg;

基于GTID截取二进制日志

mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql

跳过某些gtid不截取（跳过2和4）
mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' --exclude-gtids='ee956c61-9653-11e9-8518-000c29099eb6:2,ee956c61-9653-11e9-8518-000c29099eb6:4' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql