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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
On your Slave issue the
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?
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.
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.