一个脚本实现全量增量备份,并推送到远端备份中心服务器
__Untitled 发表于1周前
一个脚本实现全量增量备份,并推送到远端备份中心服务器
  1. 发表于 1周前
  2. 阅读 693
  3. 收藏 70
  4. 点赞 3
  5. 评论 2
“粉丝福利”_IT在线,精品课程讲解!>>>   
摘要 由于工作需要,刚好需要这样一个功能的脚本,主要解决: 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 rsa Generating 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-wj39106 The 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 keys root@10.0.0.3's password: Number of key(s) added: 1 Now 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.3 Last 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 mysql Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 541 Server version: 10.1.21-MariaDB MariaDB Server Copyright (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 fi eval set -- "$OPTS" # 参数默认值设定 HELP=0 VERSION=0 MYSQL_HOST='127.0.0.1' MYSQL_PORT=3306 MYSQL_USER='backup' MYSQL_PASS='' MYSQL_SOCK='' INCREMENTAL=0 COMPRESS_THREADS=8 EXTRA_LSNDIR='' INCREMENTAL_BASEDIR='' PARALLEL=4 STREAM='xbstream' REMOTE_SERVER='' BACKUP_DIR='/var/tmp' REMOTE_DIR='' # 参数赋值 while true do 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 ;; esac done # 显示版本 if [[ $VERSION -eq 1 ]] then echo "MySQL Adaptive Backup v1.0.0" exit 0 fi # 显示帮助 if [[ $HELP -eq 1 ]] then usage exit 0 fi # 对参数进行判断,如果没有提供则报错并退出 if ! [ -n "${EXTRA_LSNDIR}" ] then echo "Please specify the action you want to run with -e or -extra_lsndir" exit 1; fi if ! [ -n "${REMOTE_SERVER}" ] then echo "Please specify the action you want to run with -R or --remote-server" exit 1 fi if ! [ -n "${BACKUP_DIR}" ] then echo "Please specify the action you want to run with -b or --backup-dir" exit 1 fi if ! [ -n "${REMOTE_DIR}" ] then echo "Please specify the action you want to run with -D or --remote-dir" exit 1 fi if ! [ -n "${INCREMENTAL_BASEDIR}" ] then echo "Please specify the action you want to run with -d or --incremental-basedir" exit 1 fi # 开始备份 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.log else echo "[${d}] Make incremental backup" >> /var/tmp/backup.log CMD="${CMD} --incremental" CMD="${CMD} --incremental-basedir=${INCREMENTAL_BASEDIR}" fi CMD="${CMD} ${BACKUP_DIR}" CMD="${CMD} | ssh ${REMOTE_SERVER}" CMD="${CMD} \"cat - > ${REMOTE_DIR}_${t}.xbs\"" echo "[${d}] ${CMD}" >> /var/tmp/backup.log # 执行备份操作 eval $CMD exit 0 2.3 在数据库服务器端使用系统任务调度 # crontab -l 0 */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 -lah total 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。
标签: 数据库备份
  1. 打赏
  2. 点赞
  3. 收藏
  4. 分享
共有 人打赏支持
粉丝 7
博文 16
码字总数 20086
评论 (2)
clouddyy
好东西
kubei
要是几年前看到就好好了,因为备份问题不能很好解决 现在数据都放阿里云上了。
×
__Untitled
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额:
利发国际官方网