#!/bin/bash
# MySQL连接参数
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASSWORD="123456"
# 备份保存路径
BACKUP_DIR="/server/backup"
# 获取所有数据库列表
databases=$(mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")
# 循环遍历每个数据库
for db in $databases;
do
# 创建数据库备份目录
backup_path="$BACKUP_DIR/$db"
mkdir -p $backup_path
# 获取数据库中的所有表
tables=$(mysql -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD $db -e "SHOW TABLES;" | grep -v "Tables_in")
# 循环遍历每个表进行备份
for table in $tables;
do
# 备份文件名为 数据库名_表名.sql
backup_file="$backup_path/$db"_"$table".sql
# 使用 mysqldump 导出表数据到备份文件
mysqldump -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASSWORD --opt --skip-lock-tables --skip-comments --single-transaction --default-character-set=utf8mb4 $db $table > $backup_file
if [ $? -eq 0 ]; then
echo "备份成功:$backup_file"
else
echo "备份失败:$backup_file"
fi
done
done
echo "备份完成!"