MySQLをアップデートする話

はじめに

こんにちは、アプリケーションエンジニアの

id:t_kytです。今日はMySQLのアップデートについて書きたいと思います。

去年MySQL4.xを含むいくつかのMySQLをアップデートしたのですが、その手法や注意した点などについてインターネット上に残しておきたいと思います。自分自身半年前までMySQLの運用についてほとんどわからなかった状態でしたが、社内の知見やネット上に存在する上質な資料にかなり助けられたところがあり、自分の知見もそういったものの一つになれれば幸いだと思っています。学びとオープンネスの精神です。

ちなみに4.0からのアップデートの話をしますが、5.5->5.6みたいな普通のアップデートの際にも役に立つと思います。必要なければ4特有の部分は飛ばして読んでください。

また、1/23には東京で、1/30には京都でエンジニアセミナーがあり、僕も登壇する予定です。テーマは「クラウド活用」で、当日はこの記事の更に先にあるAmazon Aurora化について話したいと思います。興味があればお越しください。

developer.hatenastaff.com

アップデートのモチベーション

一般的なMySQLアップデートのモチベーションについてはインターネット上にいくらでもあると思うので、我々特有のモチベーションについて。

MySQL4.xが未だに存在している
MySQLのバージョンが古いせいで運用のコストが掛かっている
古いバージョンに引きずられて新しいバージョンが使えない

1.について

現状まだ4系統が残っている状態にある。もはや4系統の仕様に精通している知ってる人がいないので厳しい。

2.について

最近の運用ツールはInnoDBが前提だったり、そもそも古いバージョンサポートされていなかったりするので使えず、独自に4.xでも使えるようにしたグッズを用意したり4系用途のオペレーション手順を用意するなど、運用面でコストがかかっているのでこれをどうにかしたい。
またmysqlサーバーが乗っているOSも古く(CentOS5)、Mackerelでさえ少し工夫しないとインストールできない状況にあった。

3.について

古のDBほど共有DBというアンチパターンにズッポリ乗っかっていて、4.xや古いMySQLに周りが引きずられる形になっている。具体的にはold_passwordsなどの事情。

以上を解決したいのがモチベーションである。実際、去年から今までの半年にかけてそこそこの数のMySQLをアップデートし上記の問題は大体解決が見えてきた。

MySQLのアップデートの手法

MySQLのアップデート手法には大きく分けて2つある。

mysql_upgradeを用いたアップグレード(インプレースアップグレード)
レプリケーションを用いたアップグレード

どちらの手法を使うかはケースバイケースであるが、今回は後者の方法にしか言及しない。自分が携わってきたmysqlサーバーの性質上

メンテナンスウィンドウを取ることができない
- 通常のスイッチオーバー、フェイルオーバー以上のダウンタイムを許容できない
バージョンを一気にあげたいMySQL4.0 -> (MySQL5.0 -> MySQL5.1 -> MySQL5.5) -> MySQL5.6
エンジンをついでに変更したい(MyISAM -> InnoDB)
- 運用上MyISAMは辛い

という要件があったためレプリケーションによるアップグレードを行った。mysql_upgradeアップグレードは長時間のダウンタイムを必要とするため今回の目的には合致しなかった。

スキーマの変更について補足すると、基本的にはInnoDB化以外は行っていない。InnoDB化については運用の観点でMUSTであったのでついでに実施したが、その他については極力アップデートとは切り離した。具体的にはSQLモードについても空のままアップデートしたし、文字コード周りもskip-character-set-client-handshakeで濁したりした。

アップデートの概観

MySQLはメジャーバージョンが1世代以内であればレプリケーションできるのを利用してアップグレードする。

参考:

具体的には4.0 -> 5.6のアップグレードをしたいと考えた場合、以下のようなトポロジでレプリケーションを設定する。(実際には以下に加え、現/新の参照用slaveやbackupホスト、書き込み検証用の5.6ホストなどがあるが省略する)

現master(4.0) <- クライアント(アプリケーション)から参照/更新系クエリを受け取っている 
   -> relay(5.0) 
       -> relay(5.1) 
           -> relay(5.5) 
              -> 新master(5.6)

現masterにクライアントから発行されたクエリは新masterまでレプリケーションされるという状態をつくる(もちろんレプリケーションする前にリストアはしている)。relayについては完全にレプリケーションの中継になっているだけのmysqlサーバーである。社内で(というか僕が)慣習的にrelayと呼んでいる。

この状態で現master(4.0)から新master(5.6)へのスイッチオーバーを行いアップデート完了というのが基本的なアイデアである。

ちなみに1バージョン(5.5->5.6など)のバージョンアップであればもちろんrelayサーバー必要ない。

アップグレード可能かの検証

まず非互換な変更によりアップデートが不可能になっていないかを確認する。基本的には4.0->5.0は注意が必要だがそれ以外についてはそこまで大変な変更はないはず。

実際自分は10近くのmysqlサーバーをアップデートチャレンジしたが、5.0以上ならばMySQLの非互換な変更でなにか対応した記憶がない(ただしパラメータのデフォルト値の変更は注意)。

MySQL4.0の場合timestamp(n)(文字列表現の変更と定義の廃止)とvarchar(n)(byte数から文字数に)の仕様変更が大きい。影響がある場合アプリケーションの改修の必要がある。
参考: http://download.nust.na/pub6/mysql/doc/refman/4.1/ja/datetime.html

主要な変更点は以下の通りなので必要に応じてチェックしていく。

バージョン	影響範囲	変更内容	対応方法
4.1	全テーブル		テーブル定義の見直し/仕様変更の検討
4.1	MyISAM	チェックサム機能の改善	REPAIR TABLE
4.1	テーブル名	utf8に変更	ASCII以外の文字を利用してる場合, dump/restoreが必須
4.1	SQL	LOAD (DATA or TABLE) FROM MASTER コマンドの廃止	-
4.1	パスワード	パスワード形式が変更	アプリケーションの変更/--old-passwordsオプション
4.1.3	InnoDB	文字列照合順序の変更	テーブルのリビルド(ALTER TABLE)
4.1.5	全テーブル	utf8文字コードのカラムでprefix-indexを用いてる場合はリビルドが必要	テーブルのリビルド(ALTER TABLE)
4.1.13	日付型	日付型に0を足して数値変換する仕様が変更	アプリケーションの変更
4.1.23 5.0.36 5.1.15	ENUM	ENUMの内部表現変更, 0xffを含むなら対応が必要	dump/restore
5.0	ISAM	ISAMストレージエンジン廃止	-
5.0	MyISAM	RAIDオプション廃止	テーブルのリビルド(ALTER TABLE), アップグレード前にRAIDオプション削除
5.0	日付型	timestamp(n)定義の仕様が廃止	アプリケーションの変更
5.0	SQL	ユーザ変数がcase insensitive	アプリケーションの変更
5.0	C API	DECIMAL型を利用したプリペアドステートメントが非互換	5.0用libmysqlclient.soの利用
5.0.3	BIT	BITデータ型の仕様変更	アプリケーションの変更
5.0.3	VARCHAR, VARBINARY	末尾スペースが削除されないようになった。また最大長65536に変更	テーブルのリビルド(ALTER TABLE)
5.0.12	JOIN	JOINの形式がSQL標準に近いものへ変更	アプリケーションの変更
5.0.12	SYSDATE()	クエリ開始時点 -> 関数呼び出しのたびに時刻取得へ変更	-
5.0.13	UNSIGNED	UNSIGNED整数から引き算して負の数になったときの挙動が変更	アプリケーションの変更
5.0.13	GRATEST() LEAST()	引数にNULLとNULLでない値を渡したときの挙動が変更	アプリケーションの変更
5.0.15	BINARY	PaddingがスペースからNULL文字へと変更	アプリケーションの変更
5.0.48	全テーブル	eucjpms,euc_kr,gb2312,latin7,macce,ujis,asciiにおける照合順序変更	テーブルのリビルド(ALTER TABLE)
5.1	テーブル名	ASCII以外の文字のエンコーディング方法が変更	mysqlcheckの利用,5.1.31以降のmysql_upgradeコマンド
5.1.6	TRIGGER	TRIGGER権限の導入.今まではSUPER権限	ユーザー権限の操作
5.1.21 5.1.22 5.1.23 5.1.24	全テーブル	eucjpms,euc_kr,gb2321,latin7,macce,ujis,ascii,utf8における照合順序変更	テーブルのリビルド(ALTER TABLE), 5.1.30以降のmysql_upgradeコマンド
5.5.3	SQL	DELETEで一部正しくない構文をエラーにするように変更	アプリケーションの変更
5.5.6	SQL	CREATE TABLE IF NOT EXISTS ... SELECTの挙動が変更	アプリケーションの変更,レプリからアップグレードする場合は5.1.51以降から
5.5.6	全テーブル	サーバーの文字セットが ucs2、utf16、utf32 である間に、FULLTEXT インデックスを持つテーブルが作成された場合は修復が必要	REPAIR TABLE tbl_name QUICK;

(この表は社内のwikiにまとまっていたものである)

追記: この表はエキスパートのための MySQL［運用＋管理］トラブルシューティングガイド：書籍案内｜技術評論社をもとに手を加えてものである

もちろん公式ドキュメントも目を通すと良い。

予約語の確認

まともなライブラリ使っていれば適切にエスケープされるはずだが、一応確認しておくと良い。書捨てなので生SQL書いたスクリプトがいろいろあって現役で動いてるとかある。

予約語一覧: https://dev.mysql.com/doc/refman/5.6/ja/reserved-words.html

InnoDB化

オプショナルであるがついでにInnoDB化もする場合の考慮ポイントは以下の通り。

全文検索(match)を使っていないか
where句なしのselect count(*) from hogeを使っていないか
データサイズの増大

5.6かつInnoDBにアップデートする場合日本語の全文検索はデフォルトでは使えないので、使っていたらあきらめる。FULLTEXTカラムがなければ使っていないと判断して良い。

select count(*)はInnoDBでは激おそなので使い所によっては諦める(バッチなどなら問題ない場合もある、where句がある場合MyISAMでもInnoDBでも変わらないので大丈夫)。これはアプリケーションコードを把握できるのであればコードを見る、無理そうならばtcpdumpで使っていないかを特定した。

データサイズの増大は大体2倍近くのディスクサイズを要求するようになるので注意。今どきディスクサイズがシビアなことはないと思うが、ホストを立てる際大きくなることを念頭に入れないと悲しいことになる。

以上を検討し自分のmysqlサーバーがアップデートできるようになっているかを確認する。体感だが5.0以降であればそこまで大きな非互換変更はないので大体できるはず。InnoDB化するかどうかはオプショナルだが、運用上はしたいはずなので検討しておくと良い。

サーバー設定の確認

SQLモード
skip-character-set-client-handshake(MySQL4.xからのアップデート)

あたりがポイントだと思う。

SQLモードについては最近のMySQLの場合デフォルト値があるので注意。古いMySQLからのアップグレードの場合SQLモードはデフォルトのまま設定してないこともあると思うのでそういうときはSQLモードは一旦空でアップグレードしてしまい、必要ならばアップグレード後に設定すれば良い。設定しているならばその設定を踏襲しないと不具合につながる。

skip-character-set-client-handshakeについてはMySQL4.xからのアップグレードの際に使うパラメータだが、4時代はクライアントのcharacter-setという概念はないので5のクライアントからつなぐときこれを無視するという設定である。基本的には互換性のために用意されている措置なのでないほうが好ましいが一旦有効にして、アップデート後に消せるようにアプリケーション改修すればよい。

パフォーマンス系のパラメータについてはいくらでもあるのであげきれない。というか、自分の場合パラメータが多すぎるので社内で標準的に使われている(あるいは似たようなサーバーの)パラメータを一旦踏襲し、動作検証時に各種メトリクス(ほげstat、show engine innodb status、mackerel-plugin-mysql、etc.)をみて問題ないか判断した。各バージョンの地雷パラメータみたいなものはインターネット上に情報が出揃ってるのでそこだけ注意した。

たとえば MySQL 5.5の秘伝のタレが5.6では腐っていたはなし | GMOメディアエンジニアブログなどである。このあたりの話は先人がいろいろな情報をネットに公開してくださっているのでそれを参考にした。

スキーマの作成

mysqldumpコマンドで現masterからダンプしてくる。スキーマはリポジトリ内でも管理している場合があるだろうが、今動いている定義が確実。

データのダンプはまだ行わず、スキーマだけを作成する(--no-data)。

mysqldump --no-data --databases hoge > scheme.sql

このダンプしてきたスキーマを次のバージョンのMySQLにリストア、再度ダンプを繰り返す。なぜそのようなことを行うかというと暗黙的にテーブル定義の変換が行われる可能性があるためである。たとえば4系統でダンプしてきたものが created_at timestamp NOT NULL のようなカラム定義だったとしてそれを5.0でリストアすると created_at timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMPになったりする。このような暗黙のデフォルト値などを確認するため一旦リストアしたものを使う。そしてバージョンによっても変わる可能性があるのでバージョンごとに繰り返す。

最終的に5.6からdumpしてきたスキーマに対して適宜変更(InnoDB化など)を加えスキーマの作成完了となる。

MySQL4.xでの非互換な変更

MySQL4.0からのアップデートの場合非互換なテーブル定義がいくつかある。具体的にはvarchar binaryとtimestampの記述方法で以下のように置換する必要がある。

s/varchar\((\d+)\) binary/varbinary($1)/ 
s/timestamp\(\d+\)/timestamp/;

してから5.0サーバーにリストアするとよい。

データのダンプ/リストア

スキーマが出来上がったら次に実データをダンプ/リストアすることを目指す。基本的には以下のようなコマンドで良い。

mysqldump -c  -t -n --add-drop-table=0 --skip-comments --default-character-set=binary --order-by-primary --skip-extended-insert -B hoge  > mysql.dump

mysql DATABASE_NAME --default-character-set=binary < mysql.dump

ポイントとしては

-c(--complete-insert)
- insertにカラム名も含める。カラムの増減、順序の変更行っても大丈夫なように
-t(--no-create-info)
- create table文は前項の通り、別に作っているので出力しない
-order-by-primary
- リストア時の時間短縮
--skip-extended-insert
- insert文が1行ずつになる。リストア時間は遅くなる
- 後述
--default-character-set=binary
- ダンプ/リストア時バイナリを指定して余計な文字コードの変換が起こらないように

以上のようにダンプしてきたデータを予めレプリケーションを設定していたmysqlサーバーへリストアする。つまり、MySQL4.0からのアップグレードの場合は5.0 -> 5.1 -> 5.5 -> 5.6までのレプリケーションを設定しておき5.0サーバーへリストア、その後、4.0と5.0のレプリケーションを設定して完了という運びになる。

change master to文の生成については新し目のMySQLであれば--dump-slaveなどが使えるのでそれを使えば良い。MySQL4.0にはそういうのない(正確に言うとクライアントの機能だが)のでレプリケーション止めてshow master statusみて〜〜という手順踏んだ。

ここらへんのオペレーションについては一般的な話なので詳しくしないが、WEB+DB PRESS Vol.94 の「大規模インフラ運用最前線 ── 増え続けるデータをどう処理し，活用するか【第3回】データベースのバックアップとリストア ……すばやく安全に復旧するために必要なこと」が詳しいのでよく分かっていなければ読むと良い。

リストアが終わらない

ありがちな話としてデータ量が多かったりインデックスの生成が重かったりして、リストアが終わらないパターンがある。普段物理バックアップのリストアしかしていないと気づかないことがある。対処法としてはAWSなどのクラウドならばiopsを金で買う、リストア時のみインデックス定義のない(primary keyのみ)スキーマを使いリストアが完了した後にalter table文を流しインデックスを復元するなどの手段がある。

無駄にインデックス貼りまくってるパターンの場合、事前に解消できるのであればしておくと良い。

MySQL4/MyISAMの話

MySQL4が悪いのかMyISAMが悪いのか定かではないが、とにかく予想外のデータが入っていることが多かった。予想外とは時刻系のカラムに時刻ではないものが入っていたり、テーブル定義の文字コード範囲外の文字が入っていたり(ujisに絵文字が入っていてアプリケーションとしても普通に表示できてしまっている)という具合である。

こういうときどうすればいいかというと気合で直すか諦めるしかない。気合で直すとはデータを一つ一つみてそれらしいデータに直したりすることで、諦めるとは仕様上char系のカラムある必要ないのであればbinary系にしてしまったり、アプリケーションの仕様を変え壊れたデータがそもそも必要なくす方向にもっていったりという感じである。

またMyISAMにはデータの修復コマンドmysqlcheckがあるので試してみると解決したりしなかったりする。

データがおかしい事になってるとリストア時にエラーになるわけだが--skip-extended-insertするとエラーが出ている行数のダンプファイルをみればどういうデータによってエラーになっているのか特定しやすくなるので修復作業がしやすくなる。

動作検証

クライアントから接続できるか

特に古いMySQLから一気にバージョンを上げると問題になりがちだが、クライアント(libmysqlclient)のバージョンが低すぎて繋げない可能性がある。具体的には新サーバーをold_passwords=0で作ったがクライアントはハッシュ長16バイトpasswordしか対応していないみたいなパターンである。(いつの時代のmysqlクライアントの話をしているのと思われるかも知れないが、MySQL4.xがある時点で察してほしい)

今どきold_passwordsを気にしなくてはならない世界は大半の人間にとって関係ない話ではあるが、サーバーとクライアントのバージョンや設定によっては接続できなくなる可能性があるのでしっかり確認すること。(そもそも接続できるかは一番最初に確認してどこまでバージョンあげるかどういう設定にするかを決めるべきなのでここでは最終的な確認ということになる)

データの確認

ダンプファイルからリストアしたデータ
現masterからのレプリケーションによって生成されたデータ
クライアントから直接発行されたクエリにより生成されたデータ

の3種類があるのでそれぞれおかしくなっていないか確認する。3.については検証用mysqlサーバーを一つ作成しておいて開発環境からつないで検証する。

レプリケーションが止まっていないか

止まっている場合、何かしらおかしいので確認する。ありがちなのはバージョンがあがったMySQLでSQLモードが設定されていて今までのクエリを受け付けなくなっていたパターン。

参照用slaveに組み込んでみる。

現master(4.0)  <- アプリケーションからの更新クエリ/参照クエリ 
   -> relay(5.0) 
       -> relay(5.1) 
           -> relay(5.5) 
              -> 新master(5.6)

こうなっているのを

現master(4.0) <- アプリケーションからの更新クエリ 
   -> relay(5.0) 
       -> relay(5.1) 
           -> relay(5.5) 
              -> 新master(5.6)   <- アプリケーションからの参照クエリ

こうすることにより上手く参照できるか確認してみる。パフォーマンスの差異も確認する。この段階では参照クエリをしか向けていないので問題があればもとに戻せる。

ただし、ある程度レプリケーションが止まらないという確証ができてからやらないと止まったときに本番に影響が出るので注意。また当然レプリケーションの遅延は増大するのでそのあたりも頭に入れておく必要がある。

自分のときは実行計画が変わってしまいクエリの処理が遅くなってしまったみたいな事があった(analyze tableしたらもとに戻った)。

更新クエリの確認

基本的にはレプリケーションにより更新クエリは受けているので、そこまで念入りに確認する必要はないが、一応やっておくと良い。更新系クエリに関しては本番に組み込んで検証、ということはできないので検証用のmysqlサーバーを作成し開発環境からそこに繋ぎ問題ないか確認する。

スイッチオーバー

いよいよ最終工程である。これが終わればアップデート完了だが、これ以降は切り戻しできないので慎重に。

現master(4.0) <- アプリケーションからの更新クエリ 
   -> relay(5.0) 
       -> relay(5.1) 
           -> relay(5.5) 
              -> 新master(5.6)   <- アプリケーションからの参照クエリ

を現master(4.0)と新master(5.6)間でスイッチオーバーさせ

現master(4.0) 
   -> relay(5.0) 
       -> relay(5.1) 
           -> relay(5.5) 
              -> 新master(5.6)   <- アプリケーションからの更新クエリ/参照クエリ

のようにする。

スイッチオーバーについては普段やっているやり方でやれば良い(keepalivedだったりMHAだったり)。mysqlサーバーのスイッチオーバーではなく、アプリケーションのDSNを書き換えて切り替える方法も考えられるが、自分は後述する理由でスイッチオーバーで切り替えるほうが多かった。

以上がMySQLアップデートの全行程である。

MySQLのアップデートをしやすくするために

アップデートを通じて学んだポイントとして以下のように設計/運用していればアップデートしやすかったなと感じた。

メンテナンスウィンドウ設けられるように
複数のアプリケーションから接続されている状態(共有DBやめる)を作らない
使わないデータベース/テーブル/インデックスは削除する
MyISAMやめる
参照用のslaveを見れる仕組みを用意しておく

1.について

メンテナンスウィンドウを設けられるとそもそもmysql_upgradeで良かったり、レプリケーション組まなくてもメンテナンス開始->ダンプ->リストア->動作確認でよかったりと取れる手法の幅が広がる。

2.について

接続してるアプリケーションが多いと確認箇所も増えるし、非互換な変更があったときコード改修がアプリケーション分必要になるので大変になる。また更新系の切り替えの際、アプリケーションのDSNで切り替えるやり方を取りづらくなる(メンテナンスウィンドウ設けない場合、同時に切り替えなくちゃならないので難しい)。

3.について

確認が必要な箇所を少なくしましょうという話と、データ量やリストア時間が長いとそれだけで精神的にキツイし、取れる手法も減っていくので不要なものは事前に削除するとよい。

4.について

データ壊れてると別次元の難しさになるのでMyISAMは壊れるものとして割り切って運用できるようにしたい(特定用途のサーバーだけMyISAMにしてmasterはInnoDBにするなど)。

5.について

MySQLは優秀なのである程度の規模までmaster1台でも余裕でさばけてしまうため最近はmaster1台構成でアプリケーション作ることも多いはず(バックアップ用のサーバーなどはもちろんいるはず、あくまでアプリケーションから参照されるサーバーがmaster1台という意味)。

レプリ遅延を気にしたりする必要がないため初期段階では楽だが、アップデートの際は参照クエリ系だけ向けて様子を見るということができなくなる。すると、いきなり後戻り不可能な切り替えをすることになり緊張感がある。また負荷検証についても実際のクエリを得るにはgeneralログからselect文復元したりしないと"本番ではどうなるか"見極めにくく、これが結構な手間となる。

特定のエンドポイントだけ接続先を参照用DSNに切り替える仕組みがないのであればまず仕組みをつくることを考えても良いと思う。

よくある質問

数珠つなぎでアップデートしていいの？

問題ないとおもってた(し、実際数珠つなぎでアップデートして特に問題おきてない)が、こういう話もある。
MySQL レプリケーションのサポートポリシーがこっそり (?) 変わっていた話 - Qiita

一気にアップデートせず1バージョンごとにあげていくのがもちろん正道だが、いかんせん社内に太古のMySQLがはびこり過ぎててそういう状況にない。

同時にスキーマ変更するの怖い

分けるのが丁寧だけど、以下同文。とはいえレプリケーションの仕組み上問題ないことしかやってないのであまり怖いなという感覚もない。

なぜ5.6で止めているのか

以下のような理由。

5.7から完全にold_passwordsが使えなくなる
社内に5.7の知見が少ない
最終的にはAurora化を目標にしていてAuroraは5.6互換が用意されているから

5.7の変更点はかなり多い上、知見が少ない&古いDBをとにかくアップデートしたいという状況でチャレンジする必要は薄いなと感じたので手を出さなかった。

あらためて: はてなエンジニアセミナーに登壇します

今回の4.0->5.6までアップデートしたMySQLをさらにAuroraへ移行する話をします。興味があればお越しください。

developer.hatenastaff.com

オリジナルのエンクロージャ:

1514236998116862

このブログを検索

IT音痴アラフィフおやじのストック記事倉庫