阮胜昌的技术记录站-LinuxMySQL数据库运维

#! /bin/bash
#
# __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