Regular MySQL database backup using shell scripts in linux

There are many ways to back up a database. the most common method is automatic and scheduled Database Backup. next I will introduce how to use shell scripts in linux to back up a MySQL database regularly.

There are many ways to back up a database. the most common method is automatic and scheduled Database Backup. next I will introduce how to use shell scripts in linux to back up a MySQL database regularly.

Method

The code is as follows:
#! /Bin/bash # Shell Command For Backup MySQL Database Everyday Automatically By Crontab # Author: Carlos Wong # Date: 2010-08-24

# Configuration parameters

The code is as follows:

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.

The code is as follows:
If [! -D $ BACKUP_DIR]; Then Mkdir-p "$ BACKUP_DIR" Fi

# Before starting backup, write the backup information header to the log file

The code is as follows:
Echo ""> $ LOGFILE Echo ""> $ LOGFILE Echo "----------------"> $ LOGFILE Echo "backup date:" $ (date + "% y-% m-% d % H: % M: % S") >>$ LOGFILE Echo "----------------"> $ LOGFILE

# Switch to the backup directory

The code is as follows:
Cd $ BACKUP_DIR

# Use the mysqldump command to back up the database and name the backup file with the formatted timestamp

The code is as follows:
Mysqldump $ OPTIONS> $ DUMPFILE

# Determine whether the database backup is successful

The code is as follows:
If [[$? = 0]; then

# Create a compressed backup file package

The code is as follows:
Tar czvf $ ARCHIVE $ DUMPFILE >>$ LOGFILE 2> & 1

# Enter a message indicating a successful backup to the log file.

The code is as follows:
Echo "[$ ARCHIVE] Backup Successful !" >>$ LOGFILE

# Delete the original backup file. you only need to keep the compressed package of the database backup file.

The code is as follows:
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.

The code is as follows:
# Mail-s "Database: $ DATABASE Daily Backup Fail" $ WEBMASTER Fi

# Output the reminder message for the backup process to end

The code is as follows:
Echo "Backup Process Done"

Usage:

01. 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)
02. add executable permissions to the script: sudo chmod + x/usr/sbin/DataBackup
03. execute the script: sudo DataBackup
04. 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:

01. the shell script in linux defines the variable format as: key = value. Note that there is no space before and after "=" between the two. Otherwise, the system cannot confirm the variable.
02. 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.
03. this script is only suitable for backup of small sites, because it backs up all the databases instead of incremental backups, and is not suitable for large-capacity database backup.
Update:

Because the socket item 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 the mysql service to be restarted, which is not a great deal.

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

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