phpMyAdminを利用したMySQLデータのエクスポート

2013/09/17 2014/11/22

phpMyAdminを利用したMySQLデータのエクスポート方法についてご紹介します。
またphpMyAdminでは通常うまく出力できないような、
大容量データのエクスポートについてもご紹介します。

はじめに

ブログなどを運営していて、常に頭の痛いデータベースのバックアップですが、
MySQLのデータベースをエクスポートするツールとして、
代表的なものはphpMyAdminではないでしょうか。

レンタルサーバーなどでも最初からインストールされていたり、利用を推奨されていたり、
phpMyAdminなどのWeb上の管理ツールしか認められていないことも多いでしょう。

そんな代表的なphpMyAdminですから、
エクスポート方法など検索すればいくらでも出てきます。

しかし、そのエクスポートデータは、本当にデータベースすべての情報を出力されていますか？

バックアップが目的だからと出力して、それで安心していませんか？
出力データが欠損していたら・・・いざリストア(インポート)してみて、
データが足りない場合だって起こり得りますよ？

その対応策についてはこちらでご紹介しています。
phpMyAdminでMySQLエクスポートが途中で切れる場合の対応策

phpMyAdminを利用したエクスポートの単位

phpMyAdminの操作を行っていて、たまに迷ってしまうことが、
最初のころにあったのですが、phpMyAdminの上部に表示される、
パンくずリストを意識して操作する必要があります。

phpMyAdminのパンくずリスト

エクスポート単位の指定

このパンくずリストに表示される現在操作対象となっている対象に応じて、
エクスポート対象の表示も異なっています。

ホスト単位のエクスポート

まず、現在ログインしているホストを選択した状態のエクスポートでは、
ログインした画面から、そのままエクスポートをクリックするとホスト全体のエクスポートが行えます。

ホスト選択状態からのエクスポート指定

エクスポート画面では[詳細]をクリックすると、
出力対象のデータベースが選択肢に表示され選択できるようになります。

ホスト選択状態からのエクスポート画面

データベース単位のエクスポート

ホスト表示の画面より、対象のデータベースを選択した状態のエクスポートでは
対象のデータベース内のテーブル単位によるエクスポートが行えます。

データベース選択状態からのエクスポート指定

エクスポート画面では[詳細]をクリックすると、
出力対象のテーブルが選択肢に表示され選択できるようになります。

データベース選択状態からのエクスポート画面

テーブル単位によるエクスポート

データベース表示の画面より、対象のテーブルを選択した状態のエクスポートでは、
対象のテーブル内のデータ単位によるエクスポートが行えます。

テーブル選択状態からのエクスポート指定

エクスポート画面では[詳細]をクリックすると、
出力対象とするデータ指定の条件が表示され指定できるようになります。

テーブル選択状態からのエクスポート画面

エクスポートオプションの指定

エクスポート方法

エクスポート方法の指定には[簡易／詳細]の選択肢の指定があります。
簡易では、あらかじめ設定してある出力方法に基づいてエクスポートが行われます。

エクスポート方法

あらかじめ設定とは、ホスト指定の画面から[設定]タブをクリックし、
[エクスポート]をクリックすることで表示されるオプション指定です。
ここでエクスポート共通の設定と出力フォーマット固有の出力オプションを、
設定しておくことができます。

これによって[簡易]を選択し出力した場合には、事前設定のオプションで出力されます。

設定のエクスポートオプション

[詳細]を選択することで事前設定のオプションから変更を行うことができるように、
オプションが表示されます。

出力

出力のオプションでは、エクスポート結果をファイルで保存するか、
ブラウザの画面上に表示するかを指定することができます。

出力

キャプチャ上に表示されている「サーバー上に保存する」オプションに関しては、
phpMyAdminの設定を行うことによって表示されるようになります。
設定方法については以下でご紹介しています。
phpMyAdminでMySQLデータをサーバー上にエクスポートする

フォーマット

フォーマットの指定は通常はSQLを利用されていることでしょう。
私もほとんどSQLしか利用していませんが、この出力形式には様々な形式が用意されています。
参考までにテストデータを出力可能な形式で出力したものをご紹介しています。

フォーマット

phpMyAdminを利用したMySQLデータのエクスポート形式一覧とデータ

フォーマット特有のオプション

さまざまな出力フォーマットごとに出力のオプションが選択できます。

フォーマット特有オプション(SQL)

生成オプション

エクスポートをホスト単位から指定し、データベース単位で処理を行う場合には、
[フォーマット特有のオプション]に対して、
[DROP DATABASE コマンドの追加]オプションが表示されます。

この選択によって、エクスポートしたSQLに対して、
インポートを行う際に、既存のデータベースを削除してから、
データベースのインポートが行われるようなSQLを出力することができます。
怖いですけどね・・・

生成オプション(SQL)

データダンプのオプション

データダンプのオプションでは、データ生成に関わるSQL文に対しての記述を指定します。
通常のINSERT文を利用する代わりに利用する記述などを行うことができます。

データのダンプオプション(SQL)

INSERTコマンドの代わりに以下を使う

通常のSQL生成ではINSERT文が利用されますが、
以下のオプションを利用することでオプションを出力することができます。

INSERT INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

INSERT DELAYEDコマンド

データの挿入に対してINSERTではなくINSERT DELAYEDと記述しSQLが出力されます。

INSERT DELAYED INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT DELAYED INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

とは言え、通常MySQLでテーブルの作成を行うと、InnoDB利用していると思います。
~~ISAMまたは、MyISAMのみで有効とありますので、あまり利用の機会はないかもしれません。~~
phpMyAdminにて通常作成されるテーブルや、
wordpressにて作成されるテーブルはMyISAMのようなので効果はあるかもしれません。
ただ作成されているテーブルの種別は確認する必要があります。

INSERT DELAYED 構文（抜粋）

INSERT DELAYED は、ISAM および MyISAM テーブルに対してのみ作用します。注意: MyISAM テーブルでは、データファイルの中央に空きブロックがない場合、同時 SELECT と同時 INSERT が可能なため、MyISAM テーブルで INSERT DELAYEDを使用する必要はほとんどありません。 See 項7.1. 「MyISAM テーブル」。

INSERT DELAYED を使用すると、クライアントは直ちに処理の続行を許可されます。そして、テーブルが別のスレッドによって使用されていなければ、レコードが挿入されます。

INSERT IGNOREコマンド

データの挿入に対してINSERTではなくINSERT IGNOREと記述しSQLが出力されます。

INSERT IGNORE INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT IGNORE INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

これによって、データ挿入の実行時エラーが発生した際に、
発生したエラーを警告として処理できるようになります。
データの精度が低下する可能性はありますが、
どうしても取り込みのエラーを回避したい場合には利用する価値はあるかもしれません。

INSERT 構文（抜粋）

もし IGNORE キーワードを利用したら、INSERT ステートメントの実行中に起きたエラーは警告として扱われます。例えば、IGNORE が無いと、テーブル内に既存の UNIQUE インデックスや PRIMARY KEY 値を複製する行に複製キーエラーが起き、そのステートメントは異常終了されます。IGNORE を利用すると、行の挿入はされませんが、エラーも発行されません。もし IGNORE が指定されなければ、データ変換はステートメントに関するエラーを引き起こします。IGNORE を利用すると、不正な値は一番近い値に調節されて挿入され、警告が発生されますがステートメントは異常終了しません。いくつのテーブルが実際に挿入されたのか、mysql_info() C API 関数を利用して調べる事ができます。

データをダンプするときに使うコマンド

データをダンプするときに使うコマンドでは、
以下のようにINSERT,UPDATE,REPLACEが選択できます。

データのダンプオプション(SQL)コマンド選択

INSERT 指定

INSERT指定時には、DELAYED、 IGNOREオプションが有効になります。

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

UPDATE 指定

UPDATE指定時には、IGNOREオプションのみが有効になります。
なおUPDATE時には複数行挿入は利用されません。

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;
UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;
UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;
UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;

UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;

UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;

UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

REPLACE 指定

REPLACE指定時には、IGNOREオプションのみが有効になります。

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

データを挿入するときに使う構文

データを挿入するときに使う構文ではINSERT文の記述方式が選択できます。
この辺は、インポートを行う先のデータベースと相性の良いSQL文で出力するといいと思います。

なお以下は、DELAYED、IGNOREオプションは双方ONで出力しています。

全ての INSERT コマンドにカラム名を含める

例: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)

この設定では例の通り、カラム名を含む単一行挿入SQLが生成されます。

INSERT

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(1, '2013-09-16');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(2, '2013-09-17');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(3, '2013-09-15');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(1, '2013-09-16');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(2, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(3, '2013-09-15');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES(4, '2013-09-17');

UPDATE

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;
UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;
UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;
UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;

UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;

UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;

UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

REPLACE

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(1, '2013-09-16');
REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(2, '2013-09-17');
REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(3, '2013-09-15');
REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(4, '2013-09-17');

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(1, '2013-09-16');

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(2, '2013-09-17');

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(3, '2013-09-15');

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES(4, '2013-09-17');

全ての INSERT コマンドを複数行挿入で行う

例: INSERT INTO tbl_name VALUES (1,2,3), (4,5,6), (7,8,9)

この設定では例の通り、カラム名を含まない複数行挿入SQLが生成されます。

INSERT

INSERT DELAYED IGNORE INTO `test` VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

UPDATE

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;
UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;
UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;
UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;

UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;

UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;

UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

REPLACE

REPLACE DELAYED INTO `test` VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

REPLACE DELAYED INTO `test` VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

上の両方を行う

例: INSERT INTO tbl_name (col_A,col_B) VALUES (1,2,3), (4,5,6), (7,8,9)

この設定では例の通り、カラム名を含む複数行挿入SQLが生成されます。

INSERT

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

UPDATE

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;
UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;
UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;
UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;

UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;

UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;

UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

REPLACE

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15'),
(4, '2013-09-17');

REPLACE DELAYED INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17'),

(3, '2013-09-15'),

(4, '2013-09-17');

上のどちらでもない

例: INSERT INTO tbl_name VALUES (1,2,3)

この設定では例の通り、カラム名を含まない単一行挿入SQLが生成されます。

INSERT

INSERT DELAYED IGNORE INTO `test` VALUES(1, '2013-09-16');
INSERT DELAYED IGNORE INTO `test` VALUES(2, '2013-09-17');
INSERT DELAYED IGNORE INTO `test` VALUES(3, '2013-09-15');
INSERT DELAYED IGNORE INTO `test` VALUES(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` VALUES(1, '2013-09-16');

INSERT DELAYED IGNORE INTO `test` VALUES(2, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` VALUES(3, '2013-09-15');

INSERT DELAYED IGNORE INTO `test` VALUES(4, '2013-09-17');

UPDATE

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;
UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;
UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;
UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

UPDATE IGNORE `test` SET `col1` = 1,`col2` = '2013-09-16' WHERE `test`.`col1` = 1;

UPDATE IGNORE `test` SET `col1` = 2,`col2` = '2013-09-17' WHERE `test`.`col1` = 2;

UPDATE IGNORE `test` SET `col1` = 3,`col2` = '2013-09-15' WHERE `test`.`col1` = 3;

UPDATE IGNORE `test` SET `col1` = 4,`col2` = '2013-09-17' WHERE `test`.`col1` = 4;

REPLACE

REPLACE DELAYED INTO `test` VALUES(1, '2013-09-16');
REPLACE DELAYED INTO `test` VALUES(2, '2013-09-17');
REPLACE DELAYED INTO `test` VALUES(3, '2013-09-15');
REPLACE DELAYED INTO `test` VALUES(4, '2013-09-17');

REPLACE DELAYED INTO `test` VALUES(1, '2013-09-16');

REPLACE DELAYED INTO `test` VALUES(2, '2013-09-17');

REPLACE DELAYED INTO `test` VALUES(3, '2013-09-15');

REPLACE DELAYED INTO `test` VALUES(4, '2013-09-17');

作成するクエリの最大長

作成するクエリの最大長では、複数行挿入SQLなどが指定の最大長よりも長くならないように、
適宜調整が行われるオプションです。

ただし、絶対に指定の長さを超えないという設定ではなく、
最低限必要なクエリ長は確保され分割されます。

以下は、データの挿入に関して「上の両方を行う」を選択し、
複数行挿入が有効なオプションで出力をしたものですが、最大長に[10]を指定しています。
複数行出力の形態でSQL生成されていますが、都度INSERTが出力されています。

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(2, '2013-09-17');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(3, '2013-09-15');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(2, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(3, '2013-09-15');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(4, '2013-09-17');

以下は同様に最大長に[100]を指定しています。
複数行出力の形態でSQL生成されていますが、２レコード単位でINSERTが出力されています。

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(3, '2013-09-15'),
(4, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(1, '2013-09-16'),

(2, '2013-09-17');

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES

(3, '2013-09-15'),

(4, '2013-09-17');

このことから、この数値はSQL１単位の概ねバイト数を示しています。
１行目: 57 byte + ２行目: 18 byte + ３行目: 18 byte ＝ 93 byteです。

これを[109]に変更すると以下のように３行まで単一SQLで処理が行われます。

※SQLの合計文字数は111 Byteですが、何故か109 byteを
指定したところから３行まで纏められる結果となりました。

INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(1, '2013-09-16'),
(2, '2013-09-17'),
(3, '2013-09-15');
INSERT DELAYED IGNORE INTO `test` (`col1`, `col2`) VALUES
(4, '2013-09-17');