MySQLのバージョンアップでサイトのパフォーマンスは大幅に改善します

MySQLはバージョンアップするごとに多くの機能が追加され、パフォーマンスも大幅に改善されています。環境が許すなら最新版の利用をお勧めします。

この記事ではMySQLのバージョンアップと初期設定、管理権限を持ったユーザーの作成方法を解説します。

---

現在インストールされているMySQLのバージョンを確認

前回同様、デフォルトでインストールされているバージョンを確認します。

※コマンドラインの見方
「コマンド」「引数、その他」「コメント」「#（rootユーザ）」「$（一般ユーザ）」

# mysql --version
mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

どうやら「5.0.95」がインストールされているようです。

「MySQL 5.0.95」をアンインストールします

# yum erase mysql

Loaded plugins: downloadonly, fastestmirror, priorities
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package mysql.x86_64 0:5.0.95-1.el5_7.1 set to be erased
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: dovecot
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: dovecot
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient_r.so.15()(64bit) for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.15(libmysqlclient_15)(64bit) for package: mysql-server
--> Processing Dependency: mysql = 5.0.95-1.el5_7.1 for package: mysql-server
--> Running transaction check
---> Package dovecot.x86_64 0:1.0.7-7.el5_7.1 set to be erased
---> Package mysql-server.x86_64 0:5.0.95-1.el5_7.1 set to be erased
---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be erased
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================
 Package                         Arch                    Version                              Repository                  Size
===============================================================================================================================
Removing:
 mysql                           x86_64                  5.0.95-1.el5_7.1                     installed                  8.2 M
Removing for dependencies:
 dovecot                         x86_64                  1.0.7-7.el5_7.1                      installed                  3.7 M
 mysql-server                    x86_64                  5.0.95-1.el5_7.1                     installed                   22 M
 perl-DBD-MySQL                  x86_64                  3.0007-2.el5                         installed                  328 k

Transaction Summary
===============================================================================================================================
Remove        4 Package(s)
Reinstall     0 Package(s)
Downgrade     0 Package(s)

Is this ok [y/N]: y　←アンインストールをしていいかYES/NOと聞かれるので「y」と入力してEnter
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Erasing        : perl-DBD-MySQL                                                                                          1/4
  Erasing        : mysql                                                                                                   2/4
  Erasing        : mysql-server                                                                                            3/4
警告: /var/log/mysqld.log は /var/log/mysqld.log.rpmsave として保存されました。
  Erasing        : dovecot                                                                                                 4/4
警告: /etc/dovecot.conf は /etc/dovecot.conf.rpmsave として保存されました。

Removed:
  mysql.x86_64 0:5.0.95-1.el5_7.1

Dependency Removed:
  dovecot.x86_64 0:1.0.7-7.el5_7.1      mysql-server.x86_64 0:5.0.95-1.el5_7.1      perl-DBD-MySQL.x86_64 0:3.0007-2.el5

Complete!

「mysql」「perl-DBD-MySQL」「mysql-server」「dovecot」がアンインストールされました。

途中で表示されている「警告」にもありますが、設定ファイルの「dovecot.conf」を自動でバックアップしています。

今回はバックアップしてくれましたが、アンインストール時に設定ファイルも削除される場合があります。そのため、アンインストールするパッケージの設定ファイルはもちろん、依存関係でアンインストールされるパッケージの設定ファイルも、バックアップを忘れないようにしてください。

---

最新版のMySQLをインストール

PHPをインストールした時と同様に、外部リポジトリを利用してインストールするため、オプションに「–enablerepo=remi,epel,ius」を追加します。

ちなみに「mysql」という指定だけだと、32bit版も同時にインストールされてしまうので「.（ドット）」の後に「アーキテクチャ（i386もしくはx86_64）」を追記します。
（例：mysql.x86_64）

yumでインストールできるMySQLの最新版は以下のコマンドで確認してください。

# yum list --enablerepo=remi,epel,ius | grep mysql

どうやらmysql55.x86_64とmysql55-mysql-serverがインストールできるようです。

# yum install --enablerepo=remi,epel,ius mysql55.x86_64 dovecot mysql55-mysql-server perl-DBD-MySQL

Loaded plugins: downloadonly, fastestmirror, priorities
Loading mirror speeds from cached hostfile
 * addons: mirror.centos.org
 * base: www.ftp.ne.jp
 * contrib: mirror.centos.org
 * epel: ftp.iij.ad.jp
 * extras: www.ftp.ne.jp
 * ius: mirror.sothatswhy.org.uk
 * remi: remi-mirror.dedipower.com
 * updates: www.ftp.ne.jp
0 packages excluded due to repository priority protections
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package dovecot.x86_64 0:1.0.7-7.el5_7.1 set to be updated
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: dovecot
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: dovecot
---> Package mysql.x86_64 0:5.5.28-1.el5.remi set to be updated
--> Processing Dependency: mysql-libs = 5.5.28-1.el5.remi for package: mysql
---> Package mysql-server.x86_64 0:5.5.28-1.el5.remi set to be updated
---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be updated
--> Running transaction check
---> Package mysql-libs.x86_64 0:5.5.28-1.el5.remi set to be updated
---> Package mysql50.x86_64 0:5.0.96-2.ius.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================
 Package                          Arch                     Version                                Repository              Size
===============================================================================================================================
Installing:
 dovecot                          x86_64                   1.0.7-7.el5_7.1                        base                   1.7 M
 mysql                            x86_64                   5.5.28-1.el5.remi                      remi                   7.5 M
 mysql-server                     x86_64                   5.5.28-1.el5.remi                      remi                    13 M
 perl-DBD-MySQL                   x86_64                   3.0007-2.el5                           base                   148 k
Installing for dependencies:
 mysql-libs                       x86_64                   5.5.28-1.el5.remi                      remi                   1.1 M
 mysql50                          x86_64                   5.0.96-2.ius.el5                       ius                    4.3 M

Transaction Summary
===============================================================================================================================
Install       6 Package(s)
Upgrade       0 Package(s)

Total download size: 27 M
Is this ok [y/N]: y　←インストールをしていいかYES/NOと聞かれるので「y」と入力してEnter
Downloading Packages:
(1/6): perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm                                                           | 148 kB     00:00
(2/6): mysql-libs-5.5.28-1.el5.remi.x86_64.rpm                                                          | 1.1 MB     00:03
(3/6): dovecot-1.0.7-7.el5_7.1.x86_64.rpm                                                               | 1.7 MB     00:00
(4/6): mysql50-5.0.96-2.ius.el5.x86_64.rpm                                                              | 4.3 MB     00:03
(5/6): mysql-5.5.28-1.el5.remi.x86_64.rpm                                                               | 7.5 MB     00:08
(6/6): mysql-server-5.5.28-1.el5.remi.x86_64.rpm                                                        |  13 MB     00:09
-------------------------------------------------------------------------------------------------------------------------------
Total                                                                                          928 kB/s |  27 MB     00:30
警告: rpmts_HdrFromFdno: ヘッダ V3 DSA signature: NOKEY, key ID 00f97f56
remi/gpgkey                                                                                             | 1.3 kB     00:00
Importing GPG key 0x00F97F56 "Remi Collet " from /etc/pki/rpm-gpg/RPM-GPG-KEY-remi
Is this ok [y/N]: y　←RPM-GPG-KEY-remiをインストールをしていいかYES/NOと聞かれるので「y」と入力してEnter
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : mysql50                                                                                                 1/6
  Installing     : perl-DBD-MySQL                                                                                          2/6
  Installing     : dovecot                                                                                                 3/6
  Installing     : mysql                                                                                                   4/6

WARNING : This MySQL RPM is not an official Fedora / Red Hat build and it
overrides the official one. Don't file bugs on Fedora Project nor Red Hat.
Use dedicated forums http://forums.famillecollet.com/

  Installing     : mysql-libs                                                                                              5/6
  Installing     : mysql-server                                                                                            6/6

Installed:
  dovecot.x86_64 0:1.0.7-7.el5_7.1          mysql.x86_64 0:5.5.28-1.el5.remi      mysql-server.x86_64 0:5.5.28-1.el5.remi
  perl-DBD-MySQL.x86_64 0:3.0007-2.el5

Dependency Installed:
  mysql-libs.x86_64 0:5.5.28-1.el5.remi                            mysql50.x86_64 0:5.0.96-2.ius.el5

Complete!

これでインストールできました。

2014年3月追記

この方法だと公式のリポジトリであるbaseのMySQLが優先してインストールされるようになったようです。remiのMySQLを優先する場合は「--disablerepo=base」というオプションを付けて公式のものを無効にする必要があります。

Dovecotをアンインストールした時にメッセージが出ていましたが、設定ファイルは「/etc/dovecot.conf.rpmsave」にバックアップがあるので、「/etc/dovecot.conf」と内容を差し替えれば元の状態に戻ります。

最後にMySQLの5.5がインストールされいるか確認します。

# mysql --version
mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1

「MySQL 5.5.28」にバージョンアップされています。

---

MySQLの初期設定

インストールが完了したので、初期設定をしていきます。
Osukiniサーバの場合は既にrootユーザーのパスワード設定やtestデータベースの削除は行われていますが、インストールした時の作法として「mysql_secure_installation」を実行します。

「mysql_secure_installation」はMySQLのrootユーザーパスワード（UNIX認証とは別）の設定や、リモートからのrootユーザー、または匿名ユーザーのアクセス拒否など「安全にMySQLを利用するためのコマンド」です。

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):　←rootユーザーのパスワードを入力。VPS提供のもの。設定していない場合は何も入力せずEnter
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y　←ユーザーパスワードを変更するか聞かれるのでYESの「y」と入力してEnter
New password:　←新しいパスワードを入力
Re-enter new password:　←再度、新しいパスワードを入力
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]　←匿名ユーザーの削除。何もせずEnterで有効
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]　←リモートからrootユーザーのログイン禁止。何もせずEnterで有効
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]　←testデータベースの削除。何もせずEnterで有効
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]　←ユーザー権限のテーブルをリロード（先ほど設定したパスワードを有効にするか）。何もせずEnterで有効
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

これで最低限の設定は完了です。

テーブルのアップデート

MySQLをアップデートした時は、テーブルデータも新しいバージョンのものに対応させるためにアップデートする必要があります。

# mysql_upgrade -u root -p
Enter password:　←MySQLのrootユーザーパスワードを入力。

これだけで、自動で変換してくれます。

「/etc/my.cnf」の設定

MySQLのチューニングに使う「/etc/my.cnf」はとても奥が深く、動作環境はもちろん、SQLコマンドの利用法、他のキャッシュ機能との連携を考慮したりと、最適な値を導き出すのは知識と経験が必要です。
今回は初期設定ということで、文字コードに関する設定だけにとどめます。

まずは設定ファイルを編集する前の作法、バックアップをとります。
「/etc/my.cnf」の場合は細かなチューニングの際にベンチマークすることが多いので「org2、org3」といった具合にバージョン管理しておくと便利です。

# cd /etc
# cp my.cnf my.cnf.org

my.cnfを編集します。

# vi /etc/my.cnf

13行目あたり「character-set-server = utf8」を編集します。
「デフォルトのエンコード」は昔「default-character-set = utf8」と指定していましたが、新しいバージョンでは廃止になったようです。

# character-set-server = utf8　←コメントアウト

# デフォルトのエンコード
character-set-server = utf8

# 文字コードの照合順序。UTF-8から照合してくれる。
collation-server = utf8_general_ci

# SET NAMES utf8を打つまでもなくエンコードを指定してくれる。
init-connect = SET NAMES utf8

続いてクライアント側の文字コードも明示的に指定します。ファイルの最後に以下設定を追加します。[mysqld]では廃止ですが、[mysql]や[client]では「default-character-set = utf8」を使います。

[mysql]
# デフォルトのエンコード。
default-character-set = utf8

[client]
# デフォルトのエンコード。
default-character-set = utf8

以上設定が終了したら、MySQLを再起動します。

# /etc/init.d/mysqld restart

続いて確認するためにMySQLにログインします。

# mysql -u root -p
Enter password:　←MySQLのパスワードを入力
（省略）
mysql>

このようにMySQLのデフォルトのコマンドプロンプト「mysql>」が表示されるはずです。

プロンプトの後に以下のコマンドを入力します。

mysql> show variables like 'char%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

このようにサーバ、クライアント共にUTF-8が指定されていれば問題ありません。

MySQLの自動起動を設定

まずは自動起動が有効になっているかチェックします。

# chkconfig --list mysqld
mysqld          0:off   1:off   2:off   3:off   4:off   5:off   6:off

「3」が「off」なら自動起動は有効ではありません。以下のコマンドで有効にします。

# chkconfig mysqld on

以上でMySQLの初期設定は終了です。

---

webminでrootパスワードを設定する方法

管理パスワード（rootユーザーのパスワード）の変更は「webmin」で設定するほうが楽なので、解説します。

「webminの管理画面」を開きメニューから「サーバ ＞ MySQL データベース サーバ」を開きます。
するとデータベース管理用のユーザー名とパスワードを求められるので、
ユーザー名「root」
パスワード「VPSから渡されたSQLのログインパスワード」（もちろん新しく設定しても構いません）
として、「保存」ボタンをクリックします。

管理パスワードを変更したい場合は「サーバ ＞ MySQL データベース サーバ」で「グローバルオプション」にある「管理パスワードの変更」をクリックして変更してください。

このように、SQLコマンドの操作に慣れるまでは、WebminやphpMyAdminなど、補助ツールの利用をお勧めします。

---

MySQLのrootユーザーパスワードを忘れた場合の対処法

「UNIX認証」と「MySQLのrootパスワード」は別に管理されているため、忘れてしまうこともあるかもしれません。そんな場合はセーフモードでログインすることで新しいパスワードを設定することができます。

まずMySQLを停止します。

# /etc/init.d/mysql stop

続いてセーフモードでMySQLを起動。

# mysqld_safe --skip-grant-tables &

rootユーザーでログイン。

# mysql -u root

MySQLで以下のSQLコマンドを打ちます。
例は、「root」ユーザーに「new_password」を設定する方法。
「PASSWORD(‘hoge’)」というのはMySQLでパスワードを暗号化する書式です。

mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE User='root';

権限テーブルから権限を再読み込みする。

mysql> FLUSH PRIVILEGES;

SQLから抜けます。

mysql> EXIT

最後にMySQLを再起動します。
このままだとパスワードなしでログインできるセーフモードのため、非常に危険です。忘れずに再起動してください。

# /etc/init.d/mysqld restart

---

rootユーザーを削除した状態で、管理権限を持ったユーザーのログインパスワードを忘れた場合の対処法

こうなるとかなり重症で「SQLコマンドでユーザーを追加する」必要があります。あまりこのような状態になることは無いと思いますが、にっちもさっちもいかなくなった人用ですｗ

まずMySQLを停止します。

# /etc/init.d/mysql stop

続いてセーフモードでMySQLを起動。

# mysqld_safe --skip-grant-tables &

rootユーザーでログイン。

# mysql -u root

mysqlテーブルへ移動。

mysql> USE mysql;

userテーブルへデータの挿入
この例では「localhost」からの接続を許可、ユーザー名「root」、パスワード「new_password」という指定です。

INSERT INTO user (Host,User,Password)
VALUES('localhost','root',PASSWORD('new_password'));

続いてrootユーザーに権限を与えます

UPDATE  `mysql`.`user` SET
`Select_priv` =  'Y',
`Insert_priv` =  'Y',
`Update_priv` =  'Y',
`Delete_priv` =  'Y',
`Create_priv` =  'Y',
`Drop_priv` =  'Y',
`Reload_priv` =  'Y',
`Shutdown_priv` =  'Y',
`Process_priv` =  'Y',
`File_priv` =  'Y',
`Grant_priv` =  'Y',
`References_priv` =  'Y',
`Index_priv` =  'Y',
`Alter_priv` =  'Y',
`Show_db_priv` =  'Y',
`Super_priv` =  'Y',
`Create_tmp_table_priv` =  'Y',
`Lock_tables_priv` =  'Y',
`Execute_priv` =  'Y',
`Repl_slave_priv` =  'Y',
`Repl_client_priv` =  'Y',
`Create_view_priv` =  'Y',
`Show_view_priv` =  'Y',
`Create_routine_priv` =  'Y',
`Alter_routine_priv` =  'Y',
`Create_user_priv` =  'Y',
`Event_priv` =  'Y',
`Trigger_priv` =  'Y',
`Create_tablespace_priv` =  'Y'  WHERE User='root';

SQLから抜けます。

mysql> EXIT

最後にMySQLを再起動します。

# /etc/init.d/mysqld restart

---

MySQLの起動時にエラーが出る場合の対処

下記のエラーが出て起動できない場合の対処法です。

# /etc/init.d/mysqld start
MySQL Daemon failed to start.
mysqld を起動中:                                           [失敗]

エラーログを表示します。

# tail -n 100 /var/log/mysqld.log

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using Linux native AIO
InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 128000 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

[Note] /usr/libexec/mysqld: Shutdown complete

以上のようなエラーが表示されていた場合は「my.cnf」ファイルで設定したinnodb用の設定「innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend」と、「/var/lib/mysql/」に保存されたファイルが異なることが原因です。

「ibdata1:2000M」とはinnodbで共通に利用する2GBの共用ディスクスペース。
「ibdata2:10M」とは「ibdata1」があふれた際に利用される追加のスペースで、「autoextend」が指定されていると、ファイルサイズは必要量に合わせて拡張されていきます。

この設定を有効にする前にMySQLを起動すると、異なるサイズの「ibdata1」が作成されます。すると起動時に「設定ファイルに2GBと書いてあるけど、実際のファイルは○GBだよ！」とエラーが出ます。（初期設定では20MB程度のファイルが作成されています）
正しいサイズでファイルを作りなおすには1度削除する必要があります。

ファイルを確認します。

# cd /var/lib/mysql/
# ls
ib_logfile0  ib_logfile1  ibdata1 …

この例では「ib_logfile0」「ib_logfile1」「ibdata1」が原因なので、削除します。

# rm ib_logfile0 ib_logfile1 ibdata1

削除していいか聞かれるので「y」と入力してEnter。

再びMySQLを起動します。初回はファイルを作成する時間がかかるので、長ければ数分待ちます。

# /etc/init.d/mysqld start
mysqld を起動中:                                           [  OK  ]

以上のように[ OK ]と出れば正常に起動できています。

---

以上でMySQLのバージョンアップと、初期設定。管理権限を持ったrootユーザーの作成ができました。
次は「phpMyAdminのバージョンアップに合わせて、初期設定と拡張機能の有効化」を解説します。