Experiment: Mistakenly delete MySQL database tables in a simulated scenario, then use full backup and binary log file recovery operations

First, the experimental environment: 1, the preparation of two virtual machines, one for the destruction of the database, one for the restoration, two in the same network 2, two minimized installation of the CentOS 7 system, and directly yum installation MARAIDB database 3, prepare a test database file, for example, hellodb_ The Innodb.mysql Test Library has a minimum of two tables. Second, the experiment steps: 1, open the database binary log function

Vim/etc/my.cnf
[mysqld] Add log-bin below to open binary log function

2. Full backup

Mysqldump-a-F--master-data=2--single-transaction |gzip >/data/all.sql.gz
Back up the database file and compress it into the/data directory using gzip

3, directly connected to the database to modify any table

Insert students (Name,age) VALUES (' a ', 20);
Insert students (Name,age) VALUES (' B ', 30);

SELECT * FROM students query for newly added table information

4. Simulating the deletion of database tables

drop table students; The students tables have been deleted;

5, pretend not to know, continue to modify other tables

Insert Teachers (Name,age) VALUES (' Zhangsir ', 30);

6, found the important table deleted, immediately lock the table

Flush tables with read lock; Add a read lock to a table

7. Restore on another machine

Vim/etc/my.cnf
Skip-networking ensure that other users cannot access the database and can only operate on their own
Rm-rf/var/lib/mysql Delete the original database file, avoid the original file
Scp/data/all.sql.gz 192.168.190.129:/data
Copy the original backup file to the current host directory

8. Full backup Restore

Gzip-d/data/all.sql.gz Unzip the packaged backup file
MySQL </data/all.sql importing files into the database

9. Restore Incremental Backups

Less/data/all.sql Viewing binary log locations

Determine the binary log, and start at 245
scp/var/lib/mysql/mariadb-bin.000002 192.168.190.129:/data
Copy the binaries.
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 drop table students; This SQL statement, commenting on this line

MySQL </data/binlog.sql
Importing binary log files
Recovering users ' access

Iii. completeness of the test data

show databases;
Show tables

The students table in the list has been restored.

, and all the changes I've made have come back. The purpose of the experiment was achieved.

Iv. Summary of the experiment

MARIADB database after the destruction, need to use full backup + binary log file for restore, both are very important, both of the production is to protect the backup database files, but also need to protect the binary files, so as to ensure the integrity of the data restore.

Experiment: Mistakenly delete MySQL database tables in a simulated scenario, then use full backup and binary log file recovery operations