原理:停止服务,通过脚本程序添加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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
@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
但没有成功
下面为之前脚本,误以为成功!
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
@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