bat 脚本处理Mysql安装初始化sql脚本

需要使用admin权限，权限脚本命令已注释

@echo off
rem admin权限
rem cacls.exe "%SystemDrive%\System Volume Information" >nul 2>nul
rem if %errorlevel%==0 goto Admin
rem if exist "%temp%\getadmin.vbs" del /f /q "%temp%\getadmin.vbs"
rem echo Set RequestUAC = CreateObject^("Shell.Application"^)>"%temp%\getadmin.vbs"
rem echo RequestUAC.ShellExecute "%~s0","","","runas",1 >>"%temp%\getadmin.vbs"
rem echo WScript.Quit >>"%temp%\getadmin.vbs"
rem "%temp%\getadmin.vbs" /f
rem if exist "%temp%\getadmin.vbs" del /f /q "%temp%\getadmin.vbs"
rem exit

rem :Admin
rem 设置端口号
set port=3306
echo 服务启动端口号


REM 后续命令使用的是：UTF-8编码
chcp 65001 >NUL
title 安装MYSQL

set cds=%~dp0
:: 生成日志文件
set logDir=%cds%log
if not exist %logDir% ( 
	md %logDir%
)
set logPath=%logDir%\log.txt
if not exist %logPath% ( 
	echo=>%logPath%
)
echo 日志路径:%logPath%>> %logPath%
echo %date:~3,4%-%date:~8,2%-%date:~11,2% %time:~0,2%:%time:~3,2%:%time:~6,2% >>%logPath%

set mysql_path=%cds%
set remain=%path%
:: 待查找字符串
set toAdd=%mysql_path%bin
:: 标记,默认没有重复
set finded=false
:: 判断服务是否安装完成
set installFinshed=false

:loop
for /f "tokens=1* delims=;" %%a in ("%remain%") do (
	::如果找到相同的了
	if "%toAdd%"=="%%a" (
		::该表标记,true表示有重复的了
		set finded=true
		echo path环境变量中已经有了该环境变量,无须重复添加>>%logPath%
		::直接退出
		goto :isFinded
	)
	rem 将截取剩下的部分赋给变量remain，其实这里可以使用延迟变量开关
	set remain=%%b
)
::如果还有剩余,则继续分割
if defined remain goto :loop
::如果没有重复:
if "%finded%"=="false" (
	echo 正在修改系统path环境变量...>>%logPath%
	setx /m "path" "%toAdd%;%path%"
	::结束程序
	goto :isFinded
)

:isFinded
set service_name=MySQL
REM 查询服务是否存在 
SC QUERY %service_name% > NUL
IF not ERRORLEVEL 1060 GOTO serverIsStop
GOTO installMysql

:serverIsStop
rem 服务是否正在运行
for /f "tokens=4" %%i in ('sc query %service_name% 2^>^&1 ^| findstr /i "STATE"') do set "zt=%%i"
if /i "%zt%"=="RUNNING" (GOTO stopServer) else GOTO deleteServer
echo zt:"%zt%">> %logPath%

rem 判断停止服务的次数
set stopServerTime = 0

:stopServer
echo 停止原MYSQL服>>%logPath%
@sc stop %service_name%
:: 延时
GOTO deleteServer

:deleteServer
echo 卸载服务中...>>%logPath%
for /f "tokens=4" %%i in ('sc query %service_name% 2^>^&1 ^| findstr /i "STATE"') do set "zt=%%i"
if /i "%zt%"=="RUNNING" (
	if "%stopServerTime%" == "3" (
		goto end
		echo 请停止%service_name%服务后，再启动脚本>>%logPath%
	) else (
		stopServerTime=stopServerTime+1
		goto stopServer
	)
) else (
	echo 服务已停止
)
set removeServerTime=1
:removeServer
mysqld --remove %service_name% 
sc query %service_name% > nul
if not errorlevel 1 (
	goto deleteDataFile
) else (
	set removeServerTime=removeServerTime + 1
	if "%removeServerTime%" == "4" (
		echo 原服务卸载失败>>%logPath%
		goto end
	) else (
		GOTO deleteDataFile
	)
)
set deleteDataFileTime=1

:deleteDataFile
echo 删除原DATA文件>>%logPath%
set dataPath=%mysql_path%data
ping 127.0.0.1 -n 10 > nul
if exist %dataPath% ( 
	rd /s/q %dataPath%
)

IF ERRORLEVEL 1072 (
	set deleteDataFileTime=deleteDataFileTime + 1
	if "%deleteDataFileTime%" == "4" (
		goto end
	) else (
		GOTO deleteDataFile
	)
) else (
	echo 删除原MYSQL服务完成>>%logPath%
	goto installMysql
)

:installMysql
echo ******开始安装MySQL*****>>%logPath%

echo 重写my.ini文件信息>>%logPath%
call %mysql_path%modifyConfigration.bat %port%

cd %mysql_path%bin
mysqld --initialize-insecure --user=mysql --console >> %logPath%

rem 安装mysql
mysqld --install %service_name% >> %logPath%

echo ******************开始启动mysql服务*******************>>%logPath%
net start %service_name% >> %logPath%

:initMySQL
rem 修改密码为root，放开root用户客户端IP限制
echo ************修改密码为root，放开root用户客户端IP限制****************>>%logPath%
mysql -uroot -P %port% --skip-password < ../refresh.sql
rem 执行初始化的sql
echo ************执行sql脚本****************>>%logPath%
mysql -uroot -P %port% -proot < ../sql/*.sql >%logPath%
echo ************执行sql脚本****************
mysql -uroot -P %port% -proot < ../sql/*.sql >%logPath%
set installFinshed=true
goto end

:end
if "%installFinshed%"=="true" (echo true)

遇到问题及解决：
服务状态判断：

for /f "tokens=4" %%i in ('sc query %service_name% 2^>^&1 ^| findstr /i "STATE"') do set "zt=%%i"

mysql远程调用设置密码：

flush privileges;
set password for root@localhost = password('root');
CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;
quit

# 远程调用命令 大写P后接端口，大写D后接数据库名称。
mysql -h 192.168.0.248 -P 3306 -D db1 -u test -p
# 无密码 --skip-password

mysql安装时需要时间的，启动都需要时间，需要延时来处理:

ping 127.0.0.1 -n 10 > nul

加上一个次数处理和服务判断的处理，就可以很好的解决问题。