前一段時(shí)間由于誤把 xen server 服務(wù)器電源拔掉（~~!!）,導(dǎo)致xen server 上的 虛擬機(jī) zabbix 數(shù)據(jù)庫(kù)出錯(cuò)，雖然不是很重要（用來(lái)監(jiān)控公司內(nèi)網(wǎng)網(wǎng)關(guān)服務(wù)器），但是監(jiān)控一年的數(shù)據(jù)不能就這么沒(méi)了，想到zabbix數(shù)據(jù)庫(kù)還有備份的sql 文件與mysql 二進(jìn)制日志文件，嘗試下恢復(fù)mysql 數(shù)據(jù)庫(kù)，成功并且恢復(fù)的過(guò)程非常簡(jiǎn)單！

#詳細(xì)步驟 
 
#1 記住誤刪除表（或者出錯(cuò)）的時(shí)間 例如 Oct 17 08:30:27 CST 2011 
#2 先 drop database zabbix ;  
   再 create database zabbix;(或者跳過(guò)直接執(zhí)行第3步) 
#3 導(dǎo)入備份的sql文件  
   mysql -uroot -pdong zabbix < zabbix20111016.sql 
#4 如果有主從，停掉從服務(wù)器  
   例如 mysql>stop slave;  
#5 從二進(jìn)制日志文件得到sql語(yǔ)句 
#20111017 
   mysqlbinlog --database=zabbix --stop-datetime="2011-10-17 08:30:00" mysql-bin.000158 > test.sql 
#6 恢復(fù)sql 文件 
#20110425 
   mysql -root -pdong zabbix < test.sql 
#20111017  
   mysql -root -pdong zabbix < test.sql  
#7 關(guān)于二進(jìn)制日志文件 
正常是每天17:59生成一個(gè)二進(jìn)制日志文件，8:34是掉電后重啟服務(wù)器生成的二進(jìn)制日志文件 
-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 關(guān)于從庫(kù)數(shù)據(jù)恢復(fù)（可選） 
-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 
 
主庫(kù),關(guān)閉了數(shù)據(jù)庫(kù)以前舊的二進(jìn)制文件全部刪除掉，重啟后將生成新的二進(jìn)制日志文件,從000001開(kāi)始 
/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 
 
 
從庫(kù)，重置了slave從庫(kù)信息，重新建立從庫(kù)信息，由于主庫(kù)二進(jìn)制日志從新開(kāi)始，所以 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語(yǔ)句 
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

結(jié)束

整個(gè)恢復(fù)過(guò)程無(wú)非就是導(dǎo)入前一天的備份的 sql 文件，然后按時(shí)間或者按position從二進(jìn)制日志中得到想要的sql語(yǔ)句，再次導(dǎo)入數(shù)據(jù)庫(kù)即可！如果有從庫(kù)的話再導(dǎo)出一份數(shù)據(jù)到從庫(kù)就可以了！

以上就是講述一個(gè)mysql 數(shù)據(jù)庫(kù)恢復(fù)的實(shí)例的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！