数据库shell脚本，用于统计并复制到另一台服务器

数据库脚本，用来统计操作，并转移至另一台服务器

#!/bin/bashmysclient1="/usr/local/mysql/bin/mysql -uroot -paohe123456 "mysclient2="/usr/local/mysql/bin/mysqldump -uroot -paohe123456 "record_date=$(date '+%Y-%m-%d')
#指定当天某个时段
day_begin_time=`date '+%Y-%m-%d 11:00:00'`
day_end_time=`date '+%Y-%m-%d 20:59:59'`#取某个时段的时间戳
begin_timestamp=`date -d "$day_begin_time" +%s`
end_timestamp=`date -d "$day_end_time" +%s`
timestamp_now=`date +%s`game_type="11200"
#获取当前时间
begin_time=`date '+%Y-%m-%d 00:00:00'`
end_time=`date '+%Y-%m-%d 23:59:59'`#获取昨天时间
yesterday_begin_time=`date -d yesterday '+%Y-%m-%d 00:00:00'`
yesterday_end_time=`date -d yesterday '+%Y-%m-%d 23:59:59'`game_count=`$mysclient1 -e"use log; select sum(x.game_count) as sum_game_countfrom ( select dl.deal_id,max(dl.cur_deal) as game_count from log.tb_open_deal_room_log as rl,log.tb_open_deal_detail_log as dlwhere dl.create_time BETWEEN '$begin_time' and '$end_time'and dl.deal_id=rl.deal_idand rl.game_type='$game_type'group by dl.deal_id) as x; " | sed -n '2p' `# 指定时段内统计昨天的数据
if [ $timestamp_now -ge $begin_timestamp ] && [ $timestamp_now -le $end_timestamp ]
thenyesterday_game_count=`$mysclient1 -e"use log; select sum(x.game_count) as sum_game_countfrom ( select dl.deal_id,max(dl.cur_deal) as game_count from log.tb_open_deal_room_log as rl,log.tb_open_deal_detail_log as dlwhere dl.create_time BETWEEN '$yesterday_begin_time' and '$yesterday_end_time'and dl.deal_id=rl.deal_idand rl.game_type='$game_type'group by dl.deal_id) as x; " | sed -n '2p' `
elseyesterday_game_count="0"
fi$mysclient1 -e"use gamesnapshot; call sp_platform_general_situation_anyminutes_snapshot_3('$record_date','$game_count','$yesterday_game_count');"waring_name=`$mysclient1 -e"use gamesnapshot; select concat('【亲友麻将】','最高在线人数',':',online_top,';','平均在线人数',':',round(avg_online),';','最高在线房间',':',open_top,';','平均在线房间',':',round(avg_open_count)) as waring_name from tb_platform_general_situation_snapshot where record_date='$record_date' and (online_top_rates>=0.2 or open_top_rates>=0.2) and type=1 " | sed -n '2p' `count=`$mysclient1 -e"use game; select count(1) from tb_waring_log where record_date='$record_date'" | sed -n '2p' `if [ $waring_name ] && [ $count -eq 0 ]
then$mysclient1 -e "use game; insert into tb_waring_log (record_date,waring_name,send_time,status,create_time,last_mod_time) values ('$record_date','$waring_name', '0000-00-00 00:00:00', 1,now(), now());"fi#导出表tmp_open_deal_player_room
if [ $timestamp_now -ge $begin_timestamp ] && [ $timestamp_now -le $end_timestamp ]
then$mysclient1 -e"use log; drop table if exists tmp_open_deal_player_room;create table tmp_open_deal_player_room as select a.deal_id,a.player_id,b.need_card_count,b.create_timefrom log.tb_open_deal_player_score_log a,log.tb_open_deal_room_log bwhere a.deal_id=b.deal_id and a.create_time BETWEEN '$yesterday_begin_time' and '$end_time'and b.create_time  BETWEEN '$yesterday_begin_time' and '$end_time'group by a.deal_id,a.player_id;"
else$mysclient1 -e"use log; drop table if exists tmp_open_deal_player_room;create table tmp_open_deal_player_room as select a.deal_id,a.player_id,b.need_card_count,b.create_timefrom log.tb_open_deal_player_score_log a,log.tb_open_deal_room_log bwhere a.deal_id=b.deal_id and a.create_time BETWEEN '$begin_time' and '$end_time'and b.create_time  BETWEEN '$begin_time' and '$end_time'group by a.deal_id,a.player_id;"
fi$mysclient2 log tmp_open_deal_player_room > /scrips/boss_send_message/data/tmp_open_deal_player_room.sqlcd /scrips/boss_send_message/data/$mysclient1 -e"use gamesnapshot; drop table if exists tmp_open_deal_player_room;source tmp_open_deal_player_room.sql;call sp_player_promotion_snapshot('$record_date');call sp_sales_promotion_snapshot('$record_date');"