mysql read lock, read_only


[Q] which variable in mysql corresponds to flush table read lock

mysql> show variables like '%read_only%';
mysql> show variables like '%lock%';

innodb_print_all_deadlocks | OFF |
| innodb_table_locks | ON |
| key_cache_block_size | 1024 |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| max_write_lock_count | 18446744073709551615


//=== http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html
FLUSH TABLES WITH READ LOCK

"""...
The manual describes it with this:
Closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executing UNLOCK TABLES. This is very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.
FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:

...
If long-running statements were started prior to step 2, then the FLUSH TABLES WITH READ LOCK command will block until they complete. This is a bad state to get stuck in as the server is in read-only mode at this point and this statement is frequently run on a primary.
It might be a good idea to kill long-running statements prior to running the FLUSH command or when it gets stuck.

..."""



//=== pattern for lock and unlock
put your whole database to read only mode by this commands:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = 1;

backup data,

then restore to normal mode with:

mysql> SET GLOBAL read_only = 0;
mysql> UNLOCK TABLES;



//=== 1,0 --> ON, OFF

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only= ON;

mysqldump to export data ...

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;