MySQL – Master/Master replication on Windows


Here you will find the step-by-step actions to succesully setup a master/master replication cluster of two MySQL 5.5 nodes on Windows.

Prerequisites
– Two servers with Windows installed (make sure all updates are installed)
– Both machines have a clean install of MySQL 5.5
– Firewall is configured to allow port 3306
– Check the my.ini file (C:\ProgramData\MySQL\) for the ‘bind-address’ option. Uncomment this for mysql to bind on all interfaces.

Now, configure MySQL to enable replication:

On server #1:
server-id=1
auto-increment-increment = 2
auto-increment-offset = 1
log-bin=mysql-bin
binlog-do-db=databasename

On server #2:
server-id=2
auto-increment-increment = 2
auto-increment-offset = 2
log-bin=mysql-bin
binlog-do-db=databasename

Small note; in Windows you have to open the my.ini file with right-click, open as Administrator.

Now restart MySQL (Windows; restart service MYSQL) for the configuration to be applied. Then open the mysql console. In Windows you will find the in the start-menu by the name “Mysql Command-line client” and enter the password. Here you will need to change the master settings.

First create a user on both servers;
create user 'replication'@'%' identified by 'password';
grant replication slave on *.* to 'replication'@'%';

Still will create the user and give it the needed permissions. Please change ‘password’ to a strong password for the replication user. Now, still in the mysql screen check the current binlog position of the servers:
mysql> show master status;

This should give you an output like this:
mysql> show master status;
+——————+———-+————–+————————————–+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+—————————————+
| mysql-bin.000003 | 120 | | |
+——————+———-+————–+—————————————+
1 row in set (0.00 sec)

Make a note of the binlog position!

Now, on the first server change the master options to enable replication to the second node. Here is an example of this command:
CHANGE MASTER TO MASTER_HOST = '192.168.100.3', MASTER_USER = 'replication', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 120;
Execute the same command on the second server (but with the correct log_pos and IP address). This will enable the two way replication.

Start replicating now by telling the master to:
start slave
Wait 5 seconds and execute this same command on the second node.

To verify if the replication is stable;
mysql> show slave status\G;

General recommendations
– Upgrade to MySQL 5.5. Under MySQL 5.5 you can tune InnoDB to access multiple CPUs
– Use InnoDB for all tables. InnoDB caches data and indexes in RAM, MyISAM only caches indexes.
– Check the amount of free RAM availible on the servers and alter your settings accordingly, or upgrade RAM
– Check the mysql_slow log to see what is causing delay – tweak those query’s
– Replication tends to send large packets between servers. Make sure the maximum packet size in MySQL is large enough (eg. 32M)
– In order to enable auto crash recovery, set the option “relay-log-recovery”
– If you feel there is something wrong with the cluster, use the tool mysqldbcompare to check if everything is still in sync.
– Memory recommendation: innodb_buffer_pool_size = 70% of RAM. If you have lots of RAM and are using 5.5, then consider having multiple pools. key_buffer_size = 20M