Page 1/2 of MySQL automatic backup and restoration methods after database destruction

I. Preface:

After the database server is set up, the first thing we need to do is not to consider which MySQL-initiated programs should be run on the server that supports the database, but when the database is damaged, how to safely restore to the last normal state, minimizing data loss.

In other words, just the establishment of a database server can only describe what it can do, and it does not mean what it can do stably. Disaster recovery efficiency and comprehensiveness are also a quasi-factor in system stability, especially for a server system.

This section describes the automatic database backup and restoration methods after the database is damaged. 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 database 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.

[Root @ CentOS ~] # Vi mysql-backup.sh notebook create 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 = ********* 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

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

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

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.

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