linux服务器下mysql数据库自动备份

定时执行脚本: crontab -e
分 时 天 月 星期几
每天5点执行任务
0 5 * * * /path/to/backupsh.up
每天下午五点执行
0 17 * * * /path/to/backupsh.up

直接环境

https://cloud.tencent.com/developer/article/2407085

docker环境

#!/bin/bash

# Docker MySQL配置
CONTAINER_NAME="medictechserver"  # MySQL容器名称
DB_USER="test"
DB_PASSWORD="test.123456"
DB_NAME="medictech"  # 留空备份所有数据库

BACKUP_DIR="/data/backup/docker_mysql"
RETENTION_DAYS=7

# 日期格式
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="$BACKUP_DIR/$DATE"

# 创建备份目录
mkdir -p $BACKUP_PATH

# 备份函数
backup_database() {
    local db=$1
    echo "开始备份数据库: $db"
    
    # 在容器内执行mysqldump
    docker exec $CONTAINER_NAME mysqldump \
        -u$DB_USER \
        -p$DB_PASSWORD \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        $db > "$BACKUP_PATH/${db}_$DATE.sql"
    
    # 压缩备份文件
    gzip "$BACKUP_PATH/${db}_$DATE.sql"
    
    echo "数据库 $db 备份完成"
}

# 备份所有数据库
backup_all() {
    echo "备份所有数据库"
    docker exec $CONTAINER_NAME mysqldump \
        -u$DB_USER \
        -p$DB_PASSWORD \
        --all-databases \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        > "$BACKUP_PATH/all_databases_$DATE.sql"
    
    gzip "$BACKUP_PATH/all_databases_$DATE.sql"
}

# 执行备份
if [ -z "$DB_NAME" ]; then
    backup_all
else
    # 获取数据库列表
    databases=$(docker exec $CONTAINER_NAME mysql -u$DB_USER -p$DB_PASSWORD \
                -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")
    
    for db in $databases; do
        backup_database $db
    done
fi

# 删除过期备份
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

echo "备份完成：$BACKUP_PATH"