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.
[root@localhost ~]# ls /var/lib/mysql/
数据创建日志和记录日志
3.插入数据
use mytest;
insert into t select union all select union all select ;
flush logs;
数据插入日志记录
5.删除数据库
drop database mytest;
flush logs;
删除记录日志
6.恢复数据
mysqlbinlog --no-defaults /var/lib/mysql/binlog. /var/lib/mysql/binlog. | mysql -uroot -p123456
数据恢复成功
二. 按时间点恢复数据
create table t2(a int PRIMARY key)ENGINE=INNODB default CHARSET=utf8; insert into t2 values(),(),(),(),();
flush logs;
其中创建记录和删除记录我们要恢复到pos997
删除库mytest，回到最原始的地方
drop database mytest;
按时间恢复记录
mysqlbinlog --no-defaults --start-position="" --stop-position="997" /var/lib/mysql/binlog. | mysql -uroot -p123456
数据恢复
mysqlbinlog --no-defaults --start-position="" --stop-position="" /var/lib/mysql/binlog. | mysql -uroot -p123456
mysqlbinlog mysql_bin. --start-datetime='2018/04/11 15:00:45' --stop-datetime='2018/04/11 15:01:35'
mysqlbinlog mysql_bin. --start-position= --stop-position=
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. --stop-position= | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR (42S01) at line : Table 't' already exists
[root@mysql115 ~]# mysqlbinlog /var/lib/mysql/mysql-bin-master. --stop-position= | 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. | tail -n
删除数据库
delete from t where a=;
此时可以看到删除记录和开始记录
[root@mysql115 ~]# mysqlbinlog /var/lib/mysql/mysql-bin-master.000005 | tail -n 50
恢复数据 注意此处的binlog日志是用的 因为05这个时间是开始日志 按时间恢复 数据会是空的是不能恢复数据的
mysqlbinlog --no-defaults --stop-datetime="2019-08-30 16:25:46" /var/lib/mysql/mysql-bin-master. | mysql -uroot -p123456
两次恢复对比
如果日志太多过滤删除记录
mysqlbinlog /var/lib/mysql/mysql-bin-master. | grep Delete_rows -C4
删除库 操作过滤
mysqlbinlog /var/lib/mysql/mysql-bin-master. | grep drop -C4
重要参考
https://www.cnblogs.com/kevingrace/p/5907254.html#4337479
https://www.cnblogs.com/kevingrace/p/5904800.html