Sunday, November 1, 2009

Database Replication in a nutshell

Database Replication allows is the making of an exact copy of one database into another in the same or a different server. This can be seen as a master-slave relationship wherein the slave copies the changes done into the master.

THE PROCESS:
All the statements that are to be run in a database are stored in a log file by the DB. The log file has a pointer that points to the last run command. At a certain lag from the main pointer a pointer to the replicated DB exits. Thus each command in the log passes through both the pointers thus updating both the databases.

HOW TO:Replication can be set up by granting the permission and setting the slave. This can be done as below:

Ensure that the master db uses a binary update log and has a server id as 1. To do this db config file (my.cnf) should have the following lines
log-bin
server-id=1

To the master database grant access to replication
GRANT REPLICATION SLAVE ON *.* TO user IDENTIFIED BY password

In the slave db config file set the master path, user and password to access and you are done.
Ofcourse there would be lot more configurations to be done based on specific cases.

No comments :