添加--skip-grant-tables后mysqld启动即停止问题求助

问题描述 #

忘记MySQL root用户密码，尝试在my.ini配置文件中添加--skip-grant-tables选项启动mysqld，但服务启动后立即停止，附上错误日志如下：

2022-08-25T17:52:06.012094Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-08-25T17:52:06.015237Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.28) starting as process 1048
2022-08-25T17:52:06.045494Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-25T17:52:06.398313Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-25T17:52:06.642873Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2022-08-25T17:52:06.725383Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-08-25T17:52:06.726481Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-08-25T17:52:06.757168Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.28'  socket: ''  port: 0  MySQL Community Server - GPL.
2022-08-25T17:52:06.759476Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2022-08-25T17:52:06.760755Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-25T17:52:07.903037Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0sys.cc:644:UT_LIST_GET_LEN(trx_sys->mysql_trx_list) == 0 thread 26320
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
17:52:07 UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff78a48fd48    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff7896c004b    mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff7896bfe13    mysqld.exe!?my_server_abort@@YAXXZ()
7ff78a47643a    mysqld.exe!?my_abort@@YAXXZ()
7ff78a6ac579    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff78a5e8167    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff78a67033b    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff78a568550    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff7894a452b    mysqld.exe!?ha_finalize_handlerton@@YAHPEAUst_plugin_int@@@Z()
7ff7894e2304    mysqld.exe!?memcached_shutdown@@YAXXZ()
7ff7894e78d9    mysqld.exe!?plugin_unlock_list@@YAXPEAVTHD@@PEAPEAUst_plugin_int@@_K@Z()
7ff7894e6630    mysqld.exe!?plugin_shutdown@@YAXXZ()
7ff789482d49    mysqld.exe!?check_valid_arguments_processor@Item_func@@UEAA_NPEAE@Z()
7ff78949679a    mysqld.exe!?underflow@?$basic_stringbuf@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@MEAAHXZ()
7ff78949531f    mysqld.exe!?setup_conn_event_handler_threads@@YAXXZ()
7ff7894988be    mysqld.exe!?win_main@@YAHHPEAPEAD@Z()
7ff7894916f5    mysqld.exe!?mysql_service@@YAHPEAX@Z()
7ff789491d54    mysqld.exe!?mysqld_main@@YAHHPEAPEAD@Z()
7ff78ab0b298    mysqld.exe!?set_timespec_nsec@@YAXPEAUtimespec@@_K@Z()
7ffe8b437034    KERNEL32.DLL!BaseThreadInitThunk()
7ffe8b742651    ntdll.dll!RtlUserThreadStart()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

错误原因分析 #

日志中关键错误行：

2022-08-25T17:52:06.759476Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS

这说明在Windows系统下，仅启用--skip-grant-tables时，MySQL要求必须配置至少一种连接接口（TCP/IP、共享内存或命名管道），否则会触发服务中止。

解决步骤 #

1. 修改my.ini配置文件：
   在添加--skip-grant-tables的同时，任选以下一种配置添加到文件中：

   • 启用TCP/IP：port=3306（使用你常用的端口号即可）
   • 启用共享内存：shared-memory
   • 启用命名管道：enable-named-pipe

2. 重启MySQL服务：
   保存my.ini后，通过任务管理器或命令行重启MySQL服务。

3. 连接MySQL并重置密码：
   打开命令行，执行以下命令无密码登录：

   mysql -u root
   

   登录成功后，执行重置密码的SQL语句（MySQL 8.0版本）：

   ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';
   

   若提示权限错误，先执行：

   FLUSH PRIVILEGES;
   

   再重新执行修改密码的语句。

4. 恢复正常配置：
   密码修改完成后，编辑my.ini，移除--skip-grant-tables以及临时添加的连接接口配置（如果不需要的话），再次重启MySQL服务即可正常使用。

内容的提问来源于stack exchange，提问作者jcomp_03