Wednesday 23 December 2009

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

2 comments:

  1. Thanks, I haven't test this, but it seems very useful for newbies like me. A little explanation about the commands in place would be nice thought but, I will not complain there. :)

    Question: Can we have Master/Slave relation on different schemas databases?

    ReplyDelete
  2. I don't mind giving explaination, you can ask which bit needs explaination?

    I'll like to answer your question with an example.

    For example:
    Server A (master), has three databases d1, d2, d3. The requirement is to replicate only d1. in this case. I'll update Salve's my.cnf file

    [mysqld]
    ..
    replicate-ignore-db=d2
    replicate-ignore-db=d3

    if you want to replicate d1 database on master server to different schema on slave e.g d3. I'm afraid this is not possible

    ReplyDelete