mysql备份脚本

1.MySQL 数据库备份单循环

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

2.MySQL 数据库备份多循环

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
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_NAME2>/dev/null; then
			echo "$BACKUP_NAME 备份失败!"
		fi
	done
done

3.MySQL 数据库备份分库备份

mysqldump -uroot -pxxx -B A > A.sql
#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
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

4.MySQL 数据库备份分表备份

mysqldump -uroot -pxxx -A t > t.sql
#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
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
done

5.MySQL 数据库备份分表备份

mysqldump -uroot -pxxx -A t > t.sql
#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
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
done

6.MySQL 数据库每天自动备份

#!/bin/sh
# Database info
DB_USER="batsing"
DB_PASS="batsingpw"
DB_HOST="localhost"
DB_NAME="timepusher"
# 一些变量
BIN_DIR="/usr/bin" #mysql bin路径
BCK_DIR="/mnt/mysqlBackup" #备份文件目录
DATE=`date +%F`
# TODO
# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher >
/mnt/mysqlBackup/db_`date +%F`.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME >
$BCK_DIR/db_$DATE.sql
#还原数据库
#用mysql-front导入前一天的 *.sql 文件即可恢复数据