Moving MySQL to a Separate Cloud Database Server
In the last tutorial, we set up a LAMP stack on Crosspeer. 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 tutorial on Setting up a LAMP Stack 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 animals, we'll call this server beagle.
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.
In the last tutorial, we set up a LAMP stack on Crosspeer. 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 tutorial on Setting up a LAMP Stack 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 animals, we'll call this server beagle.
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:
seagull$ 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 private VLAN on Crosspeer.
Simply follow the steps there to set up a private VLAN between seagull and beagle. For this tutorial, we'll give seagull 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 seagull, 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 seagull before dumping, to make sure nothing could be written to it half-way through the process. But for the purposes of this example, we won't do this.
We won't cover this in detail here, because there is a separate tutorial describing how to set up a private VLAN on Crosspeer.
Simply follow the steps there to set up a private VLAN between seagull and beagle. For this tutorial, we'll give seagull 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 seagull, 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 seagull before dumping, to make sure nothing could be written to it half-way through the process. But for the purposes of this example, we won't do this.
seagull$ mysqldump -u root -p<mysql password> -c prices > prices.dump
seagull$ rsync prices.dump root@10.0.0.2:~
seagull$ 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;
> CREATE DATABASE prices;
> EXIT;
And then import the database dump:
beagle$ mysql -u root -p<mysql password> -D prices < prices.dump
Check it has copied okay:
beagle$ mysql -u root -p
> USE prices;
> SELECT * FROM metals;
> EXIT;
> 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;
> 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:
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;
> 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 seagull, open index.php:
Now we need to change our web application to connect to the external database. On seagull, open index.php:
seagull$ 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!');
or die('Could not connect to the server!');
Close the file and restart Apache:
seagull$ /etc/init.d/apache2 restart
And let's return to our site and check it works:
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 seagull:
Just to avoid confusion in the future, let's delete our database on seagull:
seagull$ mysql -u root -p
> DROP DATABASE prices;
> EXIT;
> DROP DATABASE prices;
> EXIT;
In the next tutorial, we'll look at replicating MySQL across two servers. This not only increases your database's capacity even further, it also provides redundancy in the event of failure.
