mysql 資料庫恢複執行個體

mysql 資料庫恢複執行個體

2011年10月17日由于誤把 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     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 [email protected]:/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; 

mv /usr/local/mysql/var/zabbix /usr/local/mysql/var/zabbix_tmp 

tar zxvf 20111017.tar.gz -C /usr/local/mysql/var/ 

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語句，再次導入資料庫即可！如果有從庫的話再導出一份資料到從庫就可以了！

 本文轉自 dongnan 51CTO部落格，原文連結：http://blog.51cto.com/dngood/710322