こんにちは。
さくさくTECHブロガーのさくです。
今回は、Linux + MySQL の環境において、シェルを使ってエクスポートとインポートをおこなう方法を紹介します。
どういった場面を想定しているかといいますと、一番は定期実行のバッチですね。
例えば、1日1回、特定テーブルのデータをエクスポートする、といった要件があった場合。
その場合、エクスポートするコマンドをシェル化してクーロンに設定すると思います。
そういった時に使える手法です。
インポートについては、例えば実データが壊れてしまった場合の復旧ですね。
エクスポートしたデータをインポートすれば、データは元通りになります。
環境情報
OS:Linux
DB:MySQL
MySQLには「saku」というDBを準備します。
そのDBには「person」というテーブルが存在します。
テーブル構成は以下。
mysql> desc person;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| personID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | MUL | NULL | |
| kana | varchar(128) | NO | | NULL | |
| address | varchar(512) | YES | | NULL | |
| gender | char(1) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
また、あらかじめデータも格納しておきます。
mysql> select * from person;
+----------+--------+--------------+-----------+--------+
| personID | name | kana | address | gender |
+----------+--------+--------------+-----------+--------+
| 4 | 山田 | ヤマダ | 東京 | 0 |
| 5 | 高橋 | タカハシ | 神奈川 | 0 |
| 6 | 田中 | タナカ | 千葉 | 1 |
+----------+--------+--------------+-----------+--------+
3 rows in set (0.00 sec)
このテーブルに対して、エクスポート、および、インポートをおこないます。
エクスポート
まずはエクスポート。
エクスポートする条件も指定します。
「gender」が「1」のレコードのみをエクスポートします。
シェルは以下になります。
#!/bin/sh
#
# エクスポートシェル
#
mysqldump -h localhost \--single-transaction --skip-opt --extended-insert --quick --set-charset --no-create-info --no-autocommit \
-u ※ユーザ名 -p※パスワード -h ※ホスト名 ※DB名 person \
--where="gender = '0'" \
| gzip > person.sql.gz 2>&1 | tee -a export.log
使用するコマンドは mysqldump コマンドです。
mysqldump コマンドで、「gender」が「1」のレコードのみをエクスポートします。
かつ、エクスポートするファイルはperson.sql.gzという圧縮ファイルとして出力し、エクスポート時のログをexport.logに出力します。
エクスポート結果を圧縮することで、仮に大量データをエクスポートすることになったとしても、ある程度はディスク使用率おさえることができます。
様々なオプションを指定していますが、各オプションの用途は以下になります。
オプション
|
説明
|
–single-transaction
|
これを設定しておくと、InnoDBについてエクスポートする際、他SQLがロックされないです。運用中システムについてエクスポートする際は、このオプションを指定した方が無難。
|
–skip-opt
|
「–opt」の設定をオフにします。
「–opt」の設定の中に「–add-locks」がありますが、これが有効だとエクスポート中にテーブルがロックされてしまう可能性があります。
それを防ぐために、–skip-optを設定して、「–opt」の設定をオフにします。
|
–extended-insert
|
出力されるダンプファイルについて、バルクインサートの形でSQLを作成します。
|
–quick
|
MySQLのマニュアルをみる限りでは、大量データをエクスポートする際はつけた方がいいみたいです。意味を理解しきれませんでしたが、たぶん、メモリ上にエクスポートデータを展開するタイミングの話のようです。このオプションを指定した方が、より高速なやり方を実施するようです。
|
–set-charset
|
出力するエクスポートするファイルに SET NAMES default_character_set を出力します。
|
–no-create-info
|
エクスポートするファイルに CREATE文を出力しません。
|
–no-autocommit
|
エクスポートするファイルに AutoCommitをオフにします。
具体的には、エクスポートするファイルのSQL先頭に「set autocommit=0;」を出力します。
かつ、最後に「commit;」を出力します。
|
エクスポートはこんな感じです。
エクスポートが完了するとperson.sql.gzというファイルが作成されます。
インポート
次にインポート。
インポートは、エクスポートシェルで作成したperson.sql.gzを解凍して、解凍したファイルを参照してインポートします。
シェルは以下になります。
#!/bin/sh
#
# インポートシェル
#
gunzip -d person.sql.gz >> insert.sql
mysql -u ※ユーザ名 -h ※ホスト名 -D ※DB名 -p※パスワード < insert.sql 2>&1 \ | tee -a import.log
まずは guzip コマンドで解凍して、「insert.sql」を出力。
gunzipに-d オプションをつけているので、元ファイルの「person.sql.gz」を削除して「insert.sql」に解凍結果を出力。
インポート自体はmysqlコマンドで実行しています。
リダイレクト「<」を指定して、解凍したSQLファイルをそのままインポートします。
まとめ
いかがでしたでしょうか?
今回はたった3行しか入っていないテーブルに対してのエクスポートなので性能は意識する必要はないのですが、数億件のエクスポートする場合は性能が大事になります。
紹介したオプションを指定すれば、極力、サーバ性能に沿ったパフォーマンスを出せると思っています。
参考にしてください。
それではまた!