Hi Ben, as said, you have to consider that a database data lives both on disk and on ram, on ram you have transactions, buffers that are asyncronously written to disk, etc.
While the datadir of a 'shutdown' database is the FULL dataset(knowledge) since no information is in ram, a datadir of a running database is not the FULL dataset of any database. This is nice: http://www.percona.com/ppc2009/PPC2009_Life_of_a_dirty_pageInnoDB_disk_IO.pdf Cheers Claudio 2010/4/21 Ben Mildren <ben.mild...@gmail.com> > LVM Snapshots on InnoDB setup are quite common, the transaction logs have > to be on the same volume, and you have to pay attention to some of the > configuration options, but I don't see any reason why this isn't a viable > alternative. Why wouldn't you trust InnoDB recovery? If you can't trust > that, there's no point in running it at all.. ..I'd hate to be in a > situation where my server crashed and I couldn't trust the database to come > back up again. The key with all this is to test your backups.. > > On 21 April 2010 09:50, Claudio Nanni <claudio.na...@gmail.com> wrote: > >> Johan, >> Is the fact that InnoDB ignores the command FLUSH TABLES WITH READ LOCK; >> enough? :) >> InnoDB has buffers and activities going on even if you locked the tables >> and >> you are not sure that its buffers are on the disk when you snapshot. >> Again, you might be lucky and trust in the InnoDB recovery, what I state >> is >> that there is only one 100% guaranteed safe way to have binary backups. >> >> have a look at these, very interesting: >> >> http://forge.mysql.com/w/images/c/c1/MySQL_Backups_using_File_System_Snapshots-2009-02-26.pdf >> >> http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ >> >> Cheers >> >> Claudio >> >> >> >> 2010/4/21 Johan De Meersman <vegiv...@tuxera.be> >> >> > How does the below not guarantee me 100% that everything that can be >> > consistent, is ? >> > >> > mysql> flush tables with read lock; >> > unixhost# sync >> > unixhost# lvm create snapshot >> > mysql> unlock tables; >> > >> > I agree that there may be data inconsistencies within MyISAM, as it has >> no >> > transactions, but there's also no guarantee that there isn't an >> application >> > in the middle of multiple insert statements the moment you shut your >> > database or your application. You can't magically get full consistency >> out >> > of your database if your application hasn't been designed for it. >> > >> > Shutting systems down for backup may be fine for small setups, but on a >> > server that hosts multiple critical applications, you just can't do >> that. >> > You back up as consistently as you can without downtime, and pick the >> time >> > of night with the lowest likelyhood of activity for it. >> > >> > >> > On Wed, Apr 21, 2010 at 8:12 AM, Claudio Nanni <claudio.na...@gmail.com >> >wrote: >> > >> >> Gavin, >> >> Right, >> >> that is also an option, but you are really not sure 100% that >> everything >> >> that is on memory is on the disk (buffers etc...) >> >> also if it is definitely good for a disaster recovery. >> >> What I meant is that the only way to have a 100% guaranteed consistent >> >> binary backup is when the database is shut down. >> >> Of course this is almost never an option, unless (tada) you have a >> slave >> >> dedicated for that. >> >> One remark on your note: >> >> >> >> >> >> Just a note though, I noticed someone added replication to a slave as a >> >> backup option. I really discourage that. Replication makes no >> guarantees >> >> that the data on your slave is the same as the data on your master. >> Unless >> >> you're also checking consistency, a slave should be treated as a >> somewhat >> >> unreliable copy of your data. >> >> >> >> While it is true that replication makes no guarantees, if your slave is >> >> not the same as the master and you rely on that for production, you >> have >> >> some problems, >> >> try to go to business and say, our slave (which at least 50% of our >> >> applications use to read data) is not really in sync, watch their >> facial >> >> expression! >> >> Believe me, in many production environments the method used for backups >> >> relies on the slave, not on the master. >> >> It is so much useful and important that you should have all your >> efforts >> >> go for having a consistent read-only slave 'dedicated' only for >> backups, no >> >> other client messing with it. >> >> >> >> Just my two cents >> >> >> >> Claudio >> >> >> >> >> >> >> >> Gavin Towey wrote: >> >> >> >> You can make binary backups from the master using filesystem snapshots. >> >> You only need to hold a global read lock for a split second. >> >> >> >> Regards, >> >> Gavin Towey >> >> >> >> >> >> -- >> >> MySQL General Mailing List >> >> For list archives: http://lists.mysql.com/mysql >> >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be >> >> >> >> >> > >> > >> > -- >> > Bier met grenadyn >> > Is als mosterd by den wyn >> > Sy die't drinkt, is eene kwezel >> > Hy die't drinkt, is ras een ezel >> > >> >> >> >> -- >> Claudio >> > > -- Claudio
