#数据库备份

#!/bin/bash

#################################
# 备份 csv 表格内的数据库
# 支持 postgresql mysql kingbase
#################################


# 使用 mysqldump 备份 mysql 数据库
function backup_database_from_mysql() {
    src_host=$1
    src_port=$2
    src_user=$3
    src_password=$4
    src_dbname=$5
    backup_file_pre_dir=$6
    backup_filename=${src_host}_${src_port}_${src_dbname}.sql.gz
    backup_file=${backup_file_pre_dir}/${src_host}_${src_port}_${src_dbname}.sql.gz
    cache_dir=".cache"
    mkdir -p ${cache_dir}
    if [ "${src_host}"x == ""x ] || [ "${src_port}"x == ""x ] || [ "${src_user}"x == ""x ] || [ "${src_password}"x == ""x ] || [ "${src_dbname}"x == ""x ];then
        echo "缺少参数,src_host,src_port,src_user,src_password,src_dbname"
        exit 1;
    fi
    docker run -i --net host --rm -e MYSQL_ROOT_PASSWORD=${src_password}  \
    --log-driver=json-file \
    --log-opt max-size="100m" \
    --log-opt max-file=10 \
    ${mysql_image} \
    mysqldump --host=${src_host} --port=${src_port} --user=${src_user} --password=${src_password} --single-transaction --quick --databases ${src_dbname} | gzip > ${cache_dir}/${backup_filename}
    mv -f ${cache_dir}/${backup_filename} ${backup_file}
}

# 使用 sql 查询 mysql的库名列表
function show_mysql_databases() {
    src_host=$1
    src_port=$2
    src_user=$3
    src_password=$4
    cache_dir=.cache
    mkdir -p ${cache_dir}
    if [ "${src_host}"x == ""x ] || [ "${src_port}"x == ""x ] || [ "${src_user}"x == ""x ] || [ "${src_password}"x == ""x ];then
        echo "缺少参数,src_host,src_port,src_user,src_password"
        exit 1;
    fi
    docker run -i --net host --rm -e MYSQL_ROOT_PASSWORD=${src_password} \
    --log-driver=json-file \
    --log-opt max-size="100m" \
    --log-opt max-file=10 \
    ${mysql_image} \
    mysql --host=${src_host} --port=${src_port} --user=${src_user} --password=${src_password} \
    -e "show databases;" | grep -v "Database"
}

# 使用 pg_dump 备份 postgresql 数据库
function backup_database_from_postgres() {
    src_host=$1
    src_port=$2
    src_user=$3
    src_password=$4
    src_dbname=$5
    backup_file_pre_dir=$6
    backup_filename=${src_host}_${src_port}_${src_dbname}.sql.gz
    backup_file=${backup_file_pre_dir}/${src_host}_${src_port}_${src_dbname}.sql.gz
    cache_dir=".cache"
    mkdir -p ${cache_dir}
    if [ "${src_host}"x == ""x ] || [ "${src_port}"x == ""x ] || [ "${src_user}"x == ""x ] || [ "${src_password}"x == ""x ] || [ "${src_dbname}"x == ""x ];then
        echo "缺少参数,src_host,src_port,src_user,src_password,src_dbname"
        exit 1;
    fi
    docker run -i --net host --rm -e PGPASSWORD=${src_password} \
    --log-driver=json-file \
    --log-opt max-size="100m" \
    --log-opt max-file=10 \
    ${postgres_image} \
    pg_dump -U ${src_user} -p ${src_port} -h ${src_host} -F p ${src_dbname} | gzip > ${cache_dir}/${backup_filename}
    mv -f ${cache_dir}/${backup_filename} ${backup_file}
}

# 使用 sql 查询数据库包含的库列表，排除一些系统库
function show_postgres_databases() {
    src_host=$1
    src_port=$2
    src_user=$3
    src_password=$4
    cache_dir=.cache
    mkdir -p ${cache_dir}
    if [ "${src_host}"x == ""x ] || [ "${src_port}"x == ""x ] || [ "${src_user}"x == ""x ] || [ "${src_password}"x == ""x ];then
        echo "缺少参数,src_host,src_port,src_user,src_password"
        exit 1;
    fi
    echo "COPY (select datname from pg_database where datname != 'postgres' and datname != 'template0' and datname != 'template1') TO stdout;" > ${cache_dir}/show_postgres_databases.sql
    docker run -i --net host --rm -e PGPASSWORD=${src_password} \
    --log-driver=json-file \
    --log-opt max-size="100m" \
    --log-opt max-file=10 \
    -v $(pwd)/${cache_dir}/show_postgres_databases.sql:/tmp/running.sql \
    ${postgres_image} \
    psql -U ${src_user} -p ${src_port} -h ${src_host} -f /tmp/running.sql
}

# 上传文件到 oss
function upload_oss_func {
    local_file=$1
    oss_file=$2
    # 安装 ossutil64
    accessKeyID=
    accessKeySecret=

    cat << EOF > /root/.ossutilconfig
[Credentials]
language=CH
endpoint=oss-cn-shanghai.aliyuncs.com
accessKeyID=${accessKeyID}
accessKeySecret=${accessKeySecret}
EOF

    ossutil64 --help > /dev/null 2>&1
    oss_result=$?
    if [ $oss_result -ne 0 ];then
        curl -O http://gosspublic.alicdn.com/ossutil/1.7.6/ossutil64 && chmod 755 ossutil64 && mv ossutil64 /usr/local/bin/
    fi
    /usr/local/bin/ossutil64 cp -f ${local_file} ${oss_file}
    if [ $? != 0 ];then
      echo "上传失败！！！退出"
      exit 1;
    fi
}

# 上传文件到 七牛oss
function upload_qiniu_oss_func {
    local_file=$1
    oss_file=$2
    # 安装 ossutil64
    access_key=
    secret_key=
    bucket_name=

    mkdir -p config
    cat << EOF > config/config.yaml
access_key: ${access_key}
secret_key: ${secret_key}
bucket_name: ${bucket_name}
EOF

    cat << EOF > upload_qiniu_oss.sh
docker run -id --rm \\
--name qiniu_upload \\
-v ./config/:/app/config/ \\
-v /data/:/data/ \\
registry.cn-hangzhou.aliyuncs.com/buyfakett/qiniu_upload \\
python3 main.py ${local_file} ${oss_file}
EOF

    chmod +x upload_qiniu_oss.sh
    ./upload_qiniu_oss.sh
}


# 添加该脚本到系统计划任务
function add_cron() {
    cron_file=/var/spool/cron/root
    touch ${cron_file}
    if [ $(cat ${cron_file} | grep "数据库备份计划任务" | wc -l ) -eq 0 ];then
      echo "# 数据库备份计划任务" >> ${cron_file}
      echo "0 1 * * * cd $(pwd) && /bin/sh -x $0 >> ${log_file} 2>&1" >> ${cron_file}
    fi
    chmod 600 ${cron_file}
}

# 使用 pg_basebackup 备份 postgresql
function postgresql_basebackup() {
    host=$1
    port=$2
    user=$3
    password=$4
    backup_file_pre_dir=$5
    backup_file=${backup_file_pre_dir}/${host}_${port}.tar.gz
    cache_data_dir=.cache_data
    rm -rf ${cache_data_dir}/
    mkdir -p ${cache_data_dir}/
    docker run -i --net host --rm -e PGPASSWORD=${password} \
    --log-driver=json-file \
    --log-opt max-size="100m" \
    --log-opt max-file=10 \
    -v $(pwd)/${cache_data_dir}/:/tmp/data/ ${postgres_image} \
    pg_basebackup -v --format t --xlog --gzip --progress -D /tmp/data -h ${host} -p ${port} -U postgres
    mv -f ${cache_data_dir}/base.tar.gz ${backup_file}
}

# 使用 sys_basebackup 备份 kingbase
function kingbase_basebackup() {
    host=$1
    port=$2
    user=$3
    password=$4
    backup_file_pre_dir=$5
    backup_file=${backup_file_pre_dir}/${host}_${port}.tar.gz
    backup_wal_file=${backup_file_pre_dir}/${host}_${port}_wal.tar.gz
    cache_data_dir=.cache_data
    rm -rf ${cache_data_dir}/ && mkdir -p ${cache_data_dir}/ && chown -R 1000.1000 ${cache_data_dir}
    docker run -i --net host --rm -e PGPASSWORD=${password} -v $(pwd)/${cache_data_dir}/:/tmp/data/ ${kingbase_image} \
    sys_basebackup -v --format t --gzip --progress -D /tmp/data -h ${host} -p ${port} -U ${user}
    mv -f ${cache_data_dir}/base.tar.gz ${backup_file}
    mv -f ${cache_data_dir}/sys_wal.tar.gz ${backup_wal_file}
}

# 主流程读取 csv 的数据库列表循环备份
function main() {
    add_cron
    csv_file=$1
    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 $1}')
            src_host=$(echo "${instance}" |awk -F ',' '{print $2}')
            src_port=$(echo "${instance}" |awk -F ',' '{print $3}')
            src_user=$(echo "${instance}" |awk -F ',' '{print $4}')
            src_password=$(echo "${instance}" |awk -F ',' '{print $5}')
            instance_name=$(echo "${instance}" |awk -F ',' '{print $6}')
            vpc=$(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 ];then
                echo "缺少参数,db_type,src_host,src_port,src_user,src_password"
                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 [ "${BASE_BACKUP}" == true ] && [ "${db_type}"x == "postgresql"x ];then
                # 备份文件全路径
                postgresql_basebackup ${src_host} ${src_port} ${src_user} ${src_password} ${src_dbname} ${backup_file_pre_dir}
                if [ "${OSS}" == true ];then
                    oss_file=${oss_dir}/${db_type}/${vpc}/${instance_name}/${backup_date}/${src_host}_${src_port}.tar.gz
                    upload_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}.tar.gz ${oss_file}
                fi
                if [ "${QINIU_OSS}" == true ];then
                    oss_file=${backup_date}/${src_host}_${src_port}.tar.gz
                    upload_qiniu_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}.tar.gz ${oss_file}
                fi
            elif [ "${BASE_BACKUP}" == true ] && [ "${db_type}"x == "kingbase"x ];then
                # 备份文件全路径
                kingbase_basebackup ${src_host} ${src_port} ${src_user} ${src_password} ${src_dbname} ${backup_file_pre_dir}
                if [ "${OSS}" == true ];then
                    oss_file=${oss_dir}/${db_type}/${vpc}/${instance_name}/${backup_date}/${src_host}_${src_port}.tar.gz
                    upload_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}.tar.gz ${oss_file}
                fi
                if [ "${QINIU_OSS}" == true ];then
                    oss_file=${backup_date}/${src_host}_${src_port}.tar.gz
                    upload_qiniu_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}.tar.gz ${oss_file}
                fi
            elif [ "${db_type}"x == "postgresql"x ];then
                databases=$(show_postgres_databases ${src_host} ${src_port} ${src_user} ${src_password})
                for src_dbname in ${databases};do
                    # 备份文件全路径
                    backup_database_from_postgres ${src_host} ${src_port} ${src_user} ${src_password} ${src_dbname} ${backup_file_pre_dir}
                    if [ "${OSS}" == true ];then
                        oss_file=${oss_dir}/${db_type}/${vpc}/${instance_name}/${backup_date}/${src_host}_${src_port}_${src_dbname}.sql.gz
                        upload_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}_${src_dbname}.sql.gz ${oss_file}
                    fi
                    if [ "${QINIU_OSS}" == true ];then
                        oss_file=${backup_date}/${src_host}_${src_port}.tar.gz
                        upload_qiniu_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}.tar.gz ${oss_file}
                    fi
                done
            elif [ "${db_type}"x = "mysql"x ];then
                databases=$(show_mysql_databases ${src_host} ${src_port} ${src_user} ${src_password})
                for src_dbname in ${databases};do
                    # 备份文件全路径
                    backup_database_from_mysql ${src_host} ${src_port} ${src_user} ${src_password} ${src_dbname} ${backup_file_pre_dir}
                    if [ "${OSS}" == true ];then
                        oss_file=${oss_dir}/${db_type}/${vpc}/${instance_name}/${backup_date}/${src_host}_${src_port}_${src_dbname}.sql.gz
                        upload_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}_${src_dbname}.sql.gz ${oss_file}
                    fi
                    if [ "${QINIU_OSS}" == true ];then
                        oss_file=${backup_date}/${src_host}_${src_port}.tar.gz
                        upload_qiniu_oss_func ${backup_file_pre_dir}/${src_host}_${src_port}.tar.gz ${oss_file}
                    fi
                done
            fi
        fi
    done
}


# 文件模板
cat << EOF > template.csv
数据库类型,数据库地址,数据库端口,数据库账号,数据库密码,数据库名称,vpc,备注
postgresql,172.168.1.218,5433,postgres,password,wdh-dev,vm,wdh开发
mysql,172.168.1.218,3306,root,password,wdh-dev,vm,shop
EOF

# 必填，需要备份的数据库文件
csv_file="wx_video.csv"
if [ ! -f ${csv_file} ];then
    echo "csv文件 ${csv_file} 不存在"
    exit 1
fi

# 备份日期
backup_date=$(date +%Y-%m-%d)
# 备份数据库对应使用的 docker 镜像
postgres_image="postgres:9.6"
mysql_image="mysql:5.7.35"
kingbase_image="muen/kingbase-postgresql:V8"

# 备份目录,具体目录为 ${backup_dir}/${db_type}/${vpc}/${instance_name}/${backup_date}
backup_dir=/data/bakdb/
# 计划任务日志文件
log_file=backup.log
# 使用 pg_basebackup 备份
BASE_BACKUP=true
# 是否上传 oss
OSS=false
# 是否上传七牛 oss
QINIU_OSS=true
# oss 的存储位置
oss_dir=oss://zhwx-backup-cd/db

# 开始运行备份
main ${csv_file}