mysql数据备份脚本

mysql数据备份

---

分库 分表

#-s 去掉边框
mysql -uroot -p密码 -s -e "show databases;"
#排除所有的系统库
mysql -uroot -p密码 -s -e "show databases;" | egrep -v "infor|perfor|sys|mysql"

分库备份

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
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

分表备份

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
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_DB_DIR=$BACKUP_DIR/${DB}_${DATE}
	[ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
	TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;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 $DB $TABLE > $BACKUP_NAME 2>/dev/null; then
			echo "$BACKUP_NAME 备份失败！"
		fi
done