前一段时间由于误把 xen server 服务器电源拔掉(~~!!),导致xen server 上的 虚拟机 zabbix 数据库出错,虽然不是很重要(用来监控公司内网网关服务器),但是监控一年的数据不能就这么没了,想到zabbix数据库还有备份的sql 文件与mysql 二进制日志文件,尝试下恢复mysql 数据库,成功并且恢复的过程非常简单!
#详细步骤 #1 记住误删除表(或者出错)的时间 例如 Oct 17 08:30:27 CST 2011 #2 先 drop database zabbix ; 再 create database zabbix;(或者跳过直接执行第3步) #3 导入备份的sql文件 mysql -uroot -pdong zabbix < zabbix20111016.sql #4 如果有主从,停掉从服务器 例如 mysql>stop slave; #5 从二进制日志文件得到sql语句 #20111017 mysqlbinlog --database=zabbix --stop-datetime="2011-10-17 08:30:00" mysql-bin.000158 > test.sql #6 恢复sql 文件 #20110425 mysql -root -pdong zabbix < test.sql #20111017 mysql -root -pdong zabbix < test.sql #7 关于二进制日志文件 正常是每天17:59生成一个二进制日志文件,8:34是掉电后重启服务器生成的二进制日志文件 -rw-rw---- 1 mysql mysql 269983630 Oct 14 17:59 mysql-bin.000155 -rw-rw---- 1 mysql mysql 269744900 Oct 15 17:59 mysql-bin.000156 -rw-rw---- 1 mysql mysql 270254094 Oct 16 17:59 mysql-bin.000157 -rw-rw---- 1 mysql mysql 164263912 Oct 17 08:34 mysql-bin.000158 #8 关于从库数据恢复(可选) -rw-rw---- 1 mysql mysql 270254094 Oct 16 17:59 mysql-bin.000157 -rw-rw---- 1 mysql mysql 164263912 Oct 17 08:34 mysql-bin.000158 -rw-rw---- 1 mysql mysql 953434 Oct 17 10:07 mysql-bin.000159 -rw-rw---- 1 mysql mysql 1073749046 Oct 17 11:21 mysql-bin.000160 -rw-rw---- 1 mysql mysql 414747331 Oct 17 12:00 mysql-bin.000161 -rw-rw---- 1 mysql mysql 24960747 Oct 17 13:42 mysql-bin.000162 -rw-rw---- 1 mysql mysql 6042 Oct 17 12:00 mysql-bin.index 主库,关闭了数据库以前旧的二进制文件全部删除掉,重启后将生成新的二进制日志文件,从000001开始 /etc/init.d/mysqld stop tar czvf 20111017.tar.gz zabbix/ mkdir tmp mv mysql-bin.* tmp/ scp 20111017.tar.gz root@192.168.57.82:/root/ /etc/init.d/mysqld start ll -rw-r--r-- 1 root root 362462977 Oct 17 14:42 20111017.tar.gz -rw-rw---- 1 mysql root 812700 Oct 17 14:45 cl3.test.com.err -rw-rw---- 1 mysql mysql 6 Oct 17 14:45 cl3.test.com.pid -rw-rw---- 1 mysql mysql 17638261 Oct 17 14:46 lowquery.log drwx------ 2 mysql root 4096 May 4 10:35 mysql -rw-rw---- 1 mysql mysql 7046279 Oct 17 15:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 38 Oct 17 14:45 mysql-bin.index drwxr-xr-x 2 root root 4096 Oct 17 14:42 tmp drwx------ 2 mysql mysql 12288 Oct 17 11:23 zabbix 从库,重置了slave从库信息,重新建立从库信息,由于主库二进制日志从新开始,所以 master_log_file='mysql-bin.000001',master_log_pos=1; /etc/init.d/mysqld stop mv /usr/local/mysql/var/zabbix /usr/local/mysql/var/zabbix_tmp tar zxvf 20111017.tar.gz -C /usr/local/mysql/var/ /etc/init.d/mysqld start mysql -uroot -p mysql> reset slave; 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; Query OK, 0 rows affected (0.01 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.6.53 Master_User: dongnan Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3249706 Relay_Log_File: zabbix-slave-relay-bin.000002 Relay_Log_Pos: 3249851 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes #mysqlbinlog 其它 #找到 2011-10-16 17:00:00 到 2011-10-16 17:58:59 这期间的sql语句 mysqlbinlog --database=zabbix --start-datetime="2011-10-16 17:00:00" --stop-datetime="2011-10-16 17:58:59" mysql-bin.000157 > test2.sql du -sh test2.sql 16M test2.sql head test2.sql /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #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 BINLOG ' ZlmZTg8BAAAAZgAAAGoAAAAAAAQANS4xLjU1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 259091243 tail test2.sql /*!*/; # at 270239069 #111016 17:58:56 server id 1 end_log_pos 270239321 Query thread_id=120 exec_time=0 error_code=0 SET TIMESTAMP=1318759136/*!*/; insert into history_uint (itemid,clock,value) values (18532,1318759132,25427968),(18533,1318759133,43491328),(18534,1318759134,4308992),(18504,1318759134,15250739200),(18535,1318759135,0) /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; mysql -uroot -pdong zabbix < test2.sql #/usr/local/mysql/bin/mysqlbinlog --database=zabbix mysql-bin.000013 > test.txt #head -n 10 test.txt #/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语句,再次导入数据库即可!如果有从库的话再导出一份数据到从库就可以了!
以上就是讲述一个mysql 数据库恢复的实例的详细内容,更多请关注php中文网其它相关文章!
已抢186333个
抢已抢67190个
抢已抢352833个
抢已抢666886个
抢已抢121789个
抢已抢22751个
抢