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 