为 MySQL 恢复已删除的“root”用户和密码

Restoring deleted 'root' user and password for MySQL

提问by PHPeer

I accidentally deleted the rootuser on my local dev setup of MAMP/MySQL running on OS X. There are no other users created to get back into MySQL.

我不小心删除了root运行在 OS X 上的 MAMP/MySQL 本地开发设置中的用户。没有创建其他用户来重新使用 MySQL。

This is a mild nightmare can't seem to do anything without root.

这是一个温和的噩梦，没有它似乎什么也做不了root。

Found this: http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.htmlwhich seems like exactly what I need.

发现这个：http: //hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html这似乎正是我需要的。

I also don't think a reinstall of MAMP will do the trick, as a lot of my cursory searches yielded people who had tried without success.

我也不认为重新安装 MAMP 会成功，因为我粗略的搜索很多人都尝试过但没有成功。

Does anyone know of an OSX friendly way to recreate root@ localhostback into MAMP's MySQL? I basically just don't know where MySQL is living in MAMP or how to execute the correct commands in terminal to try and fix it.

有谁知道将root@重新创建localhost回 MAMP 的 MySQL的 OSX 友好方式吗？我基本上只是不知道 MySQL 在 MAMP 中的位置，或者如何在终端中执行正确的命令来尝试修复它。

Update

更新

I tried several options below to restore root to no avail and decided to restore a backup of the entire MAMP application. So I've got root back, I can open phpmyadmin, etc.

我尝试了以下几个选项来恢复 root 无济于事，并决定恢复整个 MAMP 应用程序的备份。所以我已经恢复了 root，我可以打开 phpmyadmin 等。

回答by RolandoMySQLDBA

I have a quick and dirty way

我有一个快速而肮脏的方式

Get someone with SysAdmin rights and do the following:

找一个拥有系统管理员权限的人并执行以下操作：

1. Add 'skip-grant-tables' to my.cnf under the [mysqld] section

2. restart mysql

3. type mysql with no password and hit enter

4. Run This:

   DELETE FROM mysql.user 
   WHERE  user = 'root' 
          AND host = 'localhost'; 
   
   INSERT INTO mysql.user 
   SET user = 'root', 
       host = 'localhost', 
       password = Password('whatevernewpassword'), 
       Select_priv = 'y',
       Insert_priv = 'y',
       Update_priv = 'y',
       Delete_priv = 'y',
       Create_priv = 'y',
       Drop_priv = 'y',
       Reload_priv = 'y',
       Shutdown_priv = 'y',
       Process_priv = 'y',
       File_priv = 'y',
       Grant_priv = 'y',
       References_priv = 'y',
       Index_priv = 'y',
       Alter_priv = 'y',
       Show_db_priv = 'y',
       Super_priv = 'y',
       Create_tmp_table_priv = 'y',
       Lock_tables_priv = 'y',
       Execute_priv = 'y',
       Repl_slave_priv = 'y',
       Repl_client_priv = 'y',
       Create_view_priv = 'y',
       Show_view_priv = 'y',
       Create_routine_priv = 'y',
       Alter_routine_priv = 'y',
       Create_user_priv = 'y',
       Event_priv = 'y',
       Trigger_priv = 'y',
       Create_tablespace_priv = 'y';
   

5. exit from mysql

6. remove 'skip-grant-tables' from my.cnf under the [mysqld] section

7. restart mysql

1. 在 [mysqld] 部分下的 my.cnf 中添加 'skip-grant-tables'

2. 重启mysql

3. 输入没有密码的 mysql 并按回车键

4. 运行这个：

   DELETE FROM mysql.user 
   WHERE  user = 'root' 
          AND host = 'localhost'; 
   
   INSERT INTO mysql.user 
   SET user = 'root', 
       host = 'localhost', 
       password = Password('whatevernewpassword'), 
       Select_priv = 'y',
       Insert_priv = 'y',
       Update_priv = 'y',
       Delete_priv = 'y',
       Create_priv = 'y',
       Drop_priv = 'y',
       Reload_priv = 'y',
       Shutdown_priv = 'y',
       Process_priv = 'y',
       File_priv = 'y',
       Grant_priv = 'y',
       References_priv = 'y',
       Index_priv = 'y',
       Alter_priv = 'y',
       Show_db_priv = 'y',
       Super_priv = 'y',
       Create_tmp_table_priv = 'y',
       Lock_tables_priv = 'y',
       Execute_priv = 'y',
       Repl_slave_priv = 'y',
       Repl_client_priv = 'y',
       Create_view_priv = 'y',
       Show_view_priv = 'y',
       Create_routine_priv = 'y',
       Alter_routine_priv = 'y',
       Create_user_priv = 'y',
       Event_priv = 'y',
       Trigger_priv = 'y',
       Create_tablespace_priv = 'y';
   

5. 退出mysql

6. 从 [mysqld] 部分下的 my.cnf 中删除“skip-grant-tables”

7. 重启mysql

That should be all!

这应该是全部！

回答by jm666

The translated version of http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html- for OS X.

http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html的翻译版本 - 适用于 OS X。

Open TextEdit.app and select in Format -> "Make plain text".
Cut and paste the following into TextEdit and save it into your HOMEfolder with name restore_root_privileges.sql

打开 TextEdit.app 并选择格式 ->“制作纯文本”。
将以下内容剪切并粘贴到 TextEdit 中，并使用名称将其保存到您的HOME文件夹中restore_root_privileges.sql

update mysql.user set Super_priv='y' where user='root';
update mysql.user set Select_priv='y' where user='root';
update mysql.user set Insert_priv='y' where user='root';
update mysql.user set Update_priv='y' where user='root';
update mysql.user set Delete_priv='y' where user='root';
update mysql.user set Create_priv='y' where user='root';
update mysql.user set Drop_priv='y' where user='root';
update mysql.user set Reload_priv='y' where user='root';
update mysql.user set Shutdown_priv='y' where user='root';
update mysql.user set Process_priv='y' where user='root';
update mysql.user set File_priv='y' where user='root';
update mysql.user set Grant_priv='y' where user='root';
update mysql.user set References_priv='y' where user='root';
update mysql.user set Index_priv='y' where user='root';
update mysql.user set Alter_priv='y' where user='root';
update mysql.user set Show_db_priv='y' where user='root';
update mysql.user set Super_priv='y' where user='root';
update mysql.user set Create_tmp_table_priv='y' where user='root';
update mysql.user set Lock_tables_priv='y' where user='root';
update mysql.user set Execute_priv='y' where user='root';
update mysql.user set Repl_slave_priv='y' where user='root';
update mysql.user set Repl_client_priv='y' where user='root';
update mysql.user set Create_view_priv='y' where user='root';
update mysql.user set Show_view_priv='y' where user='root';
update mysql.user set Create_routine_priv='y' where user='root';
update mysql.user set Alter_routine_priv='y' where user='root';
update mysql.user set Create_user_priv='y' where user='root';

Save and quit TextEdit.app.

保存并退出 TextEdit.app。

Stop you mysqld server. How to do this, depends on what installation did you use for MySQL. You probably have an PreferencePane in your system preferences. If not, you must consult the docs for your MySQL installation.

停止你的 mysqld 服务器。如何执行此操作取决于您对 MySQL 使用的安装。您的系统首选项中可能有一个 PreferencePane。如果没有，您必须查阅 MySQL 安装文档。

Open Terminal.app (Applications/Utilities) Enter the following commands:

打开 Terminal.app (Applications/Utilities) 输入以下命令：

sudo mysqld --skip-grant-tables &  #start your MySQL server without access control
mysql -vv < ~/restore_root_privileges.sql
sudo mysqladmin -p shutdown

Start your MySQL server as usually, e.g. from PreferencePanes.
In the Terminal.app: enter the following:

像往常一样启动您的 MySQL 服务器，例如从 PreferencePanes。
在 Terminal.app 中：输入以下内容：

mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
mysql> quit;

That's all. Without any warranty.You can loose all you data if do something wrong. Backup first your mysql files.

就这样。没有任何保证。如果做错了什么，您可能会丢失所有数据。首先备份你的 mysql 文件。

If you got something like:

如果你有类似的东西：

-bash: mysql: command not found

thats mean, than your installation is incorrect and you should find where are your mysql binaries, and need enter the directory into you PATH variable.

也就是说，您的安装不正确，您应该找到 mysql 二进制文件在哪里，并且需要将目录输入到您的 PATH 变量中。

回答by Alex Val

I just got the same - problem. I am using Xammp on Windows 7. I accidently deleted a root user in phpmyadmin.

我刚刚遇到了同样的问题。我在 Windows 7 上使用 Xammp。我不小心在 phpmyadmin 中删除了一个 root 用户。

It appears I was able to fix the problem in easy route like this:

看来我能够以这样的简单路线解决问题：

Stop the apache and mysql in xammp control

停止xammp控件中的apache和mysql

Go to .../xammp/mysql

转到.../xammp/mysql

You will see the file "resetroot". Run this file

您将看到文件“resetroot”。运行这个文件

After this application is finished, restart your mysql and apache

此应用程序完成后，重新启动您的 mysql 和 apache

Go to phpmyadmin and you will see the root user restored

转到phpmyadmin，您将看到root用户已恢复

Now you can access all your databases again

现在您可以再次访问所有数据库

回答by Francisco Cortes

using mamp. I made the apparently common mistake of deleting the root user when creating another user, I was locked out!

使用 mamp。我在创建另一个用户时犯了删除 root 用户的明显常见错误，我被锁定了！

so I found this post and tried to follow the directions but...

所以我找到了这篇文章并尝试按照说明进行操作，但是...

I was getting this errores: -bash: mysql: command not found when trying to execute the command mysql commands and later I was getting this error DELETE command denied to user ''@'localhost' for table 'user' when tring to set properly privileges to the root user and do anything with that user once mysql in shell.

我收到此错误：-bash: mysql: command not found when try to execute the command mysql commands and later 我收到此错误 DELETE command denied to user ''@'localhost' for table 'user' 当 tring 正确设置时授予 root 用户的权限，并在 shell 中的 mysql 后对该用户执行任何操作。

so before I could use the commands eric and rolando posted (thank you guys for that) this is what I did

所以在我可以使用 eric 和 rolando 发布的命令之前（谢谢你们）这就是我所做的

I need to start mysqld properly so I had to do the following

我需要正确启动 mysqld，所以我必须执行以下操作

1. stop mysqld process found with

   ps aux | grep mysql

1. 停止找到的 mysqld 进程

   ps辅助| mysql

并停止 mamp 接口应用程序以及..

1. and stopped any mysql process found with

   kill -9 [pid]

1. 并停止了发现的任何 mysql 进程

   kill -9 [pid]

在继续之前，请确保没有 mysql 进程正在运行。

1. then I had to restart mysqld properly from the actual location where I had mysql binaries this commnad will start mysld without asking for a password (sort of like in safemode)

1. 然后我必须从我拥有 mysql 二进制文件的实际位置正确地重新启动 mysqld 这个命令将启动 mysld 而不要求输入密码（有点像在安全模式下）

/Applications/MAMP/Library/bin/mysqld --skip-grant-tables --skip-networking &

/Applications/MAMP/Library/bin/mysqld --skip-grant-tables --skip-networking &

I tried with my.cnf file but couldn't get it to work.

我尝试使用 my.cnf 文件，但无法使其正常工作。

1. then I had to go into mysql in shell

1. 然后我不得不在shell中进入mysql

/Applications/MAMP/Library/bin/mysql -u root -p

/Applications/MAMP/Library/bin/mysql -u root -p

(if prompted for a password just hit enter)

（如果提示输入密码，只需按回车键）

1. then execute the commands eric and rolando posted, this time I didnt get any error "DELETE command denied to user ''@'localhost' for table 'user'" if you do, you didn't start mysqld properly

2. then stop the mysqld service:

1. 然后执行eric和rolando发布的命令，这次我没有收到任何错误“DELETE command denied to user ''@'localhost' for table 'user'”如果你这样做了，你没有正确启动mysqld

2. 然后停止mysqld服务：

/Applications/MAMP/Library/bin/mysqld stop

/Applications/MAMP/Library/bin/mysqld 停止

then restarted mamp as usual with the gui

然后像往常一样使用gui重新启动mamp

and all started to work again as it was before... I was so relieved!!!

一切又开始像以前一样工作......我松了一口气！！！

2 hours of panic and trial and error with the console... valuable lessons learned..

控制台的 2 小时恐慌和反复试验......吸取了宝贵的经验教训......

If I messed up any of the commands or the explanation (which could be the case as I was tracing my steps back as writing this post please let me know. I'd be happy to update the post.

如果我搞砸了任何命令或解释（这可能是我在写这篇文章时追溯我的步骤时的情况），请告诉我。我很乐意更新这篇文章。

lastly don't give. it can be done!!

最后不给。可以办到！！

回答by Adeel Raza Azeemi

MySQL 5.7 had replaced Password field with authentication_string (in mysql.user table); so this worked for me.

MySQL 5.7 已将 Password 字段替换为 authentication_string（在 mysql.user 表中）；所以这对我有用。

INSERT INTO mysql.user 
SET user = 'root', 
host = 'localhost', 
authentication_string =Password('Yours_own_password'), 
Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y',
ssl_cipher = '',
x509_issuer = '',
x509_subject = '';

回答by user3349250

Just wanted to contribute how I resolved this. If you accidentally deleted the root user in MAMP PRO and are using OSX with Time Machine backups. Simply go to this folder in finder:

只是想贡献我如何解决这个问题。如果您不小心删除了 MAMP PRO 中的 root 用户并且正在使用 OSX 和 Time Machine 备份。只需转到finder中的此文件夹：

/Library/Application Support/appsolute/

/图书馆/应用程序支持/appsolute/

Then 'Enter Time Machine' from the top menu bar and restore a recent backup of the MAMP PRO folder. Saved a lot of hassle for me.

然后从顶部菜单栏中“进入 Time Machine”并恢复 MAMP PRO 文件夹的最近备份。为我省去了很多麻烦。

回答by Eddie

I did exactly the same thing yesterday, being new to macs and the sql server. I accidentally deleted the root user because I pressed the wrong button in Privileges whilst trying to set up a new user and password.

我昨天做了完全相同的事情，是 macs 和 sql server 的新手。我不小心删除了 root 用户，因为我在尝试设置新用户和密码时按错了权限中的按钮。

I did not have any work of any use on this computer so was not worried about deleting my websites.

我在这台电脑上没有任何有用的工作，所以不担心删除我的网站。

• I first uninstalled MAMP Pro (for which I had an icon in Launchpad).
• I then deleted MAMP directory from the Applications folder.
• I then reinstalled MAMP from the internet download.
• I then DELETED all my cookies, cache etc from safari.
• I shut down my computer and rebooted.

• 我首先卸载了 MAMP Pro（我在 Launchpad 中有一个图标）。
• 然后我从 Applications 文件夹中删除了 MAMP 目录。
• 然后我从互联网下载重新安装了 MAMP。
• 然后我从 safari 中删除了我所有的 cookie、缓存等。
• 我关闭了我的电脑并重新启动。

I now have access to phpMyAdmin through the link on the startup page once MAMP is booted.

一旦 MAMP 启动，我现在可以通过启动页面上的链接访问 phpMyAdmin。

回答by hands

Ok, stop running your MySQL, and open up the installation files it came with. There should be a bat file named resetroot.bat. Run it and your problem will be gone.

好的，停止运行您的 MySQL，并打开它附带的安装文件。应该有一个名为 resetroot.bat 的 bat 文件。运行它，你的问题就会消失。

回答by Robert Jeffares

Worked for me on CentOS 6. I had to take out Create_tablespace_priv = 'y' because my version squawked otherwise all good.

在 CentOS 6 上为我工作。我不得不去掉 Create_tablespace_priv = 'y' 因为我的版本尖叫，否则一切都很好。