Allow users to connect to remote MySQL database

When you are working with MySQL, chances are that your client and server are on two different hosts.
In such a case, its natural to want to connect to a remote MySQL database.
Lets say your local host (or client) is 10.10.80.103 and your remote host (or server) is 10.10.80.102.
So when you try connecting from client to server, default behavior of MySQL is to deny access:


[root@10.10.80.103 /root]# mysql -u myuser -p -h10.10.80.102
Enter password: *****
ERROR 1045 (28000): Access denied for user 'myuser'@'redhat3.maprtech.com' (using password: YES)

In such a situation, to allow access from remote hosts, go to the server (in this case 10.10.80.102), and do this as the admin user (root):


[root@10.10.80.102 /root]# mysql -u root -p
Enter password:*****

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7982
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> grant all on *.* to myuser@'10.10.80.103' identified by 'mypass';
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


Then go back to the client side (here 10.10.80.103) and make a connection attempt to the server (here 10.10.80.102):


[root@10.10.80.103 /root]# mysql -h 10.10.80.102 -u myuser-p 
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8340
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>                          [[[ connection succeeds and you are in.]]]


Note: Ensure that the firewall settings are permissive on the server side or simply turn off firewall if you want (using 'service iptables stop' command).