How To Set Up Master Slave Replication in MySQL

How To Set Up Master Slave Replication in MySQL

1. Create new user for the purpose of replication which will be used by client to connect master.

mysql > CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Grant replication privileges to newly created user

mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

2. Configure Master Server. On master server open my.ini file available in MySql program files folder.
Add following variables to that file. (For this you may need administrator privileges)

Variable name	Value	Comment
tmpdir	Temp folder path	Value should be placed in double quotes
server_id	1	Value should be unique among the master and client db server.
log-bin	Log file path	File should have extension “.log”. Place in double quotes
log-error	Error log file path	File should have extension “.log”. Place in double quotes
log-bin-index	Index file path	File should have extension “.index”. Place in double quotes

3. Configure Slave (client) server.On client server open my.ini file available in Mysql program files folder.

Add following variables to that file. (For this you may need administrator privileges)

Variable name	Value	Comment
server_id	2	Value should be unique among the master and client db server.
master-host	IP address of master db sever
master-port	Port no of master db server.
master-user	Username of master db server
master-password	Password of master db server
log-bin	Log file path	Same as in master.
log-bin-index	Index file path	Same as in master.
log-error	Error log file path	Same as in master.
relay-log	Relay log file path	Same as in master.
relay-log-info-file	Info file path	Same as in master.
relay-log-index	Index file path	Same as in master.

4. Export / Import existing MySql database.

This step is not required if you are setting up replication master-client for new database.

Restart master and client server services respectively.

Create a database dump from master server of your choice database.

On client MySql server, enter command

mysql > STOP SLAVE

Restore same dump in the client server where you want to replicate existing database.

Note: For the above process, stop all the writable queries on the master server so that no further updates are possible. This is to ensure that both client and master have same database with same table structure and with same data.

If database is particularly very large then just create a zip file of the database you want from data folder in MySql server and place it in same data folder of client server and unzip it.

5. Start Replication

On the master database server restart the MySQL service.
On the slave database server restart the MySQL service.
On the client MySQL server, enter command :
mysql > START SLAVE
On the slave database server issue the following command from the MySQL command line client or regular command Window :