mysql binlog 时间 set_MySQL Binlog二进制日志基于时间点恢复数据

Binlog时间恢复：

根据dump全量备份全量和二进制日志增量恢复数据到指定时间点。

模拟每天晚上的12点整进行mysqldump全量备份

在第二天早上 9点出现数据故障，需要基于全量备份恢复 12点到 9点的数据

当前为普通模式，没有开启GTID模式，否则恢复无效？暂定疑问？？？

模拟前天数据：

mysql> create database course charset utf8mb4;

Query OK, 1 row affected (0.01 sec)

mysql> use course

Database changed

mysql> create table temp(id int,name varchar(64));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into temp values(1,'a'),(2,'b');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

数据库全量备份：

当前时间为晚上12点整，进行了数据库全量备份。

[root@db01 ~]# mysqldump -uroot -p123456 -B course -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

模拟数据变化：

当前是前天晚上12点整 - 今天早上9点的数据变化。

mysql> insert into temp values(3,'a'),(4,'b');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> update temp set name='cc' where id=3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from temp;

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | cc |

| 4 | b |

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

4 rows in set (0.01 sec)

模拟删除course库：

早上9点时误操作删除了course数据库

mysql> drop database course;

Query OK, 1 row affected (0.03 sec)

全量恢复：

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/full.sql

mysql> use course

Database changed

mysql> select * from temp;

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

| id | name |

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

| 1 | a |

| 2 | b |

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

2 rows in set (0.00 sec)

增量恢复

利用二进制日志恢复数据到早上9点的状态

在截断日志时要把最后一个事务的commit操作也包含进去，不然数据恢复失败。

[root@db01 data]# mysqlbinlog -v --start-datetime="2019-11-05 00:00:00" --stop-datetime="2019-11-05 09:00:00" binlog.000001 | mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from temp;

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | cc |

| 4 | b |

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

4 rows in set (0.00 sec)

重要(部分恢复)：

如果一个生产库中的某一个表损坏了，那就截取对应库的binlog日志，然后在一个新数据库实例中恢复整个库，最后把损坏的表导出，然后再导入到生产库

#导出db库中的t1表

mysqldump -uroot -predhat db t1 >/tmp/t1.sql

删除binlog：

reset master

清空所有bin1og,从000001开始重新记录,一般是在全备份之后可以执行。

(2)过期时间设置

SET GLOBAL expire logs days =7;

注意:这个时间设置,要考虑,全备的周期。

(3)删除到几天之前

PURGE BINARY LOGS BEFORE now()-INTERVAL 3 day;

(4)根据文件名删除日志:

PURGE BINARY LOGS TO 'mysql-bin.000010;