On 18/01/2012 20:01, Simon wrote:
> [...]
>
> Each night at 12:30am we run a mysqldump backup from the slave using the following
> process. This process takes about 45 mins to complete.
>
> 1). Stop Slave
> 2). Mysqldump each database (/usr/bin/mysqldump -u ********* -p'*********' -h
> SLAVE_HOST --routines --databases $db | /bin/gzip -9>
> /path/to/backup/MySQL5Dump-$db-$NOW.gz)
[...]
I would suggest to change the command to:
/usr/bin/mysqldump -u ********* -p'*********' -h SLAVE_HOST --routines --databases
--events "$db"> "/path/to/backup/MySQL5Dump-$db-$NOW"
gzip -9 "/path/to/backup/MySQL5Dump-$db-$NOW"
This is only possible if you have space for the uncompressed dump file on your filesystem,
of course,
but the export (and the lock) will take less time.
If the user databases don't contain MyISAM tables, you could use the --single-transaction
mysqldump option
and avoid the locking.
Regards
Dimitre
P.S. I've added the --events option to your command for completeness.
