Step-by-step configuration of MySQL Replication

Environment:

Master : 192.168.0.2
Slave : 192.168.0.3

MASTER CONFIGURATION

1) Login to MySQL with root user & Create a user for replication.
 
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

2) Edit your my.cnf on master & add below lines in [mysqld] section

log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=my_database
server-id=1

3) Restart master server

/etc/rc.d/init.d/mysqld restart



Configuring the Slave

1) Edit my.cnf

server-id=2
master-host=128.0.0.1
master-connect-retry=60
master-user=slave_user
master-password=slave_password
replicate-do-db=my_database

2) Restart MySQL Service

/etc/init.d/mysqld restart



3) Again login to mysql On the Master...

mysql > FLUSH TABLES WITH READ LOCK;

mysql > SHOW MASTER STATUS;

+---------------------+----------+-------------------------------+------------------+
 | File                          | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+---------------------+----------+-------------------------------+-----------------+
 | mysql-bin.000001   | 98           | my_database       |                  |
+---------------------+----------+-------------------------------+------------------+

You need this info while starting slave So copy it somewhere.


5) Take a DBdump from master & copy it to slave

6) login to MySQL On the Slave

mysql > stop slave;
mysql > CREATE DATABASE `my_database`;

7) Import Ur master dump

8) Login to MySQL on slave.

mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

slave start;

9) Back on the Master, log in to mysql & unlock the tables.
mysql > unlock tables;

10) Here we completed mysql replication configuration Now change any field in "my_database" on Master server, It will reflect on slave server.