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

梦里梦外; 2021-12-04 04:25 274阅读 0赞

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

第一步：编写一个自动化的脚本，每天0点02分将mysql中的数据拉出来，[保存为bag.sql.xxxx.xxx][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)"

[bag.sql.xxxx.xxx]: http://xn--bag-c88dw8e312a.sql.xxxx.xxx