Shell 脚本 - MySQL 数据库备份单循环

安装 mysql client 客户端工具

yum install mysql

#!/bin/bash
source /etc/profile
# 自动备份数据库下的用户库
# 数据库相关信息
HOST=localhost
USER=root
PASS=123456

# 备份目录，没有会自动创建
BACKUP_DIR=/data/db_backup/$(date +%F)
DATE=$(date +%F_%H-%M-%S)

[ ! -d ${BACKUP_DIR} ] && mkdir -p ${BACKUP_DIR} &>/dev/null

DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -vw "Database|information_schema|mysql|performance_schema|sys")

for DB in ${DB_LIST} 
do
    BACKUP_NAME=${BACKUP_DIR}/${DB}_${DATE}.sql
    if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB --default-character-set=utf8 --events --force --single-transaction |gzip > ${BACKUP_NAME}.gz 2>/dev/null; then
        echo "$BACKUP_NAME 备份失败!"
    fi
done

配置mysql备份保留时长

#!/bin/bash
source /etc/profile
# 自动备份数据库下的用户库
# 数据库相关信息
HOST=172.16.16.109
USER=root
PASS=root
PORT=3306
# 数据库备份保留多少天
BDAY=1


# 备份目录，没有会自动创建
BACKUP_PARENT=/data/db_backup
BACKUP_DIR=${BACKUP_PARENT}/$(date +%F)
DATE=$(date +%F_%H-%M-%S)

[ ! -d ${BACKUP_DIR} ] && mkdir -p ${BACKUP_DIR} &>/dev/null

# 保存多少天的数据
find ${BACKUP_PARENT} -maxdepth 1 -type d  -mtime +${BDAY}  -exec rm -rf {} \;

DB_LIST=$(mysql -h$HOST -P$PORT -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -vw "Database|information_schema|mysql|performance_schema|sys")

for DB in ${DB_LIST} 
do
    BACKUP_NAME=${BACKUP_DIR}/${DB}_${DATE}.sql
    if ! mysqldump -h$HOST -P$PORT -u$USER -p$PASS -B $DB --default-character-set=utf8 --events --force --single-transaction |gzip > ${BACKUP_NAME}.gz 2>/dev/null; then
        echo "$BACKUP_NAME 备份失败!"
    fi
done

注意：
2>/dev/null 会把标准错误重定向到 /dev/null

K8S 中使用容器 mysqldump 进行备份

#!/bin/bash
# K8S 环境下自动备份 MySQL 数据库下的用户库
# 数据库相关信息
source /etc/profile
USER=root
PASS=HDCDB.4ERxydt2

#宿主机保存备份目录
DEST_PATH="/data/data-bak/mysqlbak/"

# 容器内临时存放目录
BACKUP_DIR="/tmp"
DATE=$(date +%F_%H-%M)
# 数据库备份保留多少天
BDAY=1


MYSQL=`kubectl get po | grep mysql|head -1|awk '{print $1}'`
if [ ! $MYSQL ]
  then
    exit 1
else
    DB_LIST=$(kubectl exec -i ${MYSQL} -- mysql -u${USER} -p${PASS} -s -e "show databases;" 2>/dev/null |egrep -vw "Database|information_schema|mysql|performance_schema|sys")

    for DB in ${DB_LIST}
    do
        BACKUP_NAME=${BACKUP_DIR}/${DB}_${DATE}.sql
        kubectl exec ${MYSQL} -- bash -c "mysqldump -u${USER} -p${PASS} -B ${DB} --default-character-set=utf8 --events --force --single-transaction |gzip > ${BACKUP_NAME}.gz"
        kubectl cp ${MYSQL}:${BACKUP_NAME}.gz ${DEST_PATH}/${DB}_${DATE}.sql.gz
        kubectl exec -i ${MYSQL} -- rm -rf ${BACKUP_NAME}.gz
    done
fi

/bin/find ${DEST_PATH} -type f -name "*.gz" -mtime +${BDAY} -exec rm -rf {} \;