Click here to Skip to main content
Click here to Skip to main content

MySQL Database Replication Procedure

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

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:
  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
    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)
  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:
  13. FLUSH PRIVILEGES;
    USE databasename;
    FLUSH TABLES WITH READ LOCK;
    UNLOCK TABLES;
  14. Run the following command:
  15. SHOW MASTER STATUS;

    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:
  23. STOP 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:
  25. 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.

  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:
  28. START SLAVE;
  29. Run the following command to check the status of the slave:
  30. SHOW SLAVE STATUS\G

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

License

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

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberhrachk12-Mar-14 3:41 
GeneralMy vote of 5 PinmemberThe Sql Coder26-Oct-12 1:32 
GeneralMy vote of 5 PinmemberThe Sql Coder26-Oct-12 1:29 
Good one to start with.
GeneralMy vote of 5 PinmemberThe Sql Coder22-Oct-12 19:58 
SuggestionMySql Database Replication PinmemberAadhar Joshi4-Sep-12 18:34 

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 | Mobile
Web04 | 2.8.140721.1 | Last Updated 4 Sep 2012
Article Copyright 2012 by Aadhar Joshi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid