ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server
$mysql -u root --host=127.0.0.1 -p
mysql>use mysql
mysql>GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'redhat@123';
mysql>FLUSH PRIVILEGES;
mysql> SELECT host FROM mysql.user WHERE User = 'root';
Your root
account, and this statement applies to any account, may only have been added with localhost access (which is recommended).
You can check this with:
SELECT host FROM mysql.user WHERE User = 'root';
If you only see results with localhost
and 127.0.0.1
, you cannot connect from an external source. If you see other IP addresses, but not the one you're connecting from - that's also an indication.
You will need to add the IP address of each system that you want to grant access to, and then grant privileges:
CREATE USER 'root'@'ip_address' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip_address';
If you see %
, well then, there's another problem altogether as that is "any remote source". If however you do want any/all systems to connect via root, use the %
wildcard to grant access:
CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Finally, reload the permissions, and you should be able to have remote access:
FLUSH PRIVILEGES;
mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'passWord'; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON . TO 'name'@'%'; Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
mysql>
- Make sure you have your name and % the right way round
- Makes sure you have added your port 3306 to any firewall you may be running (although this will give a different error message)
hope this helps someone...
Following two steps worked perfectly fine for me:
Comment out the bind address from the file
/etc/mysql/my.cnf
:#bind-address = 127.0.0.1
Run following query in phpMyAdmin:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; FLUSH PRIVILEGES;