大牛们,刚接触MySQL,对MySQL操作不太熟练。现在遇到数据误删除的问题,查了资料,很遗憾没找到具体的解决方法,都是一两句话带过,也没有个详细的过程。只有在这里向大牛们请教了。
操作系统版本:CentOS release 5.10 (Final) 32位
MySQL版本:5.5.10-log
误删除之前,我已经使用mysqldump备份了t表。
mysqldump -uroot -p123456 wzcp t > t.sql
而且启用了log-bin日志模式
log-bin=/home/mysql/mysql/data/bin-log/mysql-bin
binlog_format=row
具体操作如下:
mysql> select * from t;
+------+---------+
| id | name |
+------+---------+
| 1 | Thomas |
| 2 | Lucy |
| 3 | Jack |
| 4 | Goodman |
| 5 | Alisen |
| 7 | John |
| 8 | Scott |
+------+---------+
8 rows in set (0.00 sec)
mysql> delete from t where id = 8;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+------+---------+
| id | name |
+------+---------+
| 1 | Thomas |
| 2 | Lucy |
| 3 | Jack |
| 4 | Goodman |
| 5 | Alisen |
| 7 | John |
+------+---------+
7 rows in set (0.01 sec)
binlog日志内容是这样的:
# at 1332
#140225 14:50:00 server id 1 end_log_pos 1400 Query thread_id=77 exec_time=0 error_code=0
SET TIMESTAMP=1393311000/*!*/;
BEGIN
/*!*/;
# at 1400
#140225 14:50:00 server id 1 end_log_pos 1489 Query thread_id=77 exec_time=0 error_code=0
SET TIMESTAMP=1393311000/*!*/;
delete from t where id = 8
/*!*/;
# at 1489
#140225 14:50:00 server id 1 end_log_pos 1516 Xid = 653
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
http://database.51cto.com/art/201212/374563.htm这个网友给了解决方案,但我按照他这个方式,发现日志内容不对,日志里只有delete from t where id = 8这样的数据,没有具体的列名、数据类型、值等等。
请问大牛,到底这条数据应该如何恢复呢?谢谢