windows bat脚本实例
@echo off & setlocal EnableDelayedExpansion
set enddate=%1
set logtabledir=%2
set attachfilepath=%3
if not defined attachfilepath (
echo Usage: delrec.bat [enddate] [logtabledir] [attachfilepath]
pause
goto :eof
)
if not exist %logtabledir% (
echo ERROR logtabledir:%logtabledir% not exist
pause
goto :eof
)
if not exist %attachfilepath% (
echo ERROR attachfilepath:%attachfilepath% not exist
pause
goto :eof
)
set mytask=mysqld-nt.exe
tasklist|find /i "%mytask%">nul
if %errorlevel% equ 0 (
for /f "eol= delims== tokens=2" %%i in ('wmic process where "name='%mytask%'" get executablepath /value') do (
set mysqld-nt_exe=%%~fi
)
)
if "%mysqld-nt_exe%"=="" (
echo ERROR the mysql server not exist or not start
set mysqld-nt_exe=
pause
goto :eof
)
set mysql_exe=!mysqld-nt_exe:mysqld-nt.exe=mysql.exe!
set runmysql="%mysql_exe%" -psinfors -uroot --skip-column-names -e
echo create database ipsetdbxxx
%runmysql% "create database ipsetdbxxx"
%runmysql% "create table ipsetdbxxx.ipset(ip int unsigned primary key)engine=myisam"
echo convert ip set
for /f %%i in (ipsetorigxxx) do (
iptoint %%i >>ipsetresxxx
)
set ipsetrespathrr=%CD%\ipsetresxxx
set ipsetrespathtt=%ipsetrespathrr:\=\\%
set ipsetrespathtt=%ipsetrespathtt:/=\\%
echo load ip to table
%runmysql% "load data infile '%ipsetrespathtt%' replace into table ipsetdbxxx.ipset"
if not %errorlevel% equ 0 (
echo "load ip error"
pause
goto :eof
)
echo show databases
%runmysql% "show databases" >HYFORTHYdatabasesxxx
for /f %%d in (HYFORTHYdatabasesxxx) do (
set databasenow=%%d
if not "%%d"=="information_schema" if not "%%d"=="mysql" if not "%%d"=="performance_schema" if not "%%d"=="sinfor_ac_auth" if not "%%d"=="ipsetdbxxx" (
set runmysql="%mysql_exe%" -psinfors -uroot --skip-column-names %%d -e
::clean 原始表
echo %%d: 开始清理原始表
!runmysql! "select table_name from information_schema.tables where table_schema = '%%d' and table_name regexp '^[Aa][0-9]{8}'" >sqlresxxx
for /f %%i in (sqlresxxx) do (
call :dealtable %%i %%d
)
::dlete 中间表
echo %%d: 开始清理中间表
!runmysql! "select table_name from information_schema.tables where table_schema = '%%d' and table_name regexp '^mid.*[0-9]{8}.*'" >sqlresxxx
for /f %%i in (sqlresxxx) do (
set midtabxxx=%%i
set midtabdatexxx=!midtabxxx:~8,8!
if !midtabdatexxx! leq %enddate% (
echo drop table %%i
!runmysql! "drop table %%i"
)
)
echo %%d: 开始清理原始日志
dir /b /d %logtabledir%\%%d\logtable >sqlresxxx
for /f %%i in (sqlresxxx) do (
set deldir=%%i
echo !deldir!|findstr "^2[0-9][0-9][0-9][0-9][0-9][0-9][0-9]$" >nul 2>&1
if !errorlevel! equ 0 (
if !deldir! leq %enddate% (
echo remove %logtabledir%\%%d\logtable\!deldir!
rd /s /q %logtabledir%\%%d\logtable\!deldir! >nul 2>&1
)
)
)
)
)
set runmysql="%mysql_exe%" -psinfors -uroot --skip-column-names -e
!runmysql! "drop database ipsetdbxxx"
del /q HYFORTHYdatabasesxxx
del /q sqlresxxx
del /q ipsetresxxx
echo 所有的表都处理完毕
pause
goto :eof
::A F T S(单独删除) U P M O<删除附件> C I W Q (连接A表进行删除)
:dealtable
set table=%1
set attachdb=%2
set tabdate=%table:~1%
if %tabdate% gtr %enddate% (
goto :eof
)
echo %databasenow%: 已处理到日期 %tabdate%
::一定最后清理A表
for %%i in (F T S U P M O C I W Q A) do (
set it=%%i%tabdate%
!runmysql! "desc !it!" >nul 2>&1
echo %databasenow%: 清理!it!
if !errorlevel! equ 0 (
!runmysql! "create table !it!_newxxx like !it!"
set checktype=0
if "%%i" == "F" (
set checktype=1
)
if "%%i" == "T" (
set checktype=1
)
if "%%i" == "S" (
set checktype=1
)
if "%%i" == "A" (
set checktype=1
)
if !checktype! equ 1 (
!runmysql! "insert into !it!_newxxx select !it!.* from !it!, ipsetdbxxx.ipset where !it!.host_ip=ipsetdbxxx.ipset.ip"
) else (
!runmysql! "insert into !it!_newxxx select !it!.* from !it!, %table%, ipsetdbxxx.ipset where %table%.host_ip=ipsetdbxxx.ipset.ip and !it!.record_id=%table%.record_id and !it!.dev_id=%table%.dev_id and !it!.account_id=%table%.account_id"
::删除attachment
set checktype=0
if "%%i" == "U" (
set checktype=1
)
if "%%i" == "P" (
set checktype=1
)
if "%%i" == "M" (
set checktype=1
)
if "%%i" == "O" (
set checktype=1
)
if !checktype! equ 1 (
set pathstr=path
if "%%i" == "M" (set pathstr=mail_path)
!runmysql! "select !it!.!pathstr! from !it!, %table% where %table%.host_ip not in (select ip from ipsetdbxxx.ipset) and !it!.record_id=%table%.record_id and !it!.dev_id=%table%.dev_id and !it!.account_id=%table%.account_id" >sqlresxxx
for /f %%a in (sqlresxxx) do (
set tabattachpath=%%a
set tabattachpathtt=!tabattachpath:/aclog/=\%attachdb%\!
set tabattachpathtt=!tabattachpathtt:/=\!
del /q %attachfilepath%\!tabattachpathtt! >nul 2>&1
call :strlenn !tabattachpathtt! \
del /q %attachfilepath%\!tabattachpath! >nul 2>&1
)
)
)
!runmysql! "drop table !it!"
!runmysql! "rename table !it!_newxxx to !it!"
)
)
goto :eof
:strlenn
set strnow=%1
set tabattachpath=%1
set chrfind=%2
set /a numstr=0
set /a posfind=-1
:nextstr
if not "!strnow!"=="" (
set chrbegin=!strnow:~0,1!
if "!chrbegin!"=="%chrfind%" (
set /a posfind=!numstr!
)
set /a numstr+=1
set strnow=!strnow:~1!
goto :nextstr
)
set /a posfind=%posfind%+1
set tabattachpath=!tabattachpath:~0,%posfind%!e_!tabattachpath:~%posfind%!
goto :eof