資料庫自動備份到七牛雲（省錢的土方案）

公司項目采用了多種程式設計語言，權衡業界各類API工具，最終采用了eolinker做為API管理工具。

無奈項目人數和關聯周邊較多，線上免費配額明顯不夠用（之前可以通過設定管理者可以繞過限制，後面新版本被封了），so 又另謀出路。

後面發現eolinker真是業界良心産品，開源了基礎功能代碼，完全滿足小公司需求：https://github.com/eolinker

自建管理系統就需要考慮備份的問題，目前思路如下：

1、注冊七牛賬号---有10G免費空間；

2、下載下傳七牛qshell程式檔案（七牛雲上傳檔案程式）到linux伺服器，然後熟悉使用方法：https://github.com/qiniu/qshell

3、編寫資料庫導出腳本，腳本導出完成後qshell上傳檔案到七牛；

4、linux定時執行備份腳本；

linux執行：
crontab -e
插入：
0 */12 * * *     /etc/scripts/mysql_backup.sh  >>/var/log/mysql_backup.log 2>&1

# 00.00, 08:00, 16:00 這幾個時間自動執行備份.      

注意：需要提前執行qshell指令完成密鑰設定（qshell account ak sk name），具體檢視：https://developer.qiniu.com/kodo/tools/1302/qshell#4

備份腳本思路如下：

#!/bin/bash
# Author: Sean,[email protected]
# Used for mysql backup

export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
BIN=mysql-backup-customhouse
LOGTIME='eval date "+%Y-%m-%d %H:%M:%S"'
export LC_ALL=en_US.UTF-8 LANG=en_US.UTF-8
echo "[ `$LOGTIME` | ${BIN} ]"

# Define the DB's user & password 
DB_HOST=$1
DB_PORT=$2
DB_USER=$3
DB_PASS=123456

# databases like: aaa,bbb,ccc
DB_TO_BACKUP=eolinker_os
MYSQL_CONF_FILE=/etc/my.cnf
BACKUP_PATH=/data/backup

[ -z "$DB_HOST" ] && DB_HOST=localhost
[ -z "$DB_PORT" ] && DB_PORT=3306
[ -z "$DB_USER" ] && DB_USER=root


# use ip address as hostid
if [ "$DB_HOST" = "localhost" -o "$DB_HOST" = "127.0.0.1" ]; then
    HOST_ID=`ip route get 8.8.8.8 | grep src | \
    awk '{ i=1; while(i<NF) { if ( $i == "src" ) print $(i+1); i++ }}'`
else
    HOST_ID=$DB_HOST
fi
 
# Del the old backup before 30 days, the default unit is "Day"
RESERVE_DAY=30

# backup to the dir 
DATE=`date "+%Y%m%d"`
TIME=`date "+%H%M%S"`

# loop to backup db
if [ -z "$DB_TO_BACKUP" ]; then 
    # zero means all
    DB_TO_BACKUP=`mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASS} <<EOF 2>/dev/null
show databases;
EOF
`
    # connection error
    if [ $? -ne 0 ]; then
        echo "[ `$LOGTIME` | ${BIN} ] DB connection error!"
        echo "[ `$LOGTIME` | ${BIN} ] Use: \"mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASS}\" to check, exit 10" && exit 10
    fi
    
    DB_TO_BACKUP=$(echo "$DB_TO_BACKUP" | egrep -v "Database|information_schema|performance_schema" | xargs)
fi

[ -z "$DB_TO_BACKUP" ] && echo "[ `$LOGTIME` | ${BIN} ] No database found, exit 0" && exit

for i in `echo $DB_TO_BACKUP | tr ',' ' '`; do
    echo "[ `$LOGTIME` | ${BIN} ] Begin to backup $i."
    mkdir -p ${BACKUP_PATH}/${HOST_ID}_${DATE}
    if mysqldump -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASS} \
    $i 2>/dev/null | gzip >${BACKUP_PATH}/${HOST_ID}_${DATE}/${i}_${TIME}.sql.gz; then
        echo "[ `$LOGTIME` | ${BIN} ] Successful backup to ${BACKUP_PATH}."
        BACK_SUCC="true"
    else
        echo "[ `$LOGTIME` | ${BIN} ] Backup $i failed!"
    fi
done

for i in `echo $DB_TO_BACKUP | tr ',' ' '`; do
 echo ${BACKUP_PATH}/${HOST_ID}_${DATE}/${i}_${TIME}.sql.gz
 /etc/scripts/qshellc/qshell_linux_x64 rput eolinker-mysql-virtual ${i}_${DATE}_${TIME}.sql.gz ${BACKUP_PATH}/${HOST_ID}_${DATE}/${i}_${TIME}.sql.gz -w
done

# Del the directory that old than $RESERVE_DAY
#if [ "$BACK_SUCC" = "true" ]; then
#    find $BACKUP_PATH -type d -mtime +$RESERVE_DAY -exec rm -rf {} \; 2>/dev/null
#fi

# qshell account ak sk name      

備份結果：