mysql 数据备份和恢复

数据备份：

#!/bin/bash

export user=''
export backup_dir=''
export host=''
export password=''

get_input()
{
	read -p "Enter password:" -s password
}

do_backup()
{
	dbNames="$(mysql -u$user -p$password -h$host -Bse 'show databases')"
	mkdir -p $backup_dir
	echo ''	
	for dbName in ${dbNames[*]}
	do
		if [ "$dbName" == "information_schema" ] || [ "$dbName" == "performance_schema" ];then
			continue
		else
			dumpFile=$backup_dir/$dbName.sql
			mysqldump --add-drop-table --events -u$user -p$password -h$host --database --add-drop-database $dbName > $dumpFile
			if [[ $? == 0 ]]; then
				echo $dbName "dataBase backup succeeded!"
			else
				echo $dbName "dataBase backup failed!"
				exit 2
			fi
		fi
	done
	exit 0
}

#main
if [ $# -ne 3 ];then
	echo "usage: ./mysqlbackup.sh [username] [path] [ip]"
	exit 1
else
	user=$1
	backup_dir=$2
	host=$3
fi
get_input;
do_backup;

数据恢复：

#!/bin/bash

export user=''
export backup_dir=''
export host=''
export password=''

get_input()
{
	read -p "Enter password:" -s password
}

do_recovery()
{
	echo ''
	
	for file in `ls $backup_dir`
	do
		dbName=${file%%.*}
		mysql -u$user -p$password -h$host $dbName < $backup_dir/$file
		if [[ $? == 0 ]]; then
			echo $dbName "dataBase recovery succeeded!"
		else
			echo $dbName "dataBase recovery failed!"
			exit 2
		fi
	done
	exit 0
}

#main
if [ $# -ne 3 ];then
	echo "usage: ./mysqlrecovery.sh [username] [path] [ip]"
	exit 1
else
	user=$1
	backup_dir=$2
	host=$3
fi
get_input;
do_recovery;