使用批处理进行mysql数据统计并上传

需求如下：

1. 对指定数据库进行统计，输出结果为文本格式，TAB进行分割，不带列标题，文件名为“日期_统计名.dat"
2. 对统计结果计算md5，并写入“日期_统计名.dat.md5"
3. 对统计结果进行压缩，并写入“日期_统计名.dat.7z"
4. 上传md5,和7z文件
5. 上述统计有多个，每个统计可能在分布在多个db

思路：每个统计涉及多个DB，需要把多个查询拼在一起，合成一次查询输出才能把结果放入一个文件。所以我们提前合并查询语句。

如果多个DB的查询分开在多次子处理中，好像更麻烦

涉及批处理的关键字：用日期作为文件名，分析数据列表，子函数，切换工作目录到当前文件路径

下面是批处理内容：

@echo off
echo ===============================================================
::每日统计脚本
::每天零点开始统计前一天的数据，并打包上传到指定服务器


:: 添加计划任务脚本
:: schtasks /create /tn q1_oss /tr "D:\dbstat\stat.bat >> stat_%date:~0,10%.log 2>&1" /sc daily /mo 1 /st 00:30:00 /ru SYSTEM


::[运维修改] 上传的统计库信息
set server_ip=192.168.0.1
set server_port=21
set server_user=ftpUser
set server_password=ftpPassword




::[运维修改] 要统计的数据库用户信息
set mysqlIp=192.168.0.2
set mysqlPort=3306
set user=Dbuser
set password=dbPassword


::[运维修改] 统计参数
:: 因为区ID和Db名字不匹配，所以全部要列出了
:: 格式： "区ID,gamedb,logdb" 空格 "区ID,gamedb,logdb"      双引号不能删
set list="2,qq_game0001,qq_log0001" "4,qq_game0001,qq_log0004"




:: 切换到批处理所在的工作目录
set myworkdir=%~dp0
echo 工作目录： %myworkdir%
%~d0
cd %myworkdir%


:: 建立本次处理的临时目录
set today=%date:~0,10%
set endDay=0
set startDay=-1
echo today=%today%, startDay=%startDay%, endDay=%endDay%


set ctime=%TIME: =0%
set tname=%today%T%ctime:~0,2%%ctime:~3,2%%ctime:~6,2%.000
echo target dir : %tname%
mkdir %tname%
::cd %tname%


::@set path=%path%;"C:\Program Files\MySQL\MySQL Server 5.5\bin"
@set mysql="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe"
@set zip=%myworkdir%7za.exe
@set md5=%myworkdir%md5.exe
@set ftp=%myworkdir%ncftpput.exe


setlocal EnableDelayedExpansion


:: 拼总的查询语句
set getUserInfo_sql=
set getActorInfo_sql=
set getChargeStat_sql=
set getLoginLog_sql=
set getOnlineStat_sql=
set getUserTicketHis_sql=
for %%i in (%list%) do (
    echo server config = %%i 
    for /f "tokens=1-3 delims=," %%a in (%%i) do (
        echo wrold=%%a gamedb=%%b logdb=%%c
        set getUserInfo_sql=!getUserInfo_sql!"select  %%a as 'WrolDID', Account , RegIP,  from  %%b.AccountInfo Time >= UNIX_TIMESTAMP(ADDDATE('%today%', %startDay%))  and Time < UNIX_TIMESTAMP('%today%');"
        set getActorInfo_sql=!getLoginLog_sql!"select  %%a as 'WrolDID', UserId , Name , ActorID, Level  from  %%b.RoleInfo where Zone=%%a ;"
        set getChargeStat_sql=!getActorInfo_sql!"select  %%a as 'WrolDID', UserID, 'ActorID', Point 0 as 'Type' from  %%b.GameChargeLog where   Zone=%%a and (ChargeTime >= UNIX_TIMESTAMP(ADDDATE('%today%', %startDay%)) and ChargeTime < UNIX_TIMESTAMP('%today%'));"
        set getLoginLog_sql=!getOnlineStat_sql!"select  %%a as 'WrolDID', UserId, 'ActorID' from  %%c.UserLoginLog where (TimeLogin < UNIX_TIMESTAMP('%today%') and TimeLogout = 0) or (TimeLogout >= UNIX_TIMESTAMP(ADDDATE('%today%', %startDay%)) and TimeLogout < UNIX_TIMESTAMP('%today%'));"
    )
)


:: 开始处理
@cd %tname%
@echo cur dir: %cd%


::初始化FTP命令
set tempFtpCmdFile=ftp_cmd.txt
echo open  %server_ip% %server_port% >> %tempFtpCmdFile%
echo %server_user% >> %tempFtpCmdFile%
echo %server_password% >> %tempFtpCmdFile%
echo prompt >> %tempFtpCmdFile%
echo passive >> %tempFtpCmdFile%
echo bin >> %tempFtpCmdFile%
::echo mkdir WBGmae_Receive >> %tempFtpCmdFile%
::echo mkdir WBGmae_Receive/%mysqlIp% >> %tempFtpCmdFile%


:: 统计部分
echo start get mysql stat 开始统计
call :__stat__ "%getUserInfo_sql:"=%"      WGAccountInfo_Tmp
call :__stat__ "%getLoginLog_sql:"=%"      WGUserLoginLog_Tmp
call :__stat__ "%getActorInfo_sql:"=%"     WGGameActor_Tmp
call :__stat__ "%getOnlineStat_sql:"=%"    WGUserOnlineNum_New_Tmp


:: FTP 上传
echo bye>> %tempFtpCmdFile%


::echo start upload ftp: %server_ip%:%server_port% 上传
:: type %tempFtpCmdFile%
rem ftp -s %tempFtpCmdFile%
::rm %tempFtpCmdFile%


@cd ..
echo ===============================================================
goto :eof


:__stat__
:: 处理子函数
::function __stat__ () {
::参数 要执行的SQL文件，保存的文件名
:: 下面都不需要把结果重定向到错误日志，由外部调用者自己处理
::set dbname=%1
set sqlfile=%1
set uploadfile=%2




@echo on
@echo ----------------------------------------------------------
@echo ---- [%uploadfile% run            ] ---- %time%
@set curFilename=%tname%_%uploadfile%.dat
@echo 当前文件名：%curFilename%


::执行SQL统计
@echo ---- [%uploadfile%  sql select ...] ---- %time%
%mysql% -u%user% -p%password% -h %mysqlIp% -P %mysqlPort% --default-character-set=gbk --skip-column-names --execute=%sqlfile% 1>%curFilename%




:: 压缩
@echo ---- [%uploadfile%  compress ...  ] ---- %time%
:: 必须进入这个目录，才能得到zip文件只包含这个文件，而没有目录
%zip% a %curFilename%.7z %curFilename%


:: 生成MD5
@echo ---- [%uploadfile%  md5 ...       ] ---- %time%
%md5% %curFilename%.7z > %curFilename%.md5




:: 上传FTP
@echo ---- [%uploadfile% upload ...     ] ---- %time%
@echo  %ftp%  -u %server_user% -p %server_password% -P %server_port% -m %server_ip% \WGStatCenterSource\%uploadfile%\%mysqlIp% %curFilename%.7z
@echo  %ftp%  -u %server_user% -p %server_password% -P %server_port% -m %server_ip% \WGStatCenterSource\%uploadfile%\%mysqlIp% %curFilename%.md5
@echo ---- [%uploadfile% upload finish..] ---- %time%


::生成FTP命令
@echo ---- [%uploadfile%  ftp cmd ...   ] ---- %time%
@echo mkdir -p /WGStatCenterSource/%uploadfile%/%mysqlIp% >> %tempFtpCmdFile%
@echo #mput  %myworkdir%%curFilename%.7z -O /WGStatCenterSource/%uploadfile%/%mysqlIp% >> %tempFtpCmdFile%
@echo #mput  %myworkdir%%curFilename%.md5 -O /WGStatCenterSource/%uploadfile%/%mysqlIp% >> %tempFtpCmdFile%
@echo #delete  /WGStatCenterSource/%uploadfile%/%mysqlIp%/%curFilename%.7z   >> %tempFtpCmdFile%
@echo #delete  /WGStatCenterSource/%uploadfile%/%mysqlIp%/%curFilename%.md5  >> %tempFtpCmdFile%




@echo ---- [%uploadfile%  end ...       ] ---- %time%
@echo ----------------------------------------------------------
@echo off
goto :eof
::}


:eof

============ 结束 ============

这里没有使用DOS的ftp命令是因为路由那边被限制，不能用被动模式的FTP，只有使用第三方工具“ncftpput.exe”，一次一个文件传，效率很低。

============ 结束 ============

与批处理不同的是，lftp只需要一次登录就可以把文件全部上传，效率高很多。