如何使用 --skip-grant-tables 启动 MySQL？

How to start MySQL with --skip-grant-tables?

提问by Steven

I locked my root user out from our database. I need to get all privileges back to the root user. I have my password and I can log in to MySQL. But the root user has no all privileges.

我从我们的数据库中锁定了我的 root 用户。我需要将所有权限都归还给 root 用户。我有我的密码，我可以登录到 MySQL。但是 root 用户没有所有权限。

回答by tonycoupland

I had the same problem as the title of this question, so incase anyone else googles upon this question and wants to start MySql in 'skip-grant-tables' mode on Windows, here is what I did.

我和这个问题的标题有同样的问题，所以如果其他人在这个问题上用谷歌搜索并想在 Windows 上以“skip-grant-tables”模式启动 MySql，这就是我所做的。

Stop the MySQL service through Administrator tools, Services.

通过管理员工具服务停止 MySQL 服务。

Modify the my.ini configuration file (assuming default paths)

修改my.ini配置文件（假设默认路径）

C:\Program Files\MySQL\MySQL Server 5.5\my.ini

or for MySQL version >= 5.6

或 MySQL 版本 >= 5.6

C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 

In the SERVER SECTION, under [mysqld], add the following line:

在服务器部分的 [mysqld] 下，添加以下行：

skip-grant-tables

so that you have

所以你有

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

skip-grant-tables

Start the service again and you should be able to log into your database without a password.

再次启动该服务，您应该能够在没有密码的情况下登录到您的数据库。

回答by gizmo_marco

How to re-take control of the root user in MySQL.

如何重新控制 MySQL 中的 root 用户。

DANGER: RISKY OPERATTION

危险：危险操作

• Start session ssh (using root if possible).

• Edit my.cnffile using.

  sudo vi /etc/my.cnf
  

• Add line to mysqld block.*

  skip-grant-tables
  

• Save and exit.

• Restart MySQL service.

  service mysql restart
  

• Check service status.

  service mysql status
  

• Connect to mysql.

  mysql
  

• Using main database.

  use mysql;
  

• Redefine user root password.

  UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root'; 
  

• Edit file my.cnf.

  sudo vi /etc/my.cnf
  

• Erase line.

  skip-grant-tables
  

• Save and exit.

• Restart MySQL service.

  service mysqld restart
  

• Check service status.

  service mysql status
  

• Connect to database.

  mysql -u root -p
  

• Type new password when prompted.

• 启动会话 ssh（如果可能，使用 root）。

• my.cnf使用编辑文件。

  sudo vi /etc/my.cnf
  

• 将行添加到 mysqld 块。*

  skip-grant-tables
  

• 保存并退出。

• 重启 MySQL 服务。

  service mysql restart
  

• 检查服务状态。

  service mysql status
  

• 连接到 mysql。

  mysql
  

• 使用主数据库。

  use mysql;
  

• 重新定义用户 root 密码。

  UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root'; 
  

• 编辑文件 my.cnf。

  sudo vi /etc/my.cnf
  

• 擦除线。

  skip-grant-tables
  

• 保存并退出。

• 重启 MySQL 服务。

  service mysqld restart
  

• 检查服务状态。

  service mysql status
  

• 连接到数据库。

  mysql -u root -p
  

• 出现提示时键入新密码。

This action is very dangerous, it allows anyone to connect to all databases with no restriction without a user and password. It must be used carefully and mustbe reverted quickly to avoid risks.

此操作非常危险，它允许任何人在没有用户名和密码的情况下不受限制地连接到所有数据库。它必须小心使用，并且必须迅速恢复以避免风险。

回答by Carles Alcolea

After trying lotsof things, this is what worked for me:

在尝试了很多事情之后，这对我有用：

sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword'; 

So first we use sudo to log in mysql as root without needing a password. Then we just update root's password.

所以首先我们使用sudo以root身份登录mysql，不需要密码。然后我们只需更新root的密码。

After that, I restarted mysqld:

之后，我重新启动mysqld：

sudo service mysql restart

And the newpasswordlogged root in!

和newpassword登录的root！

回答by Arpit

On the Linux system you can do following (Should be similar for other OS)

在 Linux 系统上，您可以执行以下操作（其他操作系统应该类似）

Check if mysql process is running:

检查mysql进程是否正在运行：

sudo service mysql status

If runnning then stop the process: (Make sure you close all mysql tool)

如果正在运行，则停止该进程：（确保关闭所有 mysql 工具）

sudo service mysql stop

If you have issue stopping then do following

如果您遇到问题停止，请执行以下操作

Search for process: ps aux | grep mysqldKill the process: kill -9 process_id

搜索进程：ps aux | grep mysqld杀死进程：kill -9 process_id

Now start mysql in safe mode with skip grant

现在使用跳过授权以安全模式启动 mysql

sudo mysqld_safe --skip-grant-tables &

回答by trailing slash

Use the following command (notice the "d"): mysqld --skip-grant-tables

使用以下命令（注意“d”）： mysqld --skip-grant-tables

回答by heesang

If you use mysql 5.6 server and have problems with C:\Program Files\MySQL\MySQL Server 5.6\my.ini:

如果您使用 mysql 5.6 服务器并遇到以下问题C:\Program Files\MySQL\MySQL Server 5.6\my.ini：

You should go to C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

你应该去C:\ProgramData\MySQL\MySQL Server 5.6\my.ini。

You should add skip-grant-tablesand then you do not need a password.

您应该添加skip-grant-tables然后不需要密码。

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]
skip-grant-tables

Note: after you are done with your work on skip-grant-tables, you should restore your file of C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

注意：完成对 的工作后skip-grant-tables，您应该恢复C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

回答by Saurabh Hooda

if you are running on Apple MacBook OSXthen:

如果你正在运行，Apple MacBook OSX那么：

1. Stop your MySQL server (if it is already running).
2. Find your MySQL configuration file, my.cnf. (For me it was placed @ /Applications/XAMPP/xamppfiles/etc. You can just search if you can't find it).
3. Open my.cnffile in any text editor.
4. Add "skip-grant-tables"(without quotes) at the end of [mysqld]section and save the file.
5. Now start your MySQL server. It'll start with skip-grant-tablesoption.

1. 停止您的 MySQL 服务器（如果它已经在运行）。
2. 找到您的 MySQL 配置文件my.cnf. （对我来说它被放置在 @ /Applications/XAMPP/xamppfiles/etc。如果你找不到它，你可以搜索）。
3. my.cnf在任何文本编辑器中打开文件。
4. "skip-grant-tables"在[mysqld]部分末尾添加（不带引号）并保存文件。
5. 现在启动你的 MySQL 服务器。它将从skip-grant-tables选项开始。

Do what you want now!!

现在做你想做的！！

PS: Please remove skip-grant-tablesfrom my.cnffile once you are done with whatsoever you want to do ELSE MySQL server will always run without access grants.

PS：请在完成任何您想做的事情后skip-grant-tables从my.cnf文件中删除否则 MySQL 服务器将始终在没有访问权限的情况下运行。

回答by Faltausername

I'm in windows 10, using WAMP64 server. Searched for my.cnfand my.ini. Found my.iniin C:\wamp64\bin\mariadb\mariadb10.2.14.

我在 Windows 10 中，使用 WAMP64 服务器。搜索my.cnf和my.ini。发现my.ini在C:\wamp64\bin\mariadb\mariadb10.2.14。

Following the instructions from the colleagues:

按照同事的指示：

1. Opened the quick start menu from Wampserver, selected 'Stop All Services'
2. Opened my.iniin a text editor, searched for [mysqld]
3. Added 'skip-grant-tables'at the end of the [mysqld]section (but within it)
4. Save the file, leave the editor open
5. In the Wampserver menu, select "Restart Services'. There will be a warning about the skip-grant-tablesoption
6. In the Wampserver menu select MySQL to open the prompt
7. It asked for a password, just press enter
8. Paste the command ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
9. It must report that the operation was successful (no tables affected)
10. In the my.inifile, erase the 'skip-grant-tables'line, save the file
11. In the WampServer menu, select once more Restart Service

1. 从 Wampserver 打开快速启动菜单，选择“停止所有服务”
2. my.ini在文本编辑器中打开，搜索[mysqld]
3. 'skip-grant-tables'在本[mysqld]节末尾添加（但在其中）
4. 保存文件，让编辑器保持打开状态
5. 在 Wampserver 菜单中，选择“重新启动服务”。会有一个关于该skip-grant-tables选项的警告
6. 在 Wampserver 菜单中选择 MySQL 打开提示
7. 它要求输入密码，只需按 Enter
8. 粘贴命令 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
9. 它必须报告操作成功（不影响表）
10. 在my.ini文件中，擦除该'skip-grant-tables'行，保存文件
11. 在 WampServer 菜单中，再次选择 Restart Service

Now you can enter with the new password. Thanks to all answers here.

现在您可以使用新密码进入。感谢这里的所有答案。

回答by user1001

Please run this below command from the console to skip the user table verification while launching mysql database from command prompt

请从控制台运行以下命令以在从命令提示符启动 mysql 数据库时跳过用户表验证

mysqld -skip-grant-tables

回答by Azouz Mohamadi

Edit my.ini file and add skip-grant-tables and restart your mysql server :

编辑 my.ini 文件并添加 skip-grant-tables 并重新启动您的 mysql 服务器：

[mysqld]
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
skip-grant-tables
# Change here for bind listening
# bind-address="127.0.0.1" 
# bind-address = ::1