Tell an example of mysql database recovery

Some time ago, due to accidentally unplugging the power of the xen server server (~~!!), the virtual machine zabbix database on the xen server caused an error. Although it is not very important (used to monitor the company's intranet gateway server), monitoring One year's worth of data cannot be lost just like that. Thinking of the backup sql files and mysql binary log files in the zabbix database, I tried to restore the mysql database. It was successful and the recovery process was very simple!

#详细步骤 
 
#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=&#39;mysql-bin.000001&#39;,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=&#39;192.168.6.53&#39;,master_user=&#39;dongnan&#39;,master_password=&#39;123456&#39;,master_log_file=&#39;mysql-bin.000001&#39;,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 &#39; 
ZlmZTg8BAAAAZgAAAGoAAAAAAAQANS4xLjU1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC 
&#39;/*!*/; 
# 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

End

The entire recovery process is nothing more than importing the sql file backed up the previous day, then getting the desired sql statement from the binary log by time or position, and importing it into the database again. ! If there is a slave library, just export a copy of the data to the slave library!

The above is the detailed content of Tell an example of mysql database recovery. For more information, please follow other related articles on the PHP Chinese website!