MySQLump 參數(shù)--Lock-Tables淺析
本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者 。轉(zhuǎn)載本文請(qǐng)聯(lián)系DBA閑思雜想錄公眾號(hào)。
mysqldump有一個(gè)參數(shù)--lock-tables，以前對(duì)這個(gè)參數(shù)也沒有深入、詳細(xì)了解過，直到上次有個(gè)網(wǎng)友問“參數(shù)lock-tables 是一次性鎖定當(dāng)前庫的所有表，還是鎖定當(dāng)前導(dǎo)出表?“ ，之前一直以為只是鎖定當(dāng)前導(dǎo)出表，后面看了參數(shù)說明后，
- -l, --lock-tables Lock all tables for read.
- (Defaults to on; use --skip-lock-tables to disable.)
自己也不怎么確認(rèn)了，當(dāng)時(shí)就測(cè)試了一下。準(zhǔn)備一個(gè)稍微大一點(diǎn)的庫，如果數(shù)據(jù)庫太小，那么可能mysqldum命令一下子就導(dǎo)出了所有庫，很難清晰的看到實(shí)驗(yàn)結(jié)果。
執(zhí)行下面命令做邏輯備份
- [root@DB-Server ~]# mysqldump -u root -p --default-character-set=utf8 --opt --extended-insert=false --lock-tables MyDB > db_backup_MyDB.sql
- Enter password:
同時(shí)立即執(zhí)行下面命令
- 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 |
- ....................................................................
如上截圖，執(zhí)行mysqldump命令的時(shí)候，使用show open tables where in_use >0命令，你會(huì)看到MyDB里面的所有表的In_use的值都為1，意味著是當(dāng)執(zhí)行mysqldump命令時(shí)，是一次性鎖定當(dāng)前庫的所有表。而不是鎖定當(dāng)前導(dǎo)出表。
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 by the HANDLER ... OPEN statement and decreased by HANDLER ... CLOSE.
參考資料：
https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html
https://oracle-base.com/articles/mysql/mysql-identify-locked-tables
