頑張らない!でも諦めない!必死にならずにええかげん

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

      2014/11/22

Google or AdMax Promotion(it)

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

はじめに

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

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

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

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

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

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

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

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

01_phpMyAdminのパンくずリスト

phpMyAdminのパンくずリスト

エクスポート単位の指定

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

エクスポート方法

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

17_エクスポート方法

エクスポート方法

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

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

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

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

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

出力

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

09_出力

出力

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

フォーマット

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

10_フォーマット

フォーマット

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

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

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

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

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

生成オプション

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

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

12_生成オプション(SQL)

生成オプション(SQL)

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

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

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

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

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

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

INSERT DELAYEDコマンド

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

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

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

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

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

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

INSERT 指定

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

UPDATE 指定

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

REPLACE 指定

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

 

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

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

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

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

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

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

INSERT

UPDATE

REPLACE

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

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

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

INSERT

UPDATE

REPLACE

上の両方を行う

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

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

INSERT

UPDATE

REPLACE

上のどちらでもない

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

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

INSERT

UPDATE

REPLACE

 

 作成するクエリの最大長

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

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

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

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

このことから、この数値はSQL1単位の概ねバイト数を示しています。
1行目: 57 byte + 2行目: 18 byte + 3行目: 18 byte = 93 byteです。

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

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

誤差というか振れはあるようですが、概ねの目安として利用できます。

 16進表記でバイナリカラムをダンプする(例えば、”abc” を 0x616263)

これに関してはバイナリカラムの値を16進数に置き換えてエクスポートする設定のようです。
この機能についての詳細は割愛させていただきます。

私は、通常このオプションは外してエクスポートしています。

TIMESTAMP のカラムを UTC(協定世界時)でダンプする
(ダンプするサーバと読み込ませるサーバのタイムゾーンが異なる場合に有効です。)

これに関してはタイムスタンプ値のタイムゾーン変換を行うオプションのようですが、
この機能についても詳細は割愛させていただきます。

私は、通常このオプションは外してエクスポートしています。

エンコーディングへの変換

データダンプ時にエンコードの変換の必要があれば指定します。

15_エンコーディングの変換

エンコーディングの変換

エクスポートの実行

上記の設定を確認し、問題がなければ最下部の[実行]をクリックします。
出力のオプションで指定した方式でファイルが生成され保存されます。

16_エクスポートの実行

エクスポートの実行

プロモーション(btm)

Google or AdMax Promotion (it)

さいごに

エクスポート自体は簡単に行えますが、
細かな設定を確認していくとどのようなオプションを利用して出力するといいのかの、
検討材料になると思います。

さまざまなオプションを試し、用途に合った設定を見つけていけばいいと思います。

MySQLはOracle Inc.による著作物です。
MySQLはFree Software Foundation, Inc.によってGPL Licenseでライセンスされています。

phpMyAdminはphpMyAdmin devel teamによる著作物です。
phpMyAdminはGNU General Public License, version 2.によってライセンスされています。

*本ページ内に記載されされた各社ブランド、サービス名、商標、登録商標については、
各社ブランドのロゴや商標等に関する帰属についてをご確認ください


最後までお読みいただきありがとうございました。
アルゴリズン


役に立ったと思っていただけたら、以下からシェア頂けたら幸いです^^

QRコードからもこのURLを開けます。

Pitta Promotion

アイキャッチ画像に利用させて頂いております、各社ブランドのロゴやシンボルに関しては、
各社ブランドのロゴや商標等に関する帰属についてをご確認頂けますようお願い致します。
ロゴの使用等に関する苦情・ご意見等がございましたら管理者までご連絡ください。

AdMax Promotion

カテゴリ
すべて展開 | すべて省略

Rakuten Promotion

 - MySQL, phpMyAdmin ,

  関連記事