开启binlog
show binary logs 检查bin log是否开启,Mac OS系统安装完Mysql没有my.cnf配置文件,需要自己新建,我的Mysql配置文件放在/etc/my.cnf,将binlog开启,默认如果不给值的话,log-bin 的会以mysqld-bin 为索引,创建mysqld-bin.00001等;重启mysql。
定时备份与还原
一般我们会对mysql数据库做定时备份,但是不能够满足我们的需求,因为可能会丢失数据,如果我们在每天的早上4点进行定时数据备份,在当天晚上9点,数据库挂了,此时数据库只能备份到当天4点之前的所有数据,4点-9点的数据没办法通过这种方式恢复,丢失了,这肯定是不能容忍的,通过二进制日志文件可以满足我们的需求,只要不是服务器、硬盘问题,都能通过这种方式,进行数据恢复。我们先来看下对数据库的完全备份。
mysqldump -uroot -ppassword test > /Users/ksmaster/GitHub/mysql_backup/test_DB0722-10:16.sql
本地生成test_DB0722-10:16.sql文件之后,假如数据库数据被人为无意损坏或是数据表或库被误删,我们都能够通过生成的sql文件进行数据恢复,恢复当天4点之前的数据状态。假如此处是把整个库误删了,恢复如下:
mysql -uroot -ppassword test < /Users/ksmaster/GitHub/mysql_backup/test_DB0722-10:16.sql
利用二进制日志(binlog)还原
我预先创建了一个新的数据库test,表user,之后查看二进制日志文件,因为我的是本地数据库,二进制日志存放在/usr/local/mysql/data/目录下,如mysql-bin.000001
我这里有两个二进制文件,mysql-bin.000001和mysql-bin.000002,如果你没有执行过flush logs或者没有重启mysql,一般来说只有一个mysql-bin.000001,我们先查看下mysql-bin.000001
sudo mysqlbinlog --no-defaults mysql-bin.000001
截图如下:
可以看到有创建数据库、建表、插入数据等sql语句,还有at 417等,我们插入一些脏数据或者是错误数据,甚至是把数据库删除都可以,之后我们如何利用二进制文件进行数据恢复呢?
mysqlbinlog --no-defaults --stop-position="3263" mysql-bin.000001 > /Users/ksmaster/GitHub/mysql_backup/test_backup0722-11:30.sql
我们将mysql-bin.000001的sql语句导出到本地,这里我们只导出位于3263之前的语句,因为之后的数据是错误的我们不想要;然后进行恢复,我们先来看下到处到本地的sql语句是怎样的:
这里贴代码的话,无用的代码太多,只截取部分看看是什么样子的,其实就是执行过的sql语句,如果我们把数据误删了,我们可以利用导出的sql语句进行数据恢复:
mysql -uroot -ppassword < /Users/ksmaster/GitHub/mysql_backup/test_backup0722-11\:30.sql
通过这种方式我们可以比较灵活的恢复自己想要的数据,并且没有丢失数据,只要二进制文件在硬盘没有损坏,都能恢复我们想要的数据。
总结
如果允许数据丢失,可以直接使用mysqldump直接恢复,否则可以通过二进制文件进行恢复。在这里突然想到我去年还在实习的时候,当时项目刚开始,主力成员加上我和技术经理就三个人,有次我打开了mysql workbench,有可能是我不小心碰到键盘了还是别人搞得,整个数据库表全没了...庆幸的是还好是开发环境,不是生产环境..到底是不是我搞的我到现在也不知道是不是我搞得,然后另一个同事就着急了,工作这么忙项目一个月之后上线,建表造数据这又得浪费一些时间,摊谁身上谁都不乐意,于是他去找另一个同事,看能不能恢复,最后也没搞定。现在看来其实也非常简单了,找到开发服务器上的二进制日志文件就能恢复了,最后他又一个一个表开始建的,谁叫我当初又蠢又笨...