MySQL master-master replication on Windows

This post serves as a how to on how to set up MySQL master-master replication. Since I’m doing it on Windows, I wanted to make it as easy as possible, so I will be using a free SQL client, namely SQLyog community edition, that can be downloaded from here. For the MySQL itself the official community edition installer was used downloadable from here. In the following example my two servers IPs will be: Server A192.168.0.184 and Server B192.168.0.188.

Do all of the steps for both servers, there are some differences between the two, that will be pointed out in red or blue.

Installing MySQL Server

Run the msi installer, accept the terms and click Next:

01

Select Server only and click Next:

02

Click Execute:

03

Click on Next:

04

Change the Config Type as needed, in my case I will set it to Dedicated Machine, leave everything else as is, and click Next:

05

Set a root password, try to not set a weak one like I did and click Next:

06

Leave everything as is on the Windows Service step and click Next:

07

Click on Execute:

08

Wait for it to finish than click Next:

09

And finally Finish:

10

Installing SQLyog

Run the installer downloaded earlier. Select whatever language you prefer and click OK:

11

Click Next:

12

Accept the agreement and click Next:

13

Leave everything as is and click Next:

14

Wait for the installer to finish, than click Next:

15

And finally Finish:

16

The first time SQLyog starts there will be no hosts set. Let’s set one up now by pressing New…:

17

Name the connection, I suggest it to be localhost and click OK:

18

Enter the root password, and click Test Connection:

19

If everything wen’t well you will get this message with the version number of your MySQL sever:

20

Click on Connect, than at the Save changes prompt click Yes:

21

Allow root access from local network

Note: this step is optional, but usually needed for other reasons.

Click on the red shirted person on the top button bar:

22

The User Manager screen will pop up. Create a new user by clicking Add New User:

23

Enter root as Username, your local subnet as Host (in my case 192.168.0.%) and a Password twice and click Create:

24

Select ALL the privileges and click Save Changes:

25

It should look like this:

26

Creating the replica user

While still in SQLyog’s User Manager screen click Add New User once more and enter replicator as Username, % as Host and set a password, than click Create:

27

Give the replicator user Replication Slave rights and nothing else:

28

Editing the config files

We need to edit the my.ini file, the servers configuration file. Start up windows explorer, by pressing WinLogoKey + E:

29

And navigate to %programdata%\MySQL:

30

Go inside the folder MySQL Server 5.6 (or MySQL Server 5.7, etc. depending on the version you installed):

31

And double click on my.ini:

32

Notepad should pop up:

33

Find (CTRL+F) the text log-bin:

34

Remove the hashtag symbol before it and enter the following:


binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = test

It should look like this:

35

A bit lower down the file there is an entry named server-id with a value of 1. Do not change it on this server, but on the other one it should be set to 2:

36 Save the config file and run services.msc:

37

Find the MySQL service and restart it to commit the configuration changes:

38

Setting up the replication

Back to SQLyog for a bit. Run the following query on the first server:

SHOW MASTER STATUS;

It should output something like this, with a File and a Position, take note of those, cause we are going to need them later:

39

A reminder that on the second server the server-id should be set to 2:

40

Run the following query on the second server:

SHOW MASTER STATUS;

This also should output something like this, with a File and a Position, take note of those, cause we are going to need them later:

41

We need to run the following query on both servers, but with different parameters:

STOP SLAVE; 
CHANGE MASTER TO MASTER_HOST = 'other-servers-ip', MASTER_USER = 'replicator', MASTER_PASSWORD = '1234', MASTER_LOG_FILE = 'other-servers-file', MASTER_LOG_POS = other-servers-position; 
START SLAVE; 

So on the second server we run it with the parameters the first server gave us, like so:

42

Don’t forget to select all three lines, before pressing F9:

43

44

45

Likewise on the first server we run it with the parameters the second server gave us:

46

We can check if everything is fine, by running:

SHOW SLAVE STATUS;

Here is the output on the first server:

47

And on the second server:

48

Further testing

We can create a test database on either of the servers, in my case on the second server by running:

CREATE DATABASE Replica_Test;

And after refreshing the database list, by pressing F5 we can see that it was created:

49

Refreshing it on the first server as well we can see that it did replicate:

50

Deleting it from the first server by pressing del:

51

Has the same effect on the second server:

52

Comments 3

Leave a Reply

Your email address will not be published. Required fields are marked *