Automatic backup of MySQL database and recovery of database destroyed

When the database server is established, the first thing we need to do is not to consider what MySQL-enabled programs are running on this database-supporting server, but how to recover to the last normal state after the database has been compromised, minimizing the loss of data.

Or, just the establishment of a database server, can only show what it can do, does not mean that it can be stable to do something. The efficiency and comprehensiveness of disaster recovery is also a quasi factor of system stability, especially for a server system.

This section describes the methods for automatic database backups and recovery after a database has been compromised. Here, we use mysqlhotcopy and define a shell script to automate the database backup, and make the entire data automatic backup and data recovery process based on the shell.

Conditions required to establish a database backup

[1] Establish an automatic backup script

Here, in order for database backup and recovery to meet our actual requirements, a consistent shell script is used to automate the entire backup process.

[Root@centos ~]# VI mysql-backup.sh← establishes the database automatic backup script, as follows: #!/bin/bash Path=/usr/local/sbin:/usr/bin:/bin # The Directory of Backup Backdir=/backup/mysql # The Password of MySQL rootpass=******** here, replace the asterisk with the MySQL root password # remake The Directory of Backup RM-RF $BACKDIR Mkdir-p $BACKDIR # get the Name of Database Dblist= ' ls-p/var/lib/mysql grep/tr-d/' # Backup with Database For dbname in $DBLIST Todo Mysqlhotcopy $dbname-u root-p $ROOTPASS $BACKDIR logger-t mysqlhotcopy Done

[2] Run the database automatic backup script

[Root@centos ~]# chmod mysql-backup.sh Changes script properties so that it can only be executed by the root user [Root@centos ~]#./mysql-backup.sh Run Script [Root@centos ~]# ls-l/backup/mysql/Confirm if Backup is successful Total 8 Drwxr-x---2 mysql mysql 4096 Sep 1 16:54 MySQL has been successfully backed up to/backup/mysql directory

[3] Make database backup scripts run automatically every day

[Root@sample ~]# crontab-e← edit Autorun rule (then edit window appears, operation with VI) * * * * */root/mysql-backup.sh Add this line to the file and make the database backup 3 o'clock in the morning daily

Test the normal operation of automatic backups (method of backup recovery)

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

[1] Recovery methods when the database is deleted

First, establish a test database.

[Root@centos ~]# mysql-u root-p← login 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; ← Create a test database 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)); ← 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 into this table (here take "Hello,centos" for example) Query OK, 1 row affected (0.02 sec) Mysql> select * from test; ← View the contents of the database +------+-----------------+ Num Name +------+-----------------+ 1 hello,centos← confirm the existence of the value 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 test database you just created.

[Root@sample ~]# cd← back to the root directory of the root user where the script resides [Root@sample ~]#./mysql-backup.sh← Run scripts for database backups

Next, we log on to the MySQL server again and delete the database test that was just created to test the success of the data recovery.

[root@centos ~]# mysql-u root-p← Log on to the MySQL server with root Enter PASSW Ord:← Enter the MySQL root user password Welcome to the MySQL monitor. Commands End With; or \g. Your MySQL Connection ID is-to-server version:4.1.20 Type ' help. '/' \h ' for help. Type ' \c ' to clear the buffer. Mysql> use test← connect to test database Reading table information for completion of table and column names > can turn off the feature to get a quicker startup with-a Database changed mysql> drop table test　 ;　 ← Delete the table in the data Query OK, 0 rows affected (0.04 sec) mysql> drop database test; ← Delete Test database test Query OK, 0 rows affected (0.01 sec) mysql> show databases; +---------------+ Database +---------------+ mysql← confirm test database no longer exists, has been deleted +---------------+ br> 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

Above, we are tantamount to simulating the destruction of the database process. Next, the database is "corrupted", the method of restoring with backup.

[Root@centos ~]#/bin/cp-rf/backup/mysql/test//var/lib/mysql/← Replication Backup Database test to the appropriate directory [Root@centos ~]# chown-r mysql:mysql/var/lib/mysql/test/← Changes the ownership of database test to MySQL [Root@centos ~]# chmod 700/var/lib/mysql/test/← Change Database directory property to 700 [Root@centos ~]# chmod 660/var/lib/mysql/test/*← Change the properties 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.

[Root@centos ~]# mysql-u root-p← login 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 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← confirms that the test database that has just been deleted has been successfully restored! +------------+ 2 rows in Set (0.00 sec) Mysql> use test← Connect to the test database Reading table information for completion of table and column names You can turn off the feature to get a quicker startup with-a Database changed Mysql> Show tables; ← View the tables that exist in the test database +-------------------+ Tables_in_test +-------------------+ Test +-------------------+ 1 row in Set (0.00 sec) Mysql> select * from test; ← View the contents of the database +------+---------------------+ Num Name +------+---------------------+ 1 hello,centos← confirm that the contents of the datasheet are the same as the "Hello,centos" defined before the deletion! +------+---------------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

The above results indicate that after the database was deleted, the database was successfully restored to the state before the deletion with the backed up data.

[2] The recovery method when the database is modified

The database has been modified, there may be many reasons, the intrusion, and the corresponding program there are bugs, and so on, here is not described in detail. This will only describe how to revert to a modified state before the database is modified.

This is similar to the "recovery method after database deletion" described above. Here, the test database then uses test that you just used earlier. In order to make the friends who just contacted the database not understand the confusion, we log on to the MySQL server again to confirm the information about the test database tests that we just created.

[Root@centos ~]# mysql-u root-p← login 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 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 +------------+ 2 rows in Set (0.00 sec) Mysql> use test← Connect to the test database Reading table information for completion of table and column names You can turn off the feature to get a quicker startup with-a Database changed Mysql> Show tables; ← View the tables that exist in the test database +-------------------+ Tables_in_test +-------------------+ Test +-------------------+ 1 row in Set (0.00 sec) Mysql> select * from test; ← View the contents of the database +------+--------------------+ Num Name +------+--------------------+ 1 Hello,centos +------+--------------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

Then we run the database backup script again and back up the current state of the database again.

[Root@centos ~]# cd← back to the root directory of the root user where the script resides [Root@centos ~]#./mysql-backup.sh← Run scripts for database backups

Next, we log on to the MySQL server again and make some changes to the test's database test to be able to test the success of the data recovery.

[Root@sample ~]# mysql-u root-p← login 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 to server version:4.1.20 Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer. Mysql> use test← Connect to the test database Reading table information for completion of table and column names You can turn off the feature to get a quicker startup with-a Database changed mysql> Update test set name= ' Shit,windows '; ← Then redefine the value of the table in test to "Shit,windows" (originally "Hello,centos") Query OK, 1 row affected (0.07 sec) Rows matched:1 changed:1 warnings:0 Mysql> select * from test; ← Confirm that the table in test has a defined value +------+--------------------+ Num Name +------+-------------------+ 1 shit,windows← confirms that the values in the original test database table have been modified to the new value "Shit,windows" +------+-------------------+ 1 row in Set (0.00 sec) mysql> exit← exit MySQL server Bye

Above, we are tantamount to simulating the process of database tampering. Next, the database is "tampered" with the method of restoring with backup.

[Root@centos ~]#/bin/cp-rf/backup/mysql/test//var/lib/mysql/← Replication Backup Database test to the appropriate directory

Then, log on to the MySQL server again to see if the database was restored to the state before it was tampered with.

[Root@centos ~]# mysql-u root-p← login 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 to server version:4.1.20 Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer. Mysql> use test← Connect to the test database Reading table information for completion of table and column names You can turn off the feature to get a quicker startup with-a Database changed Mysql> select * from test; ← View the contents of the database +------+----------------+ Num Name +------+----------------+ 1 hello,centos← confirm that the content in the datasheet is the same as the "Hello,centos" defined before it is modified! +------+----------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

The above results indicate that the database has been modified to successfully restore the data to the state before it was "tampered" with the backed-up database.

After the test ...

After the test is complete, delete the legacy information that was used for the test.

[root@centos ~]# mysql-u root-p← Log on to the MySQL server with root Enter password:← input m Ysql root user password Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is-to-server version:4.1.20 Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> use test← to connect to the test database Reading table information for completion of table and column names Yo　 u can turn off the feature to get a quicker startup with-a Database changed mysql> drop table test;　 ← Delete the table in the test database Query OK, 0 rows affected (0.01 sec) mysql> drop database test;　 ← Delete Test database test Query OK, 0 rows Affected (0.00 sec) mysql> show databases; ← View the currently existing database +-------------+ Database +-------------+ mysql← confirm test database test does not exist, has been deleted +----------- --+ 1 row in Set (0.00 sec) mysql> exit← exit MySQL server Bye

The above describes a way to back up the database with a mysqlhotcopy of our own set of shell scripts.

For many individual enthusiasts, the build server may not be very concerned about the data being corrupted and the recovery after the data has been compromised. But it cannot be said that for the server, the recovery efficiency after data destruction is also one of the differences between amateur and professional factors. Therefore, I suggest that when you configure the Web server and MySQL server, and so on, do not rush to apply it, but to find ways in the limited (hardware, software) conditions to make it "indestructible" after the application of the problem.

Moreover, the database automatic backup script mentioned in the method described above is set to run on a daily basis, but when you run some MySQL-related programs (forums, communities, and so on) and do something that might compromise your data security, run the database backup script to back up the current state database immediately. is also very helpful, at least to ensure the recoverability of the database in the presence of a problem.