[HOWTO] Configure remote connection to MySQL database server

I recently got a Ubuntu 8.04 Dedicated Server and wanted to move the database of one of my websites from my web host to the Dedicated Server while keeping the website in the web host. To do so, you must make sure you can connect to remote databases from your web host to the Dedicated Server (or VPS).

First, I exported the database from my web server into a .sql.gz file. You can choose .sql too. Then, I created a database (lets call it newdb) in the remote server using phpmyadmin (if you don’t have phpmyadmin, you can install it in your dedicated server) and imported the database.

Now we move on to the remote privilege part. Login to your Dedicated Server via ssh. We will need to edit my.cnf (the MySQL configuration file) to make the mysql server bind to the IP of the Dedicated Server, not localhost or We do that by typing in:

nano /etc/mysql/my.cnf

I use nano as my text editor, you may use vi or whatever text editor you want. Also, my.cnf is in /etc/mysql/my.cnf, yours might be /etc/my.cnf.
Now, scroll down until your find


Just a few lines below, you will find a line containing:

bind-address            =

That line may be commented (with a # in the beginning) or may have localhost or as bind-address. We do not want that. We want the IP of our dedicated server. Lets say it is, so the new line should look like:

bind-address            =

Save the file. If using nano, just press Ctrl+O and Enter. Press Ctrl+X to exit. If using vi, press Esc and type :w to save and :q to quit.
Restart the mysql server by using the following command.

/etc/init.d/mysql restart

Now, fire up mysql:

mysql -u username -p

Enter password when asked. The username must be of an administrative user.
Now, lets switch to the database we created earlier with phpmyadmin:

USE newdb

That should say:

Database changed

Now, we create a new user and allow it to connect to the database:


where is the IP of the web host. It can be found by pinging your domain. For example, if you have domain mydomain.com, you can simply do a:

ping mydomain.com

and find out the IP in terminal. It should give you something lie:

PING mydomain.com ( 56(84) bytes of data.

Now, we set password for the user newuser. We do that by typing in:

SET password FOR newuser@ = password('mypassword');

Finally, we cleanup and exit mysql:

FLUSH privileges;

Now, we are done. You can configure the website in the web host to connect to the database at your remote Dedicated Server with the following configuration (whatever applies to you):