Restoration of deleted MySQL database data

Restoration method after the database is deleted:

1. First, create a database for testing.

[Root @ CentOS ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 8 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> create database test; create a database test for testing
Query OK, 1 row affected (0.00 sec)

Mysql> use test connector to connect to this database
Database changed

Mysql> create table test (num int, name varchar (50); Tables create a table in the database
Query OK, 0 rows affected (0.07 sec)

Mysql> insert into test values (1, 'Hello, CentOS '); insert a value to this table using "Hello, CentOS" as an example)
Query OK, 1 row affected (0.02 sec)

Mysql> select * from test; Contents
+ ------ + ----------------- +
| Num | name |
+ ------ + ----------------- +
| 1 | Hello, Centos | confirm that the value just inserted into the table exists.
+ ------ + ------------------ +
1 row in set (0.01 sec)

Mysql> exit MySQL Server
Bye

2. Run the database backup script you just created to back up the database you just created for testing.

[Root @ sample ~] # Cd scripts return to the root directory of the root user where the script is located
[Root @ sample ~] #./Mysql-backup.sh slave run script for Database Backup

3. Next, we log on to the MySQL server again and delete the test database we just created to test whether the data recovery is successful.

[Root @ Centos ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 13 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> use test connector to connect to the test database for testing
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> drop table test; Tables Delete tables from data
Query OK, 0 rows affected (0.04 sec)

Mysql> drop database test; Revoke delete test
Query OK, 0 rows affected (0.01 sec)

Mysql> show databases;
+ --------------- +
| Database |
+ --------------- +
| Mysql | confirm that the test database used for testing does not exist or has been deleted.
+ --------------- +
1 row in set (0.01 sec)

Mysql> exit MySQL Server
Bye

Above, we simulate the database destruction process. Next, we will use the backup method to restore the database after it is damaged.

[Root @ Centos ~] #/Bin/cp-Rf/backup/mysql/test // var/lib/mysql/snapshot copy the backup database test to the corresponding directory
[Root @ Centos ~] # Chown-R mysql: mysql/var/lib/mysql/test/secrets change the database test to mysql
[Root @ Centos ~] # Chmod 700/var/lib/mysql/test/CATALOG change the database Directory attribute to 700
[Root @ Centos ~] # Chmod 660/var/lib/mysql/test/* modify the attribute of data in the database to 660

Then, log on to the MySQL server again to check whether the database has been successfully restored.

[Root @ CentOS ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 14 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show databases; databases
+ ------------- +
| Database |
+ ------------- +
| Mysql |
| Test | confirm that the database test that has just been deleted has been successfully recovered!
+ ------------ +
2 rows in set (0.00 sec)

Mysql> use test connector to connect to the test Database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> show tables; tables: View tables in the test Database
+ ------------------- +
| Tables_in_test |
+ ------------------- +
| Test |
+ ------------------- +
1 row in set (0.00 sec)

Mysql> select * from test; Contents
+ ------ + --------------------- +
| Num | name |
+ ------ + --------------------- +
| 1 | Hello, CentOS | confirm that the data table content is the same as the "Hello, CentOS" definition before deletion!
+ ------ + --------------------- +
1 row in set (0.01 sec)

Mysql> exit MySQL Server
Bye

The preceding result indicates that after the database is deleted, the data is successfully restored to the status before the database is deleted.