List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 7 2011 6:27am
Subject:Re: Doubt regarding Mysqlsump
View as plain text  
----- Original Message -----
> From: "Claudio Nanni" <claudio.nanni@stripped>
> 
> I think this is the best option for you:
> http://www.percona.com/docs/wiki/percona-xtrabackup:start

I must say, I still haven't looked very well at xtrabackup. How does it take consistent
backups of MyISAM tables? I didn't think that was possible without shutting down the
applications writing to them.


Adarsh, a vital piece of information is the storage engine you're using. Are your tables
InnoDB or MyISAM? Afaik (see my question above :-p ) your approach is the only one that
will allow you to take a consistent backup of MyISAM tables; for InnoDB tables xtrabackup
should work fine.


Another option that might be of interest would be taking only one full backup per week or
month using your current procedure, and taking daily backups of the binary logs between
those. Still no 100% guarantee of consistency, but everything is in there without load on
your database - except for the log writing overhead of course - and you can do
point-in-time restores up to the individual statement if you feel like it. Zmanda ZRM
Server is one solution that provides that level of backup.

Come to think of it, you could use your current procedure for backing up the binlogs
consistently, too:
 1. shut application
 2. issue "flush logs" to switch to a new binlog
 3. restart application
 4. backup all but the active binlog at your leisure for a consistent backup at that point
in time

That would enable you to do a quick daily backup with minimal application downtime, and
the added benefit of point-in-time restores. The downside of that approach is increased
restore time: you need to first restore the latest full backup, and then incrementally
apply each of the binlog backups to the point you need to restore to.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
Doubt regarding MysqlsumpAdarsh Sharma7 Jun
  • Re: Doubt regarding MysqlsumpClaudio Nanni7 Jun
    • Re: Doubt regarding MysqlsumpJohan De Meersman7 Jun
      • Re: Doubt regarding MysqlsumpAdarsh Sharma7 Jun
        • Re: Doubt regarding MysqlsumpJohan De Meersman7 Jun
          • Re: Doubt regarding MysqlsumpKaren Abgarian9 Jun
            • Re: Doubt regarding MysqlsumpJohan De Meersman9 Jun
              • Re: Doubt regarding MysqlsumpKaren Abgarian9 Jun
              • RE: Doubt regarding MysqlsumpJerry Schwartz9 Jun
                • RE: Doubt regarding MysqlsumpWm Mussatto9 Jun
                  • MySQL loses password?Tim Thorburn12 Jun
                    • Re: MySQL loses password?Johan De Meersman12 Jun
                      • Re: MySQL loses password?Tim Thorburn12 Jun
                        • Re: MySQL loses password?Claudio Nanni12 Jun
                          • Re: MySQL loses password?Tim Thorburn13 Jun
                            • Re: MySQL loses password?Claudio Nanni13 Jun
                            • Re: MySQL loses password?Andrew Moore13 Jun
  • Re: Doubt regarding MysqlsumpNilnandan Joshi7 Jun
    • Re: Doubt regarding MysqlsumpAdarsh Sharma7 Jun
  • Re: Doubt regarding Mysqlsumpmark carson7 Jun