Database backup Scripts

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] Create 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.

#!/bin/Bash # This was a shellscript for DB Backup # author:mjorcen#Date: the-Ten-4# Version:1.0# # Properties setting# #DB_name=Mysqldb_user=rootdb_passwd=123123Db_backpath=/alidata/www/db/bak_data/Db_logfile=/alidata/www/db/Bak.logdb_path=/var/lib/mysql/Backupmethod=mysqldump #backupMethod=mysqlhotcopy #backupMethod=Tar#Setting End #newFile="$DB _backpath"db_$ (Date+%y%m%d).Tar. Gzdumpfile="$DB _backpath"db_$ (Date+%y%m%d) OldFile="$DB _backpath"db_$ (Date+%y%m%d--Date='7 days ago').Tar. GZCat>> $DB _logfile <<end####################################################################################################### #######         Date: $(Date+"%y-%m-%d%h:%m:%s") ###### newFile: $newFile ###### D Umpfile: $dumpFile ###### oldFile: $oldFile ######### #################################################################################################### #END # DoDeleteif[ -F $oldFile] Then        RM-F $oldFile >> $DB _logfile2>&1        Echo "[$oldFile] deleted successfully">>$DB _logfileElse        Echo "[$oldFile] does not exists">>$DB _logfilefi# Verify NewFileif[ -F $newFile] Then        Echo "[$newFile] was exists"& Exit1fi# Dobak Case$backupMethodinchmysqldump)if[ -F $dumpFile] Then#                mkdir-P $dumpFileRM-F $dumpFilefi            if[ -z $DB _passwd] Thenmysqldump-u$db_user--opt $DB _name >$dumpFileElsemysqldump-U$DB_USER-P$DB_PASSWD--opt $DB _name >$dumpFilefi        Tar-cvzf $newFile $dumpFile >> $DB _logfile2>&1        RM-f $dumpFile;; Mysqlhotcopy)if[ -F $dumpFile] Then                RM-RF $dumpFile >> $DB _logfile2>&1        fi        mkdir-P $dumpFile >> $DB _logfile2>&1                if[ -z $DB _passwd] Thenmysqlhotcopy-u$db_user $DB _name $dumpFile >> $DB _logfile2>&1            Elsemysqlhotcopy-U$DB_USER-P$DB_PASSWD $dumpFile >> $DB _logfile2>&1        fi            Tar-cvzf $newFile $dumpFile >> $DB _logfile2>&1        Echo "mysqlhotcopy [$newFile] has successfully">>$DB _logfileRM-RF $dumpFile >> $DB _logfile2>&1    ;; *)        /etc/init.d/mysqld Stop >> $DB _logfile2>&1        Tar-cvzf $newFile $DB _path$db_name >> $DB _logfile2>&1/ect/init.d/mysqld Start >> $DB _logfile2>&1        Echo "tar [$newFile] backup successfully">> $DB _logfile2>&1    ;;EsacEcho... end ... >> $DB _logfile2>&1

[2] Run database automatic backup script

[Email protected] ~]# chmod  mysql-backup. SH change the Script property so that it can only be executed by the root user [[email protected] ~]#./mysql-backup. SH run script [[email protected] ~]# ll bak_data/1681170529 Oct  4  : db_141004 . Tar . gz

Successfully backed up to the/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 every day

Test whether automatic backups are working or not (method of backup recovery)
Here, we introduce the recovery method after the problem through the actual operation process.

Other information: http://wenku.baidu.com/view/46bb56cea1c7aa00b52acbc0.html

Database backup Scripts