Why oh why can I not connect to mysql?
mysql -u root -ptest101 -h xxx.xxx.xxx.xxx
ERROR 1130 (HY000): Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server
In my.cnf I have the below
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
I also ran the below...
'UPDATE mysql.user SET Password = PASSWORD('test101') WHERE User = 'root';
FLUSH PRIVILEGES;
I can access on the host machine using mysql -u root -ptest101 but not using mysql -u root -ptest101 -h xxx.xxx.xxx.xxx
Wow...why is this happening? I am n ubuntj 12.04
mysql> SELECT host FROM mysql.user WHERE User = 'root';
+---------------------------------------------+
| host |
+---------------------------------------------+
| % |
| 127.0.0.1 |
| ::1 | |
| localhost |
+---------------------------------------------+
5 rows in set (0.00 sec)
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>
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;
For those who are able to access cpanel, there is a simpler way getting around it.
log in cpanel => "Remote MySQL" under DATABASES section:
Add the IPs / hostname which you are accessing from
done!!!
Go to PhpMyAdmin, click on desired database, go to Privilages tab and create new user "remote", and give him all privilages and in host field set "Any host" option(%).
there an easy way to fix this error
just replace the files on the folder : C:\xampp\mysql\data\mysql
with the files on : C:\xampp\mysql\backup\mysql
$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';
Source: Stackoverflow.com