reset MySQL root password.
Contents
Linux
# 1、修改配置文件，一般在 /etc/my.cnf， 在[mysqld]字段下增加skip-grant-tables 字段，用以忽略权限验证；
# 2、重启MySQL服务器, CentOS 7.0操作命令是
systemctl restart mysqld.service
# 3、使用mysql -u root -p 登录数据， 密码直接回车;
# 4、修改mysql数据库（名称），user表的authentication_string字段，修改命令如下：
mysql> update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';
mysql> update mysql.user set authentication_string=password('new_password') where user='root' and Host ='localhost';
mysql> flush privileges;
mysql> quit;
# 5、修改配置文件，去掉skip-grant-tables，恢复成原来的样子， 然后重新MySQL服务器。
# 6、使用第4步设置的密码，登录mysql服务器。
# 【可选】7、通过 set password=password('new_password'); 命令重新设置密码；
set password=password('new_password');
Windows
【警告!!!】请尽可能不要在 Windows 系统使用 MySQL
MySQL 5.7 和 MySQL 8.0 均有过失败尝试
Windows 主机如果忘记 MySQL 密码，直接放弃吧。
深坑，深不见底，在此问题上耗时总计至少超过 10 小时，未曾成功解决过。
https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
文档记载内容无效。
问题的关键在于,如果添加了 --skip-grant-tables 选项 或者 --init-file 选项后， mysqld.exe 直接起不来了。
#
# 测试的启动命令
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
测试时使用的 my.ini
[mysqld]
skip-grant-tables
# The TCP/IP Port the MySQL Server will listen on
port=3306
# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 8.0/"
# Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 8.0/Data"
# General Logging
general-log=1
general_log_file="DESKTOP-XXXXXX.log"
slow-query-log=1
slow_query_log_file="DESKTOP-XXXXXX-slow.log"
long_query_time=10
# Error Logging.
log-error="DESKTOP-XXXXXX.err"
失败的方案一 init-file
尝试使用 init-file, 无法启动服务, 且 log 无记录
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --init-file="D:\mysql-init.txt" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
失败的方案二 skip-grant-tables
无法启动服务, 且 log 无记录
也尝试在 my.ini 中添加参数，亦无法启动服务
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --shared-memory --skip-grant-tables --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
对比组
可以正常启动服务,但忘记 root 密码
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
utilities for service management on Windows
%SystemRoot%\System32\services.exe
Service Control Manager (SCM) has two interfaces, one is a MMC (Microsoft Management Console) GUI-based interface called Services.msc, the other is a command-line interface called sc.exe (Service Control).
cmd tools
stop MySQL service with Windows command line
# run as (administrator)
# or use services.msc
# services.msc (Windows Services Manager)
# sc.exe
net /?
net start <service_name>
net stop <service_name>
net pause <service_name>
net continue <service_name>
sc /?
sc start <service_name>
sc stop <service_name>
sc pause <service_name>
sc continue <service_name>
net start "MySQL57"
net stop MySQL57
powershell
Get-Service
New-Service
Restart-Service
Resume-Service
Set-Service
Start-Service
Stop-Service
Suspend-Service
reset MySQL root password
special problem
When you are using cmder in Windows 10, right click bottom tab to create a new console (run as administrator).
I tried the console which is created by cmder, but it can not make mysqld.exe running.
using EventViewer, I check Windows Application Log and eventually find some error infomation.
Could not open file 'C:\Program Files\MySQL\MySQL Server 5.7\data\YourComputerName.err' for error logging: Bad file descriptorFor more information, see Help and Support Center at http://www.mysql.com.
Aborting
For more information, see Help and Support Center at http://www.mysql.com.
Binlog endFor more information, see Help and Support Center at http://www.mysql.com.
mysqld.exe: Shutdown complete
For more information, see Help and Support Center at http://www.mysql.com.
Finnally I used ctrl + x to create a (system/administrator) console, and make mysqld.exe running successfully.
debug tips
Here are some commands you can use to determine whether mysqld.exe is correctly running.
netstat -an -p TCP -o | findstr 3306 # make sure mysql service is running
tasklist | findstr "sql" # make sure mysql service is running
mysqladmin.exe -u root -p shutdown
kill <pid>
operation details
# mysql version in test environment
mysql Ver 14.14 Distrib 5.7.21, for Win64 (x86_64)
Start > Control Panel > Administrative Tools > Services
Find the MySQL service in the list and stop it.
Start MySQL service (mysqld.exe) with option --skip-grant-tables can enable anyone to connect without a password and with all privileges. Then you can use account-management statements such as ALTER USER and SET PASSWORD to reset root password.
Since this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> .\mysqld.exe --skip-grant-tables [ --skip-networking] [--datadir=C:\\Program Files\\MySQL\\MySQL Server 5.7\\data]
mysqld.exe --skip-grant-tables --datadir="C:\\Program Files\\MySQL\\MySQL Server 5.7\\data"
mysql> FLUSH PRIVILEGES;
Here are some examples about account management statements.
# MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
# MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
# Other situation
UPDATE mysql.user
SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
Leave a Reply