Setup replication of MySQL database

Creating a Data Snapshot Using mysqldump

Steps to set up the replication master server:

1. Add an replication_user with REPLICATION SLAVE as the only enabled global privilege and accessible with password from the slave server.
2. Edit the file /etc/my.cnf to include the following
   #skip-networking
# binary logging is required for replication
log-bin=mysql-bin
expire_logs_days = 7
server-id = 1
3. Shut down both master and slave mysqld and copy raw data files from master to slave (excluding the master's binary log files)

4. Start mysqld and obtain the position of binary log after making the master read-only by running following command in the mysql client: FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 98 | | |
+------------------+----------+--------------+------------------+

   Leave this mysql instance running during the rest of the replication setup
   

Steps to set up the slave server running on Windows:

1. Edit the file e:\Program Files\MySQL\MySQL Server 5.1\my.ini to include the following
   #skip-networking
bind-address=127.0.0.1
server-id = 2
relay-log=e:\Program Files\MySQL\MySQL Server 5.1\Data\mysqld-relay-bin
relay-log-index=e:\Program Files\MySQL\MySQL Server 5.1\Data\mysqld-relay-bin.index
#Check the size of the files ib_logfile? in your master data folder
innodb_log_file_size = 5M
2. Start mysqld and connect to the slave with a mysql client. Run the following commands:
   STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_PASSWORD='replication_user_password', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS\G;
The slave status can also be checked by examining the content of file master.info in the slave data folder without using a mysql client. Note the item Seconds_Behind_Master may give you some idea whether the master and slave are in sync.

Finally dont' forget to make the replication master server writable:
SET GLOBAL read_only = OFF;
UNLOCK TABLES;