一次数据库误update之后的数据恢复

工作需要，要对数据库（mysql）数据进行对比，写完测试脚本。想在表中修改一条数据，然后运行程序看看效果。正确的sql  update table1 set amount = 100 where id=123;。可是实际情况是没有写where条件，就按下了回车，shell很快返回“******条数据已经被修改”，晕了。还好，我不是DBA，我只有权限动测试库，可是测试库也是十几个人公用的。看了下时间21:30，其他都下班了，不着急自己慢慢搞。

没有备份

数据库误操作，数据恢复首先想到备份，测试库一般都是线上数据的copy，很少有备份，方法不通。

binlog全量恢复太困难

大致知道mysql的binlog日志，记录下所有的操作。关于binlog有几条命令很有用，记录下：

• show variables like 'log_%'; 查看binlog是否打开

    mysql> show variables like 'log_%';
    +----------------------------------------+----------------------------------------+
    | Variable_name                          | Value                                  |
    +----------------------------------------+----------------------------------------+
    | log_bin                                | ON                                     | 
    | log_bin_basename                       | /home/somename/mysql-bin       | 
    | log_bin_index                          | /home/somename/test/mysql-bin.index | 
    | log_bin_trust_function_creators        | OFF                                    | 
    | log_bin_use_v1_row_events              | OFF                                    | 
    | log_error                              | /home/somename/test/err.log         | 
    | log_output                             | FILE                                   | 
    | log_queries_not_using_indexes          | ON                                     | 
    | log_slave_updates                      | ON                                     | 
    | log_slow_admin_statements              | OFF                                    | 
    | log_slow_slave_statements              | OFF                                    | 
    | log_throttle_queries_not_using_indexes | 0                                      | 
    | log_warnings                           | 1                                      | 
    +----------------------------------------+----------------------------------------+
    13 rows in set (0.00 sec) 
  

• show binary logs; 查看mysql数据库中binlog文件列表(这些log基本上都很大)

    mysql> show binary logs;
    +------------------+------------+
    | Log_name         | File_size  |
    +------------------+------------+
    | mysql-bin.000067 | 1074104507 | 
    | mysql-bin.000068 | 1073900275 | 
    | mysql-bin.000069 | 1073742040 | 
    | mysql-bin.000070 | 1073742234 | 
    | mysql-bin.000071 | 1074693303 | 
    | mysql-bin.000072 | 1074476163 | 
    | mysql-bin.000073 | 1073743892 | 
    | mysql-bin.000074 | 1073742728 | 
    | mysql-bin.000075 |   76346728 | 
    +------------------+------------+
    9 rows in set (0.01 sec)
  

• `show master status \G;' 查看当前使用的binlog文件

    mysql> show master status \G;
    *************************** 1. row ***************************
               File: mysql-bin.000075
            Position: 76352042
        Binlog_Do_DB: 
    Binlog_Ignore_DB: 
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
  

• `show binlog events;' mysql控制台查看当前使用的binlong这条命令输入后，基本上都是满屏滚动的日志，将近1G的日志输出在mysql控制台中，慎重。这些日志记录了对数据库有增删改所有操作的sql、时间、行数等信息。
• show binlog events in 'mysql-bin.000002'; 查看指定的binlog。
• mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 从binlog恢复日志，mysqlbinlog不是mysql控制台命令，是mysql提供的恢复工具，需要在shell或者cmd下执行

由于没有权限登录到mysql这台机器，只能通过mysql连接执行命令。无法从数据库服务器直接拿到binlog日志。mysql提供－e参数（之前竟不知道），mysql -u root -p root -e "show binlog events" > bin.log将日志重定向到文件。

本机拿到了当前库的binlog，但是完全没有备份的情况下，使用binlog恢复数据，只能将该数据库从创建到现在产生的binlog全部执行一遍，这就纠结了，binlog太大；然后就是即使我们有耐心和时间执行binlog中的全部sql，我们也不能保证binlog从创建到现在没有人删除过，毕竟备份的日志，一般不会放太久 🙁

在mysql-bin.000075这个700M的日志中查到了那条sql，grep "update table1 set amount=100" mysql-bin.000075,不知道如何恢复了。

shell中的sql输出记录

在mysql控制台翻着之前的命令，突然看到之前执行过select * from table1;这样的命令，想到可以通过mysql控制台的日志，解析出需要的数据，再一条条update回去。

想着是在shell中直接找到select *的记录，但是这个shell窗口已经执行过一天的命令，且刚才执行了show binlog events，输出了近G的日志，在界面中使用查找基本卡死。将shell log导出外部文件，看了下1.7G！(mac电脑真是强悍，竟然在shell这个控制台中放了1.7G的东西，整体还不算太卡)

找到方法了开始干活：

1. 日志切割，split -b 100m mysql-bin.000075 将700M的日志，切成100M大小的。
2. 在切成的日志中，查找我们需要的记录 grep 'update table1 set amount=100' xsa (xsa是切割出来的一个文件)，定位到我们需要的那个
3. 使用head、cat等命令定位到我们需要的行数区间
4. sed命令将行数区间重定向到文件sed -n "N1,N2p" xsa > data.txt
5. 写java程序，解析data.txt，构造sql，一个一个update回去，呵呵。

操作数据库一定谨慎，涉及到增删改一定要小心。

Tags: javalinux代码技术自动化测试软件测试