mySQL - Master-Slave configuration in Windows

Recently I was configuring Master-Slave configuration for mySQL database. It was required for one of our PHP applications deployed in Windows environment. I faced quite a lot of issues and had to research a lot on internet before finally making it work. Thought to document the steps for my reference here and perhaps it will help others as well -

General Steps -

• Collect IP address details for both master and slave VMs. In our case, these details were – 
• Master - <MasterIP>
• Slave - <SlaveIP>
• Open MySQL on both master and slave servers using any MySQL client and create empty database for your application with same name. 
• Generally MySQL gets installed in following directory - C:\Program Files\MySQL\MySQL Server 5.6
• Data and configuration information for MySQL instance is stored in following directory - C:\ProgramData\MySQL\MySQL Server 5.6 (Notice that it’s in ProgramData directory)

Master server Steps - 

• Go to directory - C:\ProgramData\MySQL\MySQL Server 5.6. Note that this directory may be hidden and may not be visible in windows explorer. In that case, just type the full path in explorer bar and the directory will open.
• Create “log_bin’ folder if it does not exist. This folder will be used by master database to store log files. These log files will then be read by slave database for replication.
• Make following changes in MySQL configuration file – my.ini
• Specify location and name of log file – 
• log_bin = "C:\ProgramData\MySQL\MySQL Server 5.6\log_bin\mysql-bin.log"
• Specify base directory of MySQL installation – 
• basedir = "C:\Program Files\MySQL\MySQL Server 5.6"
• Specify directory where MySQL data is stored – 
• datadir = "C:\ProgramData\MySQL\MySQL Server 5.6\data"
• Specify port no. for MySQL installation. Generally it is 3360 – 
• port = 3360
• Specify server id as 1 for master database.
• server_id = 1
• Specify IP address of master server
• bind-address = <MasterIP>
• Specify name of database instance to be replicated.
• binlog_do_db = <YourDBInstanceName>
• Save the configuration file.
• Open MySQL command line. 
• Enter root password (this password is set during MySQL installation).
• Type following commands to restart MySQL instance (Note that MySQL56 is name of service for MySQL v 5.6. This can be found under Windows services list)– 
• net stop MySQL56;
• net start MySQL56;
• Now run following set of commands to create a user ‘user_name’ on slave server and grant replication privileges to it
• CREATE USER '<user_name>'@'<SlaveIP>' IDENTIFIED BY '<password>';
• GRANT REPLICATION SLAVE ON *.* TO '<user_name>'@’<SlaveIP>’ identified by '<password>';
• FLUSH PRIVILEGES;
• Now running following commands to set read-lock on master database and get details about log files. These details will be required when making configuration changes on slave server – 
• USE <YourDBInstanceName>;
• FLUSH TABLES WITH READ LOCK;
• SHOW MASTER STATUS;
• This will show log file name and log file position. Make a note of log file name and its position. These will be required on slave server.
• Take backup of master database and restore it on slave database.
• Once done, you can unlock the read mode using following commands – 
• UNLOCK TABLES;

Slave Server Steps - 

• Go to directory - C:\ProgramData\MySQL\MySQL Server 5.6. Note that this directory may be hidden and may not be visible in windows explorer. In that case, just type the full path in explorer bar and the directory will open.
• Create “log_bin’ folder if it does not exist. This folder will be used by slave database to store master database log files. 
• Make following changes in MySQL configuration file – my.ini
• Specify location and name of log file – 
• log_bin = "C:\ProgramData\MySQL\MySQL Server 5.6\log_bin\mysql-bin.log"
• relay-log = C:\ProgramData\MySQL\MySQL Server 5.6\log_bin \mysql-relay-bin.log
• Specify base directory of MySQL installation – 
• basedir = "C:\Program Files\MySQL\MySQL Server 5.6"
• Specify directory where MySQL data is stored – 
• datadir = "C:\ProgramData\MySQL\MySQL Server 5.6\data"
• Specify port no. for MySQL installation. Generally it is 3360 – 
• port = 3360
• Specify server id as 2 for slave database.
• server_id = 2
• Specify IP address of slave server
• bind-address = <SlaveIP>
• Specify name of database instance to be replicated.
• binlog_do_db = <YourDBInstanceName>
• Save the configuration file.
• Open MySQL command line. 
• Enter root password (this password is set during MySQL installation).
• Type following commands to restart MySQL instance (Note that MySQL56 is name of service for MySQL v 5.6. This can be found under Windows services list)– 
• net stop MySQL56;
• net start MySQL56;
• Now run following set of commands to link master and slave servers for replication – 
• CHANGE MASTER TO MASTER_HOST='<MasterIP>',MASTER_USER='<user_name>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<MasterLogFileName>', MASTER_LOG_POS=<MasterLogFilePosition>;
• Note the highlighted values of log file name and log position. These are available from master server.
• Start the slave server – 
• START SLAVE;
• Run following command to see the status – 
• SHOW SLAVE STATUSG
• Check for following parameters – 
• slave_io_running
• slave_sql_running 
• If the value is yes then this confirms that MySQL master-slave replication has been configured properly and is working fine.