Automatic Backup of MySQL database and restoration 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-carried servers should be running on the server that supports the database.ProgramBut how to recover to the last normal state after the database is damaged, minimizing the 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: Login 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 cases 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 ("Hello, centos" is used as an example here)
query OK, 1 row affected (0.02 Sec)
mysql> select * from test; ← view database content
+ ------ + ----------------- +
| num | Name |
+ ------ + ----------------- +
| 1 | hello, centos | confirm the existence of the value just inserted into the table in sequence
+ ------ + ------------------ +
1 row in SET (0.01 Sec)
mysql> exit from 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 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.

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

[root @ sample ~] # Mysql-u root-P login use root to log on to the MySQL server
enter password: Login 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 cases 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- A
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; confirm the table value defined in test
+ ------ + -------------------- +
| num | Name |
+ ------ + ----------------- +
| 1 | shit, windows | verify 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 from 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.

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

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

In addition, although the database automatic backup script mentioned in the above method is set to run regularly on a daily basis, when you run some programs associated with MySQL (Forum,CommunityAnd so on. When you do some operations that may compromise data security, it is very helpful to run the database backup script to instantly back up the current State database, at least recover the database after a problem occurs.