mysqldumpを使ったデータベース移行方法とそのオプションについて

9327 ワード
mysqldump MySQL mysqlpump Database MySQL テキストリンク

mysqldumpを使ったデータベース移行方法とそのオプションについて

以前VPSサーバを乗り換える際に溜まっていたデータベースのバックアップを取り、新しいVPSサーバのmysqlへデータをインポートしました。
その際にmysqldumpを利用したので、その記録と気になった点(--single-transaction)を調べてみた。

環境

VPS

  • centos7
  • mysql5.7.15

バックアップとインポート

mysqldumpの結果をリダイレクトして保存

mysqldump -u mysql_user_name >> /dump_database/file/path

データのインポート

mysql -u mysql_user -p database_name < /dump_database/file/path

mysqldumpの使い方

基本のコマンド

mysqldump -u mysql_user_name

オプション

-t

  • create table 文の発行まで行ってくれるので移行先のDBにテーブル情報を用意しなくてもいい。
mysqldump -u mysql_user_name -t
  • optionで-tをつけたときに発行されるsql文
--
-- Table structure for table `table_name`
--

DROP TABLE IF EXISTS `table_name`;
CREATE TABLE `table_name` (
...
)

--
-- Dumping data for table `table_name `
--

LOCK TABLES `table_name` WRITE;
INSERT INTO `table_name` VALUES (1,1.'xyz.jpg',0,'2016-04-14 16:02:15','2016-04-14 16:02:15')...;
UNLOCK TABLES;
...
  • optionで-tをつけないときのに発行されるsql文
--
-- Dumping data for table `table_name`
--

LOCK TABLES `table_name ` WRITE;
INSERT INTO `table_name ` VALUES (1,1,'xyz.jpg',0,'2016-04-14 16:02:15','2016-04-14 16:02:15')...;
...

--single-transaction

  • データの整合性を保つために利用
mysqldump -u mysql_user_name --single-transaction

--single-transactionの有無で何か変わるのか

気になった点です。--single-transactionって結局何のために必要なのか。何をやっているのか。

このオプションで変わること

--single-transaction
このオプションは、データのダンプ前に、トランザクション分離モードを REPEATABLE READ に設定し、START TRANSACTION SQL ステートメントをサーバーに送信します。これは、InnoDB などのトランザクションテーブルの場合にかぎって便利です。その場合、アプリケーションをブロックすることなく、START TRANSACTION が発行された時点のデータベースの一貫した状態をダンプするからです。
このオプションを使用する場合、一貫した状態でダンプされるのは InnoDB テーブルのみだということに留意してください。たとえば、このオプションの使用中にダンプされた MyISAM テーブルまたは MEMORY テーブルは状態が変化する可能性があります。
引用: https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

ここらへんからよくわからない。
トランザクション分離モードが「REPEATABLE READ」に設定されるらしい。

REPEATABLE READについて

何を隠そう、InnoDBのREPEATABLE READではその時の最新の値、つまり最も新しいバージョンが読み取られるという仕様になっている。だから他のトランザクションがロックしている行を参照しようとすると待たされるが、その後コミットされたホヤホヤの新しい値が得られるのである。
引用: http://nippondanji.blogspot.jp/2013/12/innodbrepeatable-readlocking-read.html

予想

今回の場合はmysqldumpのトランザクションはREPEATABLE READで行われ、　この時に他のトランザクションは待ち状態になるはず。
ということでmysqldumpの--single-transactionオプションの有無で、insert処理にどう変化を及ぼすかを見てみる。

方法

  • insertにかかった時間を比較するためにdumpする前にinsertして、その後にdump中にinsertする。

  • データが少ないとすぐに処理が終わってしまうのでダミーデータを入れる。

オプションあり

  • mysqldump -u user_name -p database_name -t -n --single-transaction

オプション無し

  • mysqldump -u user_name -p database_name -t -n

結果

  • 上の流れをupdate, select文でも試してみました。
  • 数値の単位はsecond
有り 無し
insert 0.00 12.40
update 0.00 10.89
select 0.00 0.00

予想ではオプション有りのほうではinsertやupdateの際に待ち時間が発生して処理時間が長くなるはずだったのですが逆の結果になりました。

内部的にはスナップショットをとって、そのデータをダンプする事でロックする事なく 整合性のとれたダンプを取る事が可能です。 通常のmysqldumpと異なる点は、ダンプデータが「ダンプが終了した時の状態」ではなく 「ダンプを開始した時の状態」であるという点です。
引用: http://blog.seeds-std.co.jp/entry/3405.html

--single-transactionを付けることでその時点のデータをdumpし、その間もテーブルに対してinsertやupdateのロックがかからないようになるという理解で良さそう。

--

考察的な

オプション有りの場合はトランザクション分離モードはREPEATABLE READとなり、
オプション無しの場合はmysqlデフォルトのトランザクション分離モードとなる。
しかしmysqlのようなInnoDBだとデフォルトのトランザクション分離モードはREPEATABLE READなのでいよいよわからなくなってきた。

調べていたらトランザクション分離モードによりダーティリード 、ファジーリード、ファントムリードという３つの現象がそれぞれ起こったり起こらなかったりするみたい。

今後はこのあたりを調べていけば理解も深まりそうです。

最後にちょっとしたmysql5.7と5.6の違い

参考