Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
WAMP is a nice and convenient way to get a Apache, MySQL and PHP set up on Windows without having to worry about configuration. If you're doing sophisticated configuration -- multi-master replication counts :-) -- then you need to dig into MySQL configuration. Personally I would consider installing something like VirtualBox and then running all of this in Ubuntu, as it has been my experience that Windows gets in the way of "down and dirty" system configuration. Presumably you're not going to be really using this config on Windows in production, so you'll get closer to the real thing, too. –  Tom Harrison Jr Nov 30 '12 at 13:27
    
@Chibuzo - It's useful to define your acronyms. Not everyone know every acronym out there and this helps to reduce any confustion and extra work on the readers. –  trygvis Nov 30 '12 at 13:55
    
@TomHarrisonJr does that mean that it is not possible to achieve that on WAMP? –  Chibuzo Nov 30 '12 at 14:38
    
You can certainly extend the MySQL configuration -- WAMP (Windows Apache MySQL PHP) is just a package that configures all of these components, but they are still the same servers that you would get if you installed manually. My point is that WAMP creates a non-standard configuration -- nothing "wrong" just different than what you would get if you did it manually. So you may end up fighting with WAMP and will need to understand its configuration in detail in order to get to multi-master. There's a point where you can no longer avoid knowing how things work, this is probably such a point. –  Tom Harrison Jr Nov 30 '12 at 14:43
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
I am trying to test your setup. on Step #7 is it Run C:\MasterSlaveSetup2.bat ? Also do you run C:\MasterSlaveSetup.bat on WDB and C:\MasterSlaveSetup2.bat on DDB? Thanks –  user24460 May 31 '13 at 16:37
    
@Fed Since the IPs are hardcoded, ypu could run both batch files on the same Windows Server. –  RolandoMySQLDBA May 31 '13 at 17:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.