Master to slave and slave to master replication

Master :
1. Create user and give the privileges:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO reply@’host-name’ IDENTIFIED BY ‘some-pass’;
2. Define the log setting in my.cnf
log-bin = mysql-bin
server_id = 2
3. Restart the server /etc/init.d/mysqld restart
4. check the status of the server by : show master status\G;

Slave :
1. Enable necessary log:
log-bin = mysql-bin
server_id = 3
relay-log = mysql-relay-bin
log_slave_updates =1
read_only =1
2. Create replication user in Slave server: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO reply@’host-name’ IDENTIFIED BY ‘some-pass’;
3. Change the informaiton in master.info by inserting bellow command in command line
CHANGE MASTER TO MASTER_HOST=’MASTER-HOST-NAME’,
-> MASTER_USER=’reply’,
->MASTER_PASSWORD=’some-pass’,
->MASTER_LOG_FILE=’mysql-bin.000001′ , { you can check log file status from SHOW MASTER STATUS\G output form Master server }
->MASTER_LOG_POS=0;

4. Restart the slave : slave start ;
5. Look for 2 things in SHOW SLAVE STATUS\G; report :
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Note : if Slave_IO_Running: = NO , then check the log /var/log/mysqld.log file for further information such as

090428 7:51:18 [ERROR] Slave I/O thread: error connecting to master ‘reply@Master-host-name:3306’: Error: ‘Host ‘your-host-name.’ is not allowed to connect to this MySQL server’ errno: 1130 retry-time: 60 retries : 86400
090428 8:07:18 [Note] Slave I/O thread killed while connecting to master
090428 8:07:18 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000001’, position 106

Leave a Reply

*