摘要

由于工作需要，刚好需要这样一个功能的脚本，主要解决：

1. 不想在crontab中调度两条备份任务，一个做全量一个做增量

2. 如果每小时做增量，凌晨4点做全量，那会导致crontab写的复杂，增量需要跳过凌晨4点

3. 自动推备份文件到远程的备份中心服务器

前提

1. 操作系统CentOS，其他没测，当然理论上没问题

2. 本地运行MySQL/MariaDB数据库服务

3. 安装必要的包

3.1 percona-toolkit

3.2 percona-xtrabackup

4. 演示服务器

4.1 10.0.0.2 CentOS 7.3 x86_64 / MariaDB 10.1.21 + percona-toolkit-3.0.2 + percona-xtrabackup-2.3.6 数据库

4.2 10.0.0.3 CentOS 7.3 x86_64 备份

步骤

1. 打通服务器互信

1.1 登录到数据库服务器，在终端执行ssh-keygen -t rsa

# ssh-keygen -t rsaGenerating public/private rsa key pair.Enter file in which to save the key (/root/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa.Your public key has been saved in /root/.ssh/id_rsa.pub.The key fingerprint is:SHA256:Nwh9Ao11ea3HcRS49wcygYmEfwQxUUL2RVS04QMZHMc root@rx-wj39106The key's randomart image is:+---[RSA 2048]----+|      .B@*oBBO*oo||      o+o==.==Eo.||      ..o.o. +=o ||       ..+. +.+o ||        S.o  +...||         . .    o||                .||                 ||                 |+----[SHA256]-----+

1.2 复制公钥到备份服务器ssh-copy-id -i ~/.ssh/id_rsa.pub  root@10.0.0.3

# ssh-copy-id -i ~/.ssh/id_rsa.pub  root@10.0.0.3/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keysroot@10.0.0.3's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh 'root@10.0.0.3'"and check to make sure that only the key(s) you wanted were added.

1.3 测试登录 ssh root@10.0.0.3

# ssh root@10.0.0.3Last login: Thu May 18 11:10:47 2017 from 10.0.0.2

第一步完成。

2. 准备备份

2.1 登录到数据库服务器，创建备份专用用户，并授权

# mysql -h 127.0.0.1 -u root -p mysqlEnter password: Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 541Server version: 10.1.21-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [mysql]> CREATE USER backup@'127.0.0.1' IDENTIFIED BY 'changeme';Query OK, 0 rows affected (0.08 sec)MariaDB [mysql]> GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW ON *.* TO backup@'127.0.0.1';Query OK, 0 rows affected (0.00 sec)MariaDB [mysql]>

2.2 保存下面脚本为/usr/bin/backup-database.sh

#!/bin/bash# 数据库自适应备份脚本# 主要功能对innobackupex做了二次封装，一个脚本实现对整个服务器的全量和增量备份# 通过crontab调度，可以每小时或者每隔一小时执行，默认在凌晨4点做全备，其他时段# 做增量备份。# 脚本采用流的方式推送备份文件到远端服务器，所以需要打通服务器互信# 当然脚本也获取了当前的星期/月/日，可以自行做其他策略，比如每周一次全备，则只# 需要在全备的判断中增加对于周的检测# 显示使用帮助usage () {	echo "Usage: ${0} [OPTION...]"	echo "  -H, --help          This option displays a help screen and exits."	echo "  -n, --compress-threads=#"	echo "                      This option specifies the number of worker threads that"	echo "                      will be used for parallel compression. It is passed"	echo "                      directly to the xtrabackup child process."	echo "  -e, --extra-lsndir=name"	echo "                      This option specifies the directory in which to save an"	echo "                      extra copy of the \"xtrabackup_checkpoints\" file. The"	echo "                      option accepts a string argument. It is passed directly"	echo "                      to xtrabackup's --extra-lsndir option. See the xtrabackup"	echo "                      documentation for details."	echo "  -d, --incremental-basedir=name"	echo "                      This option specifies the directory containing the full"	echo "                      backup that is the base dataset for the incremental"	echo "                      backup.  The option accepts a string argument."	echo "  -r, --parallel=#    On backup, this option specifies the number of threads"	echo "                      the xtrabackup child process should use to back up files"	echo "                      concurrently.  The option accepts an integer argument. It"	echo "                      is passed directly to xtrabackup's --parallel option. See"	echo "                      the xtrabackup documentation for details."	echo "  -s, --stream=name   This option specifies the format in which to do the"	echo "                      streamed backup.  The option accepts a string argument."	echo "                      The backup will be done to STDOUT in the specified"	echo "                      format. Currently, the only supported formats are tar and"	echo "                      xbstream. This option is passed directly to xtrabackup's"	echo "                      --stream option."	echo "  -u, --user=name     This option specifies the MySQL username used when"	echo "                      connecting to the server, if that's not the current user."	echo "                      The option accepts a string argument. See mysql --help"	echo "                      for details."	echo "  -h, --host=name     This option specifies the host to use when connecting to"	echo "                      the database server with TCP/IP.  The option accepts a"	echo "                      string argument. See mysql --help for details."	echo "  -P, --port=#        This option specifies the port to use when connecting to"	echo "                      the database server with TCP/IP.  The option accepts a"	echo "                      string argument. See mysql --help for details."	echo "  -p, --password=name This option specifies the password to use when connecting"	echo "                      to the database. It accepts a string argument.  See mysql"	echo "                      --help for details."	echo "  -S, --socket=name   This option specifies the socket to use when connecting"	echo "                      to the local database server with a UNIX domain socket."	echo "                      The option accepts a string argument. See mysql --help"	echo "                      for details."	echo "  -b, --backup-dir    The local backup directory for temp use"	echo "  -R, --remote-server The remote server with SSH where you want to put the backup"	echo "                      file into."	echo "  -D, --remote-dir    The backup directory on remote server"	echo "  -v, --version       Output version information and exit."}# 读取命令后参数OPTS=`getopt -o Hvh:P:u:p:S:n:e:d:r:m:R:b:D: --long help,version,host:,port:,user:,password:,socket:,compress-threads:,extra-lsndir:,incremental-basedir:,parallel:,stream:,remote-server:,backup-dir:,remote-dir: -n 'parse-options' -- "$@"`if [ $? != 0 ]then	exit 1 fieval set -- "$OPTS"# 参数默认值设定HELP=0VERSION=0MYSQL_HOST='127.0.0.1'MYSQL_PORT=3306MYSQL_USER='backup'MYSQL_PASS=''MYSQL_SOCK=''INCREMENTAL=0COMPRESS_THREADS=8EXTRA_LSNDIR=''INCREMENTAL_BASEDIR=''PARALLEL=4STREAM='xbstream'REMOTE_SERVER=''BACKUP_DIR='/var/tmp'REMOTE_DIR=''# 参数赋值while truedo	case "$1" in		-H | --help                ) HELP=1;                 break   ;; # 显示帮助信息，无需解析更多参数直接退出		-v | --version             ) VERSION=1;              break   ;; # 显示版本信息，无需解析更多参数直接退出		-h | --host                ) MYSQL_HOST=$2;          shift 2 ;; # 备份的主机，默认localhost		-P | --port                ) MYSQL_PORT=$2;          shift 2 ;; # 服务端口，默认3306		-u | --user                ) MYSQL_USER=$2;          shift 2 ;; # 登录用户，默认backup		-p | --password            ) MYSQL_PASS=$2;          shift 2 ;; # 登录密码		-S | --socket              ) MYSQL_SOCK=$2;          shift 2 ;; # 嵌套字文件位置		-n | --compress-threads    ) COMPRESS_THREADS=$2;    shift 2 ;; # 压缩线程数，默认开启压缩		-e | --extra-lsndir        ) EXTRA_LSNDIR=$2;        shift 2 ;; # 检查点信息保存的位置，存在则覆盖		-d | --incremental-basedir ) INCREMENTAL_BASEDIR=$2; shift 2 ;; # 		-r | --parallel            ) PARALLEL=$2;            shift 2 ;; # 并发子进程数量		-m | --stream              ) STREAM=$2;              shift 2 ;; # 数据流格式，默认xbstream		-R | --remote-server       ) REMOTE_SERVER=$2;       shift 2 ;; # 远程服务器信息，比如root@10.0.0.1		-b | --backup-dir          ) BACKUP_DIR=$2;          shift 2 ;; # 本地工作目录，默认/var/tmp		-D | --remote-dir          ) REMOTE_DIR=$2;          shift 2 ;; # 远程备份路径，比如/data/backup		-- ) shift; break ;;		* ) break ;;	esacdone# 显示版本if [[ $VERSION -eq 1 ]]then	echo "MySQL Adaptive Backup v1.0.0"	exit 0fi# 显示帮助if [[ $HELP -eq 1 ]]then	usage	exit 0fi# 对参数进行判断，如果没有提供则报错并退出if ! [ -n "${EXTRA_LSNDIR}" ]then	echo "Please specify the action you want to run with -e or -extra_lsndir"	exit 1;fiif ! [ -n "${REMOTE_SERVER}" ]then	echo "Please specify the action you want to run with -R or --remote-server"	exit 1fiif ! [ -n "${BACKUP_DIR}" ]then	echo "Please specify the action you want to run with -b or --backup-dir"	exit 1fiif ! [ -n "${REMOTE_DIR}" ]then	echo "Please specify the action you want to run with -D or --remote-dir"	exit 1fiif ! [ -n "${INCREMENTAL_BASEDIR}" ]then	echo "Please specify the action you want to run with -d or --incremental-basedir"	exit 1fi# 开始备份d=$(date +"%Y-%m-%d %H:%M:%S") # 当前时间，写日志需要使用h=$(date +"%k")                # 当前的小时数，24小时制d=$(date +"%-d")               # 当前日期的天，可用用于更复杂的备份策略m=$(date +"%-m")               # 当前日期的月，可以用于更复杂的备份策略w=$(date +"%u")                # 当前日期的周，可以用于更复杂的备份策略t=$(date +"%Y_%m_%d_%H_%M")    # 备份文件名上的时间戳echo "[${d}] ----------------------------------------" >> /var/tmp/backup.log# 生成备份命令CMD="innobackupex --compress"CMD="${CMD} --host=${MYSQL_HOST}"CMD="${CMD} --port=${MYSQL_PORT}"CMD="${CMD} --user=${MYSQL_USER}"CMD="${CMD} --password=${MYSQL_PASS}"CMD="${CMD} --compress-threads=${COMPRESS_THREADS}"CMD="${CMD} --parallel=${PARALLEL}"CMD="${CMD} --extra-lsndir=${EXTRA_LSNDIR}"CMD="${CMD} --stream=xbstream"if [[ ${h} -eq 4 ]] # 每天凌晨4:00做全备，其他时间做增量then	echo "[${d}] Make full backup" >> /var/tmp/backup.logelse	echo "[${d}] Make incremental backup" >> /var/tmp/backup.log	CMD="${CMD} --incremental"	CMD="${CMD} --incremental-basedir=${INCREMENTAL_BASEDIR}"fiCMD="${CMD} ${BACKUP_DIR}"CMD="${CMD} | ssh ${REMOTE_SERVER}"CMD="${CMD} \"cat - > ${REMOTE_DIR}_${t}.xbs\""echo "[${d}] ${CMD}" >> /var/tmp/backup.log# 执行备份操作eval $CMDexit 0

2.3 在数据库服务器端使用系统任务调度

# crontab -l0 */1 * * * sh /usr/bin/backup-database.sh -p changeme -e /root/backup -R root@10.0.0.3 -D /root/backup -b /root/backup -d /root/backup

测试是每个整点做备份，也可以根据自身的需求更改

2.4 查看日志

备份脚本会写日志到/var/tmp/backup.log，这个后续考虑通过命令行更改

# cat /var/tmp/backup.log[2017-05-18 04:00:01] ----------------------------------------[2017-05-18 04:00:01] Make full backup[2017-05-18 04:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream /root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_04_00.xbs"[2017-05-18 05:00:01] ----------------------------------------[2017-05-18 05:00:01] Make incremental backup[2017-05-18 05:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_05_00.xbs"[2017-05-18 06:00:01] ----------------------------------------[2017-05-18 06:00:01] Make incremental backup[2017-05-18 06:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_06_00.xbs"[2017-05-18 07:00:01] ----------------------------------------[2017-05-18 07:00:01] Make incremental backup[2017-05-18 07:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_07_00.xbs"[2017-05-18 08:00:01] ----------------------------------------[2017-05-18 08:00:01] Make incremental backup[2017-05-18 08:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_08_00.xbs"[2017-05-18 09:00:01] ----------------------------------------[2017-05-18 09:00:01] Make incremental backup[2017-05-18 09:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_09_00.xbs"[2017-05-18 10:00:01] ----------------------------------------[2017-05-18 10:00:01] Make incremental backup[2017-05-18 10:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_10_00.xbs"[2017-05-18 11:00:01] ----------------------------------------[2017-05-18 11:00:01] Make incremental backup[2017-05-18 11:00:01] innobackupex --compress --host=127.0.0.1 --port=3306 --user=backup --password=changeme --compress-threads=8 --parallel=4 --extra-lsndir=/root/backup --stream=xbstream --incremental --incremental-basedir=/root/backup /root/backup | ssh root@10.0.0.3 "cat - > /root/backup_2017_05_18_11_00.xbs"

2.5 登录备份中心服务器查看

# ls -lahtotal 1644-rw-------. 1 root root    880 Feb  7 16:58 anaconda-ks.cfg-rw-r--r--  1 root root 4.2M May 18 04:01 backup_2017_05_18_04_00.xbs-rw-r--r--  1 root root 315K May 18 05:01 backup_2017_05_18_05_00.xbs-rw-r--r--  1 root root 315K May 18 06:01 backup_2017_05_18_06_00.xbs-rw-r--r--  1 root root 315K May 18 07:01 backup_2017_05_18_07_00.xbs-rw-r--r--  1 root root 315K May 18 08:01 backup_2017_05_18_08_00.xbs-rw-r--r--  1 root root 315K May 18 09:01 backup_2017_05_18_09_00.xbs-rw-r--r--  1 root root 344K May 18 10:01 backup_2017_05_18_10_00.xbs-rw-r--r--  1 root root 348K May 18 11:01 backup_2017_05_18_11_00.xbs

本文仅是演示目的，目录没有做更多的规划，考虑在生产服务器，具体备份位置看磁盘挂载的位置，一般可能是/opt/data或者/data。