两个阿里云账号，一个windows一个liunx，两个mysql怎样同步数据。

现在又这么一种情况，我们给客户做了一个程序，数据都在我们的阿里云RDS（MYSQL）中，对方提出要求，要把部分关键表的数据，每天同步到他们自己的数据库中，要做数据分析。

第一步：编写一个自动化的脚本，每天0点02分将mysql中的数据拉出来，保存为bag.sql.xxxx.xxx

说明一下
第一、对于只涉及到单个表的简单查询条件的sql，比如： select * from member_info where org_id=‘1-27’
可以将mysqldump命令书写为这样：

mysqldump -h XXXXXXX.mysql.rds.aliyuncs.com -u XXXXXX -pXXXXXX geerunner member_info --where "org_id='1-27'" >> /tmp/bag.sql

第二、对于涉及到 join等多表查询的，就只能创建另外的一个数据库，然后把数据同步到一个数据库中，再从数据库中导出数据。

mysql -h XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXX -pXXXXXXXXX -e "source /home/dump/bag_dump_join_temp_data.sql"

bag_dump_join_temp_data.sql的全部内容如下：

use bag_dump;
truncate table bag_dump.cmpt_item;
truncate table bag_dump.cmpt_userdefine;
truncate table bag_dump.cmpt_extra_charge;
insert into bag_dump.cmpt_item select a.* from geerunner.cmpt_item a INNER JOIN geerunner.cmpt_main b ON a.cmpt_id = b.cmpt_id WHERE b.org_id = '1-27-1';
insert into bag_dump.cmpt_userdefine select a.* from geerunner.cmpt_userdefine a INNER JOIN geerunner.cmpt_main b ON a.cmpt_id = b.cmpt_id WHERE b.org_id = '1-27-1';
insert into bag_dump.cmpt_extra_charge select a.* from geerunner.cmpt_extra_charge a INNER JOIN geerunner.cmpt_main b ON a.cmpt_id = b.cmpt_id WHERE b.org_id = '1-27-1';

最后将数据通过mysqldump到sql中

mysqldump -h rm-XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXXXX -pXXXXXXXXX bag_dump cmpt_item >> /tmp/bag.sql
mysqldump -h rm-XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXXXX -pXXXXXXXXX bag_dump cmpt_extra_charge >> /tmp/bag.sql
mysqldump -h rm-XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXXXX -pXXXXXXXXX bag_dump cmpt_userdefine >> /tmp/bag.sql

第二步：为了数据的安全，将数据迁移到有外网的访问权限的一台阿里云ECS机器上。

echo "now copying to 152"
/usr/bin/rsync --progress -r --port=19873 --exclude=*.sh --delete --password-file=/etc/rsyncd.secrets ${dump_home}/ rsync@10.51.236.15::bag/

第三步：window拉取数据方的配置，这样就完成了数据的迁移

set CURDATE=%date%
echo %CURDATE%

d:
cd "D:\datasync\bin"
D:\datasync\bin\rsync -r --progress --port=XXXX --delete --password-file=rsyncd.secrets   rsync@118.XXX.102.XXX::bag/ ./bag_data/

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -D sync_bag_data -u vendor_eventsite -pvendor_eventsite@2019 < D:\datasync\load_bag.sql

附注：全部的liunx脚本代码如下：

#!/bin/bash
echo "========================================"
echo "start at $(date)"
dump_home=/home/dump/bag_dump
dat=`date +%Y-%m-%d`

> /tmp/bag.sql
while read line
do

n=1
while [ $n -ne 10 ]
do
eval $line
ret=$?
if [ $ret -eq 0 ]
then
echo "dump from rds successfully!"
break
else
echo "retry $n"
sleep 3
fi
if [ $n -eq 9 ]
then
#!/bin/bash
echo "========================================"
echo "start at $(date)"
dump_home=/home/dump/bag_dump
dat=`date +%Y-%m-%d`

> /tmp/bag.sql
while read line
do

n=1
while [ $n -ne 10 ]
do
eval $line
ret=$?
if [ $ret -eq 0 ]
then
echo "dump from rds successfully!"
break
else
echo "retry $n"
sleep 3
fi
if [ $n -eq 9 ]
then
echo "too many retry, $n times tried, quit"
exit 3
fi
let n=$n+1
done

done << Eof

mysql -h XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXX -pXXXXXXXXX -e "source /home/dump/bag_dump_join_temp_data.sql"
mysqldump -h XXXXXXX.mysql.rds.aliyuncs.com -u XXXXXX -pXXXXXX geerunner member_info --where "org_id='1-27'" >> /tmp/bag.sql

mysqldump -h rm-XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXXXX -pXXXXXXXXX bag_dump cmpt_item >> /tmp/bag.sql
mysqldump -h rm-XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXXXX -pXXXXXXXXX bag_dump cmpt_extra_charge >> /tmp/bag.sql
mysqldump -h rm-XXXXXXXXX.mysql.rds.aliyuncs.com -u XXXXXXXXX -pXXXXXXXXX bag_dump cmpt_userdefine >> /tmp/bag.sql


Eof

rm -f ${dump_home}/bag.sql.${dat}
sed -i 's/geerunner/sync_bag_data/g' /tmp/bag.sql
cp -f /tmp/bag.sql ${dump_home}/bag_latest.sql
mv /tmp/bag.sql ${dump_home}/bag.sql.${dat}

echo "now copying to 152"
/usr/bin/rsync --progress -r --port=19873 --exclude=*.sh --delete --password-file=/etc/rsyncd.secrets ${dump_home}/ rsync@10.51.236.15::bag/

echo "cleaning old dumps"
find ${dump_home} -type d -mtime +30 -name "bag.sql.*" | xargs rm -f
echo "all done $(date)"