List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:July 26 2011 9:58pm
Subject:Re: Chronicle of fixing broken replication, and a question
View as plain text  

Am 26.07.2011 19:13, schrieb Todd Lyons:
> On Tue, Jul 26, 2011 at 8:18 AM, Reindl Harald <h.reindl@stripped> wrote:
>>> 1.  I did a full copy of the running master database server using
>>> xtrabackup to a backup server via nfs.  It took 2 hours, of which the
>>> last 15 minutes did a write lock of the entire server as it copied
>>> over the *.frm files and the few myisam tables.  This was the most
>>> troublesome part as it was visible to both users and website owners
>> why are not using two rsync-runs?
>> the first while mysqld is running
>> the second directly after stop mysqld
>>
>> this way you can be 100% sure that you can start the replication
>> from scratch and your downtime is only a few seconds, best if
> 
> 43 GB is more than a few seconds.

well, but it is faster in summary and without any pitfalls

>> enough space to have this target on the master-machine because
>> while you take the slow way over the network the master is running
>> with a fresh binlog
> 
> I tested a slightly modified version of your quickie script, first
> using the nfs share:
> Starting first rsync
> real	37m48.201s
> Stopping MySQL:                                            [  OK  ]
> Starting second rsync
> real	4m24.536s
> Starting MySQL:                                            [  OK  ]

this is a good value for remote

> Then I ran it using local spindles:
> Starting first rsync
> real	26m10.747s
> Stopping MySQL:                                            [  OK  ]
> Starting second rsync
> real	3m11.945s
> Starting MySQL:                                            [  OK  ]

weel, 3 minutes is a acceptable downtime with the benefit
that the slave will be binary-identical and all old logs
are gone away

> So I could have lowerd the amount of time mysql was not available by
> quite a bit doing it that way.  Plus I would have removed the
> requirement to apply-logs (due to not copying innodb files while they
> were open).  In the end, xtrabackup worked as designed, but the fact
> that my large number of databases and tables and innodb_file_per_table
> slows things down tremendously, so it isn't the best fit in this case

with smaller databases it is the same, our main webserver has
only 800 MB database files and here we are speaking really
about few seconds for the second rsync and the only thing to do
is re-init the replication after that - i find really no case where
the rsync will not win at the end :-)

i am doing this since years for all sort of backups (as example
stopping the slave shortly and make a consistent off-site-backup)
because i do not trust any tool which makes a hot-backup and trying
a sync with binary logs


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Chronicle of fixing broken replication, and a questionTodd Lyons26 Jul
  • Re: Chronicle of fixing broken replication, and a questionReindl Harald26 Jul
    • Re: Chronicle of fixing broken replication, and a questionTodd Lyons26 Jul
      • Re: Chronicle of fixing broken replication, and a questionReindl Harald26 Jul