Regular MySQL database backup using shell scripts _ MySQL

Use shell scripts to regularly back up MySQL database bitsCN. comPhp
Data Backup is required for any online e-commerce site. Regardless of version updates or server migration, the importance of backup data is self-evident. Manual data backup not only consumes a lot of time and effort, but is not very professional. As a result, the following script appears. I have referenced many tutorials on the Internet and added my own tests. the following scripts are available for test.

#! /Bin/bash
# Shell Command For Backup MySQL Database Everyday Automatically By Crontab
# Author: Carlos Wong
# Date: 2012-03-17

# Configuration parameters
USER = root # Database username "> username
PASSWORD = ×××× # database user PASSWORD
DATABASE = TIENIUZAI # DATABASE name
WEBMASTER = tieniuzai@qq.com # administrator mailbox address to send backup failure message reminder
BACKUP_DIR =/var/www/Data_Backup/topons/# backup file storage path
LOGFILE =/var/www/Data_Backup/topons/data_backup.log # log file path
DATE = 'Date' + % Y % m % d-% H % M'' # date format (as file name)
DUMPFILE = $ DATE. SQL # backup file name
ARCHIVE = $ DATE. SQL. tgz # compressed file name
OPTIONS = "-u $ USER-p $ PASSWORD-opt-extended-insert = false-triggers = false-R-hex-blob-flush-logs-delete-master-logs- B $ DATABASE "# for details about mysqldump parameters, see help mysqldump-help

# Determine whether the backup file storage directory exists; otherwise, the directory is created.
If [! -D $ BACKUP_DIR];
Then
Mkdir-p "$ BACKUP_DIR"
Fi

# Before starting backup, write the backup information header to the log file
Echo ""> $ LOGFILE
Echo ""> $ LOGFILE
Echo "----------------"> $ LOGFILE
Echo "backup date:" $ (date + "% y-% m-% d % H: % M: % S") >>$ LOGFILE
Echo "----------------"> $ LOGFILE

# Switch to the backup directory
Cd $ BACKUP_DIR
# Use the mysqldump command to back up the database and name the backup file with the formatted timestamp
Mysqldump $ OPTIONS> $ DUMPFILE
# Determine whether the database backup is successful
If [[$? = 0]; then
# Create a compressed backup file package
Tar czvf $ ARCHIVE $ DUMPFILE >>$ LOGFILE 2> & 1
# Enter a message indicating a successful backup to the log file.
Echo "[$ ARCHIVE] Backup Successful !" >>$ LOGFILE
# Delete the original backup file. you only need to keep the compressed package of the database backup file.
Rm-f $ DUMPFILE
Else
Echo "Database Backup Fail !" >>$ LOGFILE

# Send an email notification to the website administrator after the backup fails. you need support from mailutils or similar email tools.
# Mail-s "Database: $ DATABASE Daily Backup Fail" $ WEBMASTER
Fi
# Output the reminder message for the backup process to end
Echo "Backup Process Done"

Usage:

Save the above code to:/usr/sbin/DataBackup (the file name is random, as long as it does not have the same name as the original command of the system; the code can be put anywhere, put it in the sbin directory for easy execution. the files/directories under the sbin directory can be directly called on the terminal, similar to the directory specified by the PATH variable in windows)
Add executable permissions to the script: sudo chmod + x/usr/sbin/DataBackup
Execute the script: sudo DataBackup
If you need to regularly execute the backup command, you only need to put the following code in the crontab File (sudo vim/etc/crontab:

01 3 *** root/usr/sbin/DataBackup # it indicates that the DataBackup script will be executed at every day

Note the following:

In linux, shell scripts define variables in the format of key = value. Note that there is no space before and after "=" between the two. Otherwise, the system cannot confirm the variables.
For the line marked in red, the first single quotation mark character "'" is not a single quotation mark. its input key is below the ESC key on the keyboard.
This script is only suitable for backup of some small sites, because it backs up all the databases instead of incremental backup, and is not suitable for large-capacity database backup.

Update:

2012-03-17: Because the socket value in the MySQL configuration file on the server is "/tmp/mysql. "sock", while mysqldump searches for mysql in the/var/lib/mysql/directory when linking to mysql server. sock file. If not, an error is returned:

Mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql. sock'

Solution: First, modify the value of the socket entry in the mysql configuration file to '/var/lib/mysql. sock '. However, this method requires restarting the mysql service, which is not very good.

Second, create a soft link for/tmp/mysql. sock/var/lib/mysql. sock:

Ln-s/tmp/mysql. sock/var/lib/mysql. sock




Author: zhengdl126bitsCN.com