… OPEN TABLES and flush only open tables, when run it again and see how many tables are open and in use and if FLUSH TABLES WITH READ LOCK can… is holding lock for this table ? I would much rather see all entries in the table_cache used, not grouped by the table, with thread_id using the table set (0 if table is not in use), lock_type READ/WRITE/READ…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… 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…, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table_new` ( -> `id…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… blog post. Tips/Tricks: run with –kill-long-queries to avoid nasty problems with “FLUSH TABLES WITH READ LOCK” –compress, compresses tables per file and should typically be… with “FLUSH TABLE WITH READ LOCK“. Namely when a select is blocking the flush from completing and queuing all requests behind it. Our current solution to deal with…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… and wait until InnoDB flushes all pages. To stop write activity we can either stop application or lock tables: mysql> flush tables with read lock; Query OK, 0 rows affected (0.37 sec) Now let’s wait until all dirty pages are flushed on…
Comment: An argument for not using mysqldump
November 8, 2010 By 26 Comments
… issue flush tables with read lock, but to do this right you have to hold the lock until your dump completes. A read lock is just that, a lock… 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
… when it is not – FLUSH TABLES WITH READ LOCK may take quite a while to complete on systems with long queries. Problems with data on multiple volumes… 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 FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 26, 2012 By 20 Comments
… with FLUSH TABLES WITH READ LOCK you are getting in some cases a snapshot that is not recoverable’ I mean that i work in an environment with… have been experiencing this with many versions of MySQL. We are now using ‘service mysql stop’ instead of FLUSH TABLES WITH READ LOCK to get reliable…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
April 25, 2010 By 30 Comments
… instead of issuing FlUSH TABLES WITH READ LOCK on the first call; instead one should issue FLUSH TABLES and may be another FLUSH TABLES followed by the READ LOCK as the… is if there is new query in the middle of flushes. FLUSH TABLES; FLUSH TABLES; FLUSH TABLES WITH READ LOCK;
Comment: How fast is FLUSH TABLES WITH READ LOCK?
February 24, 2012 By 30 Comments
… 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: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… Locking the table to prevent writes to it. I think you’re correct in theory as FLUSH TABLES WITH READ LOCK suppose to flush tables not just make them read only. Most tools however do not need FLUSH PART (for Innodb tables) and might…
