MYSQL4.0→MYSQL5.7へデータを移し替えしてみた

10年以上前のホームページは、得てしてMYSQL4.0を使用しているケースがあります。
最近サーバーから「そろそろMYSQLのバージョンを上げてください。」というようなメッセージがあり、MYSQL4.0→MYSQL5.7へデータを移動させることにしました。

最初は簡単だろうと思いましたが、思いのほかはまりましたので、忘備録として記録いたします。

まずは通常通り4.0エクスポート→5.7インポートをしてみたらエラーになりました。

MYSQLのデータを移動させる手段として、私はphpMyAdminでエクスポートし、対象のMYSQL側のphpMyAdminでインポートします。

そもそもこれだけで済めばらくちんです。

ということで、特に何もさわらずやってみました。

MYSQL5.7のphpMyAdminにはインポートに互換指定があったので、MYSQL40を指定してインポートしてみました。

すると・・

読み込みできませんという旨のエラーメッセージが表示されました。
具体的なエラーが出ているので、ひとまずエクスポートしたSQLの該当箇所を見てみることにしました。

エクスポートしたSQLをテキストエディタで開こうとすると文字コードエラーで文字化け

私は、テキストエディターは秀丸を使っています。
文字コード(Shift-jis、UTF-8等)に対応しているので、MYSQLなど文字コードがシビアなデータには向いています。

MYSQL4.0からエクスポートしたSQLを秀丸で開いてみたところ、次のようになりました。

エクスポートしたデータはUTF-8なので、デフォルトShift-jisの秀丸ではエラーになります。
そこで、UTF-8に設定したところ、さらに文字コードエラーになりました。

つまり、文字化け状態でしか開けないということです。

無視してやればいいような気もしますが、もし肝心のデータ自体が文字化けしていたら、うまく読めたとしても文字化け状態でデータが入ります。

それは具合が悪いと思い、次の手順でMYSQL4.0のデータを再度エクスポートしなおしました。

注意!
この現象は、MYSQLデータの中に「Shift-JIS文字」があったために起こった現象です。
全ての文字がUTF-8であれば、この部分は省略してもOKです。

MYSQL4.0はSJISでエクスポートする

phpMyAdminには、エクスポートする際のオプション設定があります。
そこで、SJISを選択してエクスポートしました。
これならば、エディタで文字化け問題が発生しません。

SJISでエクスポートしたデータはすぐにUTF-8に再変換する

あえてSJISで出力しておいてなんですが、MYSQL5.7は大抵UTF-8ベースです。
そのため、エディタを使ってSJIS→UTF-8変換しておきます。

テキストエディタでToo big precision 14 specified for ‘colum名’ Maximum is 6を修正する

文字化けしない状態にしたエクスポートSQLをエディタで開き、エラーメッセージの該当箇所を直そうと思いました。

そのときに、そもそも「Too big precision 14 specified for ‘colum名’ Maximum is 6」って何エラー?

と思い、翻訳してみました。
エラーメッセージって、翻訳すると意外に問題がわかりやすくなります。

エラーメッセージを翻訳してみると、timestamp(14)の記述がMYSQL5.7ではエラーになるようです。
そこで、timestampだけにしました。
timestamp(6)にする方法もあるようですが、かっこがなくてもOKでした。

今度は謎エラーが発生

文法エラー的なものを修正後、MYSQL5.7のphpMyAdminで再度インポートしてみました。
すると、今度はこのようなエラーがでました。

何エラーかわからないエラーが出ました…。

詰んだ・・・。

そんな感じがしましたが、ここでかつての経験から気になる個所を見つけました。
それがこちらです。

コメント行だから一見関係ないように思えましたが、以前EC-CUBE4の対応で、コメント行を直すと反映される事例があったため、ピンときました。

これは単にphpMyAdmin内で文字コード不一致による誤動作が発生しているのではないかと。

つまり、UTF-8の文字コードでShift-JISとして登録しようとしているのではないかと思いました。

事実、この部分を修正したことで謎エラーは消えました。
一見コメントのようで、重要な意味のある記述でした。

TYPE=MyISAMの記述をなおす

SET NAMESをUTF-8に変更し再度MYSQL5.7にインポートしてみました。
すると今度は次のようなエラーがでました。

MyISAMは、今となっては古いMYSQLの型になります。
いまではInnoDBを指定するのが主流です。

ということで、この部分の記述を次のように変更しました。

この部分の記述は、テーブル分あったので、全てのテーブルの記述をなおしました。
ついでに、DEFAULT CHARSETも追加しました。

これは、MYSQL5.7のphpMyAdminでエクスポートしたところ、SQLにDEFAULT CHARSETが記述されていたため、それにならい追記しました。

今度こそと思いインポートしてみました。

ようやくうまくインポートできました。

インポートして、エラーが出て、調べて、修正してを繰り返しようやく正常にインポートできました。

なかなか思うようにいきませんでしたが、MYSQL4.0→MYSQL5.7へのデータ移動実績を作ることができました。

もしかしたらSQL内容によっては、さらなるSQL修正が必要になると思います。
またMYSQL5.7へインポートできても、PHP等のプログラム側が古く、MYSQL5.7の読み書きに対応するために、プログラム側も直す必要があるかもしれません。

いずれにせよ、古い代物は互換性が低いということがわかりました。

MYSQL4.0→5.7へデータ移動するためのまとめ

今回の要点を以下に記載いたします。

  1. MYSQL4.0のエクスポートは「SJIS」で行う
  2. エクスポートしたSQLはテキストエディタ等で「UTF-8」に変換する
  3. SET NAMESの記述を「utf8mb4」等データベースの指定コードにする
  4. timestamp(14)はtimestampに書き換える
  5. 「TYPE=MyISAM」は「TYPE=InnoDB」に書き換える

場合によっては、これだけで対応できないこともあると思われますが、今回はこの内容で移し替えすることができました。

またご参考になれば幸いです。

今回のレポートは以上です。
読んでいただいてありがとうございました。


ホームページに関するお悩み事やご相談事がございましたら私どもまでご連絡ください。 鋭意ご対応申し上げます。
ホームページのご提案もさせていただいております