MySQLdump pause event.

[laurent mercier]

Our company is currently looking into a master --> slave replication setup for a mysqldb.

Our Slave replicates the entire master except for the mysql user table.
Now were looking into the next step which is backing up the slave on intervals.
This has to be done by a automated process which will be looked later into (probably cronjobs running a script using mysqldump).

As the master is constantly being written to and the slave is nonstop replicating, is there a possibility to pause the slave as in STOP SQL_THREAD, continue logging the changes on the slave and after the dump has been made, continue the process of replicating.

Since the database is > 70GB this could take a while so we cannot afford to pause the master aswell.

In an overview:
- Master runs nonstop
- Slave gets paused at certain time
- Dump from slave is created to external server
- Slave is continued AFTER dump.
- Changes during the dump are continued to be replicated onto the slave.

So heres the question:
Can mysqldump deal with a pause event (waiting for the dump to be completed and then continue replicating so theres a snapshot of the slave database from a specific time)? Or what are the other options to backup the slave without pausing the master.



Edited 1 time(s). Last edit at 03/02/2012 07:02AM by laurent mercier.