Problem: I was developing using PHPMyAdmin when I added a new root user (with a host value of %), and removed the rights of the default root user.
When you do that, you’ll get a gut-wrenching feel that you just made a huge boo-boo, find that suddenly everything will not work, and kick yourself in the ass.
Fortunately, you have a fix. It’s in the mysqld –skip-grant-tables command, but you can’t just run it directly. First of all, fire up terminal (applications > utilities > terminal), you’ll be using this exclusively to fix your problem.
For me, the I was using MAMP, and the path to my mysqld is within MAMP, so it was found in /Applications/MAMP/Library/libexec/mysqld
You can’t just perform a mysqld –skip-grant-tables command from the terminal though, because you’ll just see this error message:
[ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!
So what you need to do is to run this command instead:
mysqld -u root --skip-grant-tables
And you’re good! That terminal window is now running the mysqld instance, so open a new window by pressing CMD+T.
In this new window, type and run mysql. You should be able to get in now, because MySQL is now open to everyone. If you got into MySQL fine, you should see this:
mysql>
Great! You can’t grant any privileges now that you skipped the grant tables, but you can modify the users database. Run these commands – what they do is to use the mysql database, remove the root user, and insert a new root user (but with all privileges), and then flush the privileges to reset it to the new state.
mysql> use mysql;
mysql> delete from user where user='root';
mysql> insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,Index_priv, Alter_priv, Super_priv, Create_user_priv) VALUES ('localhost', 'root', PASSWORD('root'),'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
mysql> flush privileges;
mysql> quit;
And that’s it! You have just reset the root user, its password and its privileges, and you can now enter PHPMyAdmin. If you want to change the password, just change the part that says PASSWORD(‘root’) to PASSWORD(‘whateveryouwant’).
On a side note, if you are having trouble with a running mysqld process that you need to kill, simply type top to see the list of running processes on your system, then take note of the process number of mysqld. For e.g. mine was 3977.
Now type :q to exit, and type sudo bash to go to superuser mode. Next type kill -9 3977 (or whatever number your mysqld process is taking), and you’re good to go.
Protein bars have become the go-to snack for fitness enthusiasts, busy professionals, and health-conscious snackers…
A salary raise can significantly enhance the motivation and loyalty of your Philippines team members.…
The narrative of young entrepreneurs securing vast sums from venture capitalists for their startups, particularly…
Launching a startup is challenging. Despite many founders having innovative ideas, only a handful successfully…
What is retirement after all? A better way of looking at retirement is that it’s…
ElementsKit has a very simple and easy-to-use Mega Menu for Elementor, but it has a…
View Comments
Hey!
I get "-bash: mysqld: command not found" when I typ "mysqld -u root --skip-grant-tables" and I'm sure I'm in the right place (/Applications/MAMP/Library/libexec). I can see "mysqld" among the files when I list the directory. Any idea what I am doing wrong?
try typing in a ./ before the command, like this:
./mysqld -u root --skip-grant-tables
or trying the full path to the command, e.g.
/Applications/MAMP/Library/libexec/mysqld -u root --skip-grant-tables
Thank you for your quick answer! I solved it by backing up my databases and reinstalled MAMP. Damn, I will not do the same mistake twice! :D
I added ./ and now it worked! Strange...
heh yep..mysql can be frustrating...as I've found out too lol
./ tells it that u want it to look into the current directory to run the command/program, so that may be why u couldn't run it before (otherwise it'll just look into your path, which is why it couldn't find it)
Hi,
I'm installing wordpress on my MacBook Pro; I have MySQL on there already but I'd forgotten the root password :( I'm not running MAMP. Just wanted to let you know that your instructions worked perfectly! Thanks! Props to you!
David.
Glad that helped u!
Thousand thanks!!! Your tip just saved a lot of projects i was working on! You're the best !!!!!!
Thanks so much for this. I owe you a biscuit.
Thank you so much for this, was searching around for this for ages but this was easy to follow and worked like a charm :)
Thanks a lot for that. Phew!
I adjusted the string to where mysqld is located on my computer, but I get the following error:
/Applications/MAMP/Library/bin/mysqld -u root –skip-grant-tables
130508 7:05:48 [Warning] Setting lower_case_table_names=2 because file system for /Applications/MAMP/db/mysql/ is case insensitive
130508 7:05:48 [Warning] One can only use the --user switch if running as root
130508 7:05:48 [Note] Plugin 'FEDERATED' is disabled.
130508 7:05:48 InnoDB: The InnoDB memory heap is disabled
130508 7:05:48 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130508 7:05:48 InnoDB: Compressed tables use zlib 1.2.3
130508 7:05:48 InnoDB: Initializing buffer pool, size = 128.0M
130508 7:05:48 InnoDB: Completed initialization of buffer pool
130508 7:05:48 InnoDB: highest supported file format is Barracuda.
130508 7:05:48 InnoDB: Waiting for the background threads to start
130508 7:05:49 InnoDB: 1.1.5 started; log sequence number 39729833
/Applications/MAMP/Library/bin/mysqld: Too many arguments (first extra is '–skip-grant-tables').
Use --verbose --help to get a list of available options
130508 7:05:49 [ERROR] Aborting
130508 7:05:49 InnoDB: Starting shutdown...
130508 7:05:49 InnoDB: Shutdown completed; log sequence number 39729833
130508 7:05:49 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
please help