因开发与测试需要,经常需要临时性地将若干MySQL表跨服务器传输,于是通过Shell脚本封装命令行的“mysqldump”(dump操作)与mysql(import操作),就可以简单实现需求了。
#! /bin/bash
# Author : 蛙鳜鸡鹳狸猿
# create_ts :
# program : do a MySQL table dump
# crontab : NULL
#
#
# __init__
host_src=yourht_A # source db server config list
user_src=root
pswd_src=123
dbas_src=yourdb_A
#
host_tar=yourht_B # target db server config list
user_tar=root
pswd_tar=456
dbas_tar=yourdb_B
#
tble_dump=(tb_a tb_b tb_c) # dump table config
cach_dump=/ # cache directory config
#
#
# dump tables
for tba in ${tble_dump[*]}
do
if mysqldump -h${host_src} -u${user_src} -p${pswd_src} --single-transaction --skip-lock-tables --set-gtid-purged=OFF ${dbas_src} ${tba} > ${cach_dump}${tba}.sql && \
mysql -h${host_tar} -u${user_tar} -p${pswd_tar} ${dbas_tar} < ${cach_dump}${tba}.sql && \
echo -e "\n\n ……${tba} dump OK…… \n\n"
then > /dev/null
else exit && echo -e "\n\n ……MySQL dump from ${dbas_src} to ${dbas_tar} failed…… \n\n"
fi
done
#
#
# clear cache
if
cd ${cach_dump} && ls | grep .sql | xargs rm -f
then echo -e "\n\n ……\(^o^)/YES ╮(╯▽╰)╭ \(^o^)/YES…… \n\n"
else exit && echo -e "\n\n ……o(>﹏<)o YaMieDie o(>﹏<)o…… \n\n"
fi
#
#
如以上小脚本所示,不同的操作只需要改动一下“__init__”部分的配置。另外,可能需要MySQL策略性的整库备份,参考:http://blog.csdn.net/sweeper_freedoman/article/details/52717911。