> For AI agents: the complete documentation index is available at https://www.tteam.icu/llms.txt, the full documentation bundle is available at https://www.tteam.icu/llms-full.txt.

# 数据库备份


**公司用**

```sh file="./bakdb/backup_database.sh"
#!/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

```


**增加七牛云**

```sh file="./bakdb/qiniu.sh"
#!/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}
```