Mysqldump parameter -- lock-tables analysis, mysqldumpskiplock

Mysqldump parameter -- lock-tables analysis, mysqldumpskiplock

Mysqldump has a parameter -- lock-tables, which has never been described in detail until a user asks"Does the lock-tables parameter lock all tables in the current database at one time or lock the current export table?", I thought it was just locking the current export table. After reading the parameter descriptions,

 

 

-L, -- lock-tables Lock all tables for read.

(Defaults to on; use -- skip-lock-tables to disable .)

 

 

I didn't know much about it. I tested it. Prepare a slightly larger database. If the database is too small, the mysqldum command may export all the databases at once, making it difficult to clearly view the experiment results.

 

 

 

Run the following command to perform logical backup:

 

 

[Root @ DB-Server ~] # Mysqldump-u root-p -- default-character-set = utf8 -- opt -- extended-insert = false -- lock-tables MyDB> db_backup_MyDB. SQL

Enter password:

 

 

Execute the following command immediately

 

mysql> show open tables where in_use >0;

+----------+--------------------------------+--------+-------------+

| Database | Table                          | In_use | Name_locked |

+----------+--------------------------------+--------+-------------+

| MyDB     | AO_60DB71_VERSION              |      1 |           0 |

| MyDB     | AO_AEFED0_TEAM_TO_MEMBER       |      1 |           0 |

| MyDB     | AO_4B00E6_STASH_SETTINGS       |      1 |           0 |

| MyDB     | AO_2D3BEA_FOLIOCF              |      1 |           0 |

| MyDB     | AO_AEFED0_TEAM_ROLE            |      1 |           0 |

| MyDB     | AO_60DB71_DETAILVIEWFIELD      |      1 |           0 |

| MyDB     | AO_60DB71_LEXORANK             |      1 |           0 |

| MyDB     | AO_6714C7_REPORT_SCHEDULE      |      1 |           0 |

| MyDB     | AO_E8B6CC_SYNC_AUDIT_LOG       |      1 |           0 |

| MyDB     | cwd_application                |      1 |           0 |

| MyDB     | clusternode                    |      1 |           0 |

| MyDB     | AO_86ED1B_GRACE_PERIOD         |      1 |           0 |

| MyDB     | AO_60DB71_WORKINGDAYS          |      1 |           0 |

| MyDB     | cwd_directory                  |      1 |           0 |

| MyDB     | AO_2D3BEA_BASELINE             |      1 |           0 |

| MyDB     | fieldlayoutitem                |      1 |           0 |

| MyDB     | JQUARTZ_BLOB_TRIGGERS          |      1 |           0 |

| MyDB     | AO_013613_HD_SCHEME_MEMBER     |      1 |           0 |

| MyDB     | AO_2D3BEA_ALLOCATION           |      1 |           0 |

| MyDB     | AO_013613_WL_SCHEME            |      1 |           0 |

| MyDB     | AO_7DEABF_EXEC_CLUSTER_MESSAGE |      1 |           0 |

....................................................................

 

 

As shown above, when executing the mysqldump command, run the show open tables where in_use> 0 command. You will see that the value of In_use in all tables in MyDB is 1, this means that when the mysqldump command is executed, all tables in the current database are locked at one time. Instead of locking the current export table.

 

 

In_use

 

The number of table locks or lock requests there are for the table. for example, if one client acquires a lock for a table using lock table t1 WRITE, In_use will be 1. if another client issues lock table t1 WRITE while the table remains locked, the client will block waiting for the lock, but the lock request causes In_use to be 2. if the count is zero, the table is open but not currently being used. in_use is also increased byHANDLER... OPENStatement and decreasedHANDLER... CLOSE.

 

 

References:

 

Https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html

Https://oracle-base.com/articles/mysql/mysql-identify-locked-tables