List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 9 2011 11:58am
Subject:Re: Doubt regarding Mysqlsump
View as plain text  
----- Original Message -----
> From: "Karen Abgarian" <abvk@stripped>
> This however means that the resulting snapshot will be consistent.
>  The fact of taking a backup really cannot create more consistency
> than what the application has by design.  If the application inserts
> related rows in say two related tables without transactions, it kind
> of assumes that it is prepared to handle the case when updates make
> it to one table and not to the other.   If it is not prepared to
> handle it, it means that any client crash will create inconsistent
> data, not to mention the server crash.

True, but I have never seen an application that checks for inconsistency in it's tables.
Making sure all users have stopped using the app ensures no in-flight transactions, and
then you have a consistent database - save application crashes, of course, as you mention.
MyISAM was never designed for data consistency, so it is pretty hard to get, indeed. The
original question was asking for consistent backups, so I'm trying to give the best there
is :-)

Like the physics jokes go, "assume a perfectly flat surface without friction..."

> Somebody mentioned the xtrabackup to me.  How different is it from
> another wrapper program with the ibbackup at the core?   I will be

Not very, I suspect.

> very curious to know if there exists a product that does NOT do the
> following at the basic technical level:
> - FLUSH TABLES WITH READ LOCK;  <-- locks all tables
> - take backup

You only need to FLUSH TABLES if you want the datafiles instead of an SQL dump. In the
latter case, you can just lock the tables you will backup. A read lock will give you an
unchanging view of the locked tables, both on MyISAM and InnoDB. On MyISAM, that read lock
will by default prevent writes, with the exception of inserts if there are no holes in the
table; for InnoDB a read lock wil simply give a view at the then-current SCN and allow
further writes to simply go on.

If the database was in a consistent state at the time of the lock, you can take a
consistent backup at that point.

> With the products of this kind, all that can be done is to accept
> that it does what it does.   Which does not exclude the options like
> reducing the downtime with say split-mirror-like snapshots or
> creating a replica to experience the downtime there.

Correct, but with the same caveats about consistency.

> The result will be the locks taken per table, which is equally bad
> compared to the normal access pattern for MyISAM tables.

Yeps. Which is why you try to

 * take a full backup of the db in a consistent state (say, once a month) and switch the
binary logs;
 * then switch the binary logs at a point where the database is in a consistent state, and
copy all but the newly active one.

You can then restore the full snapshot, and be sure that at the end of each sequential
binlog set you apply, it is again consistent.

As I indicated, ZRM is one product which does exactly that - save for ensuring the DB is
consistent, of course - that's up to you.

> If we think about it, a MyISAM table by definition is a table, the
> consistency of which is based on whole table locks.  Considering

A single table is always consistent. Data inconsistency occurs in sets of interrelated
tables, in other words, on the database level.

Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
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