MySQL Replication Setup

Notes on how to set up MySQL replication.

Firstly, stop web application nodes and redirect users to "please come back later" web page.

Secondly, on DB node (PCUDSSX1501), switch on log-bin in /etc/my.cnf if not done yet, then perform in one DB client connection:

mysql> TRUNCATE session;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1061 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Note log file and position which will be used later down to start the replication.

In another screen window on PCUDSSX1501, take DB backup and restart DB server via:

mysqladmin -u root -p shutdown
sudo rm /opt/mysql-data/mysqld-slow.log
sudo cp -a /opt/mysql-data /opt/mysql-data-20120418
sudo /etc/init.d/mysqld start

Note that the copy may take about ~25 minutes, since DB size is close to ~40 GB.

Now the web application can be restarted back to production.

Thirdly, set up new replication user client on DB master PCUDSSX1501:

mysql> CREATE USER repl@137.138.4.157 IDENTIFIED BY 'pass123';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@137.138.4.157;

Copy over DB master snapshot into DB slave node PCUDSSW1513:

PCUDSSX1501> sudo rsync -rlptDvz -e ssh /opt/mysql-data-20120418/ root@pcudssw1513:/opt/mysql-data-from-pcudssx1501-20120418
PCUDSSX1501> sudo rsync -rlptDvz -e ssh /opt/mysql-data-20120418/ root@pcudssw1513:/opt/mysql-data-from-pcudssx1501-20120418 # once more to test
PCUDSSX1501> sudo rm -rf /opt/mysql-data-20120418/ # free space

Install parts of DB master snapshot on DB slave node PCUDSSW1513:

sudo /etc/init.d/mysqld stop
sudo mv /opt/mysql-data-from-pcudssx1501-20120418/cdsweb /opt/mysql-data/
sudo cp -a /opt/mysql-data-from-pcudssx1501-20120418/ib* /opt/mysql-data/
sudo chown -R mysql.mysql /opt/mysql-data/ib*
sudo chown -R mysql.mysql /opt/mysql-data/cdsweb/
sudo /etc/init.d/mysqld start

Fourthly, set up and start replication on slave node PCUDSSW1513:

mysql> SHOW SLAVE STATUS\G;
mysql> CHANGE MASTER TO MASTER_HOST='137.138.198.204',MASTER_USER='repl',MASTER_PASSWORD='pass123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1061;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

Monitor progress watching how Seconds_Behind_Master decreases as the replication catches up. We are done.