CentOS6.8のMySQLをyumでMySQL5.6にアップデートする

WordPressの推奨環境はMySQL5.6。CentOS6標準のMySQLは5.1系なので必要要件(5.0.15以上)はクリアしていますが、古さを否めません。5.1→5.6によるチューニングの伸びしろが隠されているのでは?と5.6を導入してみることにしました。

前提条件

MySQL5.6はInnoDBで最も性能が出るようです。WordPress用にMyISAMを利用している場合はInnoDBに変換しておく必要があります。

アップデート手順

ダウンタイムを最小限に抑えるために手順を工夫します。

1.CentOS5.6レポジトリインストール
2.my.cnfを5.6用に修正する
3.MySQL5.1を5.6にアップデート
4.mysqld起動
5.DBマイグレーション実行
6.MySQL5.6用にスクリプトを修正

1.CentOS5.6レポジトリインストール

RHEL6用の公式レポジトリを利用してyumでアップデートします。

# yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
・・・
 省略
  ・・・

インストール:
  mysql-community-release.noarch 0:el6-5
完了しました!
2.my.cnfを5.6用に修正する

5.1用コンフィグのままでも5.6で動作するようですが、仕様変更・廃止された設定・既知のバグ対策のためにコンフィグを修正します。

デフォルトストレージエンジンがInnoDBに変わりInnoDB Pluginの指定が不要になったので削除
-##innodb plugin
-ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

サービス起動時、TIMESTAMPのデフォルト値についてエラーが出るので対処する
+explicit_defaults_for_timestamp=1

メモリマネージャーをInnoDBではなくOS標準実装のものを利用する。
関連してInnoDBのメモリ割り当て設定が不要になるので削除する。※Warningログ対策
+innodb_use_sys_malloc=1
-innodb_additional_mem_pool_size = 4M

アップデート後にmysqldのメモリ消費が300MB程度増加していることに気付き、以下の設定を追加しました。MySQL5.6.10からデフォルト値が400+table_open_cache/2の計算式になったようで、値が801を超えるとメモリ消費が大幅に増える場合があるようです。メモリ1GBのサーバに300MBの増加はきついので、ひとまず5.6.9までのデフォルト値だった400を追加します。

+table_definition_cache = 400
3.MySQL5.1を5.6にアップデート

まずは現在導入saされているmysqlのバージョンを確認します。

# yum list installed |grep mysql
mysql.x86_64            5.1.73-7.el6    @base
mysql-connector-odbc.x86_64
mysql-libs.x86_64       5.1.73-7.el6    @base
mysql-server.x86_64     5.1.73-7.el6    @base

準備ができているのでyum updateでmysql一式を5.6にアップデートします。

# yum update mysql*
mysql-connectors-community                               | 2.5 kB     00:00
mysql-connectors-community/primary_db                    | 9.9 kB     00:00
mysql-tools-community                                    | 2.5 kB     00:00
mysql-tools-community/primary_db                         |  31 kB     00:00
mysql56-community                                        | 2.5 kB     00:00
mysql56-community/primary_db                             | 159 kB     00:00
依存性を解決しました

================================================================================
 パッケージ                アーキテクチャ
                                  バージョン   リポジトリー                容量
================================================================================
インストールしています:
 mysql-community-client    x86_64 5.6.33-2.el6 mysql56-community           18 M
     置き換えています  mysql.x86_64 5.1.73-7.el6
 mysql-community-libs      x86_64 5.6.33-2.el6 mysql56-community          1.9 M
     置き換えています  mysql-libs.x86_64 5.1.73-7.el6
 mysql-community-libs-compat
                           x86_64 5.6.33-2.el6 mysql56-community          1.6 M
     置き換えています  mysql-libs.x86_64 5.1.73-7.el6
 mysql-community-server    x86_64 5.6.33-2.el6 mysql56-community           54 M
     置き換えています  mysql-server.x86_64 5.1.73-7.el6
更新:
 mysql-connector-odbc      x86_64 5.3.6-1.el6  mysql-connectors-community 3.0 M
依存性関連でのインストールをします。:
 mysql-community-common    x86_64 5.6.33-2.el6 mysql56-community          308 k

トランザクションの要約
================================================================================
インストール         5 パッケージ
アップグレード       1 パッケージ

総ダウンロード容量: 79 M
これでいいですか? [y/N]y

レポジトリ初回利用時は公開鍵導入を確認されるので、「y」で次に進みます。

パッケージをダウンロードしています:
警告: rpmts_HdrFromFdno: ヘッダ V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Retrieving key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid : MySQL Release Engineering <mysql-build@oss.oracle.com>
 Package: mysql-community-release-el6-5.noarch (@/mysql-community-release-el6-5.noarch)
 From   : file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
これでいいですか? [y/N]y
インストール:
  mysql-community-client.x86_64 0:5.6.33-2.el6
  mysql-community-libs.x86_64 0:5.6.33-2.el6
  mysql-community-libs-compat.x86_64 0:5.6.33-2.el6
  mysql-community-server.x86_64 0:5.6.33-2.el6

依存性関連をインストールしました:
  mysql-community-common.x86_64 0:5.6.33-2.el6

更新:
  mysql-connector-odbc.x86_64 0:5.3.6-1.el6

置換:
  mysql.x86_64 0:5.1.73-7.el6             mysql-libs.x86_64 0:5.1.73-7.el6
  mysql-server.x86_64 0:5.1.73-7.el6

完了しました!
4.mysqld起動

アップデート完了後、mysqld(mysql-community-server)サービスを手動起動する必要があります。起動に成功できたら、次にchkconfigで自動起動するように再設定します。

# service mysqld start
mysqld を起動中:                                           [  OK  ]

# chkconfig mysqld on
# chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
5.DBマイグレーション実行

この状態でもDBとWordPressは正常に動作していますが、mysqld起動時に大量のエラーログがmysqld.logに記録されています。

[ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

5.1→5.6の間に追加されたDB構造の足りない個所についてのエラーです。ログに表示されているようにmysql_upgradeを実行します。

# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
wordpress_db.wp_commentmeta                        OK
wordpress_db.wp_comments                           OK
wordpress_db.wp_links                              OK
wordpress_db.wp_options                            OK
wordpress_db.wp_postmeta                           OK
wordpress_db.wp_posts                              OK
wordpress_db.wp_term_relationships                 OK
wordpress_db.wp_term_taxonomy                      OK
wordpress_db.wp_termmeta                           OK
wordpress_db.wp_terms                              OK
wordpress_db.wp_usermeta                           OK
wordpress_db.wp_users                              OK
OK
6.MySQL5.6用にスクリプトを修正

MySQL5.6から、コマンドに直接パスワードを入力すると下記のWarningが表示されます。

Warning: Using a password on the command line interface can be insecure.

実際に手で入力する時は無視するだけで良いのですが、スクリプトをcronで定期実行していると毎回上記のWarningエラーが発生しメールで通知されてしまい非常に厄介なので対策を行います。

ユーザ・パスワード情報をファイルに保存し、mysql系コマンドの直後に--defaults-extra-fileでファイルを指定するのが良いとのことです。なおファイルのパーミッションにはご注意を。

# vi /etc/.mypswd.cnf
[client]
user = root
password = mysqlpassword

スクリプトを修正する際は--defaults-extra-fileの位置に気を付けてください。mysql系コマンドの直後に書かないと認識できないようです。
下記のスクリプトはmysqldumpでDBをバックアップをMySQL5.6用に修正したものです。

# vi mysqlbackup.sh#!/bin/bash
## 変数に値を設定
HOSTNAME=XXXXXXXX
-SQLUSER=YYYYYYY
-SQLPASSWD=ZZZZZZ
+SQLPSWD=/etc/.mypwd.cnf
DESTINATION=/data/backup/ #バックアップ先
BACKUP_RETENTION_PERIOD=14 #バックアップ期間(14日間)

## mysqldump実行
-mysqldump --opt --events -u${SQLUSER} -p${SQLPASSWD} -x --all-databases | gzip > ${DESTINATION}${HOSTNAME}_dbackup_`date +%Y%m%d`.gz
+mysqldump --defaults-extra-file=${SQLPSWD} --opt --events -x --all-databases | gzip > ${DESTINATION}${HOSTNAME}_dbackup_`date +%Y%m%d`.gz

## BACKUP_RETENTION_PERIODで設定した期間以前のものは自動削除
find ${DESTINATION} -mindepth 0 -mtime +${BACKUP_RETENTION_PERIOD} -exec rm -f {} \;

exit
MySQL5.6へアップデートした効果は?

上記の設定ではapacheベンチ上では1割ほど速度が低下してしまいました。その代わりLoad averageが減少したことを確認できているので全体としては高速化しているようです。
パラメータチューニングでもう少しパフォーマンスを改善できましたので、次回の記事で紹介しようと思います。