…FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 28 Comments
… the lock. Flushing tables After the FLUSH TABLES WITH READ LOCK command finally acquires the lock, it must begin flushing data. This does not apply to all storage engines. However, MyISAM… assume that FLUSH TABLES WITH READ LOCK will complete quickly. In some cases, it is unavoidable. This includes backing up a mixture of MyISAM and InnoDB…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int…do is create another table with desired table structure and switch .frm table definition files. For safety, I’d recommend to flush tables with read lock while switching .frm…
Comment: An argument for not using mysqldump
November 8, 2010 By 24 Comments
… (myisam I’m looking at you), then there really is no way to get a “hot” consistent dump. You can issue flush tables with read lock… you an execute: Flush tables with read lock SNAP Release lock This will get you a consistent snap even on myisam. Problem is the FLUSH can take an…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… disk based storage engines It works with MyISAM and Innodb and BDB, It also should work with Solid, PrimeXT and Falcon storage engines… run FLUSH TABLES WITH READ LOCK Note – this command may take a while to complete if you have long running queries. The catch here is FLUSH TABLES WITH READ LOCK…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
February 24, 2012 By 28 Comments
… a way to take online backups of MyISAM tables without issuing FLUSH TABLES WITH READ LOCK command and/or setting flush_time (performance killer)? As far as I… about data being flushed to disk 100% ie. leaving the OS buffers and getting flushed to disk after the FLUSH TABLES WITH READ LOCK command returns? If…
Comment: Using LVM for MySQL Backup and Replication Setup
August 15, 2008 By 53 Comments
…. At least that’s what my tests reveal on a MyISAM InnoDB hybrid system. Is this always consistent? If so, then… transcript… /data/dev/mysql# mysql < FLUSH TABLES WITH READ LOCK; > SHOW MASTER STATUS; > \! ls -lart mysql-bin*|tail -n1 > UNLOCK TABLES; > MYSQL File Position Binlog_Do…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
April 25, 2010 By 28 Comments
… agree, however, that using FLUSH TABLES WITH READ LOCK for backups is a misnomer. This entire command is a MySQL and MyISAM specific hack. To help the matters a little bit in 5.5 we implement FLUSH TABLES WITH READ LOCK, so that you…
Comment: Using LVM for MySQL Backup and Replication Setup
January 16, 2009 By 53 Comments
… can skip the FLUSH TABLES WITH READ LOCK for my Innodb tables, but I also noticed the warning: “If you’re using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step… MyISAM and Innodb, but mostly Innodb, is there a work around for this so I don’t have to run the FLUSH TABLES WITH READ LOCK…
Comment: Using LVM for MySQL Backup and Replication Setup
August 5, 2008 By 53 Comments
… a myisam slave: If updates via replication are not cached(further they invalidate cache?) then is it really necessary to “flush tables with read lock” or will “stop slave” be sufficient? Does the same apply to buffers? IE, we are already ok with running myisam tables, at…
