mysql中bin-log日志备份以及恢复误删数据
mysql版本:5.7
系统:centos7.6
先登入mysql查看确定是否已经开启了bin-log日志
show variables like "%bin%";
如果log_bin为off则表示没有打开bin-log日志,则无法通过此方式恢复,如果为on则可以
打开bin-log日志的方式(配置文件的方式):
配置文件中添加:log_bin=/usr/local/mysql/data/mysql-bin.log
server-id=1
binlog_format=MIXED
然后保存退出,重启mysql
如果确定在误删除数据前打开了bin-log日志,则进去bin-log日志的目录,将bin-log日志转化成sql文件(需先将mysql停止,此期间不能使用数据库,防止新数据插入):
找到bin-log日志文件,假设bin-log日志文件为mysql-bin.000011 ,然后打开此文件,找到之前执行的那段删除数据的sql语句,并且把此文件中该sql语句以下的都删除,并且保存退出,然后执行:
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000011 >test.sql,注意:此处/usr/local/mysql是mysql的安装目录,执行之后就可以看到在此目录下会先生成一个test.sql文件
然后再启动mysql,并且登入mysql,然后将test.sql文件重新导入mysql中,source /usr/local/mysql/test.sql(也可以通过navicat等工具导入),此时就会发现数据已经恢复
补充:恢复备份数据
同时,bin-log日志也经常用于数据备份
数据备份一般有:物理备份(如直接备份安装之后的mysql整个目录,但是所占空间非常大);逻辑备份(一般就是备份sql语句了,比如bin-log日志,但是如果数据量特别大,就不怎么适合,恢复时间,成本特别高)
同样也可以分为:
冷备(cold backup):需要关mysql服务,读写请求均不允许状态下进行;
温备(warm backup): 服务在线,但仅支持读请求,不允许写请求;
热备(hot backup):备份的同时,业务不受影响。
还可以分为:
完全备份:full backup,备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据,不能单独使用,要借助完全备份,备份的频率取决于数据的更新频率。
一般的情况下,使用热备和完全备份与增量备份并行比较多,但是如果数据量比较大的话,一般是进行物理备份,或者直接通过读写分离,主从库的方式备份
完全备份+增量备份(一般是通过mysqldump以及bin-log日志来实现)
此备份一般是逻辑备份
逻辑备份 (mysqldump)
InnoDB引擎热备,MyISM温备
常用参数: -A:备份所有库
--databases db1 db2...:备份指定的多个库
--single-transaction:能够对InnoDB存储引擎实现热备。
-F:刷新binlog,如果binlog打开了,-F参数会在备份时自动刷新binlog进行切换 -d 参数,只备份数据库中表结构 -t 参数,只备份数据
database table 备份数据库中的某个表
比如:mysqldump -uroot -p123456 -F --single-transaction test info > info.sql(表示完全备份test库中的info表,存储在当前目录为info.sql,这个时间就可以在当前目录发现新生成了一个info.sql文件)
比如:我们可以每个周进行完全备份,完全备份之时通过-F刷新bin-log日志,重新生成一个bin-log日志文件来写入,然后每天通过bin-log日志进行增量备份,这样如果一旦数据不小心误删除了,则可以通过完全备份+增量备份的sql文件进行数据恢复
备注:完全备份的时间越短丢失的数据量越小,但是所占资源消耗也越大
同时如果数据量大也可通过物理备份,一般通过中间件如:xtrabackup 等插件