Master to Master Replication of MySQL Server in Windows Server 2008

This blog provides step by step tutorial for setting up master to master replication of MySQL on Windows Server 208 R2 platform. I have implemented on my corporate network successfully. I have faced so many problem during deployment because it is quite hard to find proper implementation method specially for Windows Server 2008 R2. After it, I decided to write blog on it so that other system administrator like me may not face same kind of issues.

Through out this blog, I have used two machine named as Master 1 (192.168.1.2) and Master 2 (192.168.2.2) .

 Prerequisites:

  • Windows Server 2008 R2 with proper network management
  • Both machines are installed properly with MySQL server 5.5
  • Open port no 3306 on both machines.

1. For replication, first you have to make some changes in “my.ini” file under [mysqld] of both servers. It should be like this:

#For master 1 configuration id
server-id=1
auto-increment-increment = 2
auto-increment-offset = 1
log-bin=mysql-bin
bind-address= 0.0.0.0

#For master 2 configuration id
server-id=2
auto-increment-increment = 2
auto-increment-offset = 2
log-bin=mysql-bin
bind-address=0.0.0.0

Note:

  • You have to run “my.ini” in administrator mode to make changes on it.
  • Remember to disable skip-networking on .ini file as “#skip-networking

2.  Now move yourself to MySQL from command prompt with following command:

C:\Program Files\MySQL\MySQL Server 5.5>mysql -u root -p

Here you need to change your master setting  and need to create a user which will have replication rights:

Master 1:

mysql>create user ‘replication’@192.168.1.2 identified by ‘slave’;

(Here slave is your password of user replication and remember to use shorten-length password)

mysql>grant replication slave on *.* to ‘replication’@192.168.1.2;

Master 2:

mysql>create user ‘replication’@192.168.2.2 identified by ‘slave2’; (Remember to use different password)

mysql>grant replication slave on *.* to ‘replication’@192.168.2.2;

3. Before changing master host, please note following things on each of your master.

Master 1:

mysql> show master status;
+——————+———-+————–+————————————–+
| File                                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+—————————————+
| mysql-bin.000046 |     5145 |                 |                 |
+——————+———-+————–+—————————————+
1 row in set (0.00 sec)

Master 2:

mysql> show master status;
+——————+———-+————–+————————————–+
| File                                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+—————————————+
| mysql-bin.000019 |     2186 |                 |                 |
+——————+———-+————–+—————————————+
1 row in set (0.00 sec)

4. After collecting all the information, now its time to change master host in both of your server:

Master 1:

mysql> CHANGE MASTER TO

-> MASTER_HOST=’192.168.2.2′,

-> MASTER_PORT=3306,

-> MASTER_USER=’replication’,

-> MASTER_PASSWORD=’slave2′,

-> MASTER_LOG_FILE=’mysql-bin.000019′,

-> MASTER_LOG_POS= 2186;

Master 2:

mysql> CHANGE MASTER TO

-> MASTER_HOST=’192.168.1.2′,

-> MASTER_PORT=3306,

-> MASTER_USER=’replication’,

-> MASTER_PASSWORD=’slave’,

-> MASTER_LOG_FILE=’mysql-bin.000046 ‘,

-> MASTER_LOG_POS= 5145;
5. Now you are on the final step of your replication. It will be better if you  provide full access to you user replication on both servers  in order to replicate whole database. In Windows 2008 R2, it will be easy to confirm from your wizard of MYSQL as :

Grant all rights to user “Replication”

6.  Restart your slave status of both server as:

mysql> stop slave;

mysql> start slave;

6. Now its time, to make sure that replication is working in both directions

In master 1:

mysql> use test

Database changed

mysql> create table numbers (num1 int, num2 int);

Query OK, 0 rows affected (0.00 sec)

 mysql> insert into numbers values (1,10),(2,20),(3,30);

Query OK, 3 rows affected (0.00 sec)

In master 2:

mysql> use test

Database changed

mysql> select * from numbers;

+——+——+

| num1 | num2 |

+——+——+

|    2 |   20 |

|    3 |   30 |

|    1 |   10 |

+——+——+

3 rows in set (0.01 sec)

 mysql> insert into numbers values (4,40),(5,50),(6,60);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

Again, In master 1:

mysql> use test

Database changed

mysql> select * from numbers;

+——+——+

| num1 | num2 |

+——+——+

|    1 |   10 |

|    6 |   60 |

|    3 |   30 |

|    5 |   50 |

|    2 |   20 |

|    4 |   40 |

+——+——+

6 rows in set (0.02 sec)
Bravo !!!!, It shows that your database has been replicating all the changes that you have made on any of the server.

Please feel free to comment, discuss and ask any question related to it.

Thank you for reading my blog.

Regards,

Udhyan.

Advertisements

9 thoughts on “Master to Master Replication of MySQL Server in Windows Server 2008

  1. Hi.,

    Thanks for the information. It’s nice of you to take the time to blog about it. It’s very helpful.

    I can’t seem to get the slave started again. I get:
    “ERROR 1200 (HY000): The server is not configured as a slave; finx in config file or change with CHANGE MASTER TO”.

    I’ve double-checked the command line I used and it seems OK.

    Looking at the logs I seet this (which seems OK):
    2014-01-17 18:04:13, 552, Note, ‘CHANGE MASTER TO executed’. Previous state master_host=’10.200.1.25′, master_port= 3306, master_log_file=’VIRBHS215-bin.000001′, master_log_pos= 1043, master_bind=”. New state master_host=’10.200.1.25′, master_port= 3306, master_log_file=’VIRBHS215-bin.0 [truncated, 301 bytes total]

    Any ideas would be appreciated!

    Thanks,
    Paul

  2. Shouldn’t the IP address in the following be pointing to master 2?
    Master 1:
    mysql>create user ‘replication’@192.168.1.2 identified by ‘slave’;

  3. I am unable to do the replication.Below is the error mesaage
    Your help will be much appreciated

    Slave_IO_Running: Connecting
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 374
    Relay_Log_Space: 107
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 1045
    Last_IO_Error: error connecting to master ‘replication@192.168.
    .131:3306’ – retry-time: 60 retries: 86400

  4. — I’m leaving this here since I was unable to find anything on Master to Master Replication for Windows 2012. Thought this might benefit some people. Thanks!

    MASTER TO MASTER REPLICATION Windows Server 2012

    ——————————-
    Prerequisites:
    Windows Server 2012 with proper Network Management
    Both machines are installed properly with MySQL server 5.7
    Open port no 3306 on both machines
    ——————————–

    C:\ProgramData\MySQL\MySQL Server 5.7\my.ini (Master & Slave Servers)
    ——————————————
    1. For replication, first you have to make some changes in “my.ini” file under [mysqld] of both servers.
    It should be like this:
    ———–
    Server 1 (MASTER SERVER 1)
    —————————-
    [mysqld]

    #For master 1 configuration id
    server-id=1
    auto-increment-increment = 2
    auto-increment-offset = 1
    log-bin=mysql-bin
    bind-address= 0.0.0.0

    # skip-networking

    # General and Slow logging.
    log-output=FILE
    general-log=0
    general_log_file=”WIN-AAR5P961DT4.log”
    slow-query-log=1
    slow_query_log_file=”WIN-AAR5P961DT4-slow.log”
    long_query_time=10

    Note: There is a section of the my.ini file where it already gives the Server Id. Comment this out.
    # Server Id.
    #server-id=1
    ——————————-
    Server 2 (MASTER SERVER 2)

    [mysqld]

    #For master 2 configuration id
    server-id=2
    replicate-do-db=new_gtcuw
    auto-increment-increment = 2
    auto-increment-offset = 2
    log-bin=mysql-bin
    bind-address=0.0.0.0

    # skip-networking

    # General and Slow logging.
    log-output=FILE
    general-log=0
    general_log_file=”WIN-AAR5P961DT4.log”
    slow-query-log=1
    slow_query_log_file=”WIN-AAR5P961DT4-slow.log”
    long_query_time=10

    Note: There is a section of the my.ini file where it already gives the Server Id. Comment this out.
    # Server Id.
    #server-id=1
    ———————————-
    Master 1:
    mysql> GRANT REPLICATION SLAVE ON *.* to ‘replication’@’%’ IDENTIFIED BY ‘PASSWORD’;

    Master 2:
    mysql> GRANT REPLICATION SLAVE ON *.* to ‘replication’@’%’ IDENTIFIED BY ‘PASSWORD’;

    Next On both servers (MASTER1 & MASTER2)

    Open MySQL Workbench > Local Instance MySQL > Users and Privileges
    – Check the box for replication user
    – Click the tab for Administrative Roles
    – Make sure all check boxes are checked

    On Master 1:
    ==================================
    1) mysql> FLUSH TABLES WITH READ LOCK;
    2) mysql> SHOW MASTER STATUS;
    +——————+———-+————–+——————+—————
    —-+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_
    Set |
    +——————+———-+————–+——————+—————
    —-+
    | mysql-bin.000001 | 154 | | |
    |
    +——————+———-+————–+——————+—————
    —-+
    1 row in set (0.00 sec)
    ===================================

    **** Note > File: mysql-bin.000001 and Position: 154 ****

    3) Open MySQL Workbench > Local instance MySQL > DATA EXPORT > Object Selection
    – Check Database you want to export
    – Export to Self-Contained File
    – Check box ‘Create Dump in a Single Transaction (Self-contained file only)
    – Check box ‘Include Create Schema
    – Start Export
    – Copy Exported file to Slave Server (server2)

    Still on MASTER SERVER1:

    4) mysql> UNLOCK TABLES;

    ON MASTER 2 SERVER:
    1) mysql> STOP SLAVE;
    ————
    2) Open MySQL Workbench > Local instance MySQL > Data Import/Restore > Import from Disk
    – tick radial buttone ‘Import from Self-Contained File C:\Users\Administrators\Desktop\new_gtcuw8-50AM.sql
    – Default Target Schema: new_gtcuw
    – Start Import
    ————-
    3) mysql> RESET SLAVE;
    4) mysql> CHANGE MASTER TO MASTER_HOST = ‘204.62.150.104’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘PASSWORD’, MASTER_LOG_FILE = ‘mysql-bin.000001′, MASTER_LOG_POS = 154;
    5) mysql> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=154;
    6) START SLAVE:
    7) SHOW SLAVE STATUS\G
    ————————————————-
    ********************************1. row **************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 204.62.150.104
    Master_User: replication
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 154
    Relay_Log_File: WIN-AAR5P961DT4-relay-bin.000002
    Relay_log_Pos: 320
    Relay_Master_Log_File: mysql-bin.000001
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: new_gtcuw
    Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    *****************************************************************

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s