[tr=inherit][backcolor=inherit]本文主要描述了MySQL遭到攻击篡改数据，利用从库的备份和主库的Binlog进行不完全恢复。 [backcolor=inherit][img=578,307]file:///C:/Users/Administrator/AppData/Local/YNote/data/huichenghui123@163.com/2a6b4649313b4b74aad3d12e5b34ab29/jjmc2mf0o7jd.png[/img]一、发现问题[backcolor=inherit]今天是2014-09-26，开发大清早就说昨晚数据库遭到了攻击。数据库中某文章表的文章内容字段遭到篡改，全部改成了同一篇文章。[backcolor=inherit]通过查看日制 发现 数据是在 2014-09-25 21:53:57 遭到篡改。
二、解决方法[backcolor=inherit]这个库我们是每天凌晨备份，保留30天的备份。主库的Binlog保留时间为7天。[backcolor=inherit]因此很容易想到的方法是将从库2014-09-25凌晨的备份拿出来恢复，然后通过主库的Binlog通过时间段来筛选出凌晨至2014-09-25 21:53:56的所有更改，之后的数据，经业务确认，可以舍弃掉。或者后面再通过其他方法慢慢将这部分数据找出来。但是当务之急，是立马恢复数据库。三、找备份及时间点[backcolor=inherit]在备份的从库上检查备份：[list=1]
[*][backcolor=inherit]crontab [backcolor=inherit]-[backcolor=inherit]l
[*][backcolor=inherit]#0 3 * * * /data/opdir/mysqlbak/backup_mysqldump.sh 6084 >> /data/opdir/mysqlbak/6084/mysql-bakup.log 2>&1
[backcolor=inherit]发现备份任务让注释了[backcolor=inherit]查看备份文件：[list=1]
[*][backcolor=inherit][[backcolor=inherit]root@localhost [backcolor=inherit]6084[backcolor=inherit]]#[backcolor=inherit] ll
[*][backcolor=inherit]total [backcolor=inherit]128
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]25[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140825
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]26[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140826
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]27[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140827
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]28[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140828
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]29[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140829
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]30[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140830
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Aug[backcolor=inherit][backcolor=inherit]31[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140831
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]1[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140901
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]2[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140902
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]3[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140903
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]4[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140904
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]5[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140905
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]6[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140906
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]7[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140907
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]8[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140908
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]9[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140909
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]10[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140910
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]11[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140911
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]12[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140912
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]13[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140913
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]14[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140914
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]15[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140915
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]16[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140916
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]17[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140917
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]18[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]14[backcolor=inherit][backcolor=inherit]20140918
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]19[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]14[backcolor=inherit][backcolor=inherit]20140919
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]20[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140920
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]21[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]13[backcolor=inherit][backcolor=inherit]20140921
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]22[backcolor=inherit][backcolor=inherit]03[backcolor=inherit]:[backcolor=inherit]14[backcolor=inherit][backcolor=inherit]20140922
[*][backcolor=inherit]drwxr[backcolor=inherit]-[backcolor=inherit]xr[backcolor=inherit]-[backcolor=inherit]x [backcolor=inherit]2[backcolor=inherit] root root [backcolor=inherit]4096[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]23[backcolor=inherit][backcolor=inherit]18[backcolor=inherit]:[backcolor=inherit]33[backcolor=inherit][backcolor=inherit]20140923
[*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]r[backcolor=inherit]--[backcolor=inherit]r[backcolor=inherit]--[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] root root [backcolor=inherit]5475[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]23[backcolor=inherit][backcolor=inherit]18[backcolor=inherit]:[backcolor=inherit]33[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bakup[backcolor=inherit].[backcolor=inherit]log
[backcolor=inherit]备份只到20140923日，下午18:33分。[backcolor=inherit]备份日志最后一段截取：[list=1]
[*][backcolor=inherit]tail [backcolor=inherit]-[backcolor=inherit]n [backcolor=inherit]5[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bakup[backcolor=inherit].[backcolor=inherit]log
[*][backcolor=inherit]deleting backup of [backcolor=inherit]30[backcolor=inherit] days ago [backcolor=inherit]--[backcolor=inherit][backcolor=inherit]20140824[backcolor=inherit]
[*][backcolor=inherit]2014[backcolor=inherit]-[backcolor=inherit]09[backcolor=inherit]-[backcolor=inherit]23[backcolor=inherit] [backcolor=inherit]18[backcolor=inherit]:[backcolor=inherit]19[backcolor=inherit]:[backcolor=inherit]12[backcolor=inherit][backcolor=inherit]begin[backcolor=inherit] backup [backcolor=inherit]...
[*][backcolor=inherit]20140824[backcolor=inherit] deleted OK
[*][backcolor=inherit]2014[backcolor=inherit]-[backcolor=inherit]09[backcolor=inherit]-[backcolor=inherit]23[backcolor=inherit] [backcolor=inherit]18[backcolor=inherit]:[backcolor=inherit]33[backcolor=inherit]:[backcolor=inherit]43[backcolor=inherit][backcolor=inherit]end[backcolor=inherit] backup [backcolor=inherit]...
[backcolor=inherit]因为这些表是在从库备份的，而且表都是MyiSAM的表。查看备份脚本，是先Stop Slave之后，才开始备份，因此从备份脚本输出的日志中找到备份开始的时间是：[indent]2014-09-23 18:19:12[/indent][backcolor=inherit]通过：[indent]Drwxr-xr-x 2 root root 4096 Sep 23 18:33 20140923[/indent][backcolor=inherit]可看到结束时间是：2014-09-23 18:33:00[backcolor=inherit]现在考虑到底是以备份开始的时间：2014-09-23 18:19:12 为Start-DateTime还是以2014-09-23 18:33:00 为Start-DateTime。[backcolor=inherit]前面 提到备份脚本是从库进行备份的，是在2014-09-23 18:19:12开始的，在这个时刻备份开始，执行了Stop Slave；因此整个备份的状态反映的是从库2014-09-23 18:19:12 这个时间的状态。而且通过监控可以看到在这个时间点，从库的延迟为0，因此可以认为这个备份就是 主库在这个时间的备份。[backcolor=inherit]NOTES： [backcolor=inherit]（有人可能会因为从库上有Binlog，从库也会接受主库的Binlog之类的机制而造成混淆。这里要结合我们具体的备份方式和恢复方式来看，以选出正确的时间点。）[backcolor=inherit]前面提到通过日志查到遭到篡改的时间为：2014-09-25 21:53:57，因此可以将2014-09-25 21:53:56作为Stop-DateTime[backcolor=inherit]因此Binlog命令应该是这样：[list=1]
[*][backcolor=inherit]mysqlbinlog [backcolor=inherit]--[backcolor=inherit]database[backcolor=inherit]=[[backcolor=inherit]db_name[backcolor=inherit]][backcolor=inherit][backcolor=inherit]--[backcolor=inherit]start[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-23 18:19:12'[backcolor=inherit][backcolor=inherit]--[backcolor=inherit]stop[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-25 21:53:56'[backcolor=inherit][backcolor=inherit][[backcolor=inherit]binlog_name[backcolor=inherit]][backcolor=inherit][backcolor=inherit]>[backcolor=inherit] binlog_name0000x[backcolor=inherit].[backcolor=inherit]sql
四、具体的恢复操作[backcolor=inherit]清楚了这些，具体的操作就简单了：1.从备份机拷贝备份：[list=1]
[*][backcolor=inherit]scp [backcolor=inherit]<备份机[backcolor=inherit]IP[backcolor=inherit]>:[backcolor=inherit]/data/[backcolor=inherit]MySQLbak[backcolor=inherit]/[backcolor=inherit]20140923[backcolor=inherit]/[backcolor=inherit]20140923.db[backcolor=inherit]_name[backcolor=inherit].[backcolor=inherit]gz [backcolor=inherit]<恢复测试机[backcolor=inherit]IP[backcolor=inherit]>:[backcolor=inherit]/data/[backcolor=inherit]opdir[backcolor=inherit]/[backcolor=inherit]20140926
2.恢复测试机 解压：[list=1]
[*][backcolor=inherit]gunzip [backcolor=inherit]20140923.db[backcolor=inherit]_name[backcolor=inherit].[backcolor=inherit]gz
3.恢复测试机导入(测试恢复库中之前没有db_name这个库)：[list=1]
[*][backcolor=inherit]mysql [backcolor=inherit]-[backcolor=inherit]uroot [backcolor=inherit]-[backcolor=inherit]pxxxxxx [backcolor=inherit]-[backcolor=inherit]S [backcolor=inherit]/[backcolor=inherit]tmp[backcolor=inherit]/[backcolor=inherit]mysql[backcolor=inherit].[backcolor=inherit]sock [backcolor=inherit]<[backcolor=inherit][backcolor=inherit]20140923.db[backcolor=inherit]_name 4.将主库的Binlog拷贝到恢复测试机：[backcolor=inherit]查看主库Binlog[list=1] [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]87669492[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]23[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000469 [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]268436559[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]23[backcolor=inherit][backcolor=inherit]04[backcolor=inherit]:[backcolor=inherit]20[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000470 [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]268435558[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]23[backcolor=inherit][backcolor=inherit]17[backcolor=inherit]:[backcolor=inherit]32[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000471 [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]37425262[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]24[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000472 [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]137389819[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]25[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000473 [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]147386521[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]26[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000474 [backcolor=inherit] 我们需要的Binlog时间段为：2014-09-23 18:28:00 至 2014-09-25 21:53:56 因此只需要：[list=1] [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]37425262[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]24[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000472[backcolor=inherit] [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]137389819[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]25[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000473[backcolor=inherit] [*][backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]-[backcolor=inherit]rw[backcolor=inherit]----[backcolor=inherit][backcolor=inherit]1[backcolor=inherit] mysql mysql [backcolor=inherit]147386521[backcolor=inherit][backcolor=inherit]Sep[backcolor=inherit][backcolor=inherit]26[backcolor=inherit][backcolor=inherit]00[backcolor=inherit]:[backcolor=inherit]00[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000474 [backcolor=inherit]将这3个Binlog Copy过去：[list=1] [*][backcolor=inherit]scp mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000472[backcolor=inherit][backcolor=inherit]<恢复测试机[backcolor=inherit]IP[backcolor=inherit]>:[backcolor=inherit]/data/[backcolor=inherit]opdir[backcolor=inherit]/[backcolor=inherit]20140926[backcolor=inherit]
[*][backcolor=inherit]scp mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000473[backcolor=inherit][backcolor=inherit]<恢复测试机[backcolor=inherit]IP[backcolor=inherit]>:[backcolor=inherit]/data/[backcolor=inherit]opdir[backcolor=inherit]/[backcolor=inherit]20140926[backcolor=inherit]
[*][backcolor=inherit]scp mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000474[backcolor=inherit][backcolor=inherit]<恢复测试机[backcolor=inherit]IP[backcolor=inherit]>:[backcolor=inherit]/data/[backcolor=inherit]opdir[backcolor=inherit]/[backcolor=inherit]20140926
5.使用MySQLBinlog 生成SQL脚本：[list=1]
[*][backcolor=inherit]mysqlbinlog [backcolor=inherit]--[backcolor=inherit]database[backcolor=inherit]=[[backcolor=inherit]db_name[backcolor=inherit]][backcolor=inherit][backcolor=inherit]--[backcolor=inherit]start[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-23 18:19:12'[backcolor=inherit][backcolor=inherit]--[backcolor=inherit]stop[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-25 21:53:56'[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000472[backcolor=inherit][backcolor=inherit]>[backcolor=inherit][backcolor=inherit]472.SQL
[*][backcolor=inherit]mysqlbinlog [backcolor=inherit]--[backcolor=inherit]database[backcolor=inherit]=[[backcolor=inherit]db_name[backcolor=inherit]][backcolor=inherit][backcolor=inherit]--[backcolor=inherit]start[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-23 18:19:12'[backcolor=inherit][backcolor=inherit]--[backcolor=inherit]stop[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-25 21:53:56'[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000473[backcolor=inherit][backcolor=inherit]>[backcolor=inherit][backcolor=inherit]473.SQL
[*][backcolor=inherit]mysqlbinlog [backcolor=inherit]--[backcolor=inherit]database[backcolor=inherit]=[[backcolor=inherit]db_name[backcolor=inherit]][backcolor=inherit][backcolor=inherit]--[backcolor=inherit]start[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-23 18:19:12'[backcolor=inherit][backcolor=inherit]--[backcolor=inherit]stop[backcolor=inherit]-[backcolor=inherit]datetime[backcolor=inherit]=[backcolor=inherit]'2014-09-25 21:53:56'[backcolor=inherit] mysql[backcolor=inherit]-[backcolor=inherit]bin[backcolor=inherit].[backcolor=inherit]000474[backcolor=inherit][backcolor=inherit]>[backcolor=inherit][backcolor=inherit]474SQL
6.Binlog生成的SQL脚本导入：[backcolor=inherit]待20140923.db_name导入到恢复测试库之后，将MySQLBinlog生成的SQL脚本导入到数据库中：[list=1]
[*][backcolor=inherit]mysql [backcolor=inherit]-[backcolor=inherit]uroot [backcolor=inherit]-[backcolor=inherit]pxxxxxx [backcolor=inherit]-[backcolor=inherit]S [backcolor=inherit]/[backcolor=inherit]tmp[backcolor=inherit]/[backcolor=inherit]mysql[backcolor=inherit].[backcolor=inherit]sock db_name [backcolor=inherit]<[backcolor=inherit][backcolor=inherit]472.sql[backcolor=inherit] [*][backcolor=inherit]mysql [backcolor=inherit]-[backcolor=inherit]uroot [backcolor=inherit]-[backcolor=inherit]pxxxxxx [backcolor=inherit]-[backcolor=inherit]S [backcolor=inherit]/[backcolor=inherit]tmp[backcolor=inherit]/[backcolor=inherit]mysql[backcolor=inherit].[backcolor=inherit]sock db_name [backcolor=inherit]<[backcolor=inherit][backcolor=inherit]473.sql[backcolor=inherit] [*][backcolor=inherit]mysql [backcolor=inherit]-[backcolor=inherit]uroot [backcolor=inherit]-[backcolor=inherit]pxxxxxx [backcolor=inherit]-[backcolor=inherit]S [backcolor=inherit]/[backcolor=inherit]tmp[backcolor=inherit]/[backcolor=inherit]mysql[backcolor=inherit].[backcolor=inherit]sock db_name [backcolor=inherit]<[backcolor=inherit][backcolor=inherit]474.sql 7.导入完成后检查数据正确性：[backcolor=inherit]大致看一下数据的情况，然后可以通过时间字段来看一下情况：[list=1] [*][backcolor=inherit]mysql[backcolor=inherit]>[backcolor=inherit][backcolor=inherit]select[backcolor=inherit] max[backcolor=inherit]([backcolor=inherit]createtime[backcolor=inherit]),[backcolor=inherit]max[backcolor=inherit]([backcolor=inherit]updatetime[backcolor=inherit])[backcolor=inherit][backcolor=inherit]from[backcolor=inherit] table_name[backcolor=inherit];
[*][backcolor=inherit]+-----------------+-----------------+
[*][backcolor=inherit]|[backcolor=inherit] max[backcolor=inherit]([backcolor=inherit]createtime[backcolor=inherit])[backcolor=inherit][backcolor=inherit]|[backcolor=inherit] max[backcolor=inherit]([backcolor=inherit]updatetime[backcolor=inherit])[backcolor=inherit][backcolor=inherit]|
[*][backcolor=inherit]+-----------------+-----------------+
[*][backcolor=inherit]|[backcolor=inherit][backcolor=inherit]1411648043[backcolor=inherit][backcolor=inherit]|[backcolor=inherit][backcolor=inherit]1411648043[backcolor=inherit][backcolor=inherit]|
[*][backcolor=inherit]+-----------------+-----------------+
[*][backcolor=inherit]1[backcolor=inherit] row [backcolor=inherit]in[backcolor=inherit][backcolor=inherit]set[backcolor=inherit][backcolor=inherit]([backcolor=inherit]0.00[backcolor=inherit] sec[backcolor=inherit])
[backcolor=inherit]时间差不多为 晚上20:27了[backcolor=inherit]这个判断，作为DBA，查看部分数据，只能起到辅助作用，具体的需要 到底是否OK，需要业务开发的人来判断。[backcolor=inherit]经过业务开发确认后，即可将该数据导出后，再导入到线上主库中。8、将该库导出，并压缩：[list=1]
[*][backcolor=inherit]mysqldump [backcolor=inherit]-[backcolor=inherit]uroot [backcolor=inherit]-[backcolor=inherit]pxxxxxx [backcolor=inherit]-[backcolor=inherit]S [backcolor=inherit]/[backcolor=inherit]tmp[backcolor=inherit]/[backcolor=inherit]mysql[backcolor=inherit].[backcolor=inherit]sock [backcolor=inherit]-[backcolor=inherit]q db_name table_name [backcolor=inherit]>[backcolor=inherit] table_name[backcolor=inherit].[backcolor=inherit]sql
[backcolor=inherit]压缩：[list=1]
[*][backcolor=inherit]gzip table_name[backcolor=inherit].[backcolor=inherit]sql
[backcolor=inherit]scp 到主库 （复制的时候，请将网络因素考虑进去，确认不会占用过多带宽而影响其他线上业务）9.恢复测试的数据导入到线上主库中：[backcolor=inherit]线上主库操作：[backcolor=inherit]操作之前，最好让开发把应用业务那段先暂停，否则可能会影响导入。比如这个表示MyISAM的，应用那边如果不听有update进来，就会阻塞数据导入。[backcolor=inherit]a、主库将原始被篡改的表改名：（不要上来就drop，先rename，后续确认没问题了再考虑drop，因为很多问题不是一瞬间就能全部反映上来的）[list=1]
[*][backcolor=inherit]rename table_name to old_table_name[backcolor=inherit];
[backcolor=inherit]b、解压：[list=1]
[*][backcolor=inherit]gunzip [backcolor=inherit]-[backcolor=inherit]d table_name[backcolor=inherit].[backcolor=inherit]sql[backcolor=inherit].[backcolor=inherit]gz
[backcolor=inherit]c、导入新表数据：[list=1]
[*][backcolor=inherit]mysql [backcolor=inherit]-[backcolor=inherit]uroot [backcolor=inherit]-[backcolor=inherit]pxxxxxx [backcolor=inherit]-[backcolor=inherit]S [backcolor=inherit]/[backcolor=inherit]tmp[backcolor=inherit]/[backcolor=inherit]mysql[backcolor=inherit].[backcolor=inherit]sock db_name [backcolor=inherit]<[backcolor=inherit] table_name[backcolor=inherit].[backcolor=inherit]sql [backcolor=inherit]后面就需要开发来进一步验证数据是否 OK 了。 验证没问题后，再启动应用程序。