… 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…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… auto_increment, (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…) — 2. mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) — 3. mysql> ^Z [1]+ Stopped mysql test # mv huge_table.frm huge_table_old.frm…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… removed or overwritten A table (or entire schema) was dropped accidentally Your InnoDB table was corrupt and mysql shuts down Your … 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 …
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… 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… given table (so you can see what tables are candidates for replacement) and the timestamp when this table was locked (or lock wait started) – MySQL anyway…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… to perform backup of MySQL Database (or create slave) using LVM2 on Linux. 1) Connect to MySQL and 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: Using LVM for MySQL Backup and Replication Setup
August 15, 2008 By 53 Comments
…—- 1 mysql mysql 956920476 Aug 16 00:23 mysql-bin.000156 /data/dev/mysql# mysql < FLUSH TABLES WITH READ LOCK; > SHOW MASTER STATUS; > \! ls -lart mysql-bin*|tail -n1 > UNLOCK TABLES; > MYSQL File…—- 1 mysql mysql 957115478 Aug 16 00:23 mysql-bin.000156 /data/dev/mysql# mysql < FLUSH TABLES WITH READ LOCK; > SHOW MASTER STATUS; > \! ls -lart mysql-bin*|tail -n1 > UNLOCK TABLES; > MYSQL File…
Post: Announcing Percona XtraBackup 2.0.5
January 23, 2013 By Leave a Comment
…introduced. This option specifies from what extra file to read the default MySQL options before the standard defaults-file. It can …for it to finish before running UNLOCK TABLES. This caused database to be unnecessarily locked with FLUSH TABLES WITH READ LOCK. Innobackupex now waits only till log …
Post: 10 things you need to know about backup solutions for MySQL
March 3, 2009 By 23 Comments
… files? Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing. What other effects are there on MySQL? I’ve seen systems that do a RESET MASTER, which immediately breaks replication. Are there any FLUSH commands at all, like FLUSH…
Post: Announcing Percona XtraDB Cluster 5.5.29-23.7.1
January 30, 2013 By 8 Comments
…FLUSH TABLES WITH READ LOCK when slave applier needed to abort transaction that is on the way, caused the deadlock situation. Resolved by grabbing Global Read Lock…Cluster-server and Percona-XtraDB-Cluster-client would conflict with mysql and mysql-server packages. Bug fixed #1087506 (Ignacio Nin). …
Post: Percona XtraDB Cluster Feature 1: High Availability
January 17, 2012 By 20 Comments
… with 2 nodes too. 2. Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL… FLUSH TABLES WITH READ LOCK command). Xtrabackup SST does not require READ LOCK for full time, only for syncing .frm files (the same as with regular backup). Even with…
