tt's Note

    数据库备份

    公司用
    增加七牛云
    #!/bin/bash

#################################
# 备份 csv 表格内的数据库
# 支持 postgresql mysql kingbase
# kingbase_image="muen/kingbase-postgresql:V8"
#################################

log_args="
--log-driver=json-file \
--log-opt max-size="100m" \
--log-opt max-file=10 \
"

# 同步文件
function sync_file() {
    cmd_status=1
    run_times=0
    while [ ${cmd_status} != 0 ] && [ ${run_times} -le 3 ]; do  # 连续失败 3 次
        run_times=$((${run_times} + 1))
        cp -vrf $1 $2
        cmd_status=$?
    done
    if [ ${cmd_status} -eq 1 ];then
        rm -rf $1
    fi
}

# 使用 mysqldump 备份 mysql 数据库
function mysql_mysqldump_all() {
    backup_filename=${instance_name}.sql.gz
    docker run -i --net host --rm -e MYSQL_ROOT_PASSWORD=${src_password} ${log_args} ${docker_image} \
    mysqldump --host=${src_host} --port=${src_port} --user=${src_user} --password=${src_password} --single-transaction --master-data=2 --all-databases | gzip > ${CACHE_DIR}/${backup_filename}
    sync_file ${CACHE_DIR}/${backup_filename} ${backup_file_pre_dir}/${backup_filename}
}

# 使用 pg_basebackup 备份 postgresql
function postgresql_basebackup() {
    backup_filename=${instance_name}.tar.gz
    rm -rf ${CACHE_DIR}/postgresql_data/ && mkdir -p ${CACHE_DIR}/postgresql_data/
    docker run -i --net host --rm -e PGPASSWORD=${src_password} ${log_args} -v $(pwd)/${CACHE_DIR}/postgresql_data/:/tmp/postgresql_data/ ${docker_image} \
    pg_basebackup -v --format t --gzip --progress --write-recovery-conf --xlog --pgdata=/tmp/postgresql_data --host ${src_host} --port ${src_port} --username ${src_user}
    sync_file ${CACHE_DIR}/postgresql_data/base.tar.gz ${backup_file_pre_dir}/${backup_filename}
}

# 使用 pg_basebackup 备份 高版本 postgresql
function postgresql_high_version_basebackup() {
    backup_filename=${instance_name}.tar.gz
    rm -rf ${CACHE_DIR}/postgresql_data/ && mkdir -p ${CACHE_DIR}/postgresql_data/
    docker run -i --net host --rm -e PGPASSWORD=${src_password} ${log_args} -v $(pwd)/${CACHE_DIR}/postgresql_data/:/tmp/postgresql_data/ ${docker_image} \
    pg_basebackup -v --format t --gzip --progress --write-recovery-conf -X none --pgdata=/tmp/postgresql_data --host ${src_host} --port ${src_port} --username ${src_user}
    sync_file ${CACHE_DIR}/postgresql_data/base.tar.gz ${backup_file_pre_dir}/${backup_filename}
}

# 使用 sys_basebackup 备份 kingbase
function kingbase_basebackup() {
    backup_filename=${instance_name}.tar.gz
    backup_wal_filename=${instance_name}_wal.tar.gz
    rm -rf ${CACHE_DIR}/kingbase_data && mkdir -p ${CACHE_DIR}/kingbase_data && chown -R 1000.1000 ${CACHE_DIR}/kingbase_data
    docker run -i --net host --rm -e PGPASSWORD=${src_password} ${log_args} -v $(pwd)/${CACHE_DIR}/kingbase_data/:/tmp/kingbase_data/ ${docker_image} \
    sys_basebackup -v --format t --gzip --progress -D /tmp/kingbase_data -h ${src_host} -p ${src_port} -U ${src_user}
    sync_file ${CACHE_DIR}/kingbase_data/base.tar.gz ${backup_file_pre_dir}/${backup_filename}
    sync_file ${CACHE_DIR}/kingbase_data/sys_wal.tar.gz ${backup_file_pre_dir}/${backup_wal_filename}
}

# 主流程读取 csv 的数据库列表循环备份
function main() {
    csv_file=$1
    if [ "${csv_file}"x == ""x ] || [ ! -f ${csv_file} ];then
        echo "csv文件 ${csv_file} 不存在"
        exit 1
    fi
    # 备份日期
    BACKUP_DATE=$(date +%Y-%m-%d)
    line_num=0
    for instance in $(cat ${csv_file}|egrep -v "^#");do
        line_num=$((line_num + 1 ))
        if [ ${line_num} -ne 1 ];then
            db_type=$(echo "${instance}" |awk -F ',' '{print $9}')
            src_host=$(echo "${instance}" |awk -F ',' '{print $2}'|awk -F ':' '{print $1}')
            src_port=$(echo "${instance}" |awk -F ',' '{print $2}'|awk -F ':' '{print $2}')
            src_user=$(echo "${instance}" |awk -F ',' '{print $3}'|awk -F '/' '{print $1}')
            src_password=$(echo "${instance}" |awk -F ',' '{print $3}'|awk -F '/' '{print $2}')
            instance_name=$(echo "${instance}" |awk -F ',' '{print $1}')
            vpc=$(echo "${instance}" |awk -F ',' '{print $5}')
            docker_image=$(echo "${instance}" |awk -F ',' '{print $7}')
            if [ "${db_type}"x == ""x ] || [ "${src_host}"x == ""x ] || [ "${src_port}"x == ""x ] || [ "${src_user}"x == ""x ] || [ "${src_password}"x == ""x ] || [ "${instance_name}"x == ""x ] || [ "${vpc}"x == ""x ] || [ "${docker_image}"x == ""x ];then
                echo "参数db_type,src_host,src_port,src_user,src_password,vpc,docker_image其中一个或多个为空"
                exit 1;
            fi
            # 备份文件存放目录
            backup_file_pre_dir=${BACKUP_DIR}/${db_type,,}/${vpc}/${instance_name}/${BACKUP_DATE}
            mkdir -p ${backup_file_pre_dir}
            # 开始备份
            echo "正在备份数据库:${instance_name}"
            if [ "${db_type,,}"x == "postgresql"x ];then
                big_version=`echo ${docker_image} | sed -E 's/^[^:]+:([0-9]+)\..*$/\1/'`
                if [[ $big_version -gt 12 ]]; then
                    echo "判断版本为高版本"
                    postgresql_high_version_basebackup
                else
                    echo "判断版本为低版本"
                    postgresql_basebackup
                fi
            elif [ "${db_type,,}"x == "kingbase"x ];then
                kingbase_basebackup
            elif [ "${db_type,,}"x = "mysql"x ];then
                mysql_mysqldump_all
            fi
        fi
    done
}

# # csv 文件模板
# cat << EOF > template.csv
# ID,内网地址,外网地址,账号/密码,服务器,vpc,备注,版本,是否使用,数据库类型
# vm-mysql-70301,172.168.1.213:3306,null,root/password,vm-server-059,vm,wdh开发,mysql:5.7.35,true,MySQL
# vm-pg-70401,172.168.1.213:5433,null,postgres/password,vm-server-059,vm,wdh开发,muen/postgres:9.6.8,true,PostgreSQL
# EOF

# 必填，需要备份的数据库文件
CSV_FILE="aliyun-cn-sh.csv"
# 备份存放的目录,具体目录为 ${BACKUP_DIR}/${db_type}/${vpc}/${instance_name}/${BACKUP_DATE}
BACKUP_DIR=/data/oss/backup-mn-sh/db
# 缓存目录
CACHE_DIR=${LOG_DIR:-".cache"}
# 日志目录
LOG_DIR=${LOG_DIR:-"log"}
# 日志文件
LOG_FILE=${LOG_FILE:-"backup-$(date +%Y-%m-%d).log"}
# 工作目录
WORKDIR=/data/bakdb/scripts
# 初始化目录并开始备份
mkdir -p ${WORKDIR} && cd ${WORKDIR}
mkdir -p ${CACHE_DIR} ${LOG_DIR} && main ${CSV_FILE} >> ${LOG_DIR}/${LOG_FILE} 2>&1