Mysql Replication Setup on Windows Machine

There are several steps for installing and configuring mysql replication on windows operating system
Step 1 : Install mysql-5.5.11-win32.msi
On windows machine while installation choose custom installation and change path to D:\MySQL\MySQL Server 5.5
1.1 : Use all the default parameter like Service name is MYSQL and port number is 3306 and setup bin path.
1.2 :Modify root password to admin.
1.3 : Open dos prompt and type the following command as
mysql -u root -padmin -h localhost -P 3306
1.4 :If your mysql command get executed then everything is ok.
We will mention mysql 5.5 is MASTER.
1.5 : Now create one database named as 'wakil'
mysql> create database wakil; Step 2 :Install (another version or same) of mysql (mysql-5.1.56-win32.msi)
for SLAVE. Again while installing choose custom Installation and change installation path to
D:\MySQL\MySQL Server 5.1
2.1 :Change service name to MYSQL2 and port number to 3307
2.2 : Modify root password to admin123.
2.3 :Open new dos prompot and type below command as
C:\> mysql -u root -p admin123 -h localhost -P 3307;
2.4 : We will treat it mysql 5.1 as SLAVE.
2.5 : Now again create database names as 'wakil'
mysql> create database wakil;
Now in your windows machine two mysql instances are running, First instance ie MASTER is running on 3306 port
and second ie SLAVE is running on port 3307 port.

Step 3 : Now Start replication
Open D:\MySQL\MySQL Server 5.5\my.ini then add four options to the [mysqld] section of the my.ini file

[mysqld]
log-bin=mysql1-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Save it and Restart the MYSQL service from Computer ie . MyComputer -> Right click -> click on Manage ->
Services and Application -> Services ->On MYSQL and click on restart.

Step 4 :Creating Replication Account used for replication
Create an account on the master server that the slave server can use to connect. This account must be
given the REPLICATION SLAVE privilege.
Open one dos windows for all MASTER operation.
C:\>mysql -uroot -padmin -h localhost -P 3306;
mysql> create user 'replication_user' identified by 'password';
mysql> grant replication slave on *.* to 'replication_user'@'%' identified by 'password';
mysql> flush privileges;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mysql1-bin.000001 | 338 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Now take your backup of your MASTER database as we have new database so this below step are not required.
But when you have to create SLAVE of running database then it step must be required,
so lets go these below step too.

Open new dos prompt.
Taking backup from MASTER:
C:\>mysqldump -u root -padmin -h localhost -P3306 wakil > d:\test.sql
Now export this back to SLAVE, run below command on same dos windows.
C:\Users\wakilahmad>mysql -u root -padmin123 -h localhost -P3307 wakil < d:\test.sql

Step 5 : Now Some changes on SLAVE Side
Open D:\MySQL\MySQL Server 5.1\my.ini then add four options to the [mysqld] section of the my.ini file
[mysqld]
server-id=2

save it and Restart the MYSQL2 service from your as above
Open one dos windows for all SLAVE operation.

c:\>mysql -u root -padmin -h localhost -P 3307;

mysql> stop slave;
mysql> CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql1-bin.000001',
MASTER_LOG_POS=338;

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.
Step 6 : 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 you can check your replication work.

Create some table in MASTER wakil database or any database (those should be there at SLAVE side)
then check at slave side.
Now you stop slave again then you change of MASTER will not come but once again you will start slave then
slave will get automatically updated from last time stopped pointer.

For more details you may read mysql replication http://dev.mysql.com/doc/refman/5.0/en/replication.html


Done