MySQL Database Replication Procedure

Introduction

This is a procedure to perform database replication on MySQL with two or more databases.

Background

This database replication procedure is to perform replication between various databases of MySQL. Replication is basically performed where the user needs databases synchronized (all data must have the same information).

Steps to Perform Database Replication

To establish database replication with multiple masters, we have to establish Circular Database Replication. In this method, all the servers will work as MASTER as well as SLAVE. The procedure is as follows:

1. First of all, install MySQL, same version in all servers.
2. To create a Master Slave configuration, first we will create a Master on all the servers because to create a Slave of any server, we require the Master server's Master information (log file name and position).
3. Open my.ini or my.cnf with Notepad from C:/Program Files/MySQL/MySQL Server 5.x/ (this path will be the MySQL installation path). Here, x is your MySQL version.
4. Add the following lines after:

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

(Path will be MySQL installation path.)

log-bin = C:/Program Files/MySQL/MySQL Server 5.5/mysql-bin.log
log-slave-updates
binlog-do-db=databasename
replicate-do-db = databasename
server-id = n
replicate-same-server-id = 0
auto-increment-increment = N
auto-increment-offset = n

(where path must be your MySQL installation path)

n = Consecutive unique integer from 1,2,..n for all servers
N = total number of servers to replicate)

5. Go to Control Panel → Administrative Tools → Services.
6. Restart MySQL Service.
7. Go to Start → All Programs → MySQL → MySQL Server 5.x → MySQL 5.x Command Line Client.
8. Login with the password given at the time of creating the server instance.
9. Create user with replication rights which will be used as slave user from another server.
SQL

GRANT REPLICATION SLAVE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

Give some user name and password that will be used by the slave.

10. Execute the following commands:
SQL

FLUSH PRIVILEGES;
USE databasename;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;

11. Run the following command:
SQL

SHOW MASTER STATUS;

Note down the output (file and position).

12. Run EXIT to quit from the the MySQL client.
13. Follow steps 3 – 12 for all the servers.
14. Now to get the updates of the server, we will create a SLAVE of each server.
15. Start the MySQL Command Line Client from the given path.

Go to Start → All Programs → MySQL → MySQL Server 5.x → MySQL 5.x Command Line Client.

16. Login with the password given at the time of creating the server instance.
17. Run the following command to stop the slave:
SQL

STOP SLAVE;

18. Now, we set the MASTER of this server so that it will get the updates made in the MASTER server. To set the MASTER, run the following command:
SQL

CHANGE MASTER TO MASTER_HOST='Master_Server_IP',
MASTER_USER='Master_Server_username',
MASTER_PASSWORD='Master_Server_password',
MASTER_LOG_FILE='Master_Server_master_log_file_name',
MASTER_LOG_POS=Master_Server_master_log_position;

Where MASTER_HOST: IP address of the server from which we want to get the updates, MASTER_USER: user name of the Master Server User created with replication rights, MASTER_PASSWORD: password of the Master Server User created with replication rights, MASTER_LOG_FILE: log file name of the server, MASTER_LOG_POS: position of the log file.

You can get the last two parameter values by running the SHOW MASTER STATUS command on the master server.

19. Follow steps 15 – 18 for all servers.
20. After configuring MASTER and SLAVE on each server, run the following command to start the slave:
SQL

START SLAVE;

21. Run the following command to check the status of the slave:
SQL

SHOW SLAVE STATUS\G

The status of Slave_IO_State must be "waiting for the server to send an event".