How to Autoscale MySQL on Amazon EC2

Autoscaling your webserver tier is typically straightforward. Image your apache server with source code or without, then sync down files from S3 upon spinup. Roll that image into the autoscale configuration and you’re all set.


With the database tier though, things can be a bit tricky. The typical configuration we see is to have a single master database where your application writes. But scaling out or horizontally on Amazon EC2 should be as easy as adding more slaves, right? Why not automate that process?

Below we’ve set out to answer some of the questions you’re likely to face when setting up slaves against your master. We’ve included instructions on building an AMI that automatically spins up as a slave. Fancy!

1. How can I autoscale my database tier?
1. Build an auto-starting MySQL slave against your master.
2. Configure those to spinup. Amazon’s autoscaling loadbalancer is one option, another is to use a roll-your-own solution, monitoring thresholds on servers, and spinning up or dropping off slaves as necessary.
2. Does an AWS snapshot capture subvolume data or just the SIZE of the attached volume?

In fact, if you have an attached EBS volume and you create an new AMI off of that, you will capture the entire root volume, plus your attached volume data. In fact we find this a great way to create an auto-building slave in the cloud.

3. How do I freeze MySQL during AWS snapshot?

mysql> flush tables with read lock;mysql> system xfs_freeze -f /data

At this point you can use the Amazon web console, ylastic, or ec2-create-image API call to do so from the command line. When the server you are imaging off of above restarts – as it will do by default – it will start with /data partition unfrozen and mysql’s tables unlocked again. Voila!

If you’re not using xfs for your /data filesystem, you should be. It’s fast! The xfsprogs docs seem to indicate this may also work with foreign filesystems. Check the docs for details.

1. How do I build an AMI mysql slave that autoconnects to master?

Install mysql_serverid script below.

1. Configure mysql to use your /data EBS mount.
2. Set all your my.cnf settings including server_id
3. Configure the instance as a slave in the normal way.
4. When using GRANT to create the ‘rep’ user on master, specify the host with a subnet wildcard. For example ’10.20.%’. That will subsequently allow any 10.20.x.y servers to connect and replicate.
5. Point the slave at the master.
6. When all is running properly, edit the my.cnf file and remove server_id. Don’t restart mysql.
7. Freeze the filesystem as described above.
8. Use the Amazon console, ylastic or API call to create your new image.
9. Test it of course, to make sure it spins up, sets server_id and connects to master.
10. Make a change in the test schema, and verify that it propagates to all slaves.
2. How do I set server_id uniquely?

As you hopefully already know, in MySQL replication environment each node requires a unique server_id setting. In my Amazon Machine Images, I want the server to startup and if it doesn’t find the server_id in the /etc/my.cnf file, to add it there, correctly! Is that so much to ask?

Here’s what I did. Fire up your editor of choice and drop in this bit of code:

#!/bin/shif grep -q “server_id” /etc/my.cnf

then

: # do nothing – it’s already set

else

# extract numeric component from hostname – should be internet IP in Amazon environment

export server_id=`echo $HOSTNAME | sed ‘s/[^0-9]*//g’`

echo “server_id=$server_id” >> /etc/my.cnf

# restart mysql

/etc/init.d/mysql restart

fi

Save that snippet at /root/mysql_serverid. Also be sure to make it executable:

$ chmod +x /root/mysql_serverid

Then just append it to your /etc/rc.local file with an editor or echo:

$ echo "/root/mysql_serverid" >> /etc/rc.local

Assuming your my.cnf file does *NOT* contain the server_id setting when you re-image, then it’ll set this automagically each time you spinup a new server off of that AMI. Nice!

1. Can you easily slave off of a slave? How?

It’s not terribly different from slaving off of a normal master.

A.  First enable slave updates. The setting is not dynamic, so if you don’t already have it set, you’ll have to restart your slave.

log_slave_updates=true

          B. Get an initial snapshot of your slave data. You can do that the locking way:

mysql> flush tables with read lock;mysql> show master status\G;

mysql> system mysqldump -A > full_slave_dump.mysql

mysql> unlock tables;

You may also choose to use Percona’s excellent xtrabackup utility to create hotbackups without locking any tables. We are very lucky to have an open-source tool like this at our disposal. MySQL Enterprise Backup from Oracle Corp can also do this.

C.  On the slave, seed the database with your dump created above.

$ mysql < full_slave_dump.mysql

          D.  Now point your slave to the original slave.

mysql> change master to master_user='rep', master_password='rep', master_host='192.168.0.1', master_log_file='server-bin-log.000004', master_log_pos=399;mysql> start slave;

mysql> show slave status\G;

1. Slave master is set as an IP address. Is there another way?

It’s possible to use hostnames in MySQL replication, however it’s not recommended. Why? Because of the wacky world of DNS. Suffice it to say MySQL has to do a lot of work to resolve those names into IP addresses. A hickup in DNS can interrupt all MySQL services potentially as sessions will fail to authenticate. To avoid this problem do two things:
           A.  Set this parameter in my.cnf

skip_name_resolve = true

1. Remove entries in mysql.user table where hostname is not an IP address. Those entries will be invalid for authentication after setting the above parameter.

1. Doesn’t RDS take care of all of this for me?

RDS is Amazon’s Relational Database Service which is built on MySQL. Amazon’s RDS solution presents MySQL as a service which brings certain benefits to administrators and startups:

• Simpler administration. Nuts and bolts are handled for you.
• Push-button replication. No more struggling with the nuances and issues of MySQL’s replication management.

Simplicity of administration of course has it’s downsides. Depending on your environment, these may or may not be dealbreakers.

• No access to the slow query log.

This is huge. The single best tool for troubleshooting slow database response is this log file. Queries are a large part of keeping a relational database server healthy and happy, and without this facility, you are severely limited.

• Locked in downtime window

When you signup for RDS, you must define a thirty minute maintenance window. This is a weekly window during which your instance *COULD* be unavailable. When you host yourself, you may not require as much downtime at all, especially if you’re using master-master mysql and zero-downtime configuration.

• Can’t use Percona Server to host your MySQL data.

You won’t be able to do this in RDS. Percona server is a high performance distribution of MySQL which typically rolls in serious performance tweaks and updates before they make it to community addition. Well worth the effort to consider it.

• No access to filesystem, server metrics & command line.

Again for troubleshooting problems, these are crucial. Gathering data about what’s really happening on the server is how you begin to diagnose and troubleshoot a server stall or pileup.

• You are beholden to Amazon’s support services if things go awry.

That’s because you won’t have access to the raw iron to diagnose and troubleshoot things yourself. Want to call in an outside consultant to help you debug or troubleshoot? You’ll have your hands tied without access to the underlying server.

• You can’t replicate to a non-RDS database.

Have your own datacenter connected to Amazon via VPC? Want to replication to a cloud server? RDS won’t fit the bill. You’ll have to roll your own – as we’ve described above. And if you want to replicate to an alternate cloud provider, again RDS won’t work for you.

Related posts:

1. Deploying MySQL on Amazon EC2 – 8 Best Practices
2. Review: Host Your Web Site In The Cloud, Amazon Web Services Made Easy
3. 5 Ways to Boost MySQL Scalability
4. Top MySQL DBA interview questions (Part 2)
5. MySQL Cluster In The Cloud – Managers Guide