Phillip Pearson - Second p0st

tech notes and web hackery from the guy that brought you bzero, python community server, the blogging ecosystem, the new zealand coffee review and the internet topic exchange

2006-9-26

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.)

... more like this: []

Amazon EC2: I'm in!

My EC2 account was approved today. Just having a quick play with the 'getting started' image. Observations:

- it's running under Xen, using kernel 2.6.16.

- single 2405.448 MHz CPU ("AMD Opteron(tm) Processor 250", 4812.03 BogoMIPS) per box, 1740944 kB (= 1700.14 MB = 1.66 GB) RAM

- single SCSI (SATA?) hard disk with two ext3 partitions and one swap partition: sda1 (/, 9.84 GB), sda2 (/mnt, 146.8 GB), and sda3 (swap, 896.0 MB).

- hdparm -t and hdparm -T results:

/dev/sda2: Timing buffered disk reads: 160 MB in 3.03 seconds = 52.88 MB/sec

/dev/sda2: Timing cached reads: 1692 MB in 2.00 seconds = 845.32 MB/sec

Installing PeopleAggregator

It looks like the 'getting started' image has Apache but no MySQL. There's an FC4 image that has both, though, so I'll try that out.

ec2-run-instances ami-69ae4b00 -k gsg-keypair

ec2-describe-instances

(repeat until it tells you the instance is alive)

ssh -i id_rsa-gsg-keypair root@hostname

rpm -import /usr/share/rhn/RPM-GPG-KEY

yum install subversion ImageMagick php-gd php-mysql php-xml

apachectl restart

cd /var/www

rm -rf html

svn checkout http://update.peopleaggregator.org/svn/release/pa .

ln -s web html

chmod a+w /var/www/web/config /var/www/log /var/www/networks /var/www/web/cache /var/www/web/files /var/www/web/sb-files

Now browse to http://hostname and follow the prompts, and finally:

mv /var/www/web/config/local_config.php /var/www/

Now click on 'click here', and it should be going!

For the next few hours (until I terminate the test instance) you'll be able to see my test install at http://domu-12-31-33-00-01-5e.usma1.compute.amazonaws.com/.

... more like this: []