奇葩的同步

数据库同步与检测，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.sh
    
    3 directories, 19 files

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

    • [root@zhengaonet-db03 union_operate]# vim tb_gm_detail_info.sh 
      #!/bin/bash
      # sync for full table data
      
      gettime=`date +%Y%m%d%H%M%S`
      logfile='/etllog/tb_gm_detail_info.log'
      filename1=`echo "/etl25/chuanqihome_web/${gettime}_tb_gm_detail_info.txt"`
      
      Get_data(){
              mysql  -h192.168.1.25 -uroot -p'xxxxxxxxxxx' -Dunion_operate -e "select * from tb_gm_detail_info  into outfile '${filename1}' FIELDS TERMINATED BY '###$$$' LINE
      S TERMINATED BY '$$$###';" &> $logfile
      }
      Clean_data(){
              mysql -h192.168.1.26 -uroot -p'xxxxxxxxxx' -Dunion_operate  -e "delete  from  tb_gm_detail_info " &> $logfile
      }
      Sync_data(){
              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 BY
       '###$$$' LINES TERMINATED BY '$$$###';"  &> $logfile
      }
      
      Get_data
      if [ -e $filename1 ];then
              Clean_data
              sleep 1
              Sync_data || echo "clean error" >> $logfile
      fi

  • 监测脚本

    • [root@zhengaonet-db03 script]# cat CheckSync.sh 
      #!/bin/bash
      # description: check syncing
      # Author: qiaogy
      
      check_sync(){
          HOST=('192.168.1.25' '192.168.1.26')
          for host in ${HOST[*]};do
              echo "============================================================================================"
              DATABASE=`find /script/ -mindepth 1 -maxdepth 1 -type d | awk -F"/" '{print $3}'`
              for database in $DATABASE;do
                  TABLE=`find /script/$database -mindepth 1 -maxdepth 1 -name "*.sh" -exec basename {} ;|cut -d. -f1`
                  for table in $TABLE;do
                      number=`mysql  -h$host -uroot -p'xxxxxxxxxxx' -D$database -Ne "SELECT COUNT(*) FROM $table" 2>/dev/null`
                      if [ $? -eq 0 ];then
                          printf "%-60s:%30s
      " "$host:$database.$table" "$number"
                      else
                          echo "$database $table are not same in all host"
                      fi
                  done
              done
          done    
      }
      send_mail(){
          mail -s "check sync" xionghanqing01@zhenaonet.com < "/tmp/qiao.txt"
          mail -s "check sync" qiaoguanyu@zhenaonet.com < "/tmp/qiao.txt"
          mail -s "check sync" guanyu.qiao@qq.com < "/tmp/qiao.txt"
      }
      
      # main
      route add default gw 192.168.1.1 &> /dev/null
      check_sync > /tmp/qiao.txt
      send_mail