Remote connect, howto

Since someone is asking on LinkedIn, and I have done some tests on this subject before, digging my docs and found it, so might just post it here as well!  =)

———8<————–8<————-

Remote Connection

By default, MySQL does NOT setup remote connections from other host. localhost is being used for connecting to server on the same machine locally. You will have to setup networking yourself. This can be done in less than 10 minutes.   =)

At the remote side, log-in as root, run the following query…

root@localhost:[(none)]> select host, user, password from mysql.user;
+———–+——–+——————————————-+
| host      | user   | password                                  |
+———–+——–+——————————————-+
| localhost | root   | *CD57897962775126B3E595AF5AA2DAFCE7463C39 |
| localhost | keith  | *484C3C7404EEC699EF37C8D8AE510F883180810E |
+———–+——–+——————————————-+
2 rows in set (0.00 sec)

host column is telling mysqld that which hostname allows to connect from;
user column is telling mysqld that which user allows to connect from;
password column is the encrypted password generated by PASSWORD() function.

To setup connections from other hosts, use the following querys…

create user ‘<some_remoter>’@'<from_host>’ identified by ‘<remoter_pwd>’;

grant all on <some_db>.<some_table> to ‘<some_remoter>’@'<from_host>’;

flush privileges;

Or just simply replace <from_host> to % (as a wildcard), then this user can log-in from any host.

Now check mysql.user table again, you will this new line…

root@localhost:[(none)]> select host, user, password from mysql.user;

+———–+———+——————————————-+
| host      | user    | password                                  |
+———–+———+——————————————-+
| localhost | root    | *CD57897962775126B3E595AF5AA2DAFCE7463C39 |
| localhost | keith   | *484C3C7404EEC699EF37C8D8AE510F883180810E |
| %         | remoter | *003AB7BE68556F9FD080BB6386B2650877E76056 |
+———–+———+——————————————-+
3 rows in set (0.00 sec)

You  are all set! But don’t forget to test it from another machine though!!!

Connection string will be liked…

C:\Documents and Settings\KEITH>mysql -h <remote-machine> -u remoter -p

 

 

 

C:\Documents and Settings\KEITH>mysql -h localhost -u root -p

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s