How do I configure multi master replication on WAMP?

Question

I want to configure mysql multi master replication, but I've not seen any comprehensive article or tutorial on how to do that on WAMP. I will be grateful if some will point me to a tutorial, or better still show me how to achieve that.

Answer 1

Whether you have Server configured with WAMP or as a Dedicated DB, the concepts for setting up multimaster replication is identical.

Before I go on, let me clarify the phrase multimaster replication.

True multimaster replication would have a Slave with Multiple Masters. It is not possible at present. MySQL 5.6 is approaching this. I think MariaDB as well. What most people call multimaster replication is, in reality, Master/Master (circular) replication.

Master/Master Replication is nothing more that an interleaving of the Master/Slave paradigm between two DB servers. This is what I will answer.

Back on Feb 06, 2012, I wrote a script to create a Slave from a Given Master ( How to setup replication(Master/slave) in MySQL 5.5.20? ). The script is Linux-centric. Since you said WAMP, you should apply the concepts for the Windows environment. I will do my best to translate the concepts:

Scenario

• WDB is the WAMP Server (IP 10.1.2.20)
• DDB is the Dedicated Windows DB Server (IP 10.1.2.30)

STEP #1

Edit my.ini on WDB and make sure it has

[mysqld]
server-id=1
log-slave-updates
log-bin=mysql-bin
relay-log=relay-bin

If it does not have these, add these to my.ini and then run this

C:\> net stop mysql
C:\> net start mysql

STEP #2

Edit my.ini on DDB and make sure it has

[mysqld]
server-id=2
log-slave-updates
log-bin=mysql-bin
relay-log=relay-bin

If it does not have these, add these to my.ini and then run this

C:\> net stop mysql
C:\> net start mysql

STEP #3

Please open C:\MasterSlaveSetup.bat it in notepad.

set MYSQL_MASTER_HOST=10.1.2.20
set MYSQL_SLAVE_HOST=10.1.2.30
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set MYSQL_MASTER_CONN=-h%MYSQL_MASTER_HOST} %MYSQL_CONN%
set MYSQL_SLAVE_CONN=-h%MYSQL_SLAVE_HOST} $MYSQL_CONN%

set MYSQLDUMP_OPTIONS=--master-data=1
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --single-transaction
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --routines
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --triggers
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --flush-privileges
set MYSQLDUMP_OPTIONS=%MYSQLDUMP_OPTIONS% --all-databases

mysql ${MYSQL_MASTER_CONN} -AN -e"GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"

set RELOAD_FILE=C:\MySQLData.sql
echo STOP SLAVE; > %RELOAD_FILE%
echo CHANGE MASTER TO master_host='%MYSQL_MASTER_HOST%'," >> %RELOAD_FILE%
echo master_port=3306,             >> %RELOAD_FILE%
echo master_user='repluser',       >> %RELOAD_FILE%
echo master_password='replpass',   >> %RELOAD_FILE%
echo master_log_file='dummy-file', >> %RELOAD_FILE%
echo master_log_pos=1;             >> %RELOAD_FILE%
mysqldump %MYSQL_MASTER_CONN% %MYSQLDUMP_OPTIONS% >> %RELOAD_FILE%
echo "START SLAVE;" >> %RELOAD_FILE%

mysql %MYSQL_SLAVE_CONN% -A < %RELOAD_FILE%

STEP #4

Run C:\MasterSlaveSetup.bat

STEP #5

C:\> copy C:\MasterSlaveSetup.bat C:\MasterSlaveSetup2.bat

STEP #6

Edit C:\MasterSlaveSetup2.bat to reverse the IP Addresses as follows:

set MYSQL_MASTER_HOST=10.1.2.30
set MYSQL_SLAVE_HOST=10.1.2.20

STEP #7

Run C:\MasterSlaveSetup.bat

When done, you should have Circular Replication fully set up. To test it, login to each MySQL instance and run SHOW SLAVE STATUS\G. You should see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.80.136
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 277892198
               Relay_Log_File: relay-bin.000058
                Relay_Log_Pos: 37535484
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes  <<------
            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: 277892198
              Relay_Log_Space: 277892637
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

If you see Slave_IO_Running=Yes and Slave_SQL_Running=Yes on both DB Servers, CONGRATULATIONS !!! You did it.

CAVEAT

The Dedicated DB Server does not have to a Windows Server. It could be a Linux server.

To help you understand MySQL Replication more, here are links to my past answers on this. Some of them have answers from others that were accepted. Please check these out and enjoy.

• Feb 23, 2011 : MySQL Replication - Introduce new Slave to replication
• Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
• Feb 15, 2012 : MySQL replication to slave having VPN connection to Master
• Apr 10, 2012 : Slave settings not working
• May 07, 2012 : Setting Circular Replication in mysql