This post originated from an RSS feed registered with Python Buzz by Phillip Pearson. Original Post: MySQL replication on EC2 Feed Title: Second p0st Feed URL: http://www.myelin.co.nz/post/rss.xml Feed Description: Tech notes and web hackery from the guy that brought you bzero, Python Community Server, the Blogging Ecosystem and the Internet Topic Exchange Latest Python Buzz Posts Latest Python Buzz Posts by Phillip Pearson Latest Posts From Second p0st

OK, here's the cheat sheet for getting MySQL replication going for a PeopleAggregator install on Amazon EC2. (Based on this Howto forge HOWTO.)

Note that PeopleAggregator will not actually use the slave database yet, so this blog post will be totally useless to anyone actually running PeopleAggregator - don't try this at home (well, on your PA server)! I'm just writing it to have a record of what I did so I can script it later on.

On the master: i-f7a5419e domU-12-31-33-00-01-5E.usma1.compute.amazonaws.com

Edit /etc/my.cnf and add to the [mysqld] part:

log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=peopleaggregator
server-id=1

Now make the log dir and restart:

mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql
service mysqld restart

mysql
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%'
IDENTIFIED BY 'aslkjdhf9283764kjsadh';
FLUSH PRIVILEGES;

USE peopleaggregator;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000002 |      192 | peopleaggregator |                  |
+------------------+----------+------------------+------------------+

Now hit Ctrl-Z and dump the database:

mysqldump -B peopleaggregator | gzip > pa.sql.gz

Now type fg and exit the mysql client session (or your master DB will remain read-locked).

Now on the client - copy over the DB dump

Now on the slave: i-aea541c7 domU-12-31-33-00-01-41.usma1.compute.amazonaws.com

Edit /etc/my.cnf and add to the [mysqld] part:

server-id=2
master-host=domU-12-31-33-00-01-5E.usma1.compute.amazonaws.com
master-user=slave_user
master-password=aslkjdhf9283764kjsadh
master-connect-retry=60
replicate-do-db=peopleaggregator

And restart MySQL:

Now load the DB dump and update the log position:

mysql
source pa.sql;
SLAVE STOP;

MASTER_USER='slave_user', MASTER_PASSWORD='aslkjdhf9283764kjsadh',
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=192;
SLAVE START;

Now check /var/log/mysqld.log on both servers to make sure replication is running OK, and try changing something on the master then checking the slave to make sure the query has been replicated properly. If so, it's all going!

(Once again, though, don't do this on your PeopleAggregator install, because PA doesn't know about replication and will still only use your single master server. I'll post here when it's capable of scaling horizontally using DB replication.)

Comment