#!/bin/bash
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASSWORD="CentOS@MySQL123"
BACKUP_DIR="mysqldump/backup"
if [ ! -d $BACKUP_DIR ]; then
mkdir $BACKUP_DIR -p
fi
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
backup_file="$backup_path/$db"_"$table".sql
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
done
done
echo "备份完成!"
使用mysqldump实现分库分表备份
最新推荐文章于 2024-09-27 20:43:28 发布