MAMP mysql损坏的root用户 [英] MAMP mysql broken root user

问题描述

我已经在SO和其他一些地方阅读了很多线程，但无法在MAMP中修复我的mysql安装.

I've read many threads on SO and a few other places and haven't been able to fix my mysql installation in MAMP.

背景.前几天，我对root用户进行了更改，以尝试使本地服务正确连接.重新启动计算机后，mamp中的phpmyadmin不再显示任何数据库，并显示"No Privileges"(无特权)以创建数据库.我尝试了许多事情来恢复我在这里和其他地方阅读的特权都无济于事. --skip-grant-tables似乎不起作用.以root用户身份在Shell中进行连接我没有本地主机的权限，但我具有127.0.0.1的权限.

Background. I made a change to my root user the other day in an attempt to get a local service to connect properly. After rebooting my machine phpmyadmin in mamp no longer displayed any of my dbs and showed "No Privileges" to create a database. I tried many things to restore the privileges that I read here and elsewhere to no avail. --skip-grant-tables does not seem to work. Connecting as root in the shell I don't have permission as localhost but I do as 127.0.0.1.

show grants for root@localhost;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

我已经安装了全新的MAMP安装并复制了我的数据库.在运行的同时，我遇到了数据库未显示所有表的实例.

I've installed a fresh installation of MAMP and copied over my DBs. While it works, I've run into instances where DBs are not showing all the tables.

所以我必须解决两个问题之一.我要么必须修复原始安装，要么进行安装，以便所有表都在我的全新Mamp安装中显示我的数据库.

So I have to solve one of two problems. I either have to fix the original install or make it so the all tables show for my dbs in my fresh mamp install.

这里的经验教训是，不要懒惰，不要在本地与root用户打扰，而只需创建另一个登录名即可.

Lesson learned here is don't be lazy and screw around with your root user locally and just create another login.

更新 当我以以下身份连接时:

UPDATE When I connect as:

/Applications/MAMP/Library/bin/mysql -uroot -h127.0.0.1 -p
mysql> SHOW GRANTS FOR 'root'@'localhost';
+------------------------------------------------------------------------+
| Grants for root@localhost                                              |
+------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION    | 
| GRANT ALL PRIVILEGES ON `db`.* TO 'root'@'localhost' WITH GRANT OPTION | 
+------------------------------------------------------------------------+

但是当我以以下方式连接时:

But when I connect as:

/Applications/MAMP/Library/bin/mysql -uroot -hlocalhost -p
mysql> SHOW GRANTS FOR 'root'@'localhost';
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost | 
+----------------+
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     | 
+----------------+

因此，当我通过IP或套接字作为本地主机连接时，我莫名其妙地没有以root用户身份登录.我觉得这很令人困惑吗?

So when I connect as localhost either by IP or socket I am somehow not logged in as root. I find this very confusing?

推荐答案

经过几天的努力，我发现了解决方案.由于将MAMP安装与标准mysql安装有所不同，因此花了一些时间将它们放在一起.我仍然不了解其中的一些细节，但是我们开始吧.

After several days of beating my head on this I discovered the solution. It took a while to put the pieces together because the MAMP installation is a bit different then a standard mysql installation. I still don't understand a few of the details but here we go.

即使我以root用户身份登录，现实情况是我是上述和"@'localhost"指示的匿名用户.因此，显然存在权限错误，该错误包含三个部分:用户名，密码和主机.因此，有关我的root帐户的某些更改(我相信这是密码)，但是整个响应的方式有点奇怪，因为没有告诉我密码和用户名组合不正确，而是没有权限登录.我相信我可以通过运行以下命令来加剧这种情况.

Even though I was logging in as root the reality was that I was an anonymous user indicated by the above and ''@'localhost'. So there is obviously a permissions error which has three parts: username, password, and host. So something about my root account changed (I believe it was the password), but how the whole thing responded was a bit wonky, as in instead of telling me my password and username combo was incorrect, I was getting logged in with no permissions. I believe I exacerbated this situation a bit by running some commands like the below.

mysqladmin -u root password 'password'

最后，这就是我所做的事情，我想弄清楚这是在OS X上安装MAMP的说明，尽管我确信它们可以轻松地适用于Windoz.

In the end here is what I did, and I want to make clear this is the instructions for a MAMP installation on OS X, although I'm sure they can be easily adapted for Windoz.

非常感谢此线程帮助我了解如何解决此问题.

Big thanks to this thread for helping me see the light on how to resolve this.

1. 打开活动监视器.
2. 关闭MAMP
3. 确保在活动监视器中关闭了mysql.现在，您可能会看到_mysqld进程正在运行，这可能是默认的OSX mysql正在运行.

4. 运行以下命令，将端口号替换为您使用的端口号.这将启动mysql，减去用户权限.

1. Open the Activity Monitor.
2. Shut down MAMP
3. Ensure that mysql is shut off in Activity Monitor. Now, you might see a _mysqld process running which might be the default OSX mysql running.

4. Run the following command, replacing the port number with the one you use. This will start mysql minus the user permissions.

/Applications/MAMP/Library/libexec/mysqld --skip-grant-tables --port = 8889 --socket =/Applications/MAMP/tmp/mysql/mysql.sock

/Applications/MAMP/Library/libexec/mysqld --skip-grant-tables --port=8889 --socket=/Applications/MAMP/tmp/mysql/mysql.sock

在控制台中无需任何凭据即可登录mysql.

In your console login to mysql with no creds.

mysql

mysql

运行以下命令.

Run the below commands.

FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

关闭mysqld并重新启动它，而无需使用skip-grant-tables选项

Shut down mysqld and restart it without the skip-grant-tables option

登录mysql确认一切正常.

Login to mysql to confirm that things work.

您还需要修复phpmyadmin.由于某些原因，我想以此作为开头，因为我不理解这些更改无法迅速传播，并突然开始工作.

You will need to fix your phpmyadmin as well. I want to preface this with for some reason I do not understand these changes to not propagate quickly, and suddenly just start working.

进入/Application/MAMP/bin/phpMyAdmin/config.inc.php并为您新创建的登录名输入正确的凭据.确保它们正确无误，包括主机.

Go into /Application/MAMP/bin/phpMyAdmin/config.inc.php and enter the correct credentials for your newly created login. Make sure they are exact including host.

在phpmyadmin工作之后，最后进入mysql.user表，查看哪里可能出错了.重要的是要弄明白到底是什么.

Lastly after phpmyadmin is working go into the mysql.user table and see where things could have gone wrong. It's important to understand what got messed up.

我想澄清一下上面发生的事情，例如为什么MAMP phpmyadmin凭据更改无法立即生效.

There are a few things about what happened in the above that I would love some clarification on, like why MAMPs phpmyadmin credential changes do not work immediately.

希望所有这些都有帮助！

Hope all this helps!

这篇关于MAMP mysql损坏的root用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！