How do I set the time interval for master-slave circular database replication?

Question

I have successfully set up the master- slave replication on my 2 database servers. I have also set up the master to replicate on the slave and when the master is down, the slave to master replication also works. However, i am unable to set up the time interval for this to take place. I tried using master_delay as well as master_heartbeat_period. None of these seem to be working.

mysql> change master to master_host='192.168.0.2',
-> master_user='master',
-> master_password='masterpass',
-> master_log_file='mysql-bin.000008',
-> master_log_pos=3733
-> master_delay=30;  //to set the time limit for the interval

mysql> change master to master_host='192.168.0.2',
-> master_user='master',
-> master_password='masterpass',
-> master_log_file='mysql-bin.000008',
-> master_log_pos=3733
-> master_heartbeat_period=30;  //to set the time limit for the interval

The replication still happens instantly.. Any idea what i am doing wrong?

Answer 1

I guess you confuse the different MySQL commands and their usage.

MASTER_HEARTBEAT_PERIOD

... is used to set the interval between the replication heartbeats, which are sent by the Master. Heartbeats are sent if there are no unset events in the binlog, just to ensure the connection. They don't have anything to do with a delay of the replication.

Also MASTER_HEARTBEAT_PERIOD is only available on MySQL Cluster NDB > 6.3!

See also: http://dev.mysql.com/doc/refman/5.1/en/change-master-to.html

MASTER_DELAY=N

Might be the option you are looking for. "An event received from the master is not executed until at least N seconds later than its execution on the master."

But this option is only available on MySQL 5.6 or later! It's a pretty new release,... maybe you don't use this version?

See also: http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html