Automatic backup of MySQL database and recovery after database corruption

I. Introduction:

When the database server is set up, the first thing we need to do is not to consider which MySQL-powered programs to run on this server that supports the database. Instead, after the database is destroyed, how to safely restore to the last normal state so that the data The loss is minimized.

Or just the establishment of the database server, only to 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 automatic backup of the database and the recovery of the damaged database. Here, we use mysqlhotcopy, and define a shell script to automatically back up the database, and let the entire data automatic backup and data recovery process are based on the Shell.

The conditions required to establish a database backup

[1] Establish automatic backup script

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

[Root @ CentOS ~] # vi mysql-backup.sh ← Create a database backup automatically, as follows:

#! / bin / bash

PATH = / usr / local / sbin: / usr / bin: / bin

# The Directory of Backup
BACKDIR = / backup / mysql

# The Password of MySQL
ROOTPASS = ******** Here to replace the asterisk 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 database automatic backup script

[root @ CentOS ~] # Chmod 700 mysql-backup.sh Change the script properties, allowing only root user to perform
[root @ CentOS ~] # ./mysql-backup.sh Run the script
[Root @ CentOS ~] # ls-l / backup / mysql / Confirm whether the backup is successful
total 8
drwxr-x --- 2 mysql mysql 4096 Sep 1 16:54 mysql has been successfully backed up to the / backup / mysql directory

[3] Let the database backup script automatically run daily

[Root @ sample ~] # crontab-e ← edit automatic operation rules (and then there will be editing window, the same operation vi)
00 03 * * * /root/mysql-backup.sh Add this line to the file, so that the database backup every day at 3 am

Test the automatic backup of normal operation or not (backup and recovery methods)

Here, by the actual operation of the process to introduce the recovery method after the emergence of the problem.

[1] Recovery method when the database is deleted

First create a test database.

[Root @ CentOS ~] # mysql-u root-p ← Log in to the MySQL server as root
Enter password: ← Enter the MySQL root 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 test database test
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 to this table (here "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 the MySQL server
Bye