mysql数据库通过二进制 -【恢复数据记录】
参考相关操作
mysql> flush logs; #滚动日志 [root@stu18 data]# cp mysql-bin.000001 /zhao/mysql-bin.000001 #复制导出二进制文件 [root@stu18 data]# mysqlbinlog --start-position=14203 --stop-position=14527 mysql-bin.000001 > /zhao/binlog_`date +%F_%H`.sql [root@stu18 data]# mysqlbinlog --start-position=187 mysql-bin.000004 > /zhao/lvmlogbin_2013-08-14/binlog.sql #日志实现增量备份 mysql> FLUSH TABLES WITH READ LOCK; # 只读锁 mysql> FLUSH LOGS; mysql> UNLOCK TABLES; #解锁 #恢复数据 [root@stu18 data]# mysql -uroot –pmypass #登录测试 mysql> SHOW DATABASES; #查看数据完整性，无测试表testtd使用二进制恢复 mysql> SET sql_log_bin=0 #关闭二进制日志 mysql> source /zhao/lvmlogbin_2013-08-14/binlog.sql; #二进制恢复 mysql> SHOW TABLES; #查看恢复结果]() mysql> SET sql_log_bin=1; #开启二进制日志
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);
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
