Didier Duplech wrote:
> Let's say you want to replicate every six hours
> instead of continuously.
> Is there a way to schedule replication, or at
> least set a delay between replications ?
There is no mechanism built into MySQL to do this directly. But you can do this via cron. If you are using the 4.0 or 4.1 versions of MySQL, then there are 2 separate threads for replication: the I/O thread and the SQL thread. The I/O thread connects to the master server and reads the binary logs and copies the SQL statements to the slave's local relay log. The SQL thread then reads the local relay log and applies the SQL statements.
What you can do, is have the I/O thread running continuously using the following command on the slave:
START SLAVE IO_THREAD
This way, all of the SQL statements are replicated to the slave, but not yet applied.
In your shell script that will be kicked off via cron every six hours, you can start the SQL thread and have it run up to a certain point in the relay log. Note that this technique only works for version 4.1:
SHOW SLAVE STATUS (Capture the Relay_Log_File and Relay_Log_Pos columns)
then
START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE='log_file_name',
RELAY_LOG_POS=xxx
where log_file_name and xxx are the values for the columns Relay_Log_File and Relay_Log_Pos respectively.
You will still have to provide the logic to determine of the SQL thread is running prior to a START SLAVE command (hint: column Slave_SQL_Running from SHOW SLAVE STATUS), as well as a startup command for the slave server so that the SQL thread does not start up automatically, but in a nutshell that's how it could be done.
Hope this helps.
-- Partha
Partha Dutta
Senior Consultant, MySQL Inc.
http://www.mysql.com