Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Do you have any important information in this database installation? –  dlo Dec 10 '09 at 3:54
    
Yes, I have many tables. –  Steven Dec 10 '09 at 13:56
    
This might be helpful: webyog.com/faq/content/23/36/en/… –  Ashwin A Aug 10 '12 at 12:18

4 Answers 4

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 ;).

share|improve this answer

mysql treats % and localhost differently. You need grant all privileges to 'root'@'localhost' as well.

share|improve this answer

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.

share|improve this answer
    
One sure fire way to bring yourself to the attention of the system is to post identical answers to questions - please don't. Also from a security point of view allowing root to access from anywhere isn't great either. –  Iain Dec 18 '12 at 9:16

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.