Mysql database automatic backup and data restoration methods

This section describes automatic database backup and restoration methods after database destruction. Here, we use mysqlhotcopy and define a Shell script to implement automatic database backup. In addition, the entire process of automatic data backup and data recovery is based on Shell.

This section describes automatic database backup and restoration methods after database destruction. Here, we use mysqlhotcopy and define a Shell script to implement automatic database backup. In addition, the entire process of automatic data backup and data recovery is based on Shell.

Prerequisites for creating a backup

[1] creating an automatic backup script

Here, in order to make the database backup and recovery meet our actual requirements, use a Shell script to automate the entire backup process.

The Code is as follows:

[Root @ CentOS ~] # Vi-backup. sh scripts: #! /Bin/bash PATH =/usr/local/sbin:/usr/bin:/bin # The Directory of Backup BACKDIR =/backup/mysql # The Password of MySQL ROOTPASS = ********* Replace the star number 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 Do Mysqlhotcopy $ dbname-u root-p $ ROOTPASS $ BACKDIR | logger-t mysqlhotcopy Done

[2] Run the automatic database backup script

The Code is as follows:
[Root @ CentOS ~] # Chmod 700 mysql-backup.sh changes the 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/check whether the backup is successful Total 8 Drwxr-x --- 2 mysql 4096 Sep 1 mysql has been successfully backed up to the/backup/mysql directory

[3] Let the database backup script run automatically every day

[Root @ sample ~] # Crontab-e ← edit the automatic running rule (the edit window appears and the operation is the same as that in vi)
00 03 ***/root/mysql-backup.sh add this line to the file, let the database back up at every day

Test whether automatic backup works properly (Backup Recovery Method)

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

[1] restoration method after database Deletion

First, create a database for testing.

The Code is as follows:

[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 (here "Hello, CentOS" is used as an example) Query OK, 1 row affected (0.02 sec) Mysql> * 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

Then, 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

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

The Code is as follows:

[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.

The Code is as follows:

[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 above result indicates that after the database is deleted, the backed up database is successfully restored to the status before deletion.

2] restoration method after database Modification

The database may be modified for many reasons, such as intrusion and bugs in the corresponding program. This section only describes how to restore the database to the status before modification after it is modified.

It is similar to the "restoration method after the database is deleted" described above. Here, the test database is followed by the test that has just been used. In order to make friends who are new to the database do not understand the confusion, we will log on to the MySQL server again to confirm the information about the database test we just created for testing.

The Code is as follows:

[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 | + ------------ + 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 | + ------ + -------------------- + 1 row in set (0.01 sec) Mysql> exit MySQL Server Bye

Then, run the database backup script again to back up the database in the current state.

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

Next, we log on to the MySQL server again and make some modifications to the test database to test whether the data recovery is successful.

The Code is as follows:

[Root @ sample ~] # 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 15 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 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> update test set name = 'shit, Windows'; then, the table value in test is redefined as "Shit, Windows" (originally "Hello, CentOS ") Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 Mysql> select * from test; Tables: confirm the table value defined in test. + ------ + -------------------- + | Num | name | + ------ + ------------------- + | 1 | Shit, Windows | confirm that the value in the original test database table has been changed to the new value "Shit, Windows" + ------ + ------------------- + 1 row in set (0.00 sec) Mysql> exit MySQL Server Bye

Above, we simulate the database tampering process. Next, we will use the backup method to restore the database after it is "Tampered.

[Root @ CentOS ~] #/Bin/cp-Rf/backup/mysql/test // var/lib/mysql/snapshot copy the backup database test to the corresponding directory

Then, log on to the MySQL server again to check whether the database has been restored to the status before being tampered.

The Code is as follows:

[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 16 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 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> select * from test; Contents + ------ + ---------------- + | Num | name | + ------ + ---------------- + | 1 | Hello, CentOS | confirm that the data table content is the same as the "Hello, CentOS" defined before the modification! + ------ + ---------------- + 1 row in set (0.01 sec) Mysql> exit MySQL Server Bye

The above results indicate that, after the database is modified, the backed-up database is successfully restored to the State before it is "Tampered.

After testing...

After the test is completed, the legacy information used for the test is deleted.

The Code is as follows:

[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 19 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 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 in the test Database Query OK, 0 rows affected (0.01 sec) Mysql> drop database test; Revoke delete test Query OK, 0 rows affected (0.00 sec) Mysql> show databases; databases + ------------- + | Database | + ------------- + | Mysql | confirm that the test database for testing does not exist and has been deleted. + ------------- + 1 row in set (0.00 sec) Mysql> exit MySQL Server Bye

The preceding describes how to back up a database using mysqlhotcopy, a Shell script we have created.

For many individual enthusiasts, setting up a server may not consider data corruption and restoration after data destruction. But it cannot be said that, for servers, the restoration efficiency after data destruction is also one of the amateur and professional factors. Therefore, I suggest that you do not rush to apply the Web server, MySQL server, and so on, but try to use limited hardware and software) and then consider the application issues.