MySQL分库分表备份

MySQL分库分表备份

脚本内容如下

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=192.168.0.123
USER=root
PASS=数据库密码
BACKUP_DIR=/data/db_backup
#分库备份
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")
for DB in $DB_LIST; do
BACKUP_NAME=$BACKUP_DIR/${DB}${DATE}.sql
if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null; then
echo "$BACKUP_NAME 备份失败！"
fi
done
#分表备份
for I in $DB_LIST; do
BACKUP_DB_DIR=$BACKUP_DIR/${I}${DATE}
[ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $I;show tables;" 2>/dev/null)
for TABLE in $TABLE_LIST; do
BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql
if ! mysqldump -h$HOST -u$USER -p$PASS $I $TABLE > $BACKUP_NAME 2>/dev/null; then
echo "$BACKUP_NAME 备份失败！"
fi
done
done

测试结果