2015博客升级记(七):CentOS7定时备份MySql的shell脚本
这是《2015年博客升级记》系列文章的第七篇,主要记录在Linux系统中如何使用shell脚本备份服务器上的mysql数据库。
1 使用说明
下面是MySql数据库的shell备份脚本,其中参数DBLIST
表示要备份的mysql数据库名称、NUMDAYS
表示保留多少天以内的备份文件、MyUSER
和MyPASS
表示mysql数据库的用户名和密码。最终,每次执行这个脚本生成的备份文件名类似于系统日期时间+.sql.gz
,例如20150606.sql.gz。
使用时通过chmod +x mysql_backup.sh
命令添加执行权限,然后可以利用Linux系统的crontab定时任务工具执行该脚本。例如下面这个配置就是每天凌晨1点的时候执行该shell脚本,生成mysql数据库的备份文件。
[root@typecodes bak_list]# crontab -e
0 1 * * * /mydata/backups/bak_list/mysql_backup.sh
2 脚本mysql_backup.sh的全部内容
以后的更新都会提交到本文小节3中的两个git仓库中。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | #!/bin/bash
# MySQL Backup Script v1.0.0
# (c) 2015 vfhky https://typecodes.com/linux/centos7mysqlregularbackup.html
# Reference: https://github.com/chekolyn/bash-scripts/blob/master/mysql-dbs-backup.sh
# https://github.com/vfhky/shell-tools/blob/master/backup/mysql_backup.sh
# https://coding.net/u/vfhky/p/shell-tools/git/blob/master/backup/mysql_backup.sh
# Space separated list of databases
DBLIST="your mysql database name witch you want to backup"
# Backup to this directory
BACKUPDIR=/mydata/backups/data/mysql
# Number of days to keep
NUMDAYS=60
# Some linux command and your mysql configure
FINDCMD="find"
MYSQLCMD="mysql"
MyUSER="your mysql user name" # USERNAME
MyPASS="your mysql password" # PASSWORD
MyHOST="localhost" # Hostname
DUMPCMD="mysqldump -u$MyUSER -h $MyHOST -p$MyPASS --lock-tables --databases "
GZIPCMD="gzip"
# Backup date format,e.g 20150505_2010
BACKUPDATE=`date +%Y%m%d_%H%M`
function USAGE() {
cat << EOF
usage: $0 options
This script backs up a list of MySQL databases.
OPTIONS:
-h Show this message
-a Backup all databases
-l Databases to backup (space seperated)
-n Number of days to keep backups
EOF
}
while getopts "hal:n:" opt; do
case $opt in
a)
DBLIST=""
;;
h)
USAGE
exit 1
;;
l)
DBLIST="$OPTARG"
;;
n)
NUMDAYS=$OPTARG
;;
\?)
USAGE
exit
;;
:)
echo "Option -$OPTARG requires an argument." >&2
exit 1
;;
esac
done
function ERROR() {
echo && echo "[error] $@"
exit 1
}
function NOTICE() {
echo && echo "[notice] $@"
}
function RUNCMD() {
echo $@
eval $@
}
# Sanity checks
if [ ! -n "$DBLIST" ]; then
DBLIST=`$MYSQLCMD -N -s -e "show databases" | grep -viE '(information_schema|performance_schema|mysql|test)'`
if [ ! -n "$DBLIST" ]; then
ERROR "Invalid database list"
fi
fi
if [ ! -n "$BACKUPDIR" ]; then
ERROR "Invalid backup directory"
fi
if [[ ! $NUMDAYS =~ ^[0-9]+$ ]]; then
ERROR "Invalid number of days: $NUMDAYS"
elif [ "$NUMDAYS" -eq "0" ]; then
ERROR "Number of days must be greater than zero"
fi
# Lock down permissions
umask 077
# Create directory if needed
RUNCMD mkdir -p -v $BACKUPDIR
if [ ! -d $BACKUPDIR ]; then
ERROR "Invalid directory: $BACKUPDIR"
fi
NOTICE "Dumping MySQL databases..."
RC=0
for database in $DBLIST; do
NOTICE "Dumping $database..."
RUNCMD "$DUMPCMD $database | $GZIPCMD > $BACKUPDIR/$BACKUPDATE.sql.gz"
RC=$?
if [ $RC -gt 0 ]; then
continue;
fi
done
if [ $RC -gt 0 ]; then
ERROR "MySQLDump failed!"
else
NOTICE "Removing dumps older than $NUMDAYS days..."
RUNCMD "$FINDCMD $BACKUPDIR -name \"*.sql.gz\" -type f -mtime +$NUMDAYS -print0 | xargs -0 rm -fv"
NOTICE "Listing backup directory contents..."
RUNCMD ls -la $BACKUPDIR
NOTICE "MySQLDump is complete!"
fi
# exit 0
|
3 Shell Script工具集合
目前把这个脚本托管在自己的Coding.net和GitHub的上一个shell script工具集合,地址如下:
Coding.net地址:https://coding.net/u/vfhky/p/shell-tools/git。
GitHub地址:https://github.com/vfhky/shell-tools/。
打赏支持