使用mysqldump实现分库分表备份

最新推荐文章于 2024-07-11 21:41:59 发布

嚯.

最新推荐文章于 2024-07-11 21:41:59 发布

阅读量86

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/visuhq/article/details/132259259

版权

#!/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 "备份完成！"