Mysql一对多数据同步方案

#!/bin/bash


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

# 实际中可以换成中央数据库的信息
sou_mysql_host='10.51.7.45'
sou_mysql_port='3306'
sou_mysql_user='root'
sou_mysql_pwd='Mics@123'
sou_mysql_db_name='micssta'

# 实际中可以换成若干个车站库的IP信息
des1_mysql_host='10.51.7.41'
#des2_mysql_host='10.51.7.42'
#des3_mysql_host='10.51.7.43'
#des4_mysql_host='10.51.7.44'
#des5_mysql_host='10.51.7.46'
#des6_mysql_host='10.51.7.47'
#des7_mysql_host='10.51.7.48'
#des7_mysql_host='10.51.7.49
# ... ...

# 实际中可以换成若干个车站库的端口、数据库用户、密码、数据库名称
des_mysql_port='3306'
des_mysql_user='root'
des_mysql_pwd='Mics@123'
des_mysql_db_name='micssta' 


# 两张数据库中相同的表的名字,同步哪些填哪些，空格相隔
sou_mysql_table_name=(
DATALOG_DP_TREND
PA_ZONE
MICS_SYSTEM
MICS_PSCADAWFMAP
SE_OPERATOR_PROFILE
MICS_STATIONSUBMAP
SC_DERIVED_STATE
MICS_SUBSYSTEM
BA_SUBSYSTEM_MODETYPE_MAP
MICS_PMS_TRIGGER_PA_AUDIO
BA_MODE_DEVICES
DATALOG_DP_GROUP_REPORT
MICS_HMIAGENT_CONFIG
DATALOG_DP_GROUP_TREND
MICS_APP_TYPE
PA_DVA_MESSAGE
SE_ACTION_GROUP
PIDS_STATION
MICS_IOTGATEWAY_PARAMETER
PLAN_DSS_DP_RULE
MICS_MENU_PERMISSIONS
ENTITY
SE_PROFILE_PARAMETERS
OPERATOR
MICS_LOCATION
ENTITYTYPE
STEP_TYPE
SC_DERIVED_OUTPUT_ASSOCIATION
BA_SUBSYSTEM_MAP
DATAPOINT_VERSION_LIST
SE_ACTION_GROUP_MAP
MICS_IOTGATEWAY_MAP
NODE_TREE
MICS_IOTGATEWAY_TYPE
BA_AIRSHAFTINFO
STEP_PARAMETERS
BA_TIMETABLE_TIMETABLE
BA_TIMETABLE_MODETIME
LOCATION
DATALOG_DP_REPORT
SC_INPUT_ASSOCIATION
BA_TUNNEL_FIRECASE_MAP
SUBSYSTEM
STEP_TYPE_PARAMETER_TYPES
SE_PROFILE
MICS_IOTGATEWAY_TYPEGROUP
MICS_IOTGATEWAY_PARAMETERVALUE
BA_PARAMETERVALUETYPE
MICS_DIC_SUBSYSNAME
PIDS_ONBOARD
BA_MODES
MICS_LOCATIONAPPLICATION_MAP
PIDS_PRESET
MICS_SUBSYSTEM_OCC
DATALOG_CONFIGSETTINGS
BA_SUBSYSTEMS
SE_REGION
BA_TIMETABLE_TIMETABLEMAPPING
MICS_LOCATIONMERG
NODE_TYPE
MICS_APPLICATION
BA_TIMETABLE_SETTING
SE_OPERATOR_REGIONS
MICS_MENU
ENTITYPARAMETER
SE_PROFILE_GROUP
BA_MODETYPES
EMS_GROUP_NODE
MICS_ICESERVICE
PA_STATION
AE_ALARM_RULE_PARAM
SE_ACTION
STEP_DETAILS
MICS_APPLICATIONSERVICE_MAP
ALARM_AVALANCHE
MICS_MENU_CONSOLE
SE_PROFILE_LOCATION
EMS_ENTRY_DEVICE
SE_PROFILE_ACCESS
ENTITYPARAMETERVALUE
ALARMRULE
MICS_CLIENT_CONFIG
SH_JOB
)

# 存放备份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
        /usr/local/mysql/bin/mysqldump -h${sou_mysql_host} -P${sou_mysql_port} -u${sou_mysql_user} -p${sou_mysql_pwd}  \
        --insert-ignore=true \
        --default-character-set=utf8 \
        -t ${sou_mysql_db_name} ${tabname} >${sync_db_dir}/${sou_mysql_db_name}_${tabname}.sql 2> /dev/null
        func_writeLog "${sou_mysql_db_name}_${tabname}.sql backup ok"
    done
}
#同步到车站库1
func_syncMysqldes1(){
    for sqlname in $(ls ${sync_db_dir}/*.sql);do
        /usr/local/mysql/bin/mysql -h${des1_mysql_host} -P${des_mysql_port} -u${des_mysql_user} -p${des_mysql_pwd}  \
        --default-character-set=utf8 ${des_mysql_db_name} < ${sqlname} 2> /dev/null
         func_writeLog "${sqlname} station_1 load ok"
    done
}

#同步到车站库2
#func_syncMysqldes2(){
#    for sqlname in $(ls ${sync_db_dir}/*.sql);do
#        /usr/local/mysql/bin/mysql -h${des2_mysql_host} -P${des_mysql_port} -u${des_mysql_user} -p${des_mysql_pwd}  \
#        --default-character-set=utf8 ${des_mysql_db_name} < ${sqlname} 2> /dev/null
#        func_writeLog "${sqlname} station_2 load ok"
#    done
#}

#同步到车站库3，4，5，6，7，8 .......一个车站库对应一个function

#全同步完成后删除备份文件
func_syncDeletesql(){
  for sqlname in $(ls ${sync_db_dir}/*.sql);do
  [ $? -eq 0 ] && rm -f ${sqlname}
  func_writeLog "${sqlname} deleted ok"
  done
}
main(){
    func_sourceBackupCmd;
    func_syncMysqldes1;
    #func_syncMysqldes2;
    func_syncDeletesql;
}
main