通过中间表，在两个数据库之间同步表中指定的数据#
# __init__
host_src=192.168.1.111 # source db server config list
user_src=sync
pswd_src=123456
dbas_src=history
db_port=3306
host_tar=1.1.1.1 # target db server config list
user_tar=sync
pswd_tar=123456
dbas_tar=history
db_port_tar=3306
#
tble_dump=(dddd dddd_cccc) # dump table config
cach_dump=/tmp/ # cache directory config
#
#
# dump tables
/usr/bin/mysql -h${host_src} -u${user_src} -p${pswd_src} -P${db_port} -e "use history;DROP TABLE IF EXISTS history.dddd;create table history.dddd as SELECT * FROM source_db.dddd WHERE store_id IN (SELECT id FROM source_db.source_db_erp_store WHERE platform = '13') ORDER BY id desc;"
echo "create table history.dddd OK"
sleep 3
/usr/bin/mysql -h${host_src} -u${user_src} -p${pswd_src} -P${db_port} -e "use history; DROP TABLE IF EXISTS history.dddd_cccc;create table history.dddd_cccc as SELECT * FROM source_db.dddd_cccc WHERE order_id IN (SELECT id FROM source_db.dddd WHERE store_id IN ( SELECT id FROM source_db.source_db_erp_store WHERE platform = '13' ) ); "
echo "create table history.dddd_cccc OK"
sleep 3
for tba in ${tble_dump[*]}
do
if mysqldump -h${host_src} -u${user_src} -p${pswd_src} -P${db_port} --single-transaction --skip-lock-tables ${dbas_src} ${tba} > ${cach_dump}${tba}.sql &&
mysql -h${host_tar} -u${user_tar} -p${pswd_tar} -P${db_port_tar} ${dbas_tar} < ${cach_dump}${tba}.sql &&
echo -e "\n\n $(date) ……${tba} dump import OK…… \n\n"
then > /dev/null
else
exit && echo -e "\n\n $(date)……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 $(date)……\Clean sql ok \n\n"
else exit && echo -e "\n\n $(date) Clean Failed \n\n"
fi
