Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to restore the operation method of mistakenly deleting mysql database table, the content of the article is carefully selected and edited by the author, which has a certain pertinence and is of great significance to everyone's reference, the following is to understand with the author how to restore the operation method of mistakenly deleting mysql database table.

1. Prepare two virtual machines, one for database destruction, one for restore, two to install centos 7 system on the same network 2 and two minimally, and directly yum to install maraidb database 3 and prepare a test database file. For example, there are at least two tables in hellodb_innodb.mysql test library. 2. experimental steps: 1. Turn on the binary log function of the database.

Vim / etc/my.cnf

[mysqld] add log-bin below to enable the binary log function

2. Full backup

Mysqldump-A-F-- master-data=2-- single-transaction | gzip > / data/all.sql.gz

Back up the database files and compress them with gzip and store them in the / data directory

3. Directly connect to the database to modify any table

Insert students (name,age) values ('axiaojie 20)

Insert students (name,age) values ('baked charge 30)

Select * from students queries the information of newly added tables

4. Simulated deletion of database tables

Drop table students; as shown below, the students table has been deleted

5. Pretend not to know and continue to modify other tables

Insert teachers (name,age) values ('zhangsir',30)

6. if you find that the important table is deleted, lock the table immediately.

Flush tables with read lock; adds a read lock to the table

7. Restore on another machine

Vim / etc/my.cnf

Skip-networking ensures that other users cannot access the database and can only operate on their own.

Rm-rf / var/lib/mysql deletes the original database file to avoid having the original file

Scp / data/all.sql.gz 192.168.190.129:/data

Copy the original backup files to the directory of the current host

8. Full backup restore

Gzip-d / data/all.sql.gz decompress the packaged backup file

Mysql

< /data/all.sql 将文件导入到数据库中 9、还原增量备份 less /data/all.sql 查看二进制日志位置 确定二进制日志，以及起始位置为245 scp /var/lib/mysql/mariadb-bin.000002 192.168.190.129:/data 将二进制文件拷贝过来 mysqlbinlog -- start-position=245 /data/mariadb-bin.000002 >

/ data/binlog.sql

Use the mysqlbinlog tool to generate a new binary log

Vim / data/binlog.sq

Find the sql statement drop table students; and comment about this line

Mysql < / data/binlog.sql

Import binary log files

Restore user access

Third, check the integrity of the data

Show databases

Show tables

The students table in the above figure has been restored.

As shown in the figure, all the previous changes have come back. The purpose of the experiment has been achieved.

IV. Summary of the experiment

After the destruction of the mariadb database, it is necessary to restore with the help of full backup and binary log files, both of which are indispensable, both of which are very important. In production, we should protect not only the backed-up database files, but also the binary files, so as to ensure the integrity of data restoration.

After reading the above about how to restore the operation method of mistakenly deleting mysql database tables, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.