MySQL数据库分库分表备份及恢复SHELL脚本备份脚本：
mysql_back_schame_table.sh
#!/bin/bash
#PATH="/usr/bin/mysql/bin:$PATH"DBPATH="./backup_file"
MYUSER=root
MYPASS=123456
SOCKET=/var/lib/mysql/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S $SOCKET"
[ ! -d "$DBPATH" ]&& mkdir $DBPATH
for dbname in `$MYCMD -e "show databases;"|sed '1,2d'|egrep -v "mysql|schema"`
do
mkdir $DBPATH/${dbname}_$(date +%F_%H-%M) -p
for table in `$MYCMD -e "show tables from $dbname;"|sed '1d'`
do
$MYDUMP --add-drop-table --flush-logs --flush-privileges --triggers --routines --events --master-data=2 --single-transaction $dbname $table|gzip>$DBPATH/${dbname}_$(date +%F_%H-%M)/${dbname}_${table}_$(date +%F_%H-%M-%S).sql.gz
echo "$(date +%F_%H-%M-%S)_Backup dbname:$dbname -->Table:$table success">>./mysql_back_schame_table.log
sleep 1
done
done
恢复shell脚本
#cat mysql_recover_schame_table.sh
#!/bin/bash
BASE_DIR=./backup_file
cd $BASE_DIR
ls -al
MYUSER=root
MYPASS=123456
SOCKET=/var/lib/mysql/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
read -p "Please input DB_NAME:" DB_NAME
cd $DB_NAME
ls -al
read -p "Please input TB_NAME:" TB_NAME
/bin/gzip -d $TB_NAME
TABLE_NAME=$(echo "$TB_NAME"|awk -F ".gz" '{print $1}')
echo $TABLE_NAME
DB_NAME_1=$(echo "$DB_NAME"|awk -F "_" '{print $1}')
echo $DB_NAME_1
read -p "DB_NAME IS $DB_NAME_1 TABLE_NAME IS $TABLE_NAME,[Yes/y]" line
case $line in
Yes|y)
$MYCMD $DB_NAME_1 < $TABLE_NAME
echo "Recover is OK"
;;
*)
echo "Input Error"
;;
esac
