List:General Discussion« Previous MessageNext Message »
From:Todd Lyons Date:July 26 2011 5:13pm
Subject:Re: Chronicle of fixing broken replication, and a question
View as plain text  
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.

> 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  ]

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  ]


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.

...Todd

-- 
If Americans could eliminate sugary beverages, potatoes, white bread,
pasta, white rice and sugary snacks, we would wipe out almost all the
problems we have with weight and diabetes and other metabolic
diseases. -- Dr. Walter Willett, Harvard School of Public Health
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