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!