Flush tables with read lock, flushtables

Flush tables with read lock, flushtables

Recently, mysql used locks in master-slave replication. Reference: http://blog.csdn.net/arkblue/article/details/27376991

1. FLUSH TABLES WITH READ LOCK

This command is global read lock. After the command is executed, all tables in all databases are locked and read-only. It is generally used for online database backup. At this time, the database write operations will be blocked and the read operations will proceed smoothly.

The unlock statement is also unlock tables.

 

2. LOCK TABLESTbl_name[Alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

This command is table-level locking. You can customize to lock a table. For example, lock tables test read does not affect write operations on other tables.

The unlock statement is also unlock tables.

Refresh table

Flush tables with read lock, that is, to refresh all the dirty pages to the disk and add a read lock to all the tables. Therefore, it is safe to directly copy data files. However, if you run the flush tables with read lock command, what other operations do you need? First, let's talk about the write operation. This FTWRL must be waited, and FTWRL can be executed only after the write operation is completed. This is easy to understand. What about other read Operations? For example, if there is a query: select count (*) from tb before FLWRL sends out, then FTWRL also has to wait (show processlist can see waiting for table flush ). You may say that reading and reading in mysql are not excluded. Why do you need to wait? Because FTWRL is flush dirty pages, only in this way can we ensure data consistency (for example, when xtrabackup backs up the myisam Table), while in select count (*) during from tb execution, FTWRL has to wait because all the operations are in the memory and cannot be fully flushed. Maybe you still have doubts: the select page is not a dirty page. Why does FTWRL have to wait? Can't mysql be improved? I don't think mysql is as simple as that. The reason for waiting is that the table is large and it cannot read all the pages into the memory at a time. query is atomic, it is impossible that the execution will be blocked, so you have to let it be executed, so FTWRL will have to wait.

 

When executing these two statements, you must note that the statements are implicitly submitted. When you exit the MySQL terminal, unlock tables is implicitly executed. That is, to make the table lock take effect, you must keep the dialog.

Note that