List:Replication« Previous MessageNext Message »
From:Dimitre Radoulov Date:January 19 2012 10:14am
Subject:Re: Slow Slave Replication Catchup
View as plain text  
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.

Thread
Slow Slave Replication CatchupSimon18 Jan
  • Re: Slow Slave Replication CatchupMarcus Bointon18 Jan
  • RE: Slow Slave Replication CatchupAndrew Morgan19 Jan
    • RE: Slow Slave Replication CatchupRichard Bensley19 Jan
      • Re: Slow Slave Replication CatchupMarcus Bointon19 Jan
    • RE: Slow Slave Replication CatchupDavid Lerer19 Jan
  • Re: Slow Slave Replication CatchupDimitre Radoulov19 Jan
    • Re: Slow Slave Replication Catchupmartín arrieta19 Jan
      • Re: Slow Slave Replication CatchupDimitre Radoulov19 Jan
        • Re: Slow Slave Replication Catchupmartín arrieta19 Jan
  • Re: Slow Slave Replication CatchupDimitre Radoulov19 Jan
Re: Slow Slave Replication CatchupSimon19 Jan
  • Re: Slow Slave Replication CatchupMarcus Bointon20 Jan