I issued a command of
DROP USER 'root'@'localhost'; GRANT ALL PRIVILEGES ON . TO 'root'@'%';in PhpMyAdmin, immediately after the execution, I was forced out PhpMyAdmin,I got an error
#1130 - Host 'localhost' is not allowed to connect to this MySQL server, how to resolve my problem?
I issued a command of
closed as off-topic by HopelessN00b Feb 20 '15 at 22:33
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions should demonstrate reasonable business information technology management practices. Questions that relate to unsupported hardware or software platforms or unmaintained environments may not be suitable for Server Fault - see the help center." – HopelessN00b
locked by HopelessN00b Feb 20 '15 at 22:33
This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.
Read more about locked posts here.
Open up a terminal.
$ mysql -u root --host=127.0.0.1 -p<yourpassword> Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 521 Server version: 5.1.38yes-debug yes Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY "<yourpassword>"; Query OK, 0 rows affected (0.22 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost; Query OK, 0 rows affected (0.08 sec)
This should fix your problem ;).
mysql treats % and localhost differently. You need grant all privileges to
'root'@'localhost' as well.
On your server run mysql from command line:
mysql -u root -p -h localhost -P 3306
Then run this command in mysql shell:
>use mysql >GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'pass'; >FLUSH PRIVILEGES;
Have a nice time.
Combine dlo's answer with Darren Chamberlain's. The reason for this is that the special meaning that 'localhost' has in MySQL is that it signifies to use the local unix socket (mysql.sock) vs the TCP socket. This is why specifying 127.0.0.1 as the host will get you in so that you can fix the situation; it signifies to the MySQL client to use the TCP socket.