MySQL remote access is disabled by default, but with a few simple steps, you should be up and running with remote MySQL in just a few minutes. This is especially useful when working with multiple developers in an Agile project environment, so that you all use the same data set, and can focus on just writing the application.
Make note that I’m not focused on the “most secure” way for that you would probably want to do all of this through an ssh tunnel. However, this should be plenty secure for most developers, especially if you’re working with a dev server and not production which is what I’ve geared this post towards.
Login to SSH to edit remote MySQL config
First, we need to edit the mysql config file to accept and bind remote connections to your server. We do this by editing your my.conf file located on most unix systems at /etc/my.conf or /etc/mysql/my.conf. I’m going to hope and assume you know the basics to ssh into your remote server and vi or nano the conf file.
ssh email@example.com vi /etc/my.conf
Replace mysqld Defaults
You can either set this up as a new connection or override the default, in this case, I replaced the default connection with my own remote connection settings. Keep in mind if you want to be creative and bind to localhost without interfering with other settings or services you can bind to 0.0.0.0 or another alias.
[mysqld] bind-address = 255.112.324.12 port = 3306 user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English # skip-networking # skip-external-locking
After your done editing, just save and close the file and restart mysql services:
# Ubuntu service mysql restart # Other Unix /sbin/init.d/mysql restart # Test that your connection is allowed with telnet on your local machine: telnet 255.112.324.12 3306
Granting Remote Access to MySQL Users
Now we’ve created our remote config for MySQL, we have to grant access to this server to other machines.
mysql -uroot -pMyPass CREATE DATABASE mydb; # Grant permission to root from any host: GRANT ALL ON mydb.* TO root@'%' IDENTIFIED BY 'MyPASSWORD';
Open Up MySQL Remote Ports
Now that our user has been granted access from any host, all thats left is to make sure our OS will allow connections to the default MySQL port
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
And now we should be able to login to our server from our local machine:
mysql -h255.112.324.12 -uroot -pMyPASSWORD
Does this work for you? Comments or suggestions?