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 :
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.
Welcome to my blog 
This blog is very helpful. Lots of thanks to you Udhyan
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
UPDATE:Figured it out.. I had to set the global setting for serverid:
e.g. mysql> SET GLOBAL server_id=20;
Slave starts now 🙂
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’;
when server A change server B , server B is master of server A so, server B write changes to server A again?
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
Why visitors still use to read news papers when in this technological globe everything
is accessible on net?
— 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
*****************************************************************