List:Backup« Previous MessageNext Message »
From:Manuel Arostegui Date:September 1 2012 7:15pm
Subject:Re: Recovery from a MySQL dump is too long !
View as plain text  
Hello *,

My INNODB database has a size of 80GO. I've a replication setup on 3
slaves and I backup my db from them.
If a problem occurs on the database, a recovery from a dump takes
around 6H ! That's too long for us.

2 ideas :
1. Stop the slave and rsync the folder /var/lib/mysql to another server
2. Stop the slave, take a dump and when it's finish, pull the dump in
another MySQL instance daily.

Do you have another ideas ? What's your advices ?

 Thanks in advance,


Hello Bob,

I wouldn't use the rsync just like that. First, you'd have to make sure all
the changes are committed to disk and make sure all the dirty pages are
flushed etc. I would use xtrabackup from a slave to another server (keep in
mind it will lock tables if you don't say otherwise!).

If having a spare machine isn't a problem I would set up a replication
thread, just like the other 3 slaves you have. I would delay it XX number
of hours using mk-slave-delay. If a problem arises, you just need to
execute start slave; and it will catch up.

Using replication as a backup could be a bad idea, as it would replicate a
bad statement like "delete from users;". However, having that new slave
delayed XX number of hours, would allow you, if necessary to start
replication up to the "delete from users;" binlog position. So you can have
have the same database and skip the bad statement.

Hope this helps!

Manuel.

Thread
Re: Recovery from a MySQL dump is too long !Manuel Arostegui1 Sep
  • Re: Recovery from a MySQL dump is too long !Emre Hasegeli2 Sep