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

phpMyAdminを利用したエクスポート・インポートで最も適した設定とは?

      2014/11/22

Google or AdMax Promotion(it)

wordpressのバックアップやリストア作業にとって欠かせない、MySQLのエクスポートとインポート。
インポートする時のことを考えてエクスポートしておくには、
phpMyAdminからどのような設定で、エクスポートをするのが一番なのかご紹介します。

はじめに

phpMyAdminでデータベースのバックアップをして、
エクスポートさえしておけばインポートもそのままできると思っていたら、
いざ取り込んでみたら、そのままのSQLでは取り込めなかった経験はありませんか?

またエクスポートして安心してしまって、
実際にそのファイルをインポートさえしたことない人もいるかもしれませんね。

基本的にphpMyAdminからエクスポートしたSQLファイルは、単なるSQLファイルです。

何かがエラーとなったとしても、
やる気になればSQLの構文を置き換えたり、消したりすることで、
ちゃんと取り込めるようなSQL文に修正することができます。

いつでもゆっくり作業はできない

しかし、インポートが必要なケースは、
必ずしもゆっくり時間のとれる引っ越しや移行といった場合だけではないでしょう。

一番緊迫した状態がサーバー障害によるデータ損失です。
一刻も早く別のサーバー上にサイトを復旧し、
DNSを振り替えてあげないと、貴重なアクセスを失うことになります。

障害でアクセスができない状態が続くとどうなるか?以下でご紹介しています。
DNS障害の被害と影響の甚大さを痛感いたしました

そんな一刻を争うときに、エクスポートしたSQLがそのまま取り込めなかったら、
正直、汗がたらーーっと流れ落ち、どうしたものかと途方に暮れてしまいます。

今回はインポートする際のことを考えて、どのような設定でエクスポートすることが、
もっともインポートの際に手間をかけずにインポートができ、
日々の運用時に開発環境などにデータを取り込んでいけるのかをご紹介します。

環境

今回の環境は以下の環境で行っています。
サーバー側はUnix環境ですが、エクスポート元は一旦下記環境にインポート後に作業しています。

  • OS: Windows7 x64
  • Apache: 2.2.17
  • PHP: 5.2.17
  • MySQL: 5.6.10
  • phpMyAdmin: 3.4.2

エクスポートの方法

phpMyAdminを利用したエクスポートには様々な種類がありますが、
皆さんはどのような形式でエクスポートされていますか?

様々な形式のエクスポート方法については以下の投稿でご紹介しています。
phpMyAdminを利用したMySQLデータのエクスポート形式一覧とデータ

phpMyAdminを利用したエクスポート方法については、以下の投稿でご紹介しています。
phpMyAdminを利用したMySQLデータのエクスポート

基本的にエクスポートの形式はSQLによるエクスポートでしょう。
この方法は、エクスポートとインポートでテーブル構造やデータの型などで、
移行先おけるデータ再現性がもっとも高く安定しています。

しかし、既にテーブルが存在している場合や、
プライマリーキーの重複などデータの状態とSQL文の組み立てによっては、
そのまま取り込めない場合にも直面します。

インポートの方法

phpMyAdminでエクスポートしたファイルを再度インポートする際に、
正直、インポートでエラーにならないことをいつも祈ってしまいます。

結構、取り込めると思ってたファイルが取り込めず、
エラーが表示されることって多いんですよね。

でもこれ、エクスポートした際のオプション選択の時点で、
エラーになることは決まっていたんです。(目的によってですが)

そう、再びエラーにならないように出力条件を指定すれば、簡単に取り込めます。

様々な形式のインポート方法については以下の投稿でご紹介しています。
phpMyAdminを利用したMySQLデータのインポート形式一覧とデータ

インポート時点でエラーで既に手遅れも

でも、インポートが本当に必要なときは、
エクスポート元のデータが・・・既になかったりしますよね^^;
だって、復旧したくてインポートしてるんですし、引っ越しならまだしも。

そう、このエラーになってしまう記述でエクスポートしていると、
いざインポートとなった時に、手間がかかります。

この手間を減らすために、あらかじめ設定した出力設定で常にバックアップしよう。

そう思って、この投稿を書いています。

phpMyAdminがエクスポートの選択肢

それではphpMyAdminが各設定によって、
どのようなSQL文を生成してくるのかということですが、
phpMyAdminのエクスポート(SQL)の画面では以下のようなオプションが表示されます。

エクスポート対象を選択するオプション

エクスポート対象を指定するオプションの選択では、
以下のような選択肢がエクスポート画面に表示されています。

01_SQLオプション

SQLオプション

ダンプするテーブル
  • 構造
  • データ
  • 構造とデータ

このオプションでは記述の通り、
テーブルの構造のみをエクスポートする、テーブルデータのみをエクスポートする
その両方をエクスポートするが選択できます。

新規のデータベースでテーブルが存在していないような場合(移行時)などには、
[構造とデータ]を選択することで、
1回のSQL実行でテーブルの作成とデータのインポートが行われます。

しかしながら、既にテーブルなどの構造が存在している際には、
既に存在しているテーブルと同じ名前で作成する(重複)ことはできませんので、
当然ながら作成に失敗します。

#1050 - Table 'wp_login_fails' already exists

02_インポートエラーの例

インポートエラーの例

その回避策のオプションとして、データベース構造に関するオプションが用意されています。
このオプションはデフォルトではONになっているはずですが。

データベース構造に関わるオプション

データ構造に関するオプションは以下の部分で設定を行います。

03_生成オプション

生成オプション

生成オプション

追加コマンド

  • DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT コマンドの追加
  • CREATE PROCEDURE / FUNCTION / EVENT コマンドの追加
  • CREATE TABLE オプション
    • IF NOT EXISTS
    • AUTO_INCREMENT

この生成オプションの設定によって、既にテーブルが存在する場合の振る舞いや、
テーブルなどの設計要素を一旦削除するなどの設定が行えます。

生成オプションの設定によってどのようなSQL文が組み立てられ、
新規のデータベースと、既存データベースに対して取り込んだ際に、
そのまま取り込むことができる出力方法などを検討した結果は以下の投稿でご紹介しています。
(構造編)phpMyAdminを利用したエクスポートオプションと出力の関係

データベースデータに関わるオプション

データに関するオプションは以下の部分で設定を行います。

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

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

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

追加コマンド

  • INSERT DELAYED コマンド
  • INSERT IGNORE コマンド
  • データをダンプするときに使うコマンド
  • データを挿入するときに使う構文
    • 全ての INSERT コマンドにカラム名を含める
    • 全ての INSERT コマンドを複数行挿入で行う
    • 上の両方を行う
    • 上のどちらでもない

このデータのダンプオプションの設定によってどのようなSQL文が組み立てられ、
新規のデータベースと、既存データベースに対して取り込んだ際に、
そのまま取り込むことができる出力方法などを検討した結果は以下の投稿です。
(データ編)phpMyAdminを利用したエクスポートオプションと出力の関係

汎用的かつ安全(だと思う)出力設定

それぞれの出力オプションを検討してみた結果、
私的には以下のような条件でエクスポートデータが存在していることが、
ローカル環境への反映とバックアップを兼ねることができて便利かなと思うところです。

  1. 出力したSQLにてエクスポート時点のデータベースを新規に作成できる。
  2. SQLを利用してローカル環境の既存データベースに取り込みが行える。

結論としては同時に両方を満たす出力方法はありませんでした。

というのも、既存のデータベースの構造とデータをすべて一旦削除し、
再度データ(レコード)を生成するというエクスポート方法はあります。

おそらくこの方法が一番利用されている方法だと思います。

しかし、この方法はデータベースの構造(テーブルなど)を削除したのちに、
再度、構造(テーブルなど)を作成し、次にデータを再作成します。

一瞬でもデータベースにテーブルやデータが存在しない状態となります。
この方式での本番環境へのリストア作業は少し抵抗があります。

その為「最終的にはこの方式をとることができる」ものの、
「通常のインポート作業ではデータの削除は行わない」方法を模索しました。

一部、SQL文の修正が必要にはなりますが、
以下でご紹介する方式が一番安全かなと思うところです。

エクスポートの設定

ダンプするテーブル

フォーマット特有のオプションでは[構造とデータ]を選択します。

05_指定:フォーマット特有のオプション

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

生成オプション

生成オプションではチェックボックスすべてをONで選択します。

06_指定:生成オプション

指定:生成オプション

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

データのダンプオプションではデータをダンプするときに使うコマンドに[REPLACE]を選択します。
特に変更する必要はありませんが、クエリ長は[5000]程度までに下げておきます。

なお、クエリ長の変更がどのような影響があるのかは、以下でご紹介しています。
phpMyAdminを利用したMySQLデータのエクスポート

07_指定:データのダンプオプション

指定:データのダンプオプション

出力したSQL

以下のSQLは、エクスポート元データベースに対して、
プロシージャ、ファンクション、テーブル、ビュー、イベントと、
すべての構造が存在している場合の出力例です。

もしテーブルしか存在しない場合には、その他の処理は出力されません。

インポート前のSQL文処理

新規データベースへ取り込む際と既存データベースへ取り込む際で、
それぞれSQL文の1行を削除(コメントアウト)します。

新規データベースへの取り込み

今回出力したSQL文をそのまま新規のデータベースへ取り込みを行うと、
DROP EVENT(89行)でエラーとなってしまいます。
DROP EVENTをコメントアウトし実行することで、
新規データベースへの構造とデータの取り込みが行われます。

これはDROP EVENT関数にIF EXISTSの判定が行われないためです。

当然ながら。データベース(EVENTはインスタンス単位)に対して、
EVENTの設計要素を持たない場合には、まったく考える必要はありません。

既存データベースへの取り込み

今回出力したSQL文をそのまま既存データベースへの取り込みを行っても、
正常にデータベースのインポートが行われますが、
テーブルにデータが存在しているテーブルも一旦削除されてしまう為、
DROP TABLE(49行)をコメントアウトし実行することで、
既存データを削除することなくデータの更新が行われます。

そのまま処理しても正しくインポートは行われますが、
安全を考慮するならばDROP TABLEは避けたいところです。

わざわざ大量のテーブルに対してのDROP TABLEをコメントするのが面倒かと思いますが、
[DROP TABLE]を[– DROP TABLE ]で一括置換してしまえば、対応は完了です。 

SQLの修正と処理の概要

どちらのインポートに対しても、
1つのエクスポートファイルによって対応が行えるようになりますので、
普段の開発環境への更新処理とバックアップを兼ねることができるようになります。

SQLの処理を図示すると以下のようになります。

08_SQLの修正と処理の概要

SQLの修正と処理の概要

インポート前とインポート後の結果(参考)

新規データベースへの取り込み
取り込み前の状態

取り込み前のデータベースの状態は以下のような状態で、
設計要素は何も存在していません。

09_取込対象[新規]データベースの処理前状態

取込対象[新規]データベースの処理前状態

処理結果

新規データベースへの取り込みでは、上記のとおりDROP EVENT行をコメントアウトします。
-- DROP EVENT delete_test$$

処理結果は以下のように表示されます。
インポートは正常終了しました。21 個のクエリを実行しました。 (BestSetting_forNew.sql)

10_取込対象[新規]データベースの処理結果

取込対象[新規]データベースの処理結果

取り込み後の状態

取り込み後の状態は以下のとおりです。
テーブル、ビュー、プロシージャ、ファンクション、イベント、テーブルデータの
すべてが正常に取り込まれています。

11_取込対象[新規]データベースの処理後状態

取込対象[新規]データベースの処理後状態

既存データベースへの取り込み
取り込み前の状態

取り込み前のデータベースの状態は、
テーブル、ビュー、プロシージャ、ファンクション、イベントが設計要素として存在し、
テーブルデータは2行のみ存在しています。
また2行のうち1行(pkey:4)は値が古い値になっています。

12_取込対象[既存]データベースの処理前状態

取込対象[既存]データベースの処理前状態

処理結果

新規データベースへの取り込みでは、上記のとおりDROP TABLE行をコメントアウトします。
-- DROP TABLE IF EXISTS test;

処理結果は以下のように表示されます。
インポートは正常終了しました。21 個のクエリを実行しました。 (BestSetting_forExists.sql)

13_取込対象[既存]データベースの処理結果

取込対象[既存]データベースの処理結果

取り込み後の状態

取り込み後の状態は以下のとおりです。
テーブル、ビュー、プロシージャ、ファンクション、イベントが正しく再作成され、
テーブルデータは2行追加されています。
また既存データ1行の古い情報も新しい情報で更新されています。

14_取込対象[既存]データベースの処理後状態

取込対象[既存]データベースの処理後状態

プロモーション(btm)

Google or AdMax Promotion (it)

さいごに

phpMyAdminを利用したデータベースのエクスポート・インポートにおいて、
常にすべてを削除して、
新たに構造とデータをすべて再作成を行う方法が主流であるかのように言われていますが、
データの削除を一瞬でも行ってしまうということは、リスクの高い処理です。

今回のように設計要素の削除と再作成は、ファンクション、プロシージャ、ビュー、イベントに留め、
テーブルは削除しないことで、より安全な移行が可能になります。

今回の検証パターンでは明記していませんが、
インポート先に存在しないテーブル(プラグインなどで追加された場合)がある場合に、
エクスポートしたSQLから正常にテーブルが追加されます。

ただし、既存のテーブルにカラム(列)を追加したような改修をしたテーブルから、
エクスポートを行った場合には、対象のテーブルは削除を行う必要があります。

結局、面倒くさくて一旦すべてを削除して、
インポートという方法をとってしまうかもしれませんが、
削除をする必要なくインポートができると知っているだけでも何かの役には立つと思います。

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 ,

  関連記事