2019獨角獸企業重金招聘Python工程師標準>>>

摘要

由于工作需要，剛好需要這樣一個功能的腳本，主要解決：

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: 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.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 -AWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 541 Server 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 ] thenexit 1 fieval 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 docase "$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 ]] thenecho "MySQL Adaptive Backup v1.0.0"exit 0 fi# 顯示幫助 if [[ $HELP -eq 1 ]] thenusageexit 0 fi# 對參數進行判斷，如果沒有提供則報錯并退出 if ! [ -n "${EXTRA_LSNDIR}" ] thenecho "Please specify the action you want to run with -e or -extra_lsndir"exit 1; fiif ! [ -n "${REMOTE_SERVER}" ] thenecho "Please specify the action you want to run with -R or --remote-server"exit 1 fiif ! [ -n "${BACKUP_DIR}" ] thenecho "Please specify the action you want to run with -b or --backup-dir"exit 1 fiif ! [ -n "${REMOTE_DIR}" ] thenecho "Please specify the action you want to run with -D or --remote-dir"exit 1 fiif ! [ -n "${INCREMENTAL_BASEDIR}" ] thenecho "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 "[$ze8trgl8bvbq] ----------------------------------------" >> /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做全備，其他時間做增量 thenecho "[$ze8trgl8bvbq] Make full backup" >> /var/tmp/backup.log elseecho "[$ze8trgl8bvbq] Make incremental backup" >> /var/tmp/backup.logCMD="${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 "[$ze8trgl8bvbq] ${CMD}" >> /var/tmp/backup.log# 執行備份操作 eval $CMDexit 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。

轉載于:https://my.oschina.net/u/1261643/blog/903450

總結

以上是生活随笔為你收集整理的一个脚本实现全量增量备份，并推送到远端备份中心服务器的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。