数据库同步与检测，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多个，方法相同
-
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 '###$$$' LINE 11 S TERMINATED BY '$$$###';" &> $logfile 12 } 13 Clean_data(){ 14 mysql -h192.168.1.26 -uroot -p'xxxxxxxxxx' -Dunion_operate -e "delete from tb_gm_detail_info " &> $logfile 15 } 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 BY 18 '###$$$' LINES TERMINATED BY '$$$###';" &> $logfile 19 } 20 21 Get_data 22 if [ -e $filename1 ];then 23 Clean_data 24 sleep 1 25 Sync_data || echo "clean error" >> $logfile 26 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;do 12 TABLE=`find /script/$database -mindepth 1 -maxdepth 1 -name "*.sh" -exec basename {} ;|cut -d. -f1` 13 for table in $TABLE;do 14 number=`mysql -h$host -uroot -p'xxxxxxxxxxx' -D$database -Ne "SELECT COUNT(*) FROM $table" 2>/dev/null` 15 if [ $? -eq 0 ];then 16 printf "%-60s:%30s " "$host:$database.$table" "$number" 17 else 18 echo "$database $table are not same in all host" 19 fi 20 done 21 done 22 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 # main 31 route add default gw 192.168.1.1 &> /dev/null 32 check_sync > /tmp/qiao.txt 33 send_mail
-
- 同步脚本，N多个，方法相同