How to implement MySql replication Master Master on same Windows machine

How to implement MySql replication Master Master on same windows machine.
In Master Master replication, the change effect to reflect vice varsa. If you change in one master it will effect automatically into other master also and vice varsa.

1. First install mysql-5.5.11-win32.msi on windows machine. While installing choose custom installation and change installation path to D:\MySQL\MySQL Server 5.5
2. Use all the default parameter like Service name is MYSQL and port number is 3306 and setup bin path.
3. And also modify root password to mysql.
3. Now check whether your installation is correct or not.
4.Open dos prompt and type below command.

c:\> mysql -uroot -pmysql -hlocalhost -P3306;
If you get mysql command then everything is ok.
We will treat mysql 5.5 is ACTIVE MASTER.
Now create one database:
mysql> create database suman;

5. Now install another version of mysql (mysql-5.1.56-win32.msi from http://dev.mysql.com/downloads/mysql/5.1.html)  for Passive MASTER. Again while installing choose custome installation and change installation path to D:\MySQL\MySQL Server 5.1

6. Change service name to MYSQL2 and port number to 3307
7. Modify root password to root.
8.Now check whether your installation is correct or not.
9.Open new dos prompot and type below command.

c:\> mysql -uroot -proot -hlocalhost -P3307;
If you get mysql command then everything is ok.
We will treat mysql 5.1 is PASSIVE MASTER.

Up to here two mysql instance are running in your windows machine.
Now start the replication implementation.

1. Open D:\MySQL\MySQL Server 5.5\my.ini then add four options to the [mysqld] section of the my.ini file

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
server-id = 1
log_bin = mysql-bin.log
binlog_do_db = suman

save it.
Restart the MYSQL service from your pc. MyComputer -> Right click -> click on Manage -> Services and Application -> Services ->
search MYSQL on right side, right click on that MYSQL and click on restart.

The next step in setting up replication is creating an account that will be used exclusively for replication. We strongly advise creating a dedicated replication user be created for better security so you won't need to grant any additional privileges besides replication permissions. Create an account on the master server that the slave server can use to connect. As mentioned, this account must be given the REPLICATION SLAVE privilege.

Open one dos windows for all MASTER operation.
c:\>mysql -uroot -pmysql -hlocalhost -P3306;
mysql> create user 'user1' identified by 'password';

mysql> grant replication slave on *.* to 'user1'@'%'  identified by 'password';


mysql> flush privileges;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | suman        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Please note down this file name and position, it will use to later.


Now some change on PASSIVE MASTER side:
1. Open D:\MySQL\MySQL Server 5.1\my.ini then add four options to the [mysqld] section of the my.ini file

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3307
server-id = 2
log_bin = mysql-bin.log
binlog_do_db = suman

Restart the MYSQL2 service from your pc. MyComputer -> Right click -> click on Manage -> Services and Application -> Services ->
search MYSQL on right side, right click on that MYSQL2 and click on restart.
Open one dos windows for all SLAVE operation.
c:\>mysql -uroot -proot -hlocalhost -P3307;
mysql> stop slave;
mysql> CHANGE MASTER TO
MASTER_HOST='localhost
MASTER_USER='user1',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=107;

Note: Values for the above command taken from Active Master 'show master status' command output.

mysql> show slave status\G;
Output will come huge, among two line should be like:
    Slave_IO_Running: No
    Slave_SQL_Running: No
   
Because slave is stopped now.
Now time came to start slave.
on slave side:
mysql> start slave;
Now check slave status:
mysql> show slave status\G;
Output will come huge, among two line should be like:
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
   
 If both values are Yes, then everything are ok.

 Now create user on Passive Master side and give previleges.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'user2'@'%' IDENTIFIED BY 'password';

mysql> show master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000001 |      106 | suman        |                  |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

 Now come again on Active Master side (3306) console and type below command:
  
 mysql> stop slave;

 mysql> CHANGE MASTER TO
 MASTER_HOST='localhost',
 MASTER_USER='user2',
 MASTER_PASSWORD='password',
 MASTER_PORT=3307,
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=106;
Note: Values for the above command taken from Passive Master 'show master status' command output.


mysql> start slave;
mysql> show slave status\G;

 Now you can check your replication work.

 Create some table in MASTER suman database or any database then check at passive MASTER side. Now do some database operation
 at Passive MASTER side and check at Active MASTER side.
Thanks:

Binod Suman