批处理 mysql 5.7 一键重置密码（Windows）

原理：停止服务，通过脚本程序添加skip-grant-tables配置，生成修改密码脚本，执行修改密码脚本，成功后停止服务，再通过脚本程序注释skip-grant-tables配置，最后再启动服务

前置脚本1：SkipGrantTablesInsert.bat (添加：skip-grant-tables)

@echo off
Setlocal enabledelayedexpansion

SET FIND_DIR=%~dp0
set st=#skip-grant-tables
::把替换字符串赋值给dt，这里举例是st->dt
set dt=skip-grant-tables
set fn=my.ini

SET FULL_PATH=%FIND_DIR%%fn%

find /i "%st%" %FULL_PATH%>nul&& GOTO REPLACENEWLAB || GOTO JUDGEEXISTENCE

:REPLACENEWLAB
(
    for /f "tokens=*" %%i in ( !FULL_PATH!) do (
        set s=%%i
        set s=!s:%st%=%dt%!
        echo !s!
    )
)>%FIND_DIR%temp1126.txt
move /y %FIND_DIR%temp1126.txt "!FULL_PATH!"
GOTO END

:: 判断是否已有些标识如果存在，则不作操作
:JUDGEEXISTENCE
find /i "%dt%" %FULL_PATH%>nul&& GOTO END || GOTO ADDNEWLAB
GOTO END


:ADDNEWLAB
set mysqldLab=[mysqld]
(
    for /f "tokens=*" %%a in ( !FULL_PATH!) do (
        set s1=%%a
        echo !s1!
        if !s1! EQU !mysqldLab! (
            echo !dt!
        )
    )
)>%FIND_DIR%temp1126.txt
move /y %FIND_DIR%temp1126.txt "!FULL_PATH!"
GOTO END

:end

前置脚本2：SkipGrantTablesUnInsert.bat (注释：skip-grant-tables)

@echo off
Setlocal enabledelayedexpansion

SET FIND_DIR=%~dp0
set st=skip-grant-tables
::把替换字符串赋值给dt，这里举例是st->dt
set dt=#skip-grant-tables
set fn=my.ini

SET FULL_PATH=%FIND_DIR%%fn%

find /i "%dt%" %FULL_PATH%>nul&& GOTO END || GOTO JUDGEEXISTENCE


:REPLACENEWLAB
(
    for /f "tokens=*" %%i in ( !FULL_PATH!) do (
        set s=%%i
        set s=!s:%st%=%dt%!
        echo !s!
    )
)>%FIND_DIR%temp1126.txt
move /y %FIND_DIR%temp1126.txt "!FULL_PATH!"
GOTO END

:: 判断是否已有些标识如果存在，则不作操作
:JUDGEEXISTENCE
find /i "%st%" %FULL_PATH%>nul&& GOTO REPLACENEWLAB || GOTO ADDNEWLAB
GOTO END

:ADDNEWLAB
set mysqldLab=[mysqld]
(
    for /f "tokens=*" %%a in ( !FULL_PATH!) do (
        set s1=%%a
        echo !s1!
        if !s1! EQU !mysqldLab! (
            echo !dt!
        )
    )
)>%FIND_DIR%temp1126.txt
move /y %FIND_DIR%temp1126.txt "!FULL_PATH!"
GOTO END

:end

重置脚本：强制修改root用户密码.bat

@setlocal enabledelayedexpansion
@echo off
color f0
title 强制修改root用户密码

rem 下面为自定义参数可修改
set mysqlServiceName=MySQL4506
set mysqlPort=4506
set newPwd=test1

set basedir=%~dp0
set bin=%~dp0\bin
set tmpSql=!basedir!tmpUpdatePwd.sql
cd /d %~dp0
cd bin
echo %tmpSql%

::禁用mysql服务，跳过权限验证修改密码 
taskkill /F /IM mysqld.exe 
net stop %mysqlServiceName% >nul
call %basedir%SkipGrantTablesInsert.bat
net start %mysqlServiceName% 
echo use mysql >%tmpSql% 
echo update user set authentication_string = password("%newPwd%") where user="root";>>%tmpSql%
@REM 下面是其他版本的修改语句
@REM echo update user set password=password(“%newPwd%”) where user="root";>>%tmpSql%
echo flush privileges; >>%tmpSql% 
echo ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; >>%tmpSql% 
echo flush privileges; >>%tmpSql% 
echo alter user 'root'@'localhost' identified by '%newPwd%'; >>%tmpSql% 
echo flush privileges; >>%tmpSql% 
echo exit >>%tmpSql% 
 
::因为是交互式，所以从文件读取内容 
%bin%\mysql --host=127.0.0.1 --user=root --port=%mysqlPort% --default-character-set=utf8 mysql<%tmpSql%
taskkill /F /IM mysqld.exe 
net stop %mysqlServiceName% >nul 
call %basedir%SkipGrantTablesUnInsert.bat
net start %mysqlServiceName% 
del %tmpSql% /F 
pause 
exit

错误：

1、ERROR 1054 (42S22) at line 2: Unknown column 'password' in 'field list'

原因：是导入修改密码版本不正确（各版本存密码位置不一样），开始我以为有是不能使用无密码登录导致的

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'DESKTOP-TTNOCFG' is not allowed to connect to this MySQL server
通过加下面语句：
echo ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; >>%tmpSql%
echo flush privileges; >>%tmpSql%

1862-your password has expired. To log you must change it using a client that supports expired passwords
通过加下面语句：
echo alter user 'root'@'localhost' identified by '%newPwd%'; >>%tmpSql%

Access denied for user 'root'@'localhost' (using password YES)
上面一句表示密码不正确

参考：https://www.jb51.net/article/52555.htm

@echo off 
 
title mysql 
 
::从注册表找到Mysql的安装路径写入文件mysql.txt 
reg query HKLM\SYSTEM\ControlSet001\Services\MySQL | find /I "ImagePath">C:\mysql.txt 
if %errorlevel% neq 0 ( 
echo MySQL not found 
pause 
exit
) 
 
::以”为分隔符，截取第二段内容保存到变量mysqlPath 
FOR /F tokens^=2^ delims^=^" %%i in (C:\mysql.txt) do set mysqlPath=%%i 
del C:\mysql.txt /f 
 
::路径中/替换为\ 
set mysqlPath=%mysqlPath:/=\% 
 
::删除路径最后一个字符（该字符不可见，可能是回车换行之类的） 
set mysqlPath=%mysqlPath:~0,-1% 
 
:BACKTOMAIN 
 
::取得路径最后一个字符看等不等于\ 
set character=%mysqlPath:~-1,1% 
 
::如果最后一个字符不等于\，那么跳转到GETPATH删除mysqlPath的最后一个字符 
if not %character% == \ goto GETPATH 
 
::进入mysql安装路径C:\Program Files\MySQL\MySQL Server 5.0\bin 
cd /d "%mysqlPath%"
 
::echo %mysqlPath% 
 
if %errorlevel% neq 0 ( 
echo MySQL not found 
pause 
exit
) 
 
::禁用mysql服务，跳过权限验证修改密码 
taskkill /F /IM mysqld-nt.exe 
net stop mysql >nul 
start /b mysqld-nt --skip-grant-tables 
ping -n 2 127.0.0.1 >nul 
echo use mysql >c:\config.tmp 
echo update user set password=password("") where user="root";>>C:\config.tmp 
echo flush privileges; >>C:\config.tmp 
echo exit >>C:\config.tmp 
 
::因为是交互式，所以从文件读取内容 
mysql <C:\config.tmp 
taskkill /F /IM mysqld-nt.exe 
net stop mysql >nul 
net start mysql 
del C:\config.tmp /F 
pause 
exit
 
::删除路径最后一个字符，跳回主程序 
:GETPATH 
set mysqlPath=%mysqlPath:~0,-1% 
goto BACKTOMAIN

View Code

但没有成功

下面为之前脚本，误以为成功！

@setlocal enabledelayedexpansion
@echo off
color f0
title 强制修改root用户密码

rem 下面为自定义参数可修改
set mysqlServiceName=MySQL7506
set iniFileName=my1.ini
set mysqlPort=3306
set newPwd=root

set basedir=%~dp0
set bin=%~dp0\bin
set timeConfig=!basedir!config.tmp
cd /d %~dp0

echo %timeConfig%

::禁用mysql服务，跳过权限验证修改密码 
net stop %mysqlServiceName% >nul
taskkill /F /IM mysqld.exe 
start /b %bin%\mysqld --defaults-file="%basedir%my.ini" --console --skip-grant-tables
mysql -u root -P%mysqlPort%
echo use mysql >%timeConfig% 
echo update user set password=password(%newPwd%) where user="root";>>%timeConfig%
echo flush privileges; >>%timeConfig% 
echo exit >>%timeConfig% 
 
::因为是交互式，所以从文件读取内容 
mysql <%timeConfig% 
taskkill /F /IM mysqld.exe 
net stop %mysqlServiceName% >nul 
net start %mysqlServiceName% 
del %timeConfig% /F 
pause 
exit