MySQLを4.0から5.0へバージョンアップした話。〜そして5.6へ〜

それなりに長いので、心して読んで下さい。 mysql周りはそこまで詳しくないので、サーバーサイドの雑魚プログラマー目線で書いてあります。 半年以上前にやった事もあるので、ある程度おぼろげな記憶で書いてあります。

今現在は5.0なのですが、最終目標は5.6です。

概要

1. 規模感
2. 考え方
3. 5.0にするまでにやった事
4. 本番投入していった時の事
5. 注意しないといけない事
6. 修正の必要があるsql
7. 〜そして5.6へ〜
8. 所感
9. まとめ

1. 規模感

• slaveの台数ざっくり10台くらい。
• mysqlの容量600G
• DBをつついてるロールの数。ざっくり７個以上。
• 大体半年？くらい。主に手を動かしていたのはアプリケーションの人１人（僕）インフラ周りめっちゃすごい人１人。の合計２人だった。
• アドバイザーとしてruby界隈でめっちゃすごい人１人。インフラ周りのこれまたすごい人１人。

2. 考え方

5.6が目標なのに、何故5.0を一度経由するのか？

• 一度5.0を経由しないともろもろ難しいと判断したためです。
• 4.0から一気に5.6に移行するとなにか起きた時の影響が大きそう。
• mysql4.0→mysql5.6へ直接レプリケーションが出来ませんでした。master(4.0)→slave(5.0)→slave(5.6)という感じのレプリケーションは可能そうだったのですが、slaveを昇格する時の事を考えるともろもろ難しそうでした。
• ざっくり、このような理由です。

  個人でやるレベルの規模であれば、えいやで5.6に上げてしまっていいんだけれど、運用しつついい感じにバージョンアップするのはそれなりに大変。。。

3. 5.0にするまでにやった事

5.6の開発環境を作る

なぜ5.6の開発環境を作ったのかというと、

• 最終目標はmysql5.6
• 5.6でOKだけど、5.0ではダメというパターンは少なそう
• 5.0でテストして、5.6でテストするよりも、5.6でテストした方がテストが１回で済む（時間的な問題）

という事で5.6の開発環境を作りました。

少し工夫した点は、hostsに例えば、

192.168.11.5     mysql5.db.hoge.pb

という設定を追加して、もしその設定があれば、指定したIPにDBのアクセスを向ける（mysql5.6に向く）。この行をコメントアウトすればmysql4.0に向く。 というように、開発環境のDBの向き先をソースを変更する事なく、可能になるようにしました。

こうしておく事で、5.6でテストして問題が発生した時でも、簡単に4.0で検証する事が出来るので、 mysqlのバージョンの問題なのか？普通にバグなのか？の問題の切り分けが簡単になりました。

railsのdatabase.ymlのようなノリで向き先を変更出来るようにする方法も考えたのですが、 主に開発で使われるロールの数が４つくらいあり、hostsで設定する方が１箇所で済むのでこの方法にしました。

接続先切り替えの仕組みをcomposerで

http://qiita.com/mapyo/items/8b3e2b5923a7c584e43a

このような感じで、社内のgitリポジトリからcomposer installで入れられる仕組みを構築していたので、 上記のhostsを見る仕組みをcomposer installで入れられるようにしました。

これにしとくと、mysql4.0の環境が不要になった時でも、コードの修正が楽に出来ました。

開発環境のmysql4.0のデータをdump→5.6へリストア

僕の横でインフラ周りのめっちゃすごい人が本番データのdump、リストアを検証していました。 その過程で、mysql4.0から5.6へデータをリストアする為に必要な、 秘伝のsedスクリプトを完成させていたので、 それを頂いて、開発環境のデータを4.0から5.6へリストアしました。

厳密にはもうちょっとありましたが、 ここでは一般的にmysql4.0から5.6にリストアする時に必要そうなもののみをのせています。

mysqldump --default-character-set=ujis --extended-insert -u hogeuser hogeschema -h mysql4.0.db.hoge.pb > dump.sql
cat dump.sql \
| sed -e 's/timestamp(14)/timestamp/' \
| sed -e 's/TYPE=/ENGINE=/' \
> dump5.sql
mysql -u hogeuser hogeschema -h mysql5.6.db.hoge.pb < dump5.sql;

開発環境全般の整備

ちょっと複雑なのですが、以下の２パターンの開発方式でうちのチームでは開発をしており、 それぞれ、以下のように開発環境を構築しました。

• パターン１：maglicaで開発。
  ○ maglicaとはこちら
  ○ webサーバを各個人毎に持っており、DBは全員共通のものを使っています。
   ○ 主にデザイナさんがこの方式で開発しています。
  →共通で使えるようにmysql5.6のテスト用のDBサーバを構築しました。
• パターン２：ローカル開発環境
   ○ 主にエンジニアはこの環境で開発しています。
   ○ vagrantとpuppetで作られています。
   ○ vm1台でwebサーバかつDBサーバ（mysql4.0）という構成です。
  →ローカルでvmをもう一台起動するようにして、puppetを書いてmysql5.6がpuppet applyで入るようにしました。puppetべんりー

mysql5.6で開発してもらうように布教する

上記の仕組みを同じチームのエンジニアの人に説明して、 基本的に開発はmysql5.6を使って開発してもらうようにお願いしました。

これをやるメリットとしては、以下の２つ程考えられます。

• mysql4.0では動くが、mysql5.6では動かないクエリが今以上に増えなくなる。
• 今現状、mysql5.6で動かない部分があったら、教えてもらえる。

幸いにも？後者の部分に関しては、既に把握済の現象１件のみだったような気がします。（うる覚え。。。汗）

この試みは、一部slaveが5.0になり始めてからやったのですが、 早めにやっておくにこした事はないと思います。

テストする

デキるテスターの人がいるので、ある程度mysql5.6で動く事を確認した後、ブラックボックス的なテストを全面的にお願いしました。

一方僕は、mysqlのバージョンアップで修正が必要な部分を調べたり、ググって修正箇所を見つけたり、既に判明している修正箇所の横展開などをしていました。

修正する

1. 直さなきゃいけない箇所が見つかる
2. その部分なおす
3. 他の開発メンバーにこういうパターン動かないので注意してねと周知
4. 横展開

だいたいこのパターンでやっていきました。

横展開が地味に辛かった。。。 git grepしてこのキーワードが含まれているファイルをリストアップして、さらにそのファイルの中でこのキーワードが含まれているものを探して、、、という感じでやってました。

コマンドをもうちょっと使いこなせていたら、いい感じに出来たかもしれません。 sqlが各所にベタ書きされていたので、ひたすら出てきたファイルを開いて該当のsqlを目視確認して、OK or NGというのを判断してやってました。 直さなきゃいけない案件によっては、１００〜２００個くらいファイル開いて目視で確認して、、、という作業をやった気がします。

修正する必要のあるパターンについては、 5. 修正の必要があるsqlで詳しく説明します。

例えば、以下のようなコマンドでチェック対象のファイルを抽出しました。

git grep -i select | grep "\.php" | cut -d : -f 1 | sort | uniq | xargs grep -i from | cut -d : -f 1 | sort | uniq | xargs grep -i join | cut -d : -f 1 | sort | uniq

※select、from、joinという単語（大文字小文字無視）が含まれているphpファイルを抽出したつもりです。

ちなみに、下の方に書いてある、joinの優先度が変わる事によるエラーを調査するために書いたコマンドです。 これで出てきたファイルを１件１件開いて、 エラーになるパターンかどうかチェックしていきました。

不要なテーブルのデータの削除

とあるテーブルの容量が220Gも（盛ってます）あって、dumpやリストアにそのテーブルだけめっちゃ時間かかってる〜。という状態でした。 全データ容量が600Gなので、３分の１も占めているモンスター級のやつです。

中身を見てみたら不要なデータも含まれていたので、消しました。（消したといっても一気にガツッと消せないので少しずつ何日か書けてひっそりと消しました。） 150Gくらい？容量が減った気がします。（盛ってます）

※厳密にはDELETEしたタイミングですぐに容量が減るのではなく、dump〜リストアによって容量が減る感じです。その辺のあれな事は略します。

これによって、mysql4.0からdumpする時の時間が減ったり、5.0にリストアする時の時間が減るのです！！

4. 本番投入していった時の事

どういう流れでmysql5.0化していったか？

ざっくり言うと、slaveを１台ずつmysql5.0化していって、全台slaveがmysql5.0になったらメンテ入れてslaveをmasterに昇格させます。

• フェーズ１：mysql5.0のslaveを作ってレプリケーションに入れる（本番で稼働しているアプリケーションサーバからは参照しない）
   ○ ちゃんとレプリケーション出来てるか？更新系のクエリはちゃんと流れているか？を確認することが出来ます。
   ○ インフラ周りめっちゃすごい人が作業されてたので僕は隣でふむふむと見ていた程度ですヽ(=´▽`=)ﾉ
• フェーズ２：mysql5.0のslaveをアプリケーションサーバから参照する
   ○ 余裕があるmysqlサーバの数もそんなに多くないので、１台mysql4.0→5.0にリストアして、接続先変更して、様子みて、１台開けて、開いたサーバをまたmysql5.0にリストアする。。。という作業が続きました。
   ○ 最初は数%だけ参照して様子をみて、徐々に増やしていく。これによって、何か障害が発生した時も早めに気がつけるし、被害も抑えられるという事になります。
   ○ でもこのフェーズでも、大事なところはちゃんとテスト出来ていた！？せいか、問題は殆ど出なかった。。。ような気がします。
• フェーズ３：メンテ入れてslaveをmasterに昇格（masterを5.0にする）
   ○ 最終仕上げ！
   ○ メンテ終わった後は特に何事もなかったかのように動いていたぜ！ヽ(=´▽`=)ﾉ

幸いだった事

本番環境における更新系のクエリのエラー検出（4.0では動くけど、5.0ではエラーになるやつ）に関しては、 上記のフェーズ１で検出が可能だったので、それなりに気持ちが楽になりました。

この時発生したエラーは、手作業で流した更新系のクエリ１件のみでした。

NewRelic先生

warningやerrorがいつ頃から発生していたのか？が簡単に調べられるので、重宝しました。 これによって、mysql5.0を本番投入してから出たものなのか、以前から出ていたものなのかの切り分けが簡単に出来ました。

毎日見てました。けど、それっぽいやつは特になかったような気がします。

お問い合わせ

NewRelic先生チェックから漏れたものはお問い合わせベースで発覚します。。 主には警告やエラーが発生せずに、静かに挙動が変わっているものですね。。。

数件程度あった気がします。チームメンバーにフォローして頂きました。 お手数をお掛けしました。。。

5. 注意しないといけない事

過渡期にmasterに対して直接sqlを叩いて更新する場合は、4.0, 5.0で検証してから。

master→mysql4.0, slave→mysql5.0の期間はどうしても発生してしまいます。 slaveの台数が多いとそれなりにその期間は長いでしょう。

この時は、両方の環境でsqlが動くかどうか検証してから流す必要があります。

• 4.0で動かないsqlを作ってしまった
  • masterに流す時にsqlのエラーが起きて何もおこらない
  • 特に問題なし。
• 4.0で正常に動いて、5.0で動かないsqlを作ってしまた。
  • 最悪なパターン。
  • masterでは更新されるが、5.0のslaveにレプリケーションされた時にレプリケーションエラーで止まってしまう。。。
  • masterとslaveの差異が発生！！！

幸いにも、日中ガンガン更新される系のテーブルではなかったので、slaveに対して直接修正するsqlを流して（タイムスタンプなども同じになるようになどして）修正しました。もしそれが日中ガンガン更新される系のテーブルだったとしたら、ゾッとします。。。

また、masterのみmysql4.0、slave全台がmysql5.0の場合にこれが発生した時の事を考えると、、、

今更4.0でちゃんと動かない地雷を踏む

モダンな感じでやろうとして、逆に地雷を踏むという切ない事案に遭遇しました。

Mysql4のレプリケーションの罠

もうmysql5.0になったから大丈夫！

6. 〜そして5.6へ〜

さて現在は、5.6化に向けて粛々とやっております。 といっても、やることは5.0化していった時と基本的に同じで、slaveを5.6化していって、 その後masterを5.6化するという流れには変わりありません。

マラソンの２周目に突入した感じです。

mysqlクライアントのバージョンも上げないといけないとか、 うまくレプリケーション出来るようにゴニョゴニョするノウハウもあるっぽいです。

例：この辺の問題など

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_secure-auth

その辺は粛々とやる感じでおります。

アプリ側の人としては、5.6で既にテストは終わっているので、 本番投入〜バグが出たら全力で修正する気概でおります。

7. 修正の必要があるsql

さて、ここまでどんな感じで進めたのかを書いて来ましたが、 具体的にどういうsqlを修正したのか？という部分を書いていきます。

書き残してある限りのものを書いていますが、msyql5.0化後にひっそりと出てきたケースについては、書きそびれているかもしれません。

また、正確にどのバージョンからこのエラーが出るかなどは調べていません。 5.6の開発環境でテストした時や、5.0を本番投入後に修正が必要だと判断したものをあげています。

timestamp問題

timestamp型でDBに格納される値が変わります。 結構有名なやつです。

具体的には、

• mysql4.0
  20130819012123
• mysql5.6
  2013-08-19 01:21:23

このような違いがあります。

mysqlからデータを取得して、そのまま表示させている場合だと、注意が必要です。 phpの場合だと、DateTimeクラスに値をわたすと、 どちらのケースでも対応が可能なので、このようにしました。

もしくは、sqlの部分で、DATE_FORMATを使って、mysql4の形式に常に表示するようにしても良いかもしれません。

DATE_FORMAT(hoge_timestamp, '%Y%m%d%H%i%s')

DELETEする時にUSINGを使っていると、別名が使えない

DELETE FROM foo
USING foo f, bar b
WHERE f.id = b.id;

このsqlをmysql5.6で実行すると

Unknown table 'foo' in MULTI DELETE

というエラーが出ます。

なので、

DELETE FROM foo
USING foo, bar
WHERE foo.id = bar.id;

このように修正しました。

joinの優先度が変わる事によるエラー

SELECT * FROM foo, bar JOIN hoge ON foo.id = hoge.id;

このsqlをmysql5.6で実行すると、

Unknown column 'foo.id' in 'on clause'

このようなエラーが出ます。

対応方法としては、以下の２パターンありそうでした。

パターン１：FROM句に括弧をつける

SELECT * FROM (foo, bar) JOIN hoge ON foo.id = hoge.id;

パータン２：FROMは１つにする

SELECT * FROM foo JOIN bar JOIN hoge ON foo.id = hoge.id;

パターン１のFROM句に括弧をつけるのがお手軽だったので括弧をつけました。

joinの順番によって発生するエラー

これ結構ややこしいのですが、

SELECT * FROM hoge
INNER JOIN foo ON hoge.id = foo.id AND hoge.id = bar.id
INNER JOIN bar ON hoge.id = bar.id;

これでUnknown column 'bar.id' in 'on clause' このようなエラーが発生するので、

SELECT * FROM hoge
INNER JOIN bar ON hoge.id = bar.id
INNER JOIN foo ON hoge.id = foo.id AND hoge.id = bar.id;

このようにして対応しました。

かなり検出しにくいパターンです。。。

予約後をカラム名に使っているパターン

SELECT sql FROM hoge;

これだと以下のようなエラーが発生してしまうので、

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 'sql FROM hoge' at line 1

バッククォートで囲ってやります

SELECT `sql` FROM hoge;

8. 所感

期間が長かった

動かないところを探して修正して、横展開をして、サーバ１台ずつ構築して、切り替えて、、、 という事をしていると、めっちゃ時間がかかります。

• 効率よく進められるようにどういう順番で進めるのか考える。
• 作業待ちの状態もある程度発生してくるので、細かいタスクをやったりする

こんな感じで進めています。

インフラ周りめっちゃすごい人はすごかった

mysqlのバージョンアップで一番大変なのは、mysql周りの作業です。 隣でいろいろ作業されていて、すごいなーと思いながらみていました。

ちゃんとリストア出来るように検証したり、slaveを１台ずつ再構築したり、メンテを入れてslaveを昇格させたり、かなり大変そうだと感じていました。

テスターの人がいて心強かった

デキるテスターの人がいます。

一番仕様を把握していて、一番バグが出そうな匂いに敏感な人なので、ある程度動く事を確認したら、その人にお願いしてました。

やれる事はやった。後は本番でバグが出たら全力で修正するという心意気

ほんとに大丈夫。。。なのか！？という不安でいっぱいの時もありましたが、 やれるだけの事はやったから、後はバグが出たら全力でなおす！という感じで切り替えて望みました。 この心意気大事。

9. まとめ

ダラダラと書いていたら、めっちゃ長くなってしまいました。。。 mysql4.0に立ち向かう猛者がおりましたら、参考になれば幸いです。

ガンガン行きましょう！