MySQL数据库运维之数据恢复的方法
之前三篇文章分别介绍了MySQL数据库常见的备份方法，其中包括逻辑备份和物理备份，本篇将总结一下MySQL数据库的数据恢复相关内容。这些数据恢复方案在之前备份内容介绍时，此处总结一下恢复方案，并结合数据库的二进制日志做下数据恢复的示范！
一、恢复方案
1、数据量不是特别大，可以将mysqldump命令备份的数据使用mysql客户端命令或者source命令完成数据的恢复；
2、使用Xtrabackup完成数据库的物理备份恢复，期间需要重启数据库服务；
3、使用LVM快照卷完成数据库物理备份恢复，期间需要重启数据库服务；
二、使用mysqlbinlog进行时间点恢复
1、介绍
mysqlbinlog是一个从二进制日志中读取语句的工具，在mysql安装完成之后自带的。
2、二进制日志恢复原理
当使用mysqldump对数据库进行备份时，生成的备份文件中包含了数据库DML操作时的时间点以及备份时的二进制日志位置信息，如果单库，可以从某个时间点开始，进行时间点恢复；如果是主从架构，可以根据备份时的--master-data=2和--single-transaction，完成根据时间点或者位置点的恢复。
3、二进制日志恢复示例
（1）单库恢复示例
创建数据库，并插入测试数据
mysql>SHOWCREATEDATABASEtest_db;
mysql>CREATETABLE`student`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(20)NOTNULL,
`age`tinyint(4)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8;
mysql>INSERTINTOstudent(name,age)VALUES('Jack',23),('Tomcat',24),('XiaoHong',22),('ZhangFei',29);
使用mysqldump进行全量备份，备份时滚动日志，同时记住二进制日志文件名称和日志的位置点
[root@WB-BLOG~]#mysqldump-uroot-proot-h127.0.0.1-P3306--databasestest_db--single-transaction--triggers--routines--flush-logs--events>/tmp/test_db.sql [root@WB-BLOG~]#mysql-e"showbinarylogs">bin_pos_`date+%F`.out
此时查看二进制日志文件名称和日志点位置如下
mysql>SHOWBINARYLOGS; +------------------+-----------+ |Log_name|File_size| +------------------+-----------+ |mysql-bin.000001|1497| |mysql-bin.000002|397| +------------------+-----------+ 2rowsinset(0.00sec)
使用了一段时间，不小心误操作，执行了如下的语句，将数据库中的数据全部修改了
mysql>UPDATESTUDENTSETname='admin';
过了一段时间，可能是几分钟，也可能是几个小时，有人反映网站登录有问题了，查看发现好多数据被误修改，而这段时间内，还一直有写入操作，如又新增了如下的记录
mysql>INSERTINTOstudent(name,age)VALUES('Hbase',23),('BlackHole',30);
此时需要恢复数据，首先为了防止数据继续写入，可以先锁表，暂停写入业务，通知用户系统维护，然后执行如下操作：
#登录数据库，锁表，此时表只能读，不能写 mysql>USEtest_db; mysql>LOCKTABLEstudentREAD; #然后重新（注意是重新打开）打开一个session窗口，否则会话处出之后，锁就会释放。然后压缩备份现有数据和二进制日志文件 [root@WB-BLOGmysql_logs]#tarzcvfmysql_data.tar.gz/mysql_data/* [root@WB-BLOGmysql_logs]#tarzcvfmysql_bin.tar.gz/mysql_logs/* #导入最近备份的一次全备数据 [root@WB-BLOG~]#mysql-uroot-proot-h127.0.0.1-P3306/tmp/tmp.sql #使用vim编辑器编辑这个sql文件，找到其中的未加条件的UPDATE语句，然后将其删掉，然后将删掉UPDATE语句之后的sql脚本内容导入到数据库中 [root@WB-BLOGbin]#vim/tmp/tmp.sql use`test_db`/*!*/; SETTIMESTAMP=1522088753/*!*/; updatestudentsetname='admin'#删掉这一句 [root@WB-BLOGbin]#mysql-uroot-proot-h127.0.0.1-P3306UNLOCKTABLES;
（2）主从架构数据恢复示例
环境
主库：192.168.199.10(node01)
从库：192.168.199.11(node02)
首先停止从库的SQL线程，然后在从库上全备数据，并输入"SHOWSLAVESTATUS"信息到备份文件中，"SHOWSLAVESTATUS"的输出信息中记录了当前应用到了主库的哪个位置点的信息
#登录从库，然后关闭SQL线程 mysql>STOPSLAVESQL_THREAD; QueryOK,0rowsaffected(0.01sec) #然后记录从库中当前应用的主库的二进制日志文件信息 [root@node02mysql_data]#mysql-e"SHOWSLAVESTATUS\G">slave_`date+%F`.info [root@node02mysql_data]#mysqldump-uroot-proot-h127.0.0.1-P3306--databasestest_db--routines--triggers--single-transaction>/tmp/mysql_test_db_`date+%F`.sql
在从库上备份完成之后，重新启动从库的SQL线程
mysql>STARTSLAVESQL_THREAD; QueryOK,0rowsaffected(0.01sec)
启动SQL线程之后，备份这段时间内在主库上的DML操作会重新同步到从库上。假如在主库上发生了一个误操作，没加条件更新了student表中的所有数据，导致了表中所有数据被修改，此时由于同步操作，从库也被修改了
#登录主库，修改数据库的对外用户，使其暂不提供服务，然后滚动日志 mysql>UPDATEmysql.userSETHost='127.0.0.1'WHEREUser='tomcat'; QueryOK,1rowsaffected(0.00sec) #刷新权限表 mysql>FLUSHPRIVILEGES; QueryOK,0rowsaffected(0.00sec) #滚动日志 mysql>FLUSHLOGS; QueryOK,0rowsaffected(0.01sec) #将从库备份的数据及备份时刻的从库slave信息传到主库上 [root@node02mysql_data]#scp/tmp/mysql_test_db_2018-06-24.sql192.168.199.10:/root/ [root@node02mysql_data]#scpslave_2018-06-24.infonode01:/root/
备份主库的数据目录和二进制日志文件目录
[root@node01mysql_logs]#tarzcvfmysql_master_data.tar.gz/mysql_data/* [root@node01mysql_logs]#tarzcvfmysql_logs.tar.gz/mysql_logs/*
导入从库最近一次备份的数据
[root@node01mysql_logs]#mysql-uroot-proot-h127.0.0.1-P3306查看备份时刻的从库中应用到的主库二进制日志文件名称及位置点
[root@node01mysql_logs]#cat/root/slave_2018-03-26.info Master_Log_File:master-bin.000002#备份时所应用的主库二进制日志文件名称 Read_Master_Log_Pos:395#备份时所应用的主库二进制日志文件的位置从该日志文件及日志点开始，将395日志点之后的日志文件转换为sql脚本，如果有多个二进制日志文件可以同时转换为sql脚本，如下所示
[root@node01mysql_logs]#mysqlbinlog/mysql_logs/master-bin.000002--start-position=395>/tmp/tmp.sql #将master-bin.000003,master-bin.000004,master-bin.000005合并到/tmp.sql文件中 [root@node01mysql_logs]#mysqlbinlog/mysql_logs/master-bin.00000{3,4,5}--start-position=395>/tmp/tmp.sql找到误操作的update语句，然后删除该语句，并将增量的sql脚本导入数据库
[root@node01mysql_logs]#vim/tmp/tmp.sql use`test_db`/*!*/; updatestudentsetname='admin'#删掉这一句 [root@node01mysql_logs]#mysql-uroot-proot-h127.0.0.1-P3306登录数据库，查看数据是否正常，被误修改的数据是否已经恢复，如果恢复，则在主库上全备数据，然后传到从库，完成从库恢复
[root@node01mysql_data]#mysqldump-uroot-proot-h127.0.0.1-P3306--databasestest_db--routines--triggers--single-transaction--master-date=1>/tmp/master_test_db_`date+%F`.sql [root@node01mysql_data]#scp/tmp/master_test_db_2018-06-24.sqlnode01:/root/ #如果从库设置了只读，需要先去掉只读限制 mysql>SETGLOBALread_only=OFF; QueryOK,0rowsaffected(0.00sec) #将数据导入从库 [root@node02mysql_logs]#mysql-uroot-proot-h127.0.0.1-P3306SETGLOBALread_only=ON; QueryOK,0rowsaffected(0.00sec)由于在主库上备份时添加了--master-date=1参数，所以从库导入之后，不需要重新执行changemaster操作。
登录从库，查看SHOWSLAVESTATUS信息是否正常，如果正常，登录主库，重新修改授权表，然后对外提供服务
mysql>UPDATEmysql.usersetHost='192.168.0.%'WHEREUser='tomcat'; mysql>FLUSHPRIVILEGES; QueryOK,0rowsaffected(0.00sec)执行完成之后，主从数据恢复完毕。
至此，数据恢复介绍完毕，上述介绍了使用全备加二进制日志实现单实例数据库和主从数据库的数据恢复过程，如有问题，欢迎评论指出。也希望大家多多支持毛票票。