2

I have a master-slave replication currently configured. I know this setup runs real time. Is it possible to sync only the databases on schedule (daily or weekly) or by demand?

Thanks!

1

On your Slave issue the STOP SLAVE, START SLAVE commands to stop / start replication on Demand. If you want to run it to a schedule write a batch file / cron to STOP SLAVE and START SLAVE at the required times. I'm assuming you have a good reason for doing this?

  • Thanks! Yes, the reason is I don't want the records from master to be available immediately in the slave. The slave is exposed to the clients. There is a product table which has records that are subject for evaluation and review, however, due to the existing db design I cannot flag those records and the application is not capable in filtering those records. – Jess Lising May 22 '15 at 0:13
1

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

MySQL 5.6 supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the MASTER_DELAY option for CHANGE MASTER TO to set the delay to N seconds:

CHANGE MASTER TO MASTER_DELAY = N;

An event received from the master is not executed until at least N seconds later than its execution on the master. The exceptions are that there is no delay for format description events or log file rotation events, which affect only the internal state of the SQL thread.

  • Thanks Rick! Is it possible to set this param in 24 hrs or days? And is it efficient? – Jess Lising Jun 8 '15 at 0:14
  • As far as I know, any number of seconds will work. So 86400 for 1 day. It will need to queue up the replication queries, presumably on the Slave. The queue already exists (the "relay log"). The size depends on how much is replicated -- maybe 1GB per day, maybe less, maybe more. – Rick James Jun 8 '15 at 1:09
0

As for reference, in case somebody bumps here: I've used https://github.com/mrjgreen/db-sync to accomplish similar results.

Based on php, dbsync allows you to sync tables/records from host A to host B, including WHERE clauses (for when you need to pass records from one place to another). This directly thru' the command line.

I used it to make a gprs based database replication on a remote Rpi - without having to much time to make something dedicated or mess things up with database replication lol-.

Hope it helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.