LAMP Tutorials (2/6): Move MySQL to a Separate Cloud Database Server

This post is the second chapter of a LAMP Tutorial series in which we will show you how to set up a fully redundant, high-availability LAMP stack in the cloud with ElasticHosts. Find the other parts here:

Move MySQL to a Separate Cloud Database Server



In the last tutorial, we Set Up a LAMP Stack on a Cloud Server. Next, to help our site deal with higher traffic, we’re going to move MySQL on to a separate server. Of course, a site as simple as the one we’ve built for these tutorials would have no need for a separate server – this is for high-performance applications. But if your site is high-demand, you’ll improve performance by giving MySQL a separate machine to work on.

Step 1: Prepare a new MySQL server

Follow the steps in our previous tutorial to set up a new Ubuntu 10.04 server ready to run MySQL. Following the convention that our web servers are named after birds, and our database servers after other animals, we’ll call this server beagle.

Two ElasticHosts Cloud Servers

Once you’ve booted the new server and set a root password, ssh in. You don’t need to install Apache and PHP on this machine, but you should install MySQL:

beagle$ apt-get install mysql-server mysql-client  

We also need to check rsync is installed (on both machines) for the next step:

puffin$ apt-get install rsync  

Step 2: Set up a private VLAN

We won’t cover this in detail here because there is a separate tutorial describing how to Set Up a VLAN on ElasticHosts. Simply follow the steps there to set up a private VLAN between puffin and beagle. For this tutorial, we’ll give puffin the IP address 10.0.0.1 and beagle the IP address 10.0.0.2.

Step 3: Dump the database and import

We’re now going to dump the database we created on puffin, copy it over to beagle, and import it on beagle.

NB: if we were really doing this on a high-performance site, we would probably want to lock the database on puffin before dumping, to make sure nothing could be written to it half-way through the process. But for the purposes of our demo, we won’t do this.

puffin$ mysqldump -u root -p -c prices > prices.dump  
puffin$ rsync prices.dump root@10.0.0.2:~  

On beagle, set up a new MySQL database called prices:

beagle$ mysql -u root -p  
> CREATE DATABASE prices;
> EXIT;

And then import the database dump:

beagle$ mysql -u root -p -D prices < prices.dump  

Check it has copied okay:

beagle$ mysql -u root -p  
> USE prices;
> SELECT * FROM metals;
> EXIT;

At this point, we may want to add some information to our new database:

beagle$ mysql -u root -p  
> USE prices;
> INSERT INTO metals (name, price_usd_lb) VALUES ('nickel', 8.9);
> EXIT;

Step 4: Set up networking on MySQL

The next thing to do is grant the correct permissions on the new MySQL database on beagle:

beagle$ mysql -u root -p  
> GRANT ALL PRIVILEGES ON prices.* to elastic1@10.0.0.1 IDENTIFIED BY 'oag4Chai';
> FLUSH PRIVILEGES;
> EXIT;

We also need to edit the MySQL configuration on beagle to allow connections from other IP addresses:

beagle$ vi /etc/mysql/my.cnf  

And edit bind-address as follows:

bind-address = 10.0.0.2  

Then restart MySQL:

beagle$ /etc/init.d/mysql restart  

Step 5: Change our site to connect to the external database

Now we need to change our web application to connect to the external database. On puffin, open index.php:

puffin$ vi /var/www/index.php  

And edit the first line, changing it from localhost to connect to our database over the VLAN:

$con = mysql_connect('10.0.0.2:3306','elastic1','oag4Chai')
or die('Could not connect to the server!');  

Close the file and restart Apache:

puffin$ /etc/init.d/apache2 restart  

And let’s return to our site and check it works:Lamp Stack Running Separately

We know it’s connecting to the remote database because it shows the new entry we made for nickel. Just to avoid confusion in the future, let’s delete our database on puffin:

puffin$ mysql -u root -p  
> DROP DATABASE prices;
> EXIT;


You're finished



In the next tutorial, we’ll look at Replicating MySQL Across Two Servers. This not only increases your database’s capacity even further, but also provides redundancy in the event of a failure.