MySQL replication on EC2
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
scp -i id_rsa-gsg-keypair root@domU-12-31-33-00-01-5E.usma1.compute.amazonaws.com:pa.sql.gz .
scp -i id_rsa-gsg-keypair pa.sql.gz root@domU-12-31-33-00-01-41.usma1.compute.amazonaws.com:
Now on the slave: i-aea541c7 domU-12-31-33-00-01-41.usma1.compute.amazonaws.com
gunzip pa.sql.gz
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:
service mysqld restart
Now load the DB dump and update the log position:
mysql source pa.sql; SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='domU-12-31-33-00-01-5E.usma1.compute.amazonaws.com',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.)