Automatic backup of MySQL database and restoration after Database destruction _ MySQL

I. Preface: after the database server is established, what we need to do first is not to consider which MySQL-carrying programs should be run on this database-supporting server, but when the database is damaged, how to safely restore to the last normal state, so that the loss of data reaches 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