Click here to Skip to main content
11,708,721 members (75,186 online)
Click here to Skip to main content

MySQL Database Replication Procedure

, 4 Sep 2012 CPOL 10.7K 5
Rate this:
Please Sign up or sign in to vote.
Database replication procedure is to replicate data from different databases of MySQL.


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


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:
  5. 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
    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)
  6. Go to Control Panel → Administrative Tools → Services.
  7. Restart MySQL Service.
  8. Go to Start → All Programs → MySQL → MySQL Server 5.x → MySQL 5.x Command Line Client.
  9. Login with the password given at the time of creating the server instance.
  10. Create user with replication rights which will be used as slave user from another server.
  11. GRANT REPLICATION SLAVE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

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

  12. Execute the following commands:
    USE databasename;
  14. Run the following command:

    Note down the output (file and position).

  16. Run EXIT to quit from the the MySQL client.
  17. Follow steps 3 – 12 for all the servers.
  18. Now to get the updates of the server, we will create a SLAVE of each server.
  19. Start the MySQL Command Line Client from the given path.
  20. Go to Start → All Programs → MySQL → MySQL Server 5.x → MySQL 5.x Command Line Client.

  21. Login with the password given at the time of creating the server instance.
  22. Run the following command to stop the slave:
  24. 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:

    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.

  26. Follow steps 15 – 18 for all servers.
  27. After configuring MASTER and SLAVE on each server, run the following command to start the slave:
  29. Run the following command to check the status of the slave:

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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Aadhar Joshi
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 1 Pin
hrachk12-Mar-14 3:41
memberhrachk12-Mar-14 3:41 
GeneralMy vote of 5 Pin
The Sql Coder26-Oct-12 1:32
memberThe Sql Coder26-Oct-12 1:32 
GeneralMy vote of 5 Pin
The Sql Coder26-Oct-12 1:29
memberThe Sql Coder26-Oct-12 1:29 
GeneralMy vote of 5 Pin
The Sql Coder22-Oct-12 19:58
memberThe Sql Coder22-Oct-12 19:58 
SuggestionMySql Database Replication Pin
Aadhar Joshi4-Sep-12 18:34
memberAadhar Joshi4-Sep-12 18:34 
To perform MySql Database Replication please visit below link..
MySql Database Replication Procedure[^]

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 4 Sep 2012
Article Copyright 2012 by Aadhar Joshi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid