Mysql多表数据同步方案

1. 【my.cnf】里配置源库，目标库的数据库连接参数

 样例：/etc/my.cnf 里

添加如下内容

#目标库

[mysql]  
host=10.51.7.41
user=root
password=Mics@123

#源数据库

[mysqldump]
host=127.0.0.1
user=root
password=Mics@123

2. 同步数据的shell脚本如下（提前建好备份脚本存放目录）

#!/bin/bash


# /etc/my.cnf 文件中配置源库和目标库数据库连接信息

sou_mysql_db_name='micssta' #源中央库名称
des_mysql_db_name='micssta' #目标车站库名称



# 两张数据库中相同的表的名字,同步哪些填哪些，空格相隔
sou_mysql_table_name=(
BA_MODE_DEVICES MICS_APP_TYPE SE_ACTION_GROUP_MAP SE_PROFILE_PARAMETERS ENTITY
)

# 存放备份sql文件的目录
sync_db_dir='/home/mysql/backup_sql'

# 存放同步数据日志目录
log_file='/home/mysql/syncdata.log'


func_writeLog(){
        local getString=$1
        if [ $? -eq 0 ];then
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}"
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}"  >>${log_file}
        else
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}"
                echo "TIME:$(date +"%Y%m%d_%H:%M"),The ${getString}" >>${log_file}
        fi
}



func_sourceBackupCmd(){
    for tabname in $(echo ${sou_mysql_table_name[@]}|sed 's/ / /g');do
        home=/etc /usr/local/mysql/bin/mysqldump \
        --insert-ignore=true \
        --default-character-set=utf8 \
        -t ${sou_mysql_db_name} ${tabname} >${sync_db_dir}/${sou_mysql_db_name}_${tabname}.sql
        func_writeLog "${sou_mysql_db_name}_${tabname}.sql backup ok"
    done
}

func_syncMysqlCmd(){
    for sqlname in $(ls ${sync_db_dir}/*.sql);do
        home=/etc  /usr/local/mysql/bin/mysql \
        --default-character-set=utf8 ${des_mysql_db_name} < ${sqlname}
        func_writeLog "${sqlname} load ok"
        [ $? -eq 0 ] && rm -f ${sqlname}
        func_writeLog "${sqlname} deleted ok"
    done
}
main(){
    func_sourceBackupCmd;
    func_syncMysqlCmd;
}
main