MySQL 5.7 版本重置 root 密码

wpadmin~May 20, 2018 /System Management

reset MySQL root password.


# 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 系统使用 MySQL
MySQL 5.7 和 MySQL 8.0 均有过失败尝试

Windows 主机如果忘记 MySQL 密码,直接放弃吧。
深坑,深不见底,在此问题上耗时总计至少超过 10 小时,未曾成功解决过。

问题的关键在于,如果添加了 --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


# The TCP/IP Port the MySQL Server will listen on

# 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

# Error Logging.

失败的方案一 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


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



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  
For more information, see Help and Support Center at  
Binlog endFor more information, see Help and Support Center at  
mysqld.exe: Shutdown complete
For more information, see Help and Support Center at  

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"

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';


  1. How to open Windows Services Manager in Windows 10/8/7

  2. Service Control Manager – Wikipedia

  3. MySQL 5.7 Reference Manual > Errors, Error Codes, and Common Problems > Problems and Common Errors > Administration-Related Issues > How to Reset the Root Password

Leave a Reply

Your email address will not be published. Required fields are marked *