Still remember I was toying with AWS? A while back, I tried database replication on AWS with the most common implementation, one database server maintains the master copy of the database and additional database servers maintain slave copies of the database. This divides the database reads from one or more slaves and database writes to master, which can results in a large performance advantage due to load sharing. The instructions of set up MySQL database replicaiton are below.

The Master
First we have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant).
/etc/mysql/my.cnf

#skip-networking
#bind-address = 127.0.0.1

Beside these, we also have to tell MySQL for which database it should write logs, which log file it should use. These logs are required by the slave to monitor the changes on the master.

/etc/mysql/my.cnf

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

Restart MySQL after the editing:

/etc/init.d/mysql restart

Now we log into the MySQL as root and create a user with replication privileges:

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

Again, in MySQL shell, we run the following query to obtain master status:

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

Write down the information as we need it for slave. Similar result will show something like below:

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.009 | 123      | sampledb     |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The Slave
On the slave we first login MySQL. We have to create the database and loaded the data from master:

CREATE DATABASE sampledb;
LOAD DATA FROM MASTER;

Then we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the which master database to watch of.

/etc/mysql/my.cnf

server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=password
 
master-connect-retry=60
replicate-do-db=sampledb

Then restart MySQL:

/etc/init.d/mysql restart

Login to MySQL again and perform following commands:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.009', MASTER_LOG_POS=123;
START SLAVE;