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

(構造編)phpMyAdminを利用したエクスポートオプションと出力の関係

      2014/11/22

Google or AdMax Promotion(it)

phpMyAdminを利用してSQLのエクスポートを行う際に、
どのような設定でエクスポートすれば取り込みの際に、
安定して取り込むことができるでしょうか。
各設定の選択と出力されるSQLの各オプション設定をご紹介します。

はじめに

皆さんはphpMyAdminからエクスポートしたSQLソースで、
いざ取り込みを行った際に、エラーが出て取り込めなかった経験はないでしょうか。

新規のデータベースに取り込みを行う際の出力であったり、
既存のデータベース向けに出力する際では出力の方法が異なります。

しかし、日々のバックアップとバックアップデータの確認、
いざという時のリストア作業で異なるエクスポートファイルを使うのでは、
あまり効率的ではありません。

どのような設定でエクスポートすれば、
より汎用性の高いSQL文が生成されるのかを実際に、
各オプションでエクスポートして検証しましたのでご紹介します。

環境

  • OS: windows7 x64
  • Apache: 2.2.17 (Win32)
  • MySQL: 5.6.10
  • PHP: 5.2.17
  • phpMyAdmin: 3.4.2

phpMyAdminの出力オプション(構造)

phpMyAdminで構造の出力を選択し、
表示される出力オプションは以下のようなものがあります。

01_構造エクスポートオプション

構造エクスポートオプション

この各オプションをそれぞれONにした際に、どのようなSQL文が生成されるのかを検証します。
※上記で[構造]を選択しているのは検証用のパターンとしての選択です。
 ご利用のデータベースのでは通常通り[構造とデータ]を選択してください。
 移行先のデータベースにデータが移行されません。

検証パターン

各オプションがON状態を検証ケースに分けて以下のように分類し明記します。
それぞれのエクスポート結果と、インポートの結果を比較していきます。

構造エクスポートの設定と出力
CASE ON 備考
C01 DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT コマンドの追加
C02 CREATE PROCEDURE / FUNCTION / EVENT コマンドの追加
C03 CREATE TABLE オプション
C04  IF NOT EXISTS C03はOFF
C05  AUTO_INCREMENT C03はOFF
C06 C-01とC-02をともにON

パターン別の出力結果

上記の条件で各ケースを出力した結果、以下のようにSQL文が生成されます。

構造エクスポートの設定と出力
対象 処理 Option C01 C02 C03 C04 C05 C06 備考
TABLE DROP IF EXISTS
CREATE
IF NOT EXISTS
VIEW DROP TABLE IF EXISTS 代替構造
CREATE TABLE 代替構造
IF NOT EXISTS 代替構造
DROP VIEW IF EXISTS
CREATE VIEW
PROCEDURE DROP IF EXISTS
CREATE
FUNCTION DROP IF EXISTS
CREATE
EVENT DROP
CREATE

出力結果の考察

上記の出力結果を見ていくと、
当然ながらDROP ~ が出力されていない状態で、
CREATE ~ が生成されている場合が多く見受けられます。

その状態で、CREATE ~ IF NOT EXISTS のオプションが生成されていない場合は、
当然ながら、既に対象の構造が存在している場合には、エラーとなります。

ただし、EVENT要素に関してのCREATE・DROPでIF判定がありませんから、
既存のデータベースに対して取り込む際には常に注意が必要となります。

なお、イベントはその特性上、データベースに対してインポートを行うものではなく、
データベーススキーマ(インスタンス)に対して一意の名称で管理されます。

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

空のデータベースに対して、各ケースによって出力されたSQLの取り込みを行った結果、
以下のような取り込み結果が得られました。

結局のところ、C02のケース以外は、エクスポート元のデータベースに存在する構造から、
何かしらの欠損(不足)が発生する状態での移行になります。

普通に考えるとC06(一旦すべてを消し再作成を行う)のケースでは、
エラーが発生せず、新しくデータベースが移行できそうに思いますが、
EVENTのDROP処理でIF EXISTSがないことから、エラーとなってしまいます。
※EVENTのDROP行をSQLから削除すれば当然問題は解決できます。

よく言われるようにデータベースの移行では「移行先を一旦すべて消す必要がある」というのは、
C06のように移行を行った場合のことを示していることになりますが、
実際には新規データベースへの移行であれば、C02で事足ります。

データを消す必要があるかどうかは、データの移行方法の問題です。

 

構造インポート(新規)
CASE 処理結果 T V P F E メッセージ
C01 正常 インポートは正常終了しました。16 個のクエリを実行しました。 (C-01.sql)
C02 正常 インポートは正常終了しました。17 個のクエリを実行しました。 (C-02.sql)
C03 正常 インポートは正常終了しました。14 個のクエリを実行しました。 (C-03.sql)
C04 正常 インポートは正常終了しました。14 個のクエリを実行しました。 (C-04.sql)
C05 正常 インポートは正常終了しました。14 個のクエリを実行しました。 (C-05.sql)
C06 エラー × #1539 – Unknown event ‘delete_test’

※T:テーブル、V:ビュー、P:プロシージャ、F:ファンクション、E:イベント
●は正常に作成されたことを示す。-は処理されず生成されなかったことを示す。
×は処理過程でエラー終了を示す。

既存のデータベースへの取り込み結果(出力元への再取込)

出力元となった既存のデータベースに対して、
各ケースによって出力されたSQLの取り込みを行った結果、
以下のような取り込み結果が得られました。

結局のところ、C01とC06以外のケースでは、
設計要素が既に存在するとのことで正常に取り込みを行うことができませんでした。

C01では正常に処理が行われていますが、既存データベースであれば問題になりませんが、
このSQLを新規のデータベースに対して実行した場合には、
プロシージャ・ファンクション・イベントが存在しないデータベースとして移行されます。

C02、C03、C05では既存のテーブルの有無判定を行わずに、
CREATE TABLEを行ってしまっている為、既に存在しているとのエラーになります。

C04ではテーブルに関しては、IF NOT EXISTSで既存判定を行っているものの、
ビューに関してはDROPすることなく、CREATE VIEWを行ってしまっている為エラーになります。

C06ではすべての構造要素を一旦すべて削除したのちに新しく作成を行うため、
新規・既存を問わず再現が行えますが、一旦すべてを削除するというリスクが付きまといます。

構造インポート(既存)
CASE 処理結果 T V P F E メッセージ
C01 正常終了 インポートは正常終了しました。16 個のクエリを実行しました。 (C-01.sql)
C02 エラー × #1304 – PROCEDURE pc_test already exists
C03 エラー × #1050 – Table ‘test’ already exists
C04 エラー × #1050 – Table ‘view_test’ already exists
C05 エラー × #1050 – Table ‘test’ already exists
C06 正常終了 インポートは正常終了しました。22 個のクエリを実行しました。 (C-06.sql)

※T:テーブル、V:ビュー、P:プロシージャ、F:ファンクション、E:イベント
●は正しく処理が行われたことを示す ○は処理されず既存のままを示す。
×は処理過程でエラー終了を示す。

各処理結果フロー図

各ケースの処理結果をフローにすると以下のような図になります。
処理順序はphpMyAdminが出力するSQLの順序で処理が行われます。

02_処理フロー図1

処理フロー図1

03_処理フロー図2

処理フロー図2

このことから、一番汎用性の高いエクスポートの方法は、C06であることがわかります。

新規データベースへの取り込みを行う際には、
C06の処理ソースから、DROP EVENT ~文をコメントアウトして取り込むことで、
取り込みが行えることを示しています。

C06であれば、既存データベースへの取り込みも正常に終了します。

しかしながら、C06はデータベースをすべて削除する手法である為、
常にデータも一緒に出力を行うようなエクスポートをしておかなければ、
データのすべてが失われる結果となりますのでご注意ください。

では次にデータのエクスポートオプションについて確認してみます。
(データ編)phpMyAdminを利用したエクスポートオプションと出力の関係

wordpressに限って考えれば

とは言え、wordpressのデータベースバックアップという点に絞って考えた時には、
おそらく、プロシージャ・ファンクション・イベント・ビューは利用されていないでしょう。

この4構造を利用していないのであれば、
注意すべきはテーブルに対してのみの考慮で十分だといえます。

テーブルに対して一番安全性の高い処理でいえば、
C04のCREATE  TABLE IF NOT EXISTSによる処理だと思います。

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


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


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

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

Pitta Promotion

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

AdMax Promotion

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

Rakuten Promotion

プロモーション(btm)

Google or AdMax Promotion (it)

 - MySQL, phpMyAdmin ,

  関連記事