Linux shell script for MySQL database scheduled backup

Specific methods

The code is as follows	Copy Code
#!/bin/bash #Shell Command for Backup MySQL Database everyday automatically by Crontab #Author: Carlos Wong #Date: 2010-08-24

#配置参数

code is as follows

copy code

user=root Database user name > user name password=xxxxx #数据库用户密码 database=tieniuzai    #数据库名称 webmaster= Tieniuzai@qq.com #管理员邮箱地址 to send a backup failure message reminding backup_dir=/var/www/data_backup/topons/#备份文件存储路径 logfile=/var/www/ Data_backup/topons/data_backup.log #日记文件路径 date= ' DATE ' +%y%m%d-%h%m ' #日期格式 (as file name) dumpfile= $DATE. SQL #备份文件名 archive= $DATE. sql.tgz #压缩文件名 options=-u$user-p$password–opt–extended-insert=false–triggers=false-r– Hex-blob–flush-logs–delete-master-logs-b $DATABASE "  #mysqldump parameter details see Help mysqldump-help

#判断备份文件存储目录是否存在, otherwise create the directory

The code is as follows	Copy Code
if [!-D $BACKUP _dir]; Then Mkdir-p "$BACKUP _dir" Fi

#开始备份之前, write the backup header to the journal file

The code is as follows	Copy Code
echo "" >> $LOGFILE echo "" >> $LOGFILE echo "——————————————— –" >> $LOGFILE echo "BACKUP Date:" $ (DATE + "%y-%m-%d%h:%m:%s") >> $LOGFILE echo "——————————————— –" >> $LOGFILE

#切换至备份目录

The code is as follows	Copy Code
CD $BACKUP _dir

#使用mysqldump the command back up the database and name the backup file in a formatted timestamp

The code is as follows	Copy Code
Mysqldump $OPTIONS > $DUMPFILE

#判断数据库备份是否成功

The code is as follows	Copy Code
if [[$ = = 0]]; Then

#创建备份文件的压缩包

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

#输入备份成功的消息到日记文件

The code is as follows	Copy Code
echo [$ARCHIVE] Backup successful! >> $LOGFILE

#删除原始备份文件, just keep the compressed package of the database backup file

The code is as follows	Copy Code
Rm-f $DUMPFILE Else echo "Database Backup fail!" >> $LOGFILE

#备份失败后向网站管理者发送邮件提醒, requires support for mailutils or similar terminals to send mail tools

The code is as follows	Copy Code
#mail-S "Database: $DATABASE daily Backup Fail" $WEBMASTER Fi

#输出备份过程结束的提醒消息

The code is as follows	Copy Code
echo "Backup Process Done"

Use:

01. Save the above code to:/usr/sbin/databackup (file name is arbitrary, as long as the system does not have the same name as the original command; code can be placed anywhere, placed in the Sbin directory only for easy execution, sbin directory files/directories can be directly called in the terminal, Similar to the directory specified under Windows path variable)
02. Add executable permissions to the script: sudo chmod +x/usr/sbin/databackup
03. Execute script: sudo databackup
04. If you need to perform a backup command on a regular basis, simply place the following code in the crontab file (sudo vim/etc/crontab):
3 * * * root/usr/sbin/databackup #它代表着将于每天3点执行DataBackup脚本

Small attention to the next:

The shell script under 01.linux defines the format of the variable as: key=value, notice that there is no space before or after the "=" between them, or the system cannot confirm the variable.
02. The line marked in red, the first character that resembles a single quote "'" is actually not a single quote, and its input key is below the ESC key of the keyboard.
03. This script is only suitable for backups of small sites because it is a full backup of the database instead of an incremental backup and is not suitable for large-capacity database backups.
Update:

Because the socket entry value in the MySQL configuration file on the server is "/tmp/mysql.sock", mysqldump will look for Mysql.sock files located in the/var/lib/mysql/directory when the MySQL server is linked. If not, the error will be:

Mysqldump:got Error:2002:can ' t connect to the local MySQL server through socket '/var/lib/mysql/mysql.sock '

The solution is:

First, modify the MySQL configuration file in the socket entry value of '/var/lib/mysql/mysql.sock ', but this method needs to restart the MySQL service, very unkind.

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

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