mysql 数据库恢复实例

mysql 数据库恢复实例

2011年10月17日由于误把 xen server 服务器电源拔掉（~~!!）,导致xen server 上的 虚拟机  zabbix 数据库出错，虽然不是很重要（用来监控公司内网网关服务器），但是监控一年的数据不能就这么没了，想到zabbix数据库还有备份的sql 文件与mysql 二进制日志文件，尝试下恢复mysql 数据库，成功并且恢复的过程非常简单！

恢复过程

1. #详细步骤
   
2.  
3. #1 记住误删除表（或者出错）的时间 例如 Oct 17 08:30:27 CST 2011 
4. #2 先 drop database zabbix ;  
5.    再 create database zabbix;(或者跳过直接执行第3步) 
6. #3 导入备份的sql文件  
7.    mysql -uroot -pdong zabbix < zabbix20111016.sql 
8. #4 如果有主从，停掉从服务器  
9.    例如 mysql>stop slave;  
10. #5 从二进制日志文件得到sql语句 
11. #20111017 
12.    mysqlbinlog --database=zabbix --stop-datetime="2011-10-17 08:30:00" mysql-bin.000158 > test.sql 
13. #6 恢复sql 文件 
14. #20110425 
15.    mysql -root -pdong zabbix < test.sql 
16. #20111017  
17.    mysql -root -pdong zabbix < test.sql  
18. #7 关于二进制日志文件 
19. 正常是每天17:59生成一个二进制日志文件，8:34是掉电后重启服务器生成的二进制日志文件 
20. -rw-rw---- 1 mysql mysql  269983630 Oct 14 17:59 mysql-bin.000155 
21. -rw-rw---- 1 mysql mysql  269744900 Oct 15 17:59 mysql-bin.000156 
22. -rw-rw---- 1 mysql mysql  270254094 Oct 16 17:59 mysql-bin.000157 
23. -rw-rw---- 1 mysql mysql  164263912 Oct 17 08:34 mysql-bin.000158 
24.  
25. #8 关于从库数据恢复（可选） 
26. -rw-rw---- 1 mysql mysql  270254094 Oct 16 17:59 mysql-bin.000157 
27. -rw-rw---- 1 mysql mysql  164263912 Oct 17 08:34 mysql-bin.000158 
28. -rw-rw---- 1 mysql mysql     953434 Oct 17 10:07 mysql-bin.000159 
29. -rw-rw---- 1 mysql mysql 1073749046 Oct 17 11:21 mysql-bin.000160 
30. -rw-rw---- 1 mysql mysql  414747331 Oct 17 12:00 mysql-bin.000161 
31. -rw-rw---- 1 mysql mysql   24960747 Oct 17 13:42 mysql-bin.000162 
32. -rw-rw---- 1 mysql mysql       6042 Oct 17 12:00 mysql-bin.index 
33.  
34. 主库,关闭了数据库以前旧的二进制文件全部删除掉，重启后将生成新的二进制日志文件,从000001开始 
35. /etc/init.d/mysqld stop 
36. tar czvf 20111017.tar.gz zabbix/ 
37. mkdir tmp 
38. mv mysql-bin.* tmp/ 
39. scp 20111017.tar.gz root@192.168.57.82:/root/ 
40. /etc/init.d/mysqld start 
41. ll 
42. -rw-r--r-- 1 root  root  362462977 Oct 17 14:42 20111017.tar.gz 
43. -rw-rw---- 1 mysql root     812700 Oct 17 14:45 cl3.test.com.err 
44. -rw-rw---- 1 mysql mysql         6 Oct 17 14:45 cl3.test.com.pid 
45. -rw-rw---- 1 mysql mysql  17638261 Oct 17 14:46 lowquery.log 
46. drwx------ 2 mysql root       4096 May  4 10:35 mysql 
47. -rw-rw---- 1 mysql mysql   7046279 Oct 17 15:17 mysql-bin.000001 
48. -rw-rw---- 1 mysql mysql        38 Oct 17 14:45 mysql-bin.index 
49. drwxr-xr-x 2 root  root       4096 Oct 17 14:42 tmp 
50. drwx------ 2 mysql mysql     12288 Oct 17 11:23 zabbix 
51.  
52.  
53. 从库，重置了slave从库信息，重新建立从库信息，由于主库二进制日志从新开始，所以 master_log_file='mysql-bin.000001',master_log_pos=1; 
54.  
55. /etc/init.d/mysqld stop 
56. mv /usr/local/mysql/var/zabbix /usr/local/mysql/var/zabbix_tmp 
57. tar zxvf 20111017.tar.gz -C /usr/local/mysql/var/ 
58. /etc/init.d/mysqld start 
59. mysql -uroot -p 
60. mysql> reset slave; 
61. mysql> change master to master_host='192.168.6.53',master_user='dongnan',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1; 
62. Query OK, 0 rows affected (0.01 sec) 
63.  
64. mysql> slave start; 
65. Query OK, 0 rows affected (0.00 sec) 
66.  
67. mysql> show slave status \G; 
68. *************************** 1. row *************************** 
69.                Slave_IO_State: Waiting for master to send event 
70.                   Master_Host: 192.168.6.53 
71.                   Master_User: dongnan
    
72.                   Master_Port: 3306 
73.                 Connect_Retry: 60 
74.               Master_Log_File: mysql-bin.000001 
75.           Read_Master_Log_Pos: 3249706 
76.                Relay_Log_File: zabbix-slave-relay-bin.000002 
77.                 Relay_Log_Pos: 3249851 
78.         Relay_Master_Log_File: mysql-bin.000001 
79.              Slave_IO_Running: Yes 
80.             Slave_SQL_Running: Yes 
81.  
82.  
83.  
84. #mysqlbinlog 其它 
85. #找到 2011-10-16 17:00:00 到 2011-10-16 17:58:59 这期间的sql语句 
86. mysqlbinlog --database=zabbix --start-datetime="2011-10-16 17:00:00" --stop-datetime="2011-10-16 17:58:59" mysql-bin.000157 > test2.sql 
87.  
88. du -sh test2.sql 
89. 16M test2.sql 
90.  
91. head test2.sql 
92. /*!40019 SET @@session.max_insert_delayed_threads=0*/; 
93. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
94. DELIMITER /*!*/; 
95. # at 4 
96. #111015 17:59:02 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.55-log created 111015 17:59:02 
97. BINLOG ' 
98. ZlmZTg8BAAAAZgAAAGoAAAAAAAQANS4xLjU1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
99. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC 
100. '/*!*/; 
101. # at 259091243 
102.  
103. tail test2.sql 
104. /*!*/; 
105. # at 270239069 
106. #111016 17:58:56 server id 1  end_log_pos 270239321     Query   thread_id=120   exec_time=0 error_code=0 
107. SET TIMESTAMP=1318759136/*!*/; 
108. insert into history_uint (itemid,clock,value) values (18532,1318759132,25427968),(18533,1318759133,43491328),(18534,1318759134,4308992),(18504,1318759134,15250739200),(18535,1318759135,0) 
109. /*!*/; 
110. DELIMITER ; 
111. # End of log file 
112. ROLLBACK /* added by mysqlbinlog */; 
113. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 
114.  
115. mysql -uroot -pdong zabbix < test2.sql 
116.  
117. #/usr/local/mysql/bin/mysqlbinlog --database=zabbix mysql-bin.000013 > test.txt 
118. #head -n 10 test.txt  
119. #/usr/local/mysql/bin/mysqlbinlog --start-datetime="2011-04-20 15:33:51" mysql-bin.000013 | /usr/local/mysql/bin/mysql -uroot -p 

结束

整个恢复过程无非就是导入前一天的备份的 sql 文件，然后按时间或者按position从二进制日志中得到想要的sql语句，再次导入数据库即可！如果有从库的话再导出一份数据到从库就可以了！

 本文转自 dongnan 51CTO博客，原文链接：http://blog.51cto.com/dngood/710322