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

2013/09/28 2014/11/22

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)の画面では以下のようなオプションが表示されます。

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

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

SQLオプション

ダンプするテーブル

構造
データ
構造とデータ

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

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

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

#1050 - Table 'wp_login_fails' already exists

インポートエラーの例

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

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

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

生成オプション

追加コマンド

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

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

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

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

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

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

追加コマンド

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

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

汎用的かつ安全（だと思う）出力設定

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

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

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

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

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

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

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

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

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

エクスポートの設定

ダンプするテーブル

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

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

生成オプション

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

指定：生成オプション

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

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

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

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

出力したSQL

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

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

-- phpMyAdmin SQL Dump
-- version 3.4.2
-- http://www.phpmyadmin.net
--
-- ホスト: localhost
-- 生成時間: 2013 年 9 月 25 日 13:24
-- サーバのバージョン: 5.6.10
-- PHP のバージョン: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- データベース: `test`
--

DELIMITER $$
--
-- プロシジャ
--
DROP PROCEDURE IF EXISTS `pc_test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pc_test`()
BEGIN
Select count(*) from test;
END$$

--
-- 関数
--
DROP FUNCTION IF EXISTS `func_test`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_test`() RETURNS int(11)
BEGIN
 RETURN 1;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- テーブルの構造 `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `col1` int(5) NOT NULL AUTO_INCREMENT,
  `col2` date DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB  DEFAULT CHARSET=armscii8 AUTO_INCREMENT=5 ;

--
-- テーブルのデータをダンプしています `test`
--

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

-- --------------------------------------------------------

--
-- ビュー用の代替構造 `view_test`
--
DROP VIEW IF EXISTS `view_test`;
CREATE TABLE IF NOT EXISTS `view_test` (
`count(*)` bigint(21)
);
-- --------------------------------------------------------

--
-- ビュー用の構造 `view_test`
--
DROP TABLE IF EXISTS `view_test`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS select count(0) AS `count(*)` from `test`;

DELIMITER $$
--
-- イベント
--
DROP EVENT `delete_test`$$
CREATE DEFINER=`root`@`localhost` EVENT `delete_test` ON SCHEDULE EVERY 3 DAY STARTS '2013-09-20 18:49:07' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM `test`$$

DELIMITER ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- phpMyAdmin SQL Dump

-- version 3.4.2

-- http://www.phpmyadmin.net

-- ホスト: localhost

-- 生成時間: 2013 年 9 月 25 日 13:24

-- サーバのバージョン: 5.6.10

-- PHP のバージョン: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

-- データベース: `test`

DELIMITER $$

-- プロシジャ

DROP PROCEDURE IF EXISTS `pc_test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pc_test`()

BEGIN

Select count(*) from test;

END$$

-- 関数

DROP FUNCTION IF EXISTS `func_test`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `func_test`() RETURNS int(11)

BEGIN

RETURN 1;

END$$

DELIMITER ;

-- --------------------------------------------------------

-- テーブルの構造 `test`

DROP TABLE IF EXISTS `test`;

CREATE TABLE IF NOT EXISTS `test` (

`col1` int(5) NOT NULL AUTO_INCREMENT,

`col2` date DEFAULT NULL,

PRIMARY KEY (`col1`)

) ENGINE=InnoDB DEFAULT CHARSET=armscii8 AUTO_INCREMENT=5 ;

-- テーブルのデータをダンプしています `test`

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

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

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

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

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

-- --------------------------------------------------------

-- ビュー用の代替構造 `view_test`

DROP VIEW IF EXISTS `view_test`;

CREATE TABLE IF NOT EXISTS `view_test` (

`count(*)` bigint(21)

);

-- --------------------------------------------------------

-- ビュー用の構造 `view_test`

DROP TABLE IF EXISTS `view_test`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS select count(0) AS `count(*)` from `test`;

DELIMITER $$

-- イベント

DROP EVENT `delete_test`$$

CREATE DEFINER=`root`@`localhost` EVENT `delete_test` ON SCHEDULE EVERY 3 DAY STARTS '2013-09-20 18:49:07' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM `test`$$

DELIMITER ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

インポート前のSQL文処理

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

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

今回出力した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の修正と処理の概要

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

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

SQLの修正と処理の概要

インポート前とインポート後の結果（参考）

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

取り込み前の状態

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

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

処理結果

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

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

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

取り込み後の状態

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

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

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

取り込み前の状態

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

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

処理結果

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

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

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

取り込み後の状態

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

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

さいごに

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

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

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

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

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

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

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

- MySQL, phpMyAdmin MySQL, phpMyAdmin

: phpMyAdminを利用したMySQLデータのAuto Increments Option動作比較

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: phpMyAdminでMySQLデータをサーバー上からインポートする

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: phpMyAdminを利用したMySQL同期とWM3500Rポート解放

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: phpMyAdminを利用したMySQLデータのインポート形式一覧とデータ

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: phpMyAdminが[mysql拡張がありません。PHP の設定をチェックしてみてください。]という場合

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: PHP.iniに設定する設定をhttpd.confや.htaccessに対して行うメリット

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: GROUP_CONCATのgroup_concat_max_len()が有効にならない対処法

Google or AdMax Promotion(it) 禁断の機能がau公式 …

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

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: phpMyAdminでMySQLデータをサーバー上にエクスポートする

Google or AdMax Promotion(it) 禁断の機能がau公式 …

: wordpressのMySQLデータインポート前のドメイン置換作業(SQLファイル)

Google or AdMax Promotion(it) 禁断の機能がau公式 …

PREV: ファイルアップロード上限の変更（PHP　phpMyAdmin　wordpress）
NEXT: ファイルの拡張子を表示する(windows7)

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

はじめに

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

環境

エクスポートの方法

インポートの方法

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

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

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

ダンプするテーブル

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

生成オプション

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

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

汎用的かつ安全（だと思う）出力設定

エクスポートの設定

ダンプするテーブル

生成オプション

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

出力したSQL

インポート前のSQL文処理

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

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

SQLの修正と処理の概要

インポート前とインポート後の結果（参考）

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

取り込み前の状態

処理結果

取り込み後の状態

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

取り込み前の状態

処理結果

取り込み後の状態

さいごに

関連記事