为 MySQL 恢复已删除的“root"用户和密码 [英] Restoring deleted 'root' user and password for MySQL

问题描述

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

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

这是一个轻微的噩梦，没有root 似乎什么也做不了.

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

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

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

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

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.

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

Does anyone know of an OSX friendly way to recreate root @ localhost back 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 无济于事，并决定恢复整个 MAMP 应用程序的备份.所以我已经恢复了root，我可以打开phpmyadmin等

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.

推荐答案

我有一个又快又脏的方法

I have a quick and dirty way

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

Get someone with SysAdmin rights and do the following:

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

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

重启mysql

输入没有密码的mysql并回车

type mysql with no password and hit enter

运行这个:

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';

退出mysql

exit from mysql

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

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

重启mysql

应该就是这样了！

这篇关于为 MySQL 恢复已删除的“root"用户和密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！