mysql数据库通过二进制 -【恢复数据记录】

1.修改配置文件

vi /etc/my.cnf

log-bin = binlog

systemctl restart mysqld

mysql -uroot -p123456

mysql> show variables like 'log_bin' ;

mysql> show VARIABLES like '%log_bin%';

ls /var/lib/mysql/

2.创建数据库

create database mytest;

use mytest;
create table t(a int PRIMARY key)ENGINE = INNODB DEFAULT CHARSET=utf8;

flush logs;


mysqlbinlog --no-defaults /var/lib/mysql/binlog.000001

[root@localhost ~]# ls /var/lib/mysql/

数据创建日志和记录日志

3.插入数据

use mytest;
insert into t select 1 union all select 2 union all select 3;
flush logs;

数据插入日志记录

5.删除数据库

drop database mytest;
flush logs;

删除记录日志

6.恢复数据

mysqlbinlog --no-defaults /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 | mysql -uroot -p123456

数据恢复成功

二. 按时间点恢复数据

create table t2(a int PRIMARY key)ENGINE=INNODB default CHARSET=utf8;

insert into t2 values(1),(2),(3),(4),(5);

删除数据

delete from t2 where a < 4;

flush logs;

其中创建记录和删除记录我们要恢复到pos997

删除库mytest，回到最原始的地方

drop database mytest;

按时间恢复记录

mysqlbinlog --no-defaults --start-position="4" --stop-position="997" /var/lib/mysql/binlog.000004  | mysql -uroot -p123456

数据恢复

mysqlbinlog --no-defaults --start-position="4" --stop-position="997" /var/lib/mysql/binlog.000004  | mysql -uroot -p123456
mysqlbinlog mysql_bin.000001 --start-datetime='2018/04/11 15:00:45' --stop-datetime='2018/04/11 15:01:35'
mysqlbinlog mysql_bin.000001 --start-position=1903 --stop-position=2020

https://m.jb51.net/article/111404.htm

时间点恢复表里可能没数据因为多个事务时间一样

mysqlbinlog --no-defaults --stop-datetime="2019-08-29 20:47:30" /var/lib/mysql/mysql-bin-master.000003 | mysql -uroot -p123456

第一恢复提示表存在在库中删除表再恢复就正常恢复了

[root@mysql115 ~]# mysqlbinlog /var/lib/mysql/mysql-bin-master.000003 --stop-position=1545750   | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1050 (42S01) at line 28137: Table 't' already exists
[root@mysql115 ~]# mysqlbinlog /var/lib/mysql/mysql-bin-master.000003 --stop-position=1545750   | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

drop table t;

按时间点恢复操作注意

1.表中有数据的时候刷新日志记录生成新的日志文件/var/lib/mysql/mysql-bin-master.000005

查看旧的bing日志 2019-08-30 16:25:46 硬化了日志

 mysqlbinlog /var/lib/mysql/mysql-bin-master.000004 | tail -n 50

删除数据库

delete from t where a=3;

此时可以看到删除记录和开始记录

[root@mysql115 ~]# mysqlbinlog /var/lib/mysql/mysql-bin-master.000005 | tail -n 50

恢复数据注意此处的binlog日志是用04的因为05这个时间是开始日志按时间恢复数据会是空的是不能恢复数据的

 mysqlbinlog --no-defaults --stop-datetime="2019-08-30 16:25:46" /var/lib/mysql/mysql-bin-master.000004 | mysql -uroot -p123456

两次恢复对比

如果日志太多过滤删除记录

mysqlbinlog /var/lib/mysql/mysql-bin-master.000005 | grep Delete_rows -C4

删除库操作过滤

mysqlbinlog /var/lib/mysql/mysql-bin-master.000004 | grep drop -C4

重要参考

https://www.cnblogs.com/kevingrace/p/5907254.html#4337479

https://www.cnblogs.com/kevingrace/p/5904800.html