mysql清表恢复

       今天真惊险，帮同事订正数据，写了一条delete from的sql在客户端里，过了很久了，突然想查一下这张表，就把后面的where语句删掉，点击执行，发现没有数据查出来，才突然一惊，已经把表全清空了。悲了个剧啊，马上把连数据库的应用停了。然后开始找恢复的方法，以前没有处理过类似情况，找资料加操作共花了1小时，将该表数据全部恢复，才长吁了一口气。总结恢复步骤如下：

      1. 我们有数据库的定时备份，每天备份一次，所以只能先将数据恢复到昨天的状态。一般建一个定时任务或者crontab，执行以下脚本：

current_timestamp=`date +%s`
mysqldump -u<database_name> -h <database_ip> -<database_name> <database_pwd> | bzip2 -9 > /root/mysql/back_${current_timestamp}.sql.bz2

      这天每天会定时dump mysql的数据到sql文件中，bzip2 -d加压昨天的备份文件为sql文件，由于只删除了一张表，所以只需要将解压后的sql文件过滤一下，然后将昨天的数据恢复进去。

     2. 通过binlog将今天的数据变更恢复进去，先登录mysql -uroot -p登录到数据库执行

show variables like 'log_bin';
show master status;

查看log_bin是否打开，确认是ON状态。确认binlog文件

+--------------------------+-----------------+---------------------+---------------------------+
| File                           | Position      | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------+-----------------+---------------------+---------------------------+
| mysql-bin.000004 | 350712830 |                            |                                   |
+--------------------------+-----------------+---------------------+---------------------------+
1 row in set (0.00 sec)

然后vi /etc/my.cnf找到log-bin的目录，我的在/data/mysqldata1/binlog/mysql-bin执行

mysqlbinlog mysql-bin.000004  >/root/mysql-bin.log

然后vi /root/mysql-bin.log找到误操作的位置，查找表名，从文件尾开始找，如果是删除多条数据，肯定会有记录。

# at 360940585
#120904  2:23:33 server id 5  end_log_pos 360940653     Query   thread_id=18444 exec_time=1 error_code=0
SET TIMESTAMP=1346750613/*!*/;
SET @@session.autocommit=1/*!*/;
........
# at 361051157
#120904  2:23:33 server id 5  end_log_pos 361051184     Xid = 86734426
COMMIT/*!*/;

可恶的autocommit啊。执行以下命令后，会将本次binlog的变更信息往数据库里插入，由于只干掉了一张表，其他表的数据还在，所以报了一些主键冲突的语句，就没管了。于是将今天的数据变更恢复进去了。

[root@localhost var]# mysqlbinlog mysql-bin.000004 --stop-pos=360940585 |mysql -uroot -p  
Enter password:   
[root@localhost var]# mysqlbinlog mysql-bin.000004 --start-pos=361051184 |mysql -uroot -p  
Enter password:   

最后总结:定时备份+log恢复，将全表数据恢复，还好有定时备份的定时任务。否则只能恢复今天的数据变更，也蛮悲催的。