部分参考来自网络,没有引用出处请见谅。
背景:因为上一篇文章说的是配置异步复制,需要从其他数据库中导出相关的数据库到主机中。
但是并不是全部导出,包含数据库例如:
ser_XXX_01
ser_XXX_02
ser_XXX_03
ser_XXX_04
ser_XXX_05
cli_YYY_01
cli_YYY_02
cli_YYY_03
cli_YYY_04
iat_ZZZ_01
iat_ZZZ_02
iat_ZZZ_03
而我需要的知识ser_XXX_0*。其实数量要比这大得多。所以手工导出导入的话相当费时,不如多动脑子。
所以就有了如下节奏。
备份(导出)脚本backup - mysql备份.sh如下:
#!/bin/bash
#1.some mysql params:
mysql_host="192.168.0.110"
mysql_user="user1"
mysql_passwd="user1"
#mysql backup dir
back_dir="/home/shang/backup/"
#for filter the need backup databases.
#eg. mysql has many databases: aaa_bbb, ccc_ddd, eee_fff, ggg_hhh,
# and AAA_XXX_01, AAA_XXX_02,AAA_XXX_03,AAA_XXX_04,AAA_XXX_05,
# but I only need AAA_XXX*.
db_name_filter="AAA_XXX"
if [ ! -d $back_dir ]; then
mkdir -p $back_dir
fi
# the array of databases should be backup
db_array=$((echo "show databases;" | mysql -u$mysql_user -p$mysql_passwd -h$mysql_host) | grep "${db_name_filter}")
# echo ${db_arr}
cd $back_dir
for db_name in ${db_array}
do
mysqldump -h$mysql_host -u$mysql_user -p$mysql_passwd --routines --databases $db_name > "${db_name}_backup.sql"
done
mysql_source - 导入备份.sh脚本如下:
#!/bin/bash
#1.some mysql params:
mysql_host="192.168.0.110"
mysql_user="user1"
mysql_passwd="user1"
#mysql backup dir
# all *.sql backup file in here
back_dir="/home/shang/backup/"
cd $back_dir
# the array of databases should be backup
db_array=$(ls)
# echo ${db_arr}
cd $back_dir
for db_name in ${db_array}
do
#mysqldump -h$mysql_host -u$mysql_user -p$mysql_passwd --routines --databases $db_name > "${db_name}_backup.sql"
#debug info:
#echo $db_name
mysql -h$mysql_host -u$mysql_user -p$mysql_passwd < $db_name
done