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する。
-
データが少ないとすぐに処理が終わってしまうのでダミーデータを入れる。
-
今回はItem tableを作り、約390000レコード入れました。
-
オプションあり
- 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の違い
- あとになって調べてみると、mysql5.7からはmysqlpumpなるものが導入されたみたい。
- 厳密には5.7.8での導入。
- https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
参考
- mysqldump
- mysqlpump
- repeatable read
- table lock
- rails migrate InnoDB