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 ( https://dba.stackexchange.com/questions/12345/how-to-setup-replicationmaster-slave-in-mysql-5-5-20/12352#12352 ). 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` : https://dba.stackexchange.com/questions/798/mysql-replication-introduce-new-slave-to-replication/1409#1409
- `Dec 17, 2011` : https://dba.stackexchange.com/questions/9328/mysql-master-master-replication-topologies-on-2-machines/9336#9336
- `Feb 15, 2012` : https://dba.stackexchange.com/questions/13109/mysql-replication-to-slave-having-vpn-connection-to-master/13113#13113
- `Apr 10, 2012` : https://dba.stackexchange.com/questions/16321/slave-settings-not-working/16322#16322
- `May 07, 2012` : https://dba.stackexchange.com/questions/17529/setting-circular-replication-in-mysql/17532#17532