数据库同步与检测，3个HA节点和2个MS节点间进行部分库中的部分表同步

• 奇葩点：
  • 不同节点上A库中的a表同步给B库中的b表
• 姿势：
  • 建立目录来确定循环变量，目录代表库名，下面的文件代表名，同步不用MMM或MS自身带的功能实现，用脚本方式实现
  • 每天情况格式化后发送邮件

• 目录结构

  • [root@zhengaonet-db03 chuanqihome_web]# tree /script//script/|-- CheckSync.sh|-- chuanqihome_web|   |-- tb_ad_click_ex_info.sh|   |-- tb_ad_view_ex_info.sh|   |-- tb_game_download_detail_info.sh|   |-- tb_news_click_comments_report.sh|   |-- tb_news_frontpagead.sh|   |-- tb_news_lists.sh|   |-- tb_room_info.sh|   |-- tb_services_info.sh|   `-- tb_user_login_info.sh|-- etl.sh|-- pay_order|   |-- order_info.sh|   |-- order_state.sh|   |-- pay_order.sh|   |-- tb_gm_appid_area_info.sh|   `-- tb_gm_detail_info.sh`-- union_operate    |-- tb_gm_appid_area_info.sh    |-- tb_gm_detail_info.sh    `-- tb_user_account.sh3 directories, 19 files

• 实现：
  • 同步脚本，N多个，方法相同

    •  1 [root@zhengaonet-db03 union_operate]# vim tb_gm_detail_info.sh  2 #!/bin/bash 3 # sync for full table data 4  5 gettime=`date +%Y%m%d%H%M%S` 6 logfile=‘/etllog/tb_gm_detail_info.log‘ 7 filename1=`echo "/etl25/chuanqihome_web/${gettime}_tb_gm_detail_info.txt"` 8  9 Get_data(){10         mysql  -h192.168.1.25 -uroot -p‘xxxxxxxxxxx‘ -Dunion_operate -e "select * from tb_gm_detail_info  into outfile ‘${filename1}‘ FIELDS TERMINATED BY ‘###$$$‘ LINE11 S TERMINATED BY ‘$$$###‘;" &> $logfile12 }13 Clean_data(){14         mysql -h192.168.1.26 -uroot -p‘xxxxxxxxxx‘ -Dunion_operate  -e "delete  from  tb_gm_detail_info " &> $logfile15 }16 Sync_data(){17         mysql -h192.168.1.26 -uroot -p‘xxxxxxxxxx‘ -e "load data local infile ‘"${filename1}"‘ replace  into table union_operate.tb_gm_detail_info FIELDS TERMINATED BY18  ‘###$$$‘ LINES TERMINATED BY ‘$$$###‘;"  &> $logfile19 }20 21 Get_data22 if [ -e $filename1 ];then23         Clean_data24         sleep 125         Sync_data || echo "clean error" >> $logfile26 fi

  • 监测脚本

    •  1 [root@zhengaonet-db03 script]# cat CheckSync.sh  2 #!/bin/bash 3 # description: check syncing 4 # Author: qiaogy 5  6 check_sync(){ 7     HOST=(‘192.168.1.25‘ ‘192.168.1.26‘) 8     for host in ${HOST[*]};do 9         echo "============================================================================================"10         DATABASE=`find /script/ -mindepth 1 -maxdepth 1 -type d | awk -F"/" ‘{print $3}‘`11         for database in $DATABASE;do12             TABLE=`find /script/$database -mindepth 1 -maxdepth 1 -name "*.sh" -exec basename {} \;|cut -d. -f1`13             for table in $TABLE;do14                 number=`mysql  -h$host -uroot -p‘xxxxxxxxxxx‘ -D$database -Ne "SELECT COUNT(*) FROM $table" 2>/dev/null`15                 if [ $? -eq 0 ];then16                     printf "%-60s:%30s\n" "$host:$database.$table" "$number"17                 else18                     echo "$database $table are not same in all host"19                 fi20             done21         done22     done    23 }24 send_mail(){25     mail -s "check sync" xionghanqing01@zhenaonet.com < "/tmp/qiao.txt"26     mail -s "check sync" qiaoguanyu@zhenaonet.com < "/tmp/qiao.txt"27     mail -s "check sync" guanyu.qiao@qq.com < "/tmp/qiao.txt"28 }29 30 # main31 route add default gw 192.168.1.1 &> /dev/null32 check_sync > /tmp/qiao.txt33 send_mail

       

奇葩的同步