One week in and I’m just beginning to get my head around the EC2 way of doing things which is different than what I am used to doing in meatspace with a pile of hardware as well as being worlds away from my Microsoft background. So after tackling a couple of minor projects like Monit + Mongrel, and playing with Memcached, I decided tackle a simple replication setup to get my feet wet in preparation for building a cluster.

After hammering my head against the wall for a day or so things clicked after realizing that this exercise is so much easier if I use the private DNS for plumbing rather than laying ssh tunnels all over the place, which by the way sort-of-kind-of-not-really worked.

Prologue – Create Your World

Look, it is a well known fact that the 9 or so GB that Amazon gives you on the main partition isn’t going to cut it so you have to make some changes to your MySQL environment. The best place for bins and logs are on /mnt. So, since I am epically lazy, I created a mysql directory in /mnt, passed ownership to mysql:mysql, backed up /var/lib/mysql, created a symbolic link to mysql, and then cp -a the contents of the old /var/lib/mysql into /mnt/mysql. Why the backup? Well, if your MySQL instance ever goes down it’ll take the contents of /mnt with it and having a copy of it helps with a speedy recovery.

Step One – Be The Master

Add or edit the following line in /etc/mysql/my.cnf
log=/mnt/mysql/log/mysql.log
server-id=1
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=babygotdb
log=/var/lib/mysql/mysql.log (this part is important for seeing whether or not data is replicating)

Restart MySQL then launch the client and issue the following command to add a user with replication privileges:

GRANT REPLICATION SLAVE ON *.* TO ‘replicant’@'%’
IDENTIFIED BY ‘replicantsarepeopletoo’;

FLUSH PRIVILEGES;

Now use the database so we can finish things off…

USE babygotdb;

We want to clear any read locks before we move on so,

FLUSH TABLES WITH READ LOCK;

Now we want to check if what we set up looks like right:

SHOW MASTER STATUS;

This will tell you about the database File, Position, Binlog_Do_DB, and Binlog_Ignore_DB. You will need to know both the file and the position. In this case mine has the following:

File – mysql-bin.000023
Position – 3617723
Binlog_Do_DB – babygotdb
Binlog_Ignore_DB -

You should see something similar to that though it will be in a different format.

quit;

Now is when we dump the database and get it ready to ship over to the slave server.

$ mysqldump -B babygotdb | gzip > babygotdb.sql.gz

Then just scp it to the slave.

Step Two – Get To Know Your Slave

Like on the master we need to make some changes to /etc/mysql/my.cnf

server-id=2
master-host=domU-12-34-56-78-90-1A.usma1.compute.amazonaws.com
master-user=replicant
master-password=replicantsarepeopletoo
master-connect-retry=60
replicate-do-db=babygotdb
log=/var/lib/mysql/mysql.log

Restart mySQL and then launch the client and we have only a little more work to do until we are done.

Gunzip your SQL dump then jump into the mysql client as we need to create our replicated database:

CREATE DATABASE babygotdb;

Then we need to suck in what we dumped on the master:

SOURCE babygotdb.sql;

Stop the slave from running:

SLAVE STOP;

And get our environment ready for replication (this is one long command with each component marked out with commas)

CHANGE MASTER TO
MASTER_HOST=’domU-12-34-56-78-90-1A.usma1.compute.amazonaws.com’,
MASTER_USER=’replicant’,
MASTER_PASSWORD=’replicantsarepeopletoo’,
MASTER_LOG_FILE=’mysql-bin.000023′, MASTER_LOG_POS=3617723;

Now start the slave back up…

SLAVE START;

Watch the replication happen in real time:

$ tail -f /var/lib/mysql/mysql.log

If all is working properly you should be seeing transactions flying by on both as changes are made in the master database.

Gratefully cribbed from Phillip Pearson over at Second p0st and the HowTo Forge write up by falko.

My long term goal is to create images of slaves, masters, data nodes, and management nodes so that deployment will require less hammering on the forge and more tying up loose ends.