Automatic backup of MySQL database and recovery after database is destroyed 1

First, preface:

When the database server is set up, the first thing we need to do is not to consider which MySQL-supported programs to run on this database server, but how to recover to the last normal state when the database is compromised, so that the loss of data is minimized.

Or, the mere creation of a database server can only explain what it can do, and it does not mean that it can do something stably. The efficiency and comprehensiveness of disaster recovery are also a quasi-factor in the stability of the system, especially for a server system.

This section describes how the database is automatically backed up and restored after the database has been compromised. Here, we use mysqlhotcopy and define a shell script to automate the database backup, and let the entire data auto-backup and data recovery process be based on the shell.

Conditions required to establish a database backup

[1] Creating an automatic backup script

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

[[Email protected] ~]# VI mysql-backup.sh← set up a 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 please replace the asterisk with the root password of MySQL # remake The Directory of Backup RM-RF $BACKDIR Mkdir-p $BACKDIR # Get The Name of the 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 database automatic backup script

[[email protected] ~]# chmod mysql-backup.sh Change script properties so that it can only be performed by the root user [[email protected] ~]#./mysql-backup.sh Run Script [[email protected] ~]# ls-l/backup/mysql/Confirm if the backup was successful Total 8 Drwxr-x---2 mysql mysql 4096 Sep 1 16:54 MySQL successfully backed up to/backup/mysql directory

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

[[email protected] ~]# crontab-e← Edit auto-run rule (then the edit window will appear, operation with VI) * * * * */root/mysql-backup.sh Add this line to the file to have the database backed up 3 o'clock in the morning daily