Ranthologia: Rants on Tech, the Net and Web Development

In the first post, I explained how you could set up the server

Now to configure the slave database server.

1. Create the database.

2. Import the dump of the database that you need to replicate to the slave database.

3. Open /etc/mysql/my.cnf using the preferred editor, and insert the following lines:

server-id=2
master-host=<ip or hostname of master db>
master-user=<username to handle replication>
master-password=<password of user>
master-connect-retry=60
replicate-do-db=yoursampledb
SHOW MASTER STATUS;

Restart the server.

4. Login to MySQL and issue the following command to stop the slave:

mysql -u root -p
Enter password:
SLAVE STOP;

5. Then type in the following command to finalize the changes:

CHANGE MASTER TO MASTER_HOST=”,MASTER_USER=”,
MASTER_PASSWORD=”,MASTER_LOG_FILE=”, MASTER_LOG_POS=
;

Where:
MASTER_HOST is the IP address or hostname of the master
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.

6. Start the slave by issuing the following command:

START SLAVE; quit;

The slave database should have the same contents as the master. Try to do updates
on the master and check if the slave gets affected. Try it!