mysql备份

安装客户端软件

apt install redis-tools -y
apt install default-mysql-client -y

0 2 * * * /root/shellscript/dumpmysql.sh > /root/shellscript/log/dumpmysql.log 2>&1

备份脚本

#!/bin/bash


# 获取脚本开始执行的时间戳（秒）
start_time=$(date +%s)

# MySQL 连接参数
MYSQL_HOST="127.0.0.1"
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_PORT="3306"

time=$(date "+%Y-%m-%d %H:%M:%S")
echo "当前备份时间:${time}"
dir=$(date "+%Y%m%d")

# 输出目录
OUTPUT_DIR="/root/backupmysql/$dir"

# 确保输出目录存在
mkdir -p "$OUTPUT_DIR"

# 获取所有非系统库的名称
DATABASES=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW DATABASES;" | grep -Ev "^(information_schema|mysql|performance_schema|sys|test)$")

# 遍历每个数据库并导出
for DB in $DATABASES; do
    if [ "$DB" != "Database" ]; then  # 跳过表头
        echo "Exporting database: $DB"
        mysqldump -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$DB" > "$OUTPUT_DIR/$DB.sql"
        if [ $? -eq 0 ]; then
            echo "Backup of $DB completed successfully."
        else
            echo "Failed to backup $DB."
        fi
    fi
done


# 获取脚本结束执行的时间戳（秒）
end_time=$(date +%s)

# 计算执行耗时（秒）
elapsed_time=$((end_time - start_time))

echo "All databases have been exported to $OUTPUT_DIR. 耗时：${elapsed_time}秒"

# 删除10天前的备份
old_date=$(date -d "10 days ago" +'%Y%m%d')
old_backup_file="/root/backupmysql/${old_date}"
if [ -d "$old_backup_file" ]; then
    rm -rf "$old_backup_file"
    echo "Deleted old backup file: $old_backup_file"
fi