----- Original Message ----- > From: "Karen Abgarian" <a...@apple.com> > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
