Top Nav

Archive | Cloud

Sync MySQL Slave From Another Slave On Amazon AWS

I was looking for a procedure to setup a new MySQL replication slave on Amazon AWS without having to do a MySQL dump of the master. Ended up using a couple of AWS features to make this an easy process. Let’s assume that we have a master server (master01) and a slave (slave01). We want to create a new slave (slave02). We’ll do this without taking the master offline and without doing a MySQL dump. This is especially important with large databases.

A couple of notes before we start:

  • On AWS we put MySQL storage on it’s own EBS storage volume. We create a new empty volume, partition, add LVM and then format with xfs. This makes it easy to add additional storage in the future.
  • We prefer to use the innodb-file-per-table setting in my.cnf to force MySQL to use a separate file for each InnoDB table. This keeps all the files that belong to a database in a common folder instead of mixing multiple databases in a single file. Also in may help avoid having very large database files.
  • We’re assuming that you’ve already setup the slave02 EC2 instance. You might do this by cloning the server01 instance.

Now here’s my procedure:

1. Login to AWS and go to EC2 -> Volumes. Find the volume holding the MySQL data. If you’ve not already done so we would recommend adding a Name tag to the volume so that you can easily see which server/function it is associated with. For example you might name it “slave01-mysql”. Right click on the volume and select “Create Snapshot”. A dialog will open where you can name the snapshot … something like “slave01-mysql-snap-1” would be appropriate. Depending on the size of the volume, this snapshot will take some time to complete.

One of the greatest features of EBS snapshots is their incremental nature. Here’s how it’s described by Amazon:

Amazon EBS snapshots are incremental backups, meaning that only the blocks on the device that have changed since your last snapshot will be saved. If you have a device with 100 GBs of data, but only 5 GBs of data has changed since your last snapshot, only the 5 additional GBs of snapshot data will be stored back to Amazon S3.

So the first snapshot we take of a volume has to copy the entire volume but additional snapshots only copy changed blocks. We’re going to use this is next step. At this point we have a snapshot of the server01 slave but it is inconsistent and we don’t have the point-in-time log coordinates that we need to initialize a new slave.

2. Login to slave01, stop replication and apply a read lock as follows:

Make sure that the “flush tables …” completes before proceeding. It may take a few seconds or more if there are locked tables that have to be released before the lock can be completed. At this point the slave server is not fully functional. You’ll want to proceed quickly through the next steps.  Until the next step (3) is complete, do not close the terminal with the “flush tables …” as we need to keep the read lock active.

3. Open a second terminal session to slave01, record log coordinates and shutdown the mysql process as follows:

The slave status will look something like:


The columns that we need to record are:



Take note of these values for later use.

The slave01 database is now stopped and we have the log coordinates from right before the shutdown.

4. Next in AWS, start a new snapshot of the slave01-mysql volume. Name it slave01-mysql-snap-2 or something similar. This snapshot will run much faster since it is only copying blocks that changed since the first snapshot.

5. When the second snapshot is complete, start the mysql process on the slave01 server. Check replication and confirm that it comes back in sync with the master. The slave01 is now back to a functional status.

6. In AWS, right click on the slave01-mysql-snap-2 snapshot and select “Create volume from snapshot”. Name the new volume something like slave02-mysql.

Another great feature of EBS snapshots is “lazy loading” as described here:

New volumes created from existing Amazon S3 snapshots load lazily in the background. This means that once a volume is created from a snapshot, there is no need to wait for all of the data to transfer from Amazon S3 to your Amazon EBS volume before your attached instance can start accessing the volume and all of its data. If your instance accesses a piece of data which hasn’t yet been loaded, the volume will immediately download the requested data from Amazon S3, and then will continue loading the rest of the volume’s data in the background.

This means that we can proceed with the new slave setup without waiting for the new volume to be loaded … instead it will load as needed.

7. In AWS, right click on the newly created volume and attach it to the new slave02 instance.

8. Login to the slave02 instance, confirm that mysql is stopped and mount the new volume.

9. Edit /etc/my.conf and add “skip-slave-start” to the “[mysqld]” section.

10. In the mysql data directory (/var/lib/mysql by default), remove the following files:

  • relay logs  (mysqld-relay-bin.*)

11. Start mysql server

12. Set replication config:

13. Now start replication:

It may take a few minutes or more for replication on the new slave to catch up with the master. This is due to the “lazy load” on the volume created from the snapshot and the need to process the logs from the master.

Once the new slave catches up with the master then the task is complete and you have a new slave. This procedure can work with very large databases. There is no downtime on the master for this process. The existing slave is down for only a short period.


Secure Hosting On RackSpace Cloud

We’re going to build a highly secure hosting environment on RackSpace Cloud using Cloud Networks and the new Brocade Vyatta vRouter image. The diagrams shows the target configuration:


There are 4 networks:

Public – this is the RackSpace Public Cloud/Internet network. The vRouter connects via eth0 to this network with public address of

LocalPrivate – this is a private Cloud Network that connects the vRouter and the servers. This network uses the private subnet.

DBPrivate – this is a private Cloud Network that connects the web and database servers. This network uses the private subnet.

Service – this is RackSpaces multi-tenent management network. This network is not being used and is not shown in the diagram.

On these networks are three servers:

Brocade Vyatta vRouter – this device provides a router, firewall and VPN termination point

Web Server – provides hosting for the web application.

Database Server – provides hosting for MySQL database used by web application

The Web and Database servers are not directly connected to the Internet. Instead all traffic passes through the vRouter. The vRouter provides a stateful inspection firewall to control access. There is also a private network (DBPrivate) between the web and database server.

Part 1 – Create Networks & Servers

To get started, login to your RackSpace Cloud account.

1. Verify that your account is enable for Cloud Networks and Brocade Vyatta vRouter. Go to Servers and click the Add Server button. Look in the list of available images and find the Brocade Vyatta vRouter. If it is not listed then open a support ticket and request access. Look towards the bottom of the New Server form and find the Cloud Networks section. If you don’t see this section then open a support ticket requesting access to Cloud Networks. Once you have verified that both Cloud Networks and Brocade Vyatta vRouter are available on your account then you can proceed to the next step.

2. Create a new server using the Brocade Vyatta vRouter image. The minimum size for the server should be 1GB. You can resize up later on as needed. You can not resize down. In the Cloud Networks section at the bottom of the form, add a new network named “LocalPrivate”.

3. Create the new database server using the latest CentOS 6.x image with at least 1GB of memory. In the Cloud Networks section, create a new network named “DBPrivate”. Also add the database server to the previously created “LocalPrivate” network.

4. Create the new web server using the latest CentOS 6.x image with at least 1GB of memory. In the Cloud Networks section, add the web server to the previously created “DBPrivate” and the “LocalPrivate” networks.

Take note the root passwords and IP addresses assigned to each server as they are created.

Part 2 – Configure vRouter

Connect to the vRouter with SSH. The login will be username “vyatta” and the password set when the server was created.

Execute the following commands in order. Be careful to replace bracketted items with your actual configuration values. Most lines can be copied and pasted to the vRouter including the comments.


Part 3 – Configure VPN Client

Next you can configure a client workstation. Here’s the procedure to configure a Windows client to connect to the VPN:

These instructions include an optional step to enable split tunneling. You’ll most likely want to complete this step so that only traffic for the secure hosts is routed on the VPN.


Part 4 – Configure Web Server

Initially the web server will not have a viable default gateway so it will not be accessible from the Internet or the VPN. So you’ll need to SSH to the vRouter and then SSH again into the web server. Open an SSH terminal to the vRouter and login as user “vyatta”. Now ssh from here to the web server:

We need to set a default gateway pointing to the vRouter. So edit /etc/sysconfig/network-scripts-ifcfg-eth2 and add this line to the bottom:

If you addresses or interfaces are numbered differently then you’ll need to adjust accordingly. The key issue is that the webserver needs to point to the internal LocalPrivate interface on the vRouter for it’s default gateway in order to route to the Internet.


Part 5 – Configure Database Server

We need to set a default gateway pointing to the vRouter. So edit /etc/sysconfig/network-scripts-ifcfg-eth1 and add this line to the bottom:

If you addresses or interfaces are numbered differently then you’ll need to adjust accordingly. The key issue is that the database server needs to point to the internal LocalPrivate interface on the vRouter for it’s default gateway in order to route to the Internet.



  • For database access from the webserver, make sure you use the DBPrivate network. I like to add an entry to the /etc/hosts file on the webserver that maps a name to the correct address on the database server like this:




Windows L2TP/IPSec Client Config

Here’s the procedure to configure a Windows client to connect to a L2TP/IPsec VPN server. You’ll need to know:

VPN Server Address
IPSec Pre-shared Secret

To get started:

1. Open the “Start” menu, enter “setup a vpn” in the search box and hit Enter. The “Create a VPN connection” dialog will open. You can also get to this dialog by going to:

Start -> Control Panel -> Network and Sharing Center -> “Setup a new connection or network” -> “Connect to a workplace” -> Next -> “Use my Internet connection (VPN)”

Windows Set Up VPN

2. Enter the VPN Server Address in the “Internet Address” field. Enter a name for the VPN connection like “My VPN” in the “Destination Name” field. Click the “Don’t connect now…” checkbox. Click the Next button.

Win Type the internet address to connect to

3. Enter the username and password. Click the Create button. Click the Close button.

Win Username and Password

4. Open the network connections dialog by clicking the network icon among the status icons in the tool bar or by going to:

Start -> Control Panel -> Network and Sharing Center -> “Connect or disconnect”

You should see your newly created VPN connection listed by the name that you gave it.

Win Connect to a Network_0

Right click on the connection and select Properties. The Properties dialog will open.

Win Vyatta VPN Properties

4. Go to the Security tab and change VPN Type to “Layer 2 Tunneling Protocol with IPsec (L2TP/IPSec)”.

Win Vyatta VPN Security Tab

Click the “Advanced Settings” button. Select “Use preshared key for authentication” and enter your Pre-shared Secret. Click the OK button.

Win Vyatta Advanced Properites

5. OPTIONAL – If you want to allow split routing then select the Networking tab. Now select “Internet Protocol Version 4 (TCP/IPv4)” and click the Properties button. Click the Advanced button. Uncheck the “Use default gateway on remote network” checkbox and click OK. Click OK.

Win Split Tunneling

6. Click Ok to save modified properties.

Setup is now complete. You can start the connection from the network connections dialog which can be reach by clicking on the network status icon in the toolbar or by going to:

Start -> Control Panel -> Network and Sharing Center -> “Connect or disconnect”

Select the connection and then click on the Connect button.

Later you can disconnect by repeating the process and clicking the Disconnect button.


Brocade Vyatta vRouter on RackSpace Cloud

RackSpace has recently released the Brocade Vyatta vRouter image to general availability on Cloud Servers. In combination with Cloud Networks the Brocade image can be used to build complex firewalled environments including VPNs. Reliable Penguin participated in the Early Access beta test for this new product. Here are a few links to useful information about the vRouter.

RackSpace Vyatta Quick Start


Remote Access VPN

Masquerade Private Cloud Server (SNAT)

Quick Start (Brocade)

Firewall (Brocade)

Not all control panel actions work with the vRouter. For example you have to reboot from the CLI. See this article for details:

Vyatta Network Appliance: Supported Actions Through Control Panel





Upload to RS Cloud Files From Command Line

Great article here:

First step is to get the X-Auth-Token:

Then send the upload request: