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='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
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!

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Linux new version
SublimeText3 Linux latest version

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.
