When moving a site from one server/host to another it’s often necessary to have both servers/hosts share a third server’s database. This is necessary because as the DNS change propagates around the world some user’s ISP may send them to your old server and some may arrive at the new server.

If you had two independent servers running two instances of MySQL the records in the database won’t match and some users will never be on the new server. This is especially a problem if you’re running a subscription based website. You merchant may have already processed their account information and confirmed them on your site, but wait, were they confirmed on the old server or the new one?

Using a shared database allows users who sign up to your site through the old server to be added same database as users who sign up through the new server. But MySQL doesn’t allow this to happen by default. It thinks that any incoming request from an outside connection is a security threat.

The internet is usually a great place to find tutorials, especially relating to programming. However, configuring a MySQL database for remote connections seems to be a poorly documented thing online.

With that in mind, here is a short explanation of how to configure MySQL to allow remote connections.

Note: this will not work on shared hosting.

First login to your server with SSH.

ssh username@domainname.com

After logging you you will need to again login, but this time as the root user of MySQL. This user has permission to change the configuration of MySQL and thus we need to login as them.

When you installed MySQL you entered a password for the MySQL root user. If you don’t know the password talk to the person who installed your server, they should know it.

To login to MySQL as the root user use this command.

mysql -u root -p

The -u is the user flag. The -p is the password flag and means MySQL will ask you for a password before logging in. Enter your password to continue.

Once you’re logged into MySQL you need to create a new MySQL user and grant access to that user. This user will be the user you will use to access the MySQL server remotely.

GRANT ALL PRIVILEGES ON *.* to 'username'@'%' IDENTIFIED by 'mySeecretPassword';

Of course replace 'username' and 'mySeecretPassword' with the username and password you want to use. Make sure it’s secure, remember, this will contain all of your shared databases and if someone cracked the password they could really mess things up.

Notice the '%' after the @ symbol? This is a wildcard character and means “all databases”. I do this because this MySQL server will be dedicated to being shared, so it makes since to allow access to all databases.

After you have created that user and granted it access to all the servers we need to allow the incoming connection on any IP address.

This is the section I was most confused with when I first did it.

Use your favorite text editor and open your my.cnf file. On Ubuntu and some other systems it’s located at /etc/mysql/my.cnf. It might also be located at /etc/my.cnf.

You will need root permission to edit this file. On my system I used vim to edit the file, but this is not installed by default.

sudo vim /etc/mysql/my.cnf

You will need to find this section.

#bind-address = 127.0.0.1

And change it to this.

bind-address = *

This will allow any incoming IP address to connect to MySQL. If they use the user we created to connect they will have access to all the databases.

To have this configuration take effect you can restart MySQL, but I like restarting the entire server, after all, it might be the last time this database server will be restarted for the next several years.

sudo shutdown -r now

After the restart you should be able to access the remote MySQL from any other computer by using the user and password that you created.