List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:January 25 2011 8:05am
Subject:Re: InnoDB and rsync
View as plain text  
Am 25.01.2011 05:37, schrieb Robinson, Eric:
> Is there a way to safely backup an InnoDB database using rsync? 

Not without stop mysqld
Foregt it, do not try it and stop searching if you do not waste time

If you understand how innodb works you will see that
this is not possible by design

your whole solution is crippled because why in the world
are you killing your salves and reinit them without any
reason daily?

you can get a fresgh backup of the master with the following method
but nobody do that daily - this is only for init/reinit a salve

* rsync on master in another local folder while running
* stop master
* remove bin-logs
* second rsync to get last changes
* start master
* rsync to final destination

> When I'm all done, I have 240+ slave servers in perfect sync with their
> masters, each having a 100% identical binary copy of its master's
> database. Since these copies are truly identical, they can be used for a
> second layer of backup to other media.

why in the world do you not leave the slaves in peace and backup them
to another media because you can stop them as long you want for
a consistent backup and after starting the last changes from
the master are applied

> Right now we have a very efficient and reliable way to backup 240+
> separate instances of MySQL with MyISAM tables. The databases range in
> size from .5GB to 16GB. During this time, users can still access the
> system, so our customers can work 24x7. In the process, we also refresh
> 240+ slave instances with a perfect byte-for-byte replica of the master
> databases. 
> 
> The whole thing takes about 30 minutes. 
> 
> Here's how we do it. 
> 
> Late at night when the number of users on the system is low, we do the
> following for each of the 240+ instances of MySQL...
> 
> 1. Shut down the slave and remove all replication-related log files.
> 
> 2. Perform an rsync of the master's data directory to the slave. Users
> may be making changes to tables during this rsync.
> 
> 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET
> MASTER.
> 
> 4. Perform a second rsync of the data directory from the master to the
> slave to copy any user changes that happened during step 2. This usually
> completes in a few seconds, often less than 1. If any users were trying
> to insert records at this exact moment, their application may appear to
> pause very briefly.
> 
> 5. Start the slave.
> 
> When I'm all done, I have 240+ slave servers in perfect sync with their
> masters, each having a 100% identical binary copy of its master's
> database. Since these copies are truly identical, they can be used for a
> second layer of backup to other media.  
> 
> Like I said, the whole process takes about 30 minutes because the rsync
> algorithm only copies the byte-level changes.
> 
> IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING
> INNODB?
> 
> I've been reading about InnoDB hot copy and other approaches, but none
> of them seem to work as well as the approach I have worked out with
> MyISAM. Unfortunately, my software wants to force us to switch to
> InnoDB, so I'm really stuck. If we have to switch to InnoDB and we
> cannot come up with a method for doing fast, rsync-style backups, it
> will probably mean huge, costly, and unnecessary changes to our
> infrastructure.
> 
> Any help will be GREATLY appreciated.
> 
> --
> Eric Robinson
> 
> 
> Disclaimer - January 24, 2011 
> This email and any files transmitted with it are confidential and intended solely for
> mysql@stripped. If you are not the named addressee you should not disseminate,
> distribute, copy or alter this email. Any views or opinions presented in this email are
> solely those of the author and might not represent those of Physicians' Managed Care or
> Physician Select Management. Warning: Although Physicians' Managed Care or Physician
> Select Management has taken reasonable precautions to ensure no viruses are present in
> this email, the company cannot accept responsibility for any loss or damage arising from
> the use of this email or attachments. 
> This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesReindl Harald21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesMySQL)21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesJoerg Bruehe24 Jan
      • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
        • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
          • RE: Slow query on MySQL4 server doing simple inner join of twoInnoDB tablesGavin Towey25 Jan
            • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford25 Jan
  • InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMattia Merzi25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
      • Re: InnoDB and rsyncJohan De Meersman25 Jan
        • Re: InnoDB and rsyncReindl Harald25 Jan
        • RE: InnoDB and rsyncJerry Schwartz25 Jan
    • Re: InnoDB and rsyncSteve Musumeche25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
    • Re: InnoDB and rsyncJohan De Meersman26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncMichael Dykman28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncEric Bergen1 Feb