Master Slave Setup

Assumptions:

1. Master Host IP is: 192.168.16.41
2. Slave Host IP is: 192.168.16.52

--Preparing the Master:

1. Create Replication User on Master
a. Logon to Master database server and issue following command from MySQL prompt.
$ mysql –uroot –p
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@’192.168.16.52' IDENTIFIED BY 'slavepass';
mysql> flush privileges;
mysql> quit;
2. Update my.cnf file using your favourite editor (vi or nano).
3. Add following lines under [mysqld] section:

log-bin=mysql-bin
server-id=1

4. Restart Master database server
$ /etc/init.d/mysql restart

5. Get the master status information
a. Logon to MySQL and run following commands
$ mysql –uroot –p
mysql> FLUSH TABLES WITH READ LOCK;
b. Leave this session running until you have taken the data snapshot

6. Create a data snapshot [using mysqldump tool]
a. In another ssh session use mysqldump to create a snapshot of all the databases. For example:
$ mysqldump –uroot –p --all-databases --master-data=1 > fulldump.db

7. In the session/window where you acquired the read lock, release the lock:
mysql> UNLOCK TABLES;
8. Copy the backup file i.e. fulldump.db onto the replication database server] using scp/rsync:
$ scp fulldump.db root@192.168.16.52:

--Preparing the Slave:

1. Update my.cnf file using your favourite editor (vi or nano).
a) Add following lines under [mysqld] section

server-id=2
master-host=192.168.16.41
master-user=repl
master-password = slavepass
relay-log=localhost-relay-bin
relay-log-index=localhost-relay-bin.index

b) Restart database server
$ /etc/init.d/mysql restart
c) Restore backup from file
$ mysql –uroot –p < fulldump.db
2. Start Replication Slave
mysql> START SLAVE;
3. Monitor Replication Status
$ mysql> show slave status\G
4. The slave is catching up Master database if the Slave_IO_Running and Slave_SQL_Running values are both Yes