Automatic backup of MySQL database and recovery after database corruption (2)

Test whether automatic backups are working or not (method of backup recovery)

Here, we introduce the recovery method after the problem through the actual operation process.

[1] When the database is deleted after the recovery method

First, create a test database.

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL 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; ← Build a test database for testing Query OK, 1 row Affected (0.00 sec) Mysql> use test← connect to this database Database changed Mysql> CREATE TABLE Test (num int, name varchar (50)); ← Creating a table in the database Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO test values (1, ' Hello,centos '); ← Insert a value into this table (here "Hello,centos" for example) Query OK, 1 row affected (0.02 sec) Mysql> select * from test; ← Viewing content in a database +------+-----------------+ | num | name | +------+-----------------+ |　 Hello,centos | ← Confirm the existence of values just inserted into the table +------+------------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

Then, run the database backup script that you just created, and back up the database that you just established for the test.

[[email protected] ~]# cd← back to the root directory of the root user where the script is located [[email protected] ~]#./mysql-backup.sh← Run script for database backup

Next, we log on to the MySQL server again and delete the database test that we just built for testing so that we can test the success of the data recovery.

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is a to server version:4.1.20 Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> use test← connection to test database for testing Reading table information for completion of table and column names Can turn off this feature to get a quicker startup with-a Database changed mysql> drop table test; ← Delete a table from the data Query OK, 0 rows affected (0.04 sec) mysql> drop database test; ← Delete Test with database test Query OK, 0 rows affected (0.01 sec) mysql> show databases; +---------------+ | Database | +---------------+ |　 MySQL | ← Verify test database no longer exists, has been deleted +---------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

Above, we are tantamount to simulating the process of database corruption. Next, the database is "destroyed" after the use of backup to restore the method.

[[email protected] ~]#/bin/cp-rf/backup/mysql/test//var/lib/mysql/← Copy the backed up database test to the appropriate directory [[email protected] ~]# chown-r mysql:mysql/var/lib/mysql/test/← Change the attribution of the database test to MySQL [[email protected] ~]# chmod 700/var/lib/mysql/test/← Change the database directory property to 700 [[email protected] ~]# chmod 660/var/lib/mysql/test/*← Change the property of the data in the database to 660

Then, log on to the MySQL server again to see if the database has been successfully restored.

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is a to server version:4.1.20 Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> show databases; ← View the currently existing database +-------------+ | Database | +-------------+ | MySQL | |　 Test | ← Confirm that the test database that has just been deleted has been successfully restored! +------------+ 2 rows in Set (0.00 sec) mysql> use test← connection to the test database Reading table information for completion of table and column names Can turn off this feature to get a quicker startup with-a Database changed Mysql> Show tables; ← Viewing tables that exist in the test database +-------------------+ | Tables_in_test | +-------------------+ | Test | +-------------------+ 1 row in Set (0.00 sec) Mysql> select * from test; ← Viewing content in a database +------+---------------------+ | num | name | +------+---------------------+ | 1 |　 Hello,centos | ← Confirm that the content in the datasheet is the same as the "Hello,centos" defined before deletion! +------+---------------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

The above results indicate that after the database is deleted, the database is successfully restored to the pre-deletion state after the backup.