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 127.0.0.1. We do that by typing in:
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:
That line may be commented (with a # in the beginning) or may have localhost or 127.0.0.1 as bind-address. We do not want that. We want the IP of our dedicated server. Lets say it is 18.104.22.168, so the new line should look like:
bind-address = 22.214.171.124
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.
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:
That should say:
Now, we create a new user and allow it to connect to the database:
GRANT CREATE,INSERT,DELETE,UPDATE,SELECT ON newdb.* TO email@example.com;
where 126.96.36.199 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:
and find out the IP in terminal. It should give you something lie:
PING mydomain.com (188.8.131.52) 56(84) bytes of data.
Now, we set password for the user newuser. We do that by typing in:
SET password FOR firstname.lastname@example.org = password('mypassword');
Finally, we cleanup and exit mysql:
FLUSH privileges; exit;
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):