にしし ふぁくとりー(西村文宏 個人サイト)

Presented by Nishishi via Movable Type. Last Updated: 2015/09/22. 13:18:44.

さくらインターネット上のデータベースをMySQL4.0から5.5へ移行する際の試行錯誤の結果(^_^;)

phpMyAdminさくらインターネットのレンタルサーバでは、データベースとしてMySQLサーバが用意されています。
今、データベースを新規作成する際に利用できるバージョンは、MySQL 5.5なんですけども、MySQL 4.0が提供されていた頃から利用しているユーザは、4.0も使えます。
ただ、MySQL4.0利用者がMySQL5.5を利用するためには、4.0で作成したデータベースを削除する必要があります。

私は、サーバを2つ契約しているので、MySQL4.0と5.5の両方を使えていたんですが。(^_^;)
昔々にMySQL4.0を使って構築したツールを5.5へ移行させるのも面倒だったので、そのまま4.0を使い続けていたんですけども、さすがにメインサイトで古い4.0を使い続けるのはどうなの……と思ったので、すべて5.5へ移行することにしました。

MySQL4.0を5.5へ移行させるにあたっては、ネット上にもたくさん情報があるし、たぶんサクっと済むんじゃないかな……と思って取りかかったんですが、予想以上につまずいて時間が掛かってしまいました。(^_^;;;
というわけで、試行錯誤の結果、分かった手順をまとめずには居られなかったので、以下に記します。(笑)

まとめると、それほど複雑なことは何もないんですけども。^^;
原因が分からないトラブルは、試行錯誤に時間が掛かって仕方ないですね……。

MySQL4.0から5.5へ移行させる大まかな手順

以下は、Movable Typeで利用するMySQL4.0をMySQL5.5にする場合の手順です。
Movable Type以外の場合でも手順は同じです。(手順6だけはMovable Type固有の設定ですから不要ですが。)

私がどっぷりハマっちゃったのは、上記の(4)~(6)の部分です。(^_^;)
なお、ここでは、すべての文字コードにUTF-8を使っていることを前提にしています。

(1) MySQL 4.0 側のデータベースをSQLファイルの形でエクスポートする

さくらインターネットのコントロールパネルから、phpMyAdminにアクセスして、すべてのデータベースをエクスポートします。
だいたい以下の手順です。

  1. phpMyAdminのログイン画面で、文字コードの指定に「UTF-8」を選択した状態でログイン。
  2. メニューから「エクスポート」を選択。
  3. 「構造」欄の中にある「DROP TABLEを追加」項目と「IF NOT EXISTSを追加」項目にチェックを入れる。
  4. 「ファイルに保存する」項目にチェックを入れる(圧縮は「なし」を選択)。
  5. 「実行」ボタンをクリックする。

以上で、「 mysql16.db.sakura.ne.jp.sql 」のような感じのファイル名(拡張子は .sql )でデータベースのバックアップ(SQLファイル)がダウンロードできます。
ファイルの中身は、テキストファイルです。
文字コードは、UTF-8(BOMなし)になっているはずです。

※この手順では、以下のサイトを参考にしました。
MySQLを4から5へ」(@HashiMのたわごと(?))

(2) MySQL 4.0 のデータベースを削除する

間違いなくエクスポートできたら、さくらインターネットのコントロールパネル上から、MySQL 4.0のデータベースを削除します。
一度削除すると、決して元には戻せませんので、エクスポートがちゃんとできていることを確認した上で、覚悟を決めて削除して下さい。(^_^;)

(3) MySQL 5.5 のデータベースを新規に作成する

次に、MySQL5.5で新規にデータベースを作成します。
MySQL4.0では、データベース名はユーザ名そのままでした。
MySQL5.5では、データベースを20個作成可能(スタンダードコースの場合)なので、データベース名は「ユーザ名」の後に任意の文字列を加えられるようになっています。
Movable Typeで使うなら、例えば「mt」を加えて、「 username_mt 」のようなデータベース名にすれば良いでしょう。(ユーザ名の部分は変更できません)

(4) SQLファイルの中身をMySQL5.5の構文に合致するよう修正する

さて、現状でローカルに保存したSQLファイル( mysql16.db.sakura.ne.jp.sql )は、MySQL 4.0 の構文で記述されています。
そのままでは、Syntax Error になってしまって、MySQL 5.5にはインポートできません。
そこで、テキストエディタを使ってSQLファイルの中身を修正する必要があります。
ここで、ずいぶんとハマりました。(^_^;;;

修正する箇所は、以下の3種類です。

aは1箇所しかありませんが、bやcは複数回登場します。それらすべてを一括して修正します。

■a. 先頭付近の CREATE DATABASE と USE 行を削除

SQLファイルの先頭付近に、以下の2行があります。

CREATE DATABASE `ユーザ名`;
USE ユーザ名;

この2行は、MySQL4.0側で使っていたデータベース名になっています。
MySQL4.0で使っていたデータベース名は「ユーザ名」そのままです。
しかし、さくらインターネット側の仕様変更によって、MySQL5.5でのデータベース名は「ユーザ名+_+任意の文字列」という形になるので、同じ名称ではデータベースを作れません。
そもそも、新しいデータベースは、さくらインターネットのコントロールパネル上で作成するので、SQLファイルの中にその記述を含める必要はありません。
なので、この2行は削除しておきます。

削除するのが不安なら、とりあえず行頭に「--」を加えて、以下のようにコメントアウトしていても構わないでしょう。

-- CREATE DATABASE `ユーザ名`;
-- USE ユーザ名;

■b. 「TYPE=」を「ENGINE=」に修正

SQLファイルの途中に、何度も以下のような記述が出てきます。

TYPE=MyISAM

この記述をすべて、

ENGINE=MyISAM

に変更します。

SQLファイル中には、「TYPE=MyISAM AUTO_INCREMENT=2」などのように、「TYPE=」という記述が複数回登場します。
しかし、MySQL5.5では「TYPE」は使えないので、すべて一括して「ENGINE=」に修正します。
テキストエディタの一括置換機能を使って修正してしまえば良いと思います。

※この手順では、以下のサイトを参考にしました。
MySQLバージョン4.x以前からエクスポートしたsqlファイルはTYPE = MyISAM を ENGINE = MyISAM に変更する」(@きほんのき)

■c. 「timestamp(14)」を「timestamp」に修正

このtimestampについては、特に言及しているサイトを見かけなかったんですけども、必ずしも問題になるわけではないんでしょうかね……?
私はこの修正点に気づくまでにずいぶんと時間を掛けてしまいました。(^_^;;;
SQLファイルの途中に、何度か以下のような記述が出てきます。

timestamp(14) NOT NULL

この記述に含まれる「(14)」の部分を削除して、すべて、

timestamp NOT NULL

のように変更します。

MySQL5.5では、このtimestampの仕様が変更されており、「timestamp(14)」という記述のままだとインポートできません。
括弧と数値を削除して、ただの「timestamp」に修正します。

※この手順では、以下のサイトを参考にしました。
MySQLのデータインポートでエラー」(@Yahoo!知恵袋)
MySQL Bugs: #41510: timestamp(14) fails in table create (TikiWiki)」(@MySQL Developer Zone)

(5) SQLファイルをデータベースにインポートする

修正したSQLファイルを、MySQL5.5で作成した新規データベースにインポートします。
手順は以下の通り。

  1. phpMyAdminにログイン(※)
  2. 左側に表示されているデータベース一覧から、インポート先のデータベースをクリック
  3. 右側の上部に表示されているメニューから「インポート」をクリック
  4. インポートするファイルを選択
  5. 右下の「実行する」ボタンをクリック

※このとき、phpMyAdminのトップ画面からいきなり「インポート」を選択してはいけません。先に、左側のメニューから、作成済みのデータベース名を選択しておく必要があります。

ここで、SQLファイルの中身に何らかの問題があると、

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '~' at line XX

のようなシンタックスエラーメッセージが出てきます。
私も何度もハマりました。(^_^;;;
とりあえず、手順(4)でちゃんと修正できていれば、問題はないはずです。
それでもこのエラーが出るようなら、他に何らかの問題があるのでしょう……。

エラーメッセージに書かれた「near」の後に、問題のある箇所を示してくれています。
私が今回試行錯誤したところによると、ここでの指摘箇所の『直前』に何らかの問題があると考えて良い気がしました。
このメッセージ内に引用されている内容ばっかり注目していると気が付かないことがあるので注意した方が良いと思います。(^_^;;;

(6) Movable Type の mt-config.cgi の中身を修正する

最後に、Movable Type の設定ファイルであるmt-config.cgiの中身を、新しいサーバ用に修正します。

ObjectDriver DBI::mysql
Database データベース名
DBUser ユーザ名
DBPassword パスワード
DBHost mysql***.db.sakura.ne.jp

しかしながら、これだけの修正だと、私の場合は、Movable Type上で、日本語文字がことごとく「????」のように文字化けしてしまいました。
私は、Movable TypeでもUTF-8を使っていましたし、データベースもUTF-8に設定しましたし、出力したSQLファイルもUTF-8を指定しましたし、文字化けするような要因がないんですけども、なぜか文字化けしました。^^;;;
で、いろいろ情報を探したところ、mt-config.cgiの最後に、以下の1行を記述することで解決できました。

SQLSetNames 1

これは、DBI::mysqlでMySQLサーバに接続するときに、クライアント(CMSツール)側の文字コードを明示するオプションです。
こいつを加えてやることで、日本語文字も正しく表示されるようになりました。

※この手順では、以下のサイトを参考にしました。
Movable Type + MySQL 4.1 を組み合わせると日本語が文字化けする不具合/障害の解決方法」(@iandeth.)

というわけで、以上で、めでたし、めでたし……。
めちゃくちゃ時間が掛かってしまいました。(^_^;;;

コメント

同様にさくらのサーバーでMySQL4.0から5.5へ移行しようとしてSyntax Errorでハマっていたのですが、ここを拝見して無事うまくいきました!
TYPE=MyISAMでひっかかっていたようです。

10年前に始めたブログですが、MySQLのバージョンが壁になってそのままにしていました。
移行できてほっとしています。
ありがとうございました!!

投稿者 kujira : 2013年11月05日 00:14

kujiraさん、どうもです。
お役に立てて嬉しいです。

さくらインターネットで、MySQLのサービスが拡張されたのはありがたいですが、4.0と5.5の共存ができない仕様なのが若干面倒でしたね。^^;;;
4.0を残したまま5.5を使えるのであれば、削除する必要がないのでもうちょっと気持ち楽に作業できた気がするんですが。(^_^;;;

投稿者 にしし : 2013年11月09日 12:38

コメント数: 2件

コメント投稿欄 この日記に対するコメント投稿を歓迎します。

保存しますか?



※本文中にURLは書けません。(書くと投稿が拒否されますのでご注意下さい。)

※投稿内容は、実際にページ上に掲載される前に、管理者によって確認される設定にしている場合があります。その場合は、投稿後にその旨が表示されます。たいてい1~2日以内には表示されるはずですので、気長にお待ち願います。m(_ _)m

トラックバック

このエントリーへのトラックバックURLを表示するにはここをクリック
※スパム防止のため、トラックバックURLは別ウインドウで表示します。(JavaScriptが有効でないと表示されません。)

--- 当サイト内を検索 ---