…_table_old.frm && mv huge_table_new.frm huge_table.frm && mv huge_table_old.frm huge_table_new.frm # fg mysql test — 4. mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table` ( `id…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
….b=0 | | 5 | root | localhost | test | Query | 62 | Flushing tables | flush tables with read lock | | 6 | root | localhost | test | Field List | 35… tables” is a misnomer — connection 5 is not flushing tables yet. It’s waiting to get the lock. Flushing tables After the FLUSH TABLES… lock is released with UNLOCK TABLES or a number of other commands. Most backup systems that use FLUSH TABLES WITH READ LOCK are…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
… TABLE tbl WRITE; SELECT * FROM table INTO OUTFILE ‘/tmp/tbl.txt’; DELETE FROM tbl; LOAD DATA INFILE ‘tmp/tbl.txt’ INTO TABLE tbl; UNLOCK TABLES; This will dump master’s table content to the file clean the table on master and slave… are error tolerant (like handle errors properly) you may rename table instead of locking – for many applications this works better anyway…
Comment: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 14, 2008 By 51 Comments
… manual: The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET AUTOCOMMIT = 0 (not START TRANSACTION) followed by LOCK TABLES…
Comment: Using LVM for MySQL Backup and Replication Setup
August 15, 2008 By 53 Comments
…/dev/mysql# mysql < FLUSH TABLES WITH READ LOCK; > SHOW MASTER STATUS; > \! ls -lart mysql-bin*|tail -n1 > UNLOCK TABLES; > MYSQL File Position Binlog…/dev/mysql# mysql < FLUSH TABLES WITH READ LOCK; > SHOW MASTER STATUS; > \! ls -lart mysql-bin*|tail -n1 > UNLOCK TABLES; > MYSQL File Position Binlog…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… MyISAM tables running UPDATE query on the table is essentially equivalent to locking table for write (on MySQL Level) before operation and unlocking it straight after. Not so for Innodb. Unless table is being locked explicitly Innodb “converts” table lock to “no…
Post: Announcing Percona XtraBackup 2.0.5
January 23, 2013 By Leave a Comment
… 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 copying is finished to unlock the databases. Bug… Holzgraefe). Partitioned tables were not correctly handled by the –databases, –include, –tables-file options of innobackupex, and by the –tables and –tables-file options…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… you have long running queries. The catch here is FLUSH TABLES WITH READ LOCK actually waits for all statements to complete…-snapshot 3) Now you have created logical volume and can unlock the tables, but before that you should probably record binary log… let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection. 5) Mount backup Filesystem: mount /dev…
Comment: Using LVM for MySQL Backup and Replication Setup
March 21, 2008 By 53 Comments
… Windows server Can we use the same FLUSH TABLES WITH READ LOCK command and UNLOCK TABLES to work with our snapshot (storagecraft) SW… is finished or will it stay in read only until UNLOCK TABLES is run
Comment: Using LVM for MySQL Backup and Replication Setup
February 26, 2008 By 53 Comments
… is suited to work with masterslave situations (lock,master-status,unlock). #!/bin/sh FMT_DATE=`date +%Y-%m-%d_%Hh%Mm…=/mnt/backup _temporary_file=”/tmp/$$.sql” cat < ${_temporary_file} FLUSH TABLES WITH READ LOCK; \! /usr/local/mysql/bin/mysql –user=root… \! /sbin/lvcreate –size ${SNAPSHOT_SIZE} –snapshot –name ${SNAPSHOT_LVNAME} ${LVNAME} UNLOCK TABLES; EOF /usr/local/mysql/bin/mysql –user=root –password=$PASSWORD…
