Remote access to MySQL

I'll preface by saying I think this is A Bad Idea. Be sure you read through the MySQL reference manual section on Server Administration and that you understand the risks involved prior to doing so!!

Also, look into using SSH for the MySQL connection. At the very least use the '-C' option when connecting from a remote address.

1) Make sure your iptables / firewall is allowing external access to TCP port 3306

2) Make sure your my.cnf [mysqld] section is using TCP (port 3306) instead of / along side a socket file.

3) Make sure your my.cnf is using bind-address to your external address

4) Create a user for an external connection, e.g.

CREATE USER external@'%' IDENTIFIED BY 'somepassword';

You can also specify a specific domain or IP addr you're connecting from, e.g.

CREATE USER external@'otherdomain.com' IDENTIFIED BY 'somepassword';

5) Make sure you choke down the GRANT for this user!!

GRANT SELECT,INSERT,DELETE ON yourdb.* TO external@'%';

I think that's about it. Restart mysqld (/etc/init.d/mysql restart).

If I absolutely had to do something like this, I would definitely tunnel port 3306 over SSH, rather than open mysql to the outside world directly.

Just make sure you're bound to 127.0.0.1, and that your connection script retries on disconnect, and that it's part of startup.

http://library.linode.com/databases/mys … ssh-tunnel">http://library.linode.com/databases/mysql/mysql-ssh-tunnel

Guide from the libary

Most snazzy clients support SSH to the database server, and leaving MySQL listening on only 127.0.0.1. I know Navicat does.

If you're working with a program that doesn't, an SSH tunnel is the way to go.