Neurohazard
暮雲煙月,皓首窮經;森羅萬象,如是我聞。

MySQL 5.7 版本重置 root 密码

wpadmin~May 20, 2018 /System Management

reset MySQL root password.

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;

Reference

  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 *