MySQL

 

mysqldumpでロックせずオンラインバックアップする

mysqldumpでロックをせずオンラインバックアップをしましょう。通常書き込みロックがかかってしまうmysqldumpですが、ロックをかけない方法があります。読み込み・書き込みロックがかからないので安全にオンラインバックアップが実現できます。これを知っていないとdump中に更新ロックでアプリが停止する事があるので是非使い方を覚えましょう。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告

mysqldump時にロックをかけないオプションは「--single-transaction」です。

 --single-transaction

このオプションはサーバからデータをダンプする前にBEGIN SQLステートメントを発行します。InnoDBといったトランザクションテーブルに対してのみ便利です。なぜなら、アプリケーションをブロックせずに、BEGINが発行された当時のデータベースの状態をダンプするからです。

このオプションを使用しているときは、一定の状態でダンプされるのはInnoDBテーブルのみだということを留意してください。例えば、このオプションを使用中にダンプされたMyISAMやMEMORYテーブルは状態が変化する可能性があります。
mysqldump — データベースバックアッププログラム

リファレンスには以上のような説明が書いています。

しかし「BEGINした当時のデータをバックアップ」とは曖昧な言葉です。

具体的にどういう事をしているからロックしないか、についてはよく解りませんね。

実は答えは簡単です。mysqldump時にトランザクション分離レベルをrepeatable readにしているのです。

general_logにはdump時に以下が記録されます。この後にdump処理が始まります。

24 Query     /*!40100 SET @@SQL_MODE='' */
24 Query     /*!40103 SET TIME_ZONE='+00:00' */
24 Query     SELECT @@GTID_MODE

general_logにはdump時に以下が記録されます。この後にdump処理が始まります。

18 Query     /*!40100 SET @@SQL_MODE='' */
18 Query     /*!40103 SET TIME_ZONE='+00:00' */
18 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
18 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
18 Query     SELECT @@GTID_MODE

「--single-transaction」を付けば場合のみ、以下が追加されています。

  • SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
  • START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

repeatable readなので、読み込んだデータをスナップショットとして保持し、そのデータを出力しているのです。

だから書き込みロックも読み取りロックもかからないのです。

ただしdumpしている間にデータが更新された場合、それはdumpに含まれません。

dump中は自セッションのスナップショットを反復可能読み取りするので、他人の更新が読み取れないのです。

もし厳密なdumpを取る必要があるなら、低速なmysqldump以外の選択肢を考えた方が高速で安全かと思います。

例えばOSのLVMやZFSのスナップショット機能を使ったり、InnoDB Hot Backupを使ったバックアップ方法もあります。

treeおすすめの書籍です!
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格:4,830円(税込、送料別)

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド
価格:3,465円(税込、送料別)

【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格:3,129円(税込、送料別)

◯ 広告