Oracle MySQL Cloud Serviceのダッシュボードにパッチがアナウンスされていたのでパッチの適用を行いました。基本的な適用フローは以下の手順となります。

プリチェック → バックアップ(時間はDBサイズによる)→ パッチ適用 → パッチ適用完了
※バックアップはmysqldump(論理バックアップ)では無く、mysqlbackup(物理バックアップ)なので処理は高速です。

MySQL Cloud Serviceに関しては、こちらにてドキュメントとトライアルを確認する事が出来ます。
https://cloud.oracle.com/ja_JP/mysql
※ 2016年12月現在ではU.SとEMEAでのみ、MySQL Cloud Serviceを提供しているので検証環境、海外サービス展開、Enterprise版の機能検証に利用するのが宜しいかと思います。

■ パッチ適用前

mysql> select @@hostname,@@version,now();
+-----------------+-------------------------------------------+---------------------+
| @@hostname      | @@version                                 | now()               |
+-----------------+-------------------------------------------+---------------------+
| study01-mysql-1 | 5.7.15-enterprise-commercial-advanced-log | 2016-12-13 08:16:06 |
+-----------------+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

1) パッチ適用前の状態

2) パッチ適用前の事前チェック

3) 事前チェック完了

4) パッチ適用開始による事前フルバックアップの自動実施

5) バックアップ完了と同時にパッチの適用開始

6) パッチ適用の完了

補足:バックアップの完了した時点でのダッシュボードでの履歴確認

■ パッチ適用中のバックグラウド処理

1) MySQL5.7.16のバイナリーを取得

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   15317     1  0 08:16 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   16372 15317  1 08:16 ?        00:00:01 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   17135 17122  0 08:19 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/msaas-provisioning-utils.sh && fetchArtifact /tmp/patch20161213-081909/msaas-installer-patch.zip  https://em2.storage.oraclecloud.com/v1/backups-jimops/jcs MSCS/5.7.16/MySQL_Binaries-5.7.16.zip
oracle   17183 15032  0 08:19 pts/0    00:00:00 grep mysql

2) 展開

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   17242 17122  0 08:19 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/msaas-provisioning-utils.sh && source /u01/app/oracle/tools/mscs/mysql/vm-scripts/mysql-installation-utils.sh && extractMysql /tmp/patch-msaas-installers20161213-081909/mysql/*.tar.gz 5.7.16 /u01/bin oracle
oracle   17244 17243 11 08:19 ?        00:00:01 tar zxvf /tmp/patch-msaas-installers20161213-081909/mysql/mysql-installer-5.7.16.tar.gz --directory=/u01/bin
oracle   17289 15032  0 08:19 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

3) MySQLへのネットワークアクセスをOFFにして起動して、パッチを適用 (ホストにはアクセス可能)

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   17346     1  0 08:20 ?        00:00:00 /bin/sh ./bin/mysqld_safe --skip-networking
oracle   18416 17346 45 08:20 ?        00:00:02 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --skip-networking --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   18533 17122  0 08:20 ?        00:00:00 /bin/sh -c source /u01/app/oracle/tools/mscs/mysql/vm-scripts/mysql-installation-utils.sh && shutdownMysql oracle 1
oracle   18539 18533  0 08:20 ?        00:00:00 mysqladmin shutdown -uoracle
oracle   18582 15032  0 08:20 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

4) パッチ適用が完了してMySQLを再起動して完了

[oracle@study01-mysql-1 ~]$ ps -ef | grep mysql
oracle   18591     1  0 08:20 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   19646 18591  3 08:20 ?        00:00:00 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/study01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/study01-mysql-1.pid
oracle   19883 15032  0 08:21 pts/0    00:00:00 grep mysql
[oracle@study01-mysql-1 ~]$ 

■ パッチ適用後

mysql> select @@hostname,@@version,now();
+-----------------+-------------------------------------------+---------------------+
| @@hostname      | @@version                                 | now()               |
+-----------------+-------------------------------------------+---------------------+
| study01-mysql-1 | 5.7.16-enterprise-commercial-advanced-log | 2016-12-13 08:23:18 |
+-----------------+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

REST APIを利用したインスタンスの詳細確認
Path: /paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/applied

[admin@misc01 opc]$ cat temp.sh 
#!/bin/sh

curl -X GET -u "{user:password}" \
-H "X-ID-TENANT-NAME:{ServiceDomainID}" -H "Accept: application/json" \
https://psm.europe.oraclecloud.com/paas/api/v1.1/instancemgmt/{ServiceDomainID}/services/MySQLCS/instances/{InstanceName}/patches/applied
[admin@misc01 opc]$ 

以下、実行結果

[admin@misc01 opc]$ ./patchchk_opc_mysql.sh 
[{"backupStatus":"Available","additionalNote":"Apply Patch to Server.\nVersion Up to 5.7.16\nby admin","appliedBy":"opc_user","appliedDate":"2016-12-13T08:18:35.189+0000","totalTime":"2 min, 20 sec","patchId":"5.7.16-EE","patchDescription":"MySQL Server version 5.7.16","patchReleaseUrl":"https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-16.html","releaseDate":"2016-10-12T09:00:00.000+0000","resultMessage":"MySQL patching succeeded","lastStatus":"COMPLETED","lastStatusMessage":"MySQL patching succeeded","componentPatches":{"mysql":{"id":1201,"version":"5.7","releaseVersion":"5.7.16","displayName":"MySQL Server version 5.7.16","description":"MySQL Server version 5.7.16","zipBundles":{"MSAAS":{"id":1214,"md5sum":"ec534747c1e3d5d0f644ff3d9bd52115","provisioningObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","storageKey":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","releaseDate":"10/12/2016","releaseUrl":"https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-16.html","displayName":"MySQL Server version 5.7.16"}},"preserveFiles":[]}},"patchComponents":[{"id":1201,"component":"mysql","version":"5.7","md5sum":"ec534747c1e3d5d0f644ff3d9bd52115","provisioningObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","patchingObjectRef":"MSCS/5.7.16/MySQL_Binaries-5.7.16.zip","preserveFiles":[]}],"patchType":"Quarterly","patchCategory":"Upgrade","patchSeverity":"Normal","jobId":"3260506","displayName":"5.7.16","toVersion":"5.7.16","backupId":"90b6eda2-969d-4753-9ae6-e8d318760530","inProgress":false,"operationType":"None","id":77602,"patchingResult":{"patchingId":60654,"backupId":"90b6eda2-969d-4753-9ae6-e8d318760530","versionBeforeThisPatch":"mysql 5.7.15","strategy":"Rolling","releaseVersionBeforeThisPatch":"5.7.15","customRollbackId":"3260506_1481617115189","startDate":"2016-12-13T08:18:35.189+0000","endDate":"2016-12-13T08:20:55.660+0000","patchingStatus":"COMPLETED","resultMessage":"MySQL patching succeeded","additionalNote":"Apply Patch to Server.\nVersion Up to 5.7.16\nby admin","appliedBy":"opc_user","jobId":"3260506","completeLog":"","progressMessages":["8:18:35.161 AM Phase initialize started","8:18:35.339 AM Phase initialize completed","8:18:35.696 AM Phase backup started","8:19:06.850 AM Phase backup completed","8:19:07.178 AM Phase patch started","8:19:07.616 AM Phase patch-rex started","8:20:54.463 AM Phase patch-rex completed","8:20:54.984 AM Phase patch completed","8:20:55.360 AM Phase finalize started","8:20:55.532 AM Completed"]},"rollbackId":"77602","rollbackVersion":"mysql 5.7.15","currentPatchLevel":"mysql 5.7.16","progressMessages":["8:18:35.161 AM Phase initialize started","8:18:35.339 AM Phase initialize completed","8:18:35.696 AM Phase backup started","8:19:06.850 AM Phase backup completed","8:19:07.178 AM Phase patch started","8:19:07.616 AM Phase patch-rex started","8:20:54.463 AM Phase patch-rex completed","8:20:54.984 AM Phase patch completed","8
[admin@misc01 opc]$ 

確認:スレーブ→マスターの順番でアップグレードし、MySQL Cloud Serviceで稼働しているレプリケーションも問題無いことを確認済み

REST APIを利用してパッチ適用する場合は、以下のURLを参照下さい。
【書式】
1) Pre-Check

/paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/checks/{patchId}

http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/op-paas-api-v1.1-instancemgmt-%7BidentityDomainId%7D-services-MySQLCS-instances-%7BserviceName%7D-patches-checks-%7BpatchId%7D-put.html

2) Apply Patch

/paas/api/v1.1/instancemgmt/{identityDomainId}/services/MySQLCS/instances/{serviceName}/patches/{patchId}

http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/op-paas-api-v1.1-instancemgmt-%7BidentityDomainId%7D-services-MySQLCS-instances-%7BserviceName%7D-patches-%7BpatchId%7D-put.html

ALL) Patches REST Endpoints
http://docs.oracle.com/cloud/latest/mysql-cloud/CSMCS/api-Patches.html


MYSQL5.6がリリースされました!!

MYSQL5.6はこれまでのMYSQL5.5と比較してもパフォーマンスが良く、
レプリケーションも安定性と運用を考慮した改善がされているという
事でしたので、リリースを待っていました。

Web セミナー Web セミナー

MySQL Security Enhancements and Important Security Tips for ISVs & OEMs
Thursday, February 21, 2013  2013年02月22日 (金): 00:00 日本

MySQL 5.6 Performance & Scalability ~ Afina tu BD en este webinar gratuito!
Thursday, February 28, 2013  2013年03月01日 (金): 08:00 日本
  

MySQL Replication: An Introduction
http://www-jp.mysql.com/why-mysql/white-papers/mysql-replication-introduction/

The whitepaper discusses:
Replication concepts
Replication enhancements in MySQL 5.6
Replication use-cases
Replication topologies
Replication monitoring and management

MySQL Replication Tutorial: Configuration, Provisioning and Management
http://www-jp.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/

By reading this paper, you will be able to:
Configure and provision MySQL replication
Migrate to semi-synchronous replication
Administer and trouble-shoot MySQL replication

データベース管理者/開発者ガイド MySQL 5.6の新機能
http://www-jp.mysql.com/why-mysql/white-papers/whats-new-mysql-5-6-ja/

    以下抜粋

MySQL 5.6は、世界で最も普及しているオープンソース・データベースの、
これまでで最高のリリースです。次世代のWebシステムの実現や、組込みアプリケーション
およびサービスの構築 を可能にするために設計された先進的な新機能を提供します。
本書では、主要な機能と機能強化を項目ごとに紹介し、
詳細な技術情報や実装関連情報を提供する事例や参考資料を示します。本書によって、
以下に挙げるような、MySQL 5.6の以前のリリースより改善・強化した点を理解することができます。

パフォーマンスとスケーラビリティの向上
InnoDBストレージ・エンジンの改良によるトランザクション・スループットの向上
オプティマイザの改良によるクエリ実行時間と診断機能の改善
オンラインでのDDL/スキーマ操作によるアプリケーション可用性の向上
Memcached APIを用いたInnoDBへのNoSQLアクセスによる開発速度の向上
レプリケーションの改善による高いパフォーマンスと自己修復可能なクラスタ構成
パフォーマンス・スキーマの改良による性能統計情報管理と監視機能の改善
セキュリティの向上による容易なアプリケーション設計
その他の重要な機能強化


検証用のレプリケーションの環境にてスレーブのMYSQLを5.6にアップグレードしてみました。

レプリケーションの確認

マスター側にてデータをINSERTして確認。

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 0');
Query OK, 1 row affected (0.12 sec)

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 1');
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values('Before upgrade','MYSQL 5.6 - 2');
Query OK, 1 row affected (0.44 sec)

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+----------------+---------------+
| id | title          | comment       |
+----+----------------+---------------+
|  1 | Before upgrade | MYSQL 5.6 - 0 |
|  2 | Before upgrade | MYSQL 5.6 - 1 |
|  3 | Before upgrade | MYSQL 5.6 - 2 |
+----+----------------+---------------+
3 rows in set (0.00 sec)

mysql>

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 5     |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql>

スレーブにデータが同期されている事を確認。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+----------------+---------------+
| id | title          | comment       |
+----+----------------+---------------+
|  1 | Before upgrade | MYSQL 5.6 - 0 |
|  2 | Before upgrade | MYSQL 5.6 - 1 |
|  3 | Before upgrade | MYSQL 5.6 - 2 |
+----+----------------+---------------+
3 rows in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.08 sec)

MYSQL5.5.29で追加インストールしたプラグインが
MYSQL5.6で有効になっていない可能性があるのでアップグレードの
タイミングで事前に無効にしておく。

[root@HOME002 mysql]# cp -p /etc/my.cnf /etc/my.cnf.20130216
[root@HOME002 mysql]# vi /etc/my.cnf
[root@HOME002 mysql]# diff /etc/my.cnf /etc/my.cnf.20130216
68c68
< #rpl_semi_sync_slave_enabled=1
---
> rpl_semi_sync_slave_enabled=1
[root@HOME002 mysql]#

[root@HOME002 mysql]# /etc/init.d/mysql stop
Shutting down MySQL.                                       [  OK  ]
[root@HOME002 mysql]#

MYSQL56-0

MYSQL5.6のインストール開始。
基本的には、TARの入れ替えとシンボリックリンクの張替えで対応してます。

[root@HOME002 src]# tar zxvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz
mysql-5.6.10-linux-glibc2.5-i686/COPYING
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlaccess.conf
mysql-5.6.10-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqld_multi
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlshow
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqlaccess
mysql-5.6.10-linux-glibc2.5-i686/bin/resolve_stack_dump
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqladmin
mysql-5.6.10-linux-glibc2.5-i686/bin/mysqld

省略.........

mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/Memcache.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/File/Path.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/Exec.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/ConfigFactory.pm
mysql-5.6.10-linux-glibc2.5-i686/mysql-test/lib/My/CoreDump.pm
[root@HOME002 src]#

[root@HOME002 src]# mv mysql-5.6.10-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]#


[root@HOME002 local]# ls -lh
合計 100K
drwxr-xr-x  2 root  root  4.0K  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 etc
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 games
drwxr-xr-x  2 root  root  4.0K  1月  7 19:58 include
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 man
lrwxrwxrwx  1 mysql mysql   27 12月 22 14:43 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x 13 mysql mysql 4.0K  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxr-xr-x 13 root  root  4.0K  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4.0K  1月  7 19:58 share
drwxr-xr-x  2 root  root  4.0K  2月 16 15:53 src
[root@HOME002 local]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 775 mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME002 local]#


[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]# ln -s mysql-5.6.10-linux-glibc2.5-i686/ mysql
[root@HOME002 local]# ls -lh
合計 100K
drwxr-xr-x  2 root  root  4.0K  1月  8 14:59 bin
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 etc
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 games
drwxr-xr-x  2 root  root  4.0K  1月  7 19:58 include
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 lib
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4.0K  1月  7 19:58 man
lrwxrwxrwx  1 root  root    33  2月 16 15:56 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x 13 mysql mysql 4.0K  1月 25 15:14 mysql-5.5.29-linux2.6-i686
drwxrwxr-x 13 mysql mysql 4.0K  2月 16 15:51 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4.0K  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4.0K  1月  7 19:58 share
drwxr-xr-x  2 root  root  4.0K  2月 16 15:53 src
[root@HOME002 local]#


[root@HOME002 local]# cp -rp mysql-5.5.29-linux2.6-i686/data/* /usr/local/mysql/data/
[root@HOME002 local]#

[root@HOME002 bin]# ls -l /usr/local/mysql/bin/*up*
-rwxrwxr-x 1 mysql mysql 4471915  1月 23 02:13 /usr/local/mysql/bin/mysql_upgrade
[root@HOME002 bin]# /etc/init.d/mysql start
Starting MySQL......                                       [  OK  ]
[root@HOME002 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
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.proxies_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
test.TABLE001                                      OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK
[root@HOME002 bin]#

upgrade

アップグレード後の確認。
OFFにしてあったSEMI-SYNCを再度ONに設定しても問題無いか確認して元に戻す。

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

[root@HOME002 bin]# vi /etc/my.cnf
[root@HOME002 bin]# diff /etc/my.cnf /etc/my.cnf.20130216
[root@HOME002 bin]#

[root@HOME002 bin]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL...                                          [  OK  ]
[root@HOME002 bin]#

レプリケーションの動作確認
マスターでデータをINSERTしてみる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('After Slave upgrade','MYSQL 5.6 - 0');
Query OK, 1 row affected (0.43 sec)

mysql> select * from TABLE001;
+----+---------------------+---------------+
| id | title               | comment       |
+----+---------------------+---------------+
|  1 | Before upgrade      | MYSQL 5.6 - 0 |
|  2 | Before upgrade      | MYSQL 5.6 - 1 |
|  3 | Before upgrade      | MYSQL 5.6 - 2 |
|  4 | After Slave upgrade | MYSQL 5.6 - 0 |
+----+---------------------+---------------+
4 rows in set (0.00 sec)

mysql>

スレーブにデータが同期されている事を確認。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+---------------------+---------------+
| id | title               | comment       |
+----+---------------------+---------------+
|  1 | Before upgrade      | MYSQL 5.6 - 0 |
|  2 | Before upgrade      | MYSQL 5.6 - 1 |
|  3 | Before upgrade      | MYSQL 5.6 - 2 |
|  4 | After Slave upgrade | MYSQL 5.6 - 0 |
+----+---------------------+---------------+
4 rows in set (0.00 sec)

mysql>

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 1506
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 270
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1506
              Relay_Log_Space: 437
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             Master_Info_File: /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

insert-after-slave-upgrade

エラーログの確認
特に問題なさそうですが、ログを確認するとバージョンアップしたので
幾つかオプションファイルの設定やレプリケーションの設定も変更した方が良さそうです。
マスターをアップグレードするタイミングで設定変更してみます。

130216 16:16:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-02-16 16:16:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-16 16:16:21 13756 [Note] Plugin 'FEDERATED' is disabled.
2013-02-16 16:16:21 b7fe96d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-02-16 16:16:21 13756 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-16 16:16:21 13756 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-02-16 16:16:21 13756 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-16 16:16:21 13756 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-16 16:16:21 13756 [Note] InnoDB: Using Linux native AIO
2013-02-16 16:16:21 13756 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-02-16 16:16:21 13756 [Note] InnoDB: Completed initialization of buffer pool
2013-02-16 16:16:21 13756 [Note] InnoDB: Highest supported file format is Barracuda.
2013-02-16 16:16:22 13756 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-16 16:16:22 13756 [Note] InnoDB: 1.2.10 started; log sequence number 1727822
2013-02-16 16:16:22 13756 [Note] Server hostname (bind-address): '*'; port: 3306
2013-02-16 16:16:22 13756 [Note] IPv6 is available.
2013-02-16 16:16:22 13756 [Note]   - '::' resolves to '::';
2013-02-16 16:16:22 13756 [Note] Server socket created on IP: '::'.
2013-02-16 16:16:22 13756 [Note] Slave I/O thread: Start semi-sync replication to master 'slave_user@HOME001:3306' in log 'mysql-bin.000015' at position 1506
2013-02-16 16:16:22 13756 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-02-16 16:16:22 13756 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-02-16 16:16:22 13756 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000015' at position 1506, relay log '/usr/local/mysql/data/relay-bin.000006' position: 270
2013-02-16 16:16:22 13756 [Note] Slave I/O thread: connected to master 'slave_user@HOME001:3306',replication started in log 'mysql-bin.000015' at position 1506
2013-02-16 16:16:22 13756 [Note] Event Scheduler: Loaded 0 events
2013-02-16 16:16:22 13756 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-02-16 16:16:22 13756 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2013-02-16 16:16:22 13756 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master, maybe it is a *VERY OLD MASTER*. Error_code: 1193
[root@HOME002 data]#

9月末にMYSQL5.5.28がリリースされたので、アップグレードしておく。
最近、アップグレードの頻度が少し高いような気がする。
プライベートのサイトは良いが、会社のサービスで運用する場合に
新機能を使わないのであれば、ある程度落ち着いたバージョンが良いかと。

D.1.2. Changes in MySQL 5.5.28 (2012-09-28)

Bugs Fixed

InnoDB: Certain information_schema tables originally introduced in MySQL 5.6
are now also available in MySQL 5.5 and MySQL 5.1 (Bug #13113026)

InnoDB: When a SELECT … FOR UPDATE, UPDATE, or other SQL statement
scanned rows in an InnoDB table using a < or <= operator in a WHERE clause, the next row after the affected range could also be locked. (Bug #11765218) Partitioning: When used with a table having multiple columns in its primary key, but partitioned by KEY using a column that was not part of the primary key as the partitioning column, a query using an aggregate function and DISTINCT such as SELECT SUM(DISTINCT pk_column_1) FROM table WHERE pk_column_2 = constant was not handled correctly. (Bug #14495351) Partitioning: For tables using PARTITION BY HASH or PARTITION BY KEY, when the partition pruning mechanism encountered a multi-range list or inequality using a column from the partitioning key, it continued with the next partitioning column and tried to use it for pruning, even if the previous column could not be used. (Bug #14342883) Partitioning: The buffer for the row currently read from each partition used for sorted reads was allocated on open and freed only when the partitioning handler was closed or destroyed. For SELECT statements on tables with many partitions and large rows, this could cause the server to use excessive amounts of memory.(Bug #13025132) Replication: On 64-bit Windows platforms, values greater than 4G for the max_binlog_cache_size and max_binlog_stmt_cache_size system variables were truncated to 4G.(Bug #13961678) Replication: In master-master replication with --log-slave-updates enabled, setting a user variable and then performing inserts using this variable caused the Exec_master_log_position column in the output of SHOW SLAVE STATUS not to be updated. (Bug #13596613) mysqlhotcopy failed for databases containing views. (Bug #62472, Bug #13006947, Bug #12992993) Adding a LIMIT clause to a query containing GROUP BY and ORDER BY could cause the optimizer to choose an incorrect index for processing the query, and return more rows than required. (Bug #54599, Bug #11762052) mysqlbinlog did not accept input on the standard input when the standard input was a pipe. (Bug #49336, Bug #11757312) その他、数件のBugFixあり

ファイルをダウンロード

aws$sudo mv mysql-5.5.28-linux2.6-x86_64.tar.gz /usr/local/src/
aws$

aws$ pwd
/usr/local/src
aws$ ls -lh
total 560M
-rw-r--r-- 1 root     root     6.9M Jan 30  2012 httpd-2.2.22.tar.gz
-rw-r--r-- 1 root     root     5.9M Aug 20 13:22 httpd-2.4.3.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 178M Jul  6 23:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 178M Aug 26 11:37 mysql-5.5.27-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 179M Oct 13 13:05 mysql-5.5.28-linux2.6-x86_64.tar.gz
-rw-r--r-- 1 root     root      14M Aug 16 22:50 php-5.4.6.tar.gz
aws$

バックアップ

aws$ mysqldump --all-databases --single-transaction --flush-logs > /home/ec2-user/mysql_dump20121013.sql -p
Enter password:
aws$

Tarファイルの展開

aws$ tar zxvf mysql-5.5.28-linux2.6-x86_64.tar.gz
mysql-5.5.28-linux2.6-x86_64/docs/mysql.info
mysql-5.5.28-linux2.6-x86_64/docs/INFO_SRC
mysql-5.5.28-linux2.6-x86_64/docs/INFO_BIN
mysql-5.5.28-linux2.6-x86_64/docs/ChangeLog
mysql-5.5.28-linux2.6-x86_64/COPYING
mysql-5.5.28-linux2.6-x86_64/README
mysql-5.5.28-linux2.6-x86_64/INSTALL-BINARY
mysql-5.5.28-linux2.6-x86_64/bin/myisam_ftdump
mysql-5.5.28-linux2.6-x86_64/bin/myisamchk
mysql-5.5.28-linux2.6-x86_64/bin/myisamlog

[省略]

mysql-5.5.28-linux2.6-x86_64/man/man1/my_print_defaults.1
mysql-5.5.28-linux2.6-x86_64/man/man1/myisamlog.1
mysql-5.5.28-linux2.6-x86_64/man/man1/mysql_tzinfo_to_sql.1
mysql-5.5.28-linux2.6-x86_64/man/man1/mysqlslap.1
mysql-5.5.28-linux2.6-x86_64/man/man1/myisampack.1
mysql-5.5.28-linux2.6-x86_64/man/man8/mysqld.8

aws$ ls -lh
total 560M
-rw-r--r--  1 root     root     6.9M Jan 30  2012 httpd-2.2.22.tar.gz
-rw-r--r--  1 root     root     5.9M Aug 20 13:22 httpd-2.4.3.tar.gz
-rw-rw-r--  1 ec2-user ec2-user 178M Jul  6 23:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
-rw-rw-r--  1 ec2-user ec2-user 178M Aug 26 11:37 mysql-5.5.27-linux2.6-x86_64.tar.gz
drwxr-xr-x 13 root     root     4.0K Oct 13 13:46 mysql-5.5.28-linux2.6-x86_64
-rw-rw-r--  1 ec2-user ec2-user 179M Oct 13 13:05 mysql-5.5.28-linux2.6-x86_64.tar.gz
-rw-r--r--  1 root     root      14M Aug 16 22:50 php-5.4.6.tar.gz
aws$

MYSQLのバージョン切り替え作業

1)展開したファイルの移動
2)MYSQL停止
3)データフォルダーコピー
4)シンボリックリンク張り直し
5)権限設定
6)MYSQLサービス開始

aws$ mv mysql-5.5.28-linux2.6-x86_64 /usr/local/

aws$ /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
aws$ cp -rp /usr/local/mysql/data/ /usr/local/mysql-5.5.28-linux2.6-x86_64
aws$ rm mysql
rm: remove symbolic link `mysql'? y
aws$ ln -s /usr/local/mysql-5.5.28-linux2.6-x86_64 mysql
aws$

MYSQL SYMBOLIC LINK

aws$ chown -R mysql:mysql mysql-5.5.28-linux2.6-x86_64
aws$ ls -lh
total 60K
lrwxrwxrwx  1 root   root     23 Sep  8 22:47 apache2 -> /usr/local/httpd-2_2_22
drwxr-xr-x  2 root   root   4.0K Sep  9 01:06 bin
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 etc
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 games
drwxr-xr-x 14 apache apache 4.0K Sep  8 22:44 httpd-2_2_22
drwxr-xr-x  3 root   root   4.0K May 27 09:00 include
drwxr-xr-x  2 root   root   4.0K Sep  9 01:06 lib
drwxr-xr-x  2 root   root   4.0K Oct 13 12:52 lib64
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 libexec
lrwxrwxrwx  1 root   root     39 Oct 13 13:55 mysql -> /usr/local/mysql-5.5.28-linux2.6-x86_64
drwxrwxr-x 13 mysql  mysql  4.0K Jul  7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 14 mysql  mysql  4.0K Aug 26 12:09 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x 13 mysql  mysql  4.0K Oct 13 13:46 mysql-5.5.28-linux2.6-x86_64
drwxr-xr-x  7 root   root   4.0K Sep  9 00:54 php-5.4.6
drwxr-xr-x  2 root   root   4.0K Jan  6  2012 sbin
drwxr-xr-x  5 root   root   4.0K Oct 13 12:52 share
drwxr-xr-x  2 root   root   4.0K Oct 13 13:48 src
aws$

aws$ /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
aws$

MYSQL起動後にシステムテーブルをアップグレード

aws$ /usr/local/mysql/bin/mysql_upgrade -p
Enter password:
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/usr/local/mysql/data/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/usr/local/mysql/data/mysql.sock'
WP01.wp01_commentmeta                              OK
WP01.wp01_comments                                 OK
WP01.wp01_geo_mashup_administrative_names          OK
WP01.wp01_geo_mashup_location_relationships        OK
WP01.wp01_geo_mashup_locations                     OK
WP01.wp01_links                                    OK
WP01.wp01_mappress_maps                            OK
WP01.wp01_mappress_posts                           OK
WP01.wp01_options                                  OK
WP01.wp01_postmeta                                 OK
WP01.wp01_posts                                    OK
WP01.wp01_posts_0929                               OK
WP01.wp01_searchmeter                              OK
WP01.wp01_searchmeter_recent                       OK
WP01.wp01_term_relationships                       OK
WP01.wp01_term_taxonomy                            OK
WP01.wp01_terms                                    OK
WP01.wp01_usermeta                                 OK
WP01.wp01_users                                    OK
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.proxies_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'...
OK
aws$

mysql upgrade

aws$ cat /usr/local/mysql/data/mysql_upgrade_info
5.5.28
aws$

mysql version

参考サイト:
D.1.2. Changes in MySQL 5.5.28 (2012-09-28)

Installing and Upgrading MySQL


EC2で利用しているMYSQLのバージョンが低いので、
バグ対応とサイトレスポンス向上の為に5.5.25aにアップグレード。

mysql> select@@version;
+———–+
| @@version |
+———–+
| 5.5.20 |
+———–+
1 row in set (0.00 sec)

[ec2-user@ip-xx-xxx-xx-xxx src]$ uname -a
Linux ip-xx-xxx-xx-xxx x.x.xx-x.x.x.amzn1.x86_64 #1 SMP Thu Mar 22 08:00:08 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
[ec2-user@ip-xx-xxx-xx-xxx src]$

MySQL 5.5.25a

D.1.2. Changes in MySQL 5.5.25a (2012-07-05)
Note

Due to MSI restrictions, the MSI packages of MySQL 5.5.25a will treat the version as 5.5.26 internally;
for example, as displayed by the Installation Wizard. MySQL itself reports the version as 5.5.25a;
for example, if you check the value of the VERSION() SQL function or the version system variable.

Bugs Fixed
A regression bug in the optimizer could cause excessive disk usage for UPDATE statements. (Bug #65745, Bug #14248833)

MySQL 5.5.25

D.1.3. Changes in MySQL 5.5.25 (2012-05-30)
Note

MySQL 5.5.25 is superseded by MySQL 5.5.25a due to a regression

    bug that can cause excessive disk usage

(for details, see Bug #65745). Current users of 5.5.25: Monitor disk usage and upgrade to 5.5.25a
as soon as that is made available. Users contemplating upgrades to 5.5.25: Defer and upgrade to 5.5.25a
instead when that is made available.

Functionality Added or Changed

Important Change: Replication: The SHOW BINARY LOGS statement (and its equivalent SHOW MASTER LOGS)
may now be executed by a user with the REPLICATION CLIENT privilege. (Formerly, the SUPER privilege
was necessary to use either form of this statement.)
The –safe-mode server option now is deprecated and will be removed in MySQL 5.6.

Bugs Fixed

Performance: InnoDB:

    Improved the algorithm related to adaptive flushing.

This fix increases the rate of flushing in cases where compression is used and the
data set is larger than the buffer pool, leading to eviction. (Bug #13990648, Bug #65061)

InnoDB: In a transaction using the REPEATABLE READ isolation level, an UPDATE or DELETE
statement for an InnoDB table could sometimes overlook rows recently committed by other
transactions. As explained in Section 14.3.9.2, “Consistent Nonlocking Reads”,
DML statements within a REPEATABLE READ transaction apply to rows committed by
other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111)

InnoDB:

    The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be.

Its value should never exceed the value of Innodb_pages_written. (Bug #14000361, Bug #65030)

InnoDB: The error handling and message was improved for attempting to create a foreign key with
a column referencing itself. The message suggested a potential problem with the data dictionary,
when no such problem existed. (Bug #12902967)

InnoDB:

    The CHECK TABLE statement could fail for a large InnoDB table due to a timeout value of 2 hours.

For typical storage devices, the issue could occur for tables that exceeded approximately 200 or 350 GB,
depending on I/O speed. The fix relaxes the locking performed on the table being checked, which makes the
timeout less likely. It also makes InnoDB recognize the syntax CHECK TABLE QUICK, which avoids the
possibility of the timeout entirely. (Bug #11758510, Bug #50723)

Replication: It was theoretically possible for concurrent execution of more than one instance of
SHOW BINLOG EVENTS to crash the MySQL Server. (Bug #13979418)

Replication: Statements using AUTO_INCREMENT, LAST_INSERT_ID(), RAND(),
or user variables could be applied in the wrong context on the slave when
using statement-based replication and replication filtering server options
(see Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”). (Bug #11761686, Bug #54201)

References: See also Bug #11754117, Bug #45670, Bug #11746146, Bug #23894.

Replication: An INSERT into a table that has a composite primary key that includes an
AUTO_INCREMENT column that is not the first column of this composite key is not safe for
statement-based binary logging or replication. Such statements are now marked as unsafe
and fail with an error when using the STATEMENT binary logging format.
For more information, see Section 16.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”,
as well as Section 16.4.1.1, “Replication and AUTO_INCREMENT”. Note

Tables using the InnoDB storage engine are not affected by this issue,
since InnoDB does not allow the creation of a composite key that includes an AUTO_INCREMENT column,
where this column is not the first column in the key.
(Bug #11754117, Bug #45670)
References: See also Bug #11761686, Bug #54201, Bug #11746146, Bug #23894.

SHOW TABLES was very slow unless the required information was already in the disk cache. (Bug #60961, Bug #12427262)

Download(最新のMYSQLをダウンロード)
http://dev.mysql.com/downloads/mysql/5.5.html#downloads

[root@ip-xx-xxx-xx-xxx src]# ls -l
total 181992
-rw-rw-r– 1 ec2-user ec2-user 186355822 Jul 7 08:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
[root@ip-xx-xxx-xx-xxx src]# tar zxvf mysql-5.5.25a-linux2.6-x86_64.tar.gz
mysql-5.5.25a-linux2.6-x86_64/docs/mysql.info
mysql-5.5.25a-linux2.6-x86_64/docs/INFO_SRC
mysql-5.5.25a-linux2.6-x86_64/docs/INFO_BIN
mysql-5.5.25a-linux2.6-x86_64/docs/ChangeLog
mysql-5.5.25a-linux2.6-x86_64/COPYING
mysql-5.5.25a-linux2.6-x86_64/README
mysql-5.5.25a-linux2.6-x86_64/INSTALL-BINARY
mysql-5.5.25a-linux2.6-x86_64/bin/myisam_ftdump
mysql-5.5.25a-linux2.6-x86_64/bin/myisamchk
mysql-5.5.25a-linux2.6-x86_64/bin/myisamlog
[省略]
mysql-5.5.25a-linux2.6-x86_64/man/man1/resolve_stack_dump.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/mysqlman.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/resolveip.1
mysql-5.5.25a-linux2.6-x86_64/man/man1/mysql-stress-test.pl.1
mysql-5.5.25a-linux2.6-x86_64/man/man8/mysqld.8
[root@ip-xx-xxx-xx-xxx src]#

[root@ip-xx-xxx-xx-xxx src]# mv mysql-5.5.25a-linux2.6-x86_64 /usr/local/
[root@ip-xx-xxx-xx-xxx src]# cd /usr/local/
[root@ip-xx-xxx-xx-xxx local]# ls -l
total 44
drwxr-xr-x 2 root root 4096 May 27 18:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 18:40 etc
drwxr-xr-x 2 root root 4096 Jan 6 18:40 games
drwxr-xr-x 3 root root 4096 May 27 18:00 include
drwxr-xr-x 2 root root 4096 May 27 08:12 lib
drwxr-xr-x 3 root root 4096 Mar 25 02:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 libexec
drwxr-xr-x 13 root root 4096 Jul 7 09:19 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 sbin
drwxr-xr-x 6 root root 4096 Mar 25 02:06 share
drwxr-xr-x 2 root root 4096 Jul 7 09:29 src
[root@ip-xx-xxx-xx-xxx local]#

シンボリックリンク作成
[root@ip-xx-xxx-xx-xxx local]# ln -s mysql-5.5.25a-linux2.6-x86_64/ mysql
[root@ip-xx-xxx-xx-xxx local]# ls -l
total 44
drwxr-xr-x 2 root root 4096 May 27 18:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 18:40 etc
drwxr-xr-x 2 root root 4096 Jan 6 18:40 games
drwxr-xr-x 3 root root 4096 May 27 18:00 include
drwxr-xr-x 2 root root 4096 May 27 08:12 lib
drwxr-xr-x 3 root root 4096 Mar 25 02:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 libexec
lrwxrwxrwx 1 root root 30 Jul 7 09:30 mysql -> mysql-5.5.25a-linux2.6-x86_64/
drwxr-xr-x 13 root root 4096 Jul 7 09:19 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 18:40 sbin
drwxr-xr-x 6 root root 4096 Mar 25 02:06 share
drwxr-xr-x 2 root root 4096 Jul 7 09:29 src
[root@ip-xx-xxx-xx-xxx local]#

既存MYSQLを停止
[root@ip-xx-xxx-xx-xxx local]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@ip-xx-xxx-xx-xxx local]#

古いMYSQLのデータの場所を確認
[root@ip-xx-xxx-xx-xxx local]# cat /etc/my.cnf | grep data
datadir=/var/lib/mysql
[root@ip-xx-xxx-xx-xxx local]#

データベースデータを新しいMYSQLのデータフォルダーにコピー
[root@ip-xx-xxx-xx-xxx data]# cp -rp /var/lib/mysql/* /usr/local/mysql/data
[root@ip-xx-xxx-xx-xxx data]# ls -l
total 36892
-rw-rw—- 1 mysql mysql 27262976 Jul 7 09:33 ibdata1
-rw-rw—- 1 mysql mysql 5242880 Jul 7 09:33 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Jun 7 02:13 ib_logfile1
-rw-rw—- 1 mysql root 11922 Mar 31 15:37 ip-xx-xxx-xx-xxx.err
drwx—— 2 mysql mysql 4096 Mar 31 15:54 mysql
drwx—— 2 mysql mysql 4096 Mar 31 15:54 performance_schema
drwx—— 2 mysql mysql 4096 Mar 31 15:14 test
drwx—— 2 mysql mysql 4096 Jun 15 16:55 WP01
[root@ip-xx-xxx-xx-xxx data]#

MYSQL定義ファイルをバックアップ
[root@ip-xx-xxx-xx-xxx support-files]# mv /etc/my.cnf /etc/my.cnf.20120707
[root@ip-xx-xxx-xx-xxx support-files]#

新しくMYSQL定義ファイルをコピー
[root@ip-xx-xxx-xx-xxx support-files]# cp -p my-small.cnf /etc/my.cnf
[root@ip-xx-xxx-xx-xxx support-files]#

データフォルダー、Socketなどの場所を編集
[root@ip-xx-xxx-xx-xxx support-files]# vi /etc/my.cnf

新しいMYSQLのデータフォルダーなどに書き込めるようにオーナー変更
[root@ip-xx-xxx-xx-xxx local]# chown -R mysql:mysql mysql-5.5.25a-linux2.6-x86_64/
[root@ip-xx-xxx-xx-xxx local]#

新しいMYSQLを起動
[root@ip-xx-xxx-xx-xxx local]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@ip-xx-xxx-xx-xxx local]#

システムデータベースのアップグレード
[root@ip-xx-xxx-xx-xxx data]# /usr/local/mysql/bin/mysql_upgrade -u root -p
Enter password:
Looking for ‘mysql’ as: /usr/local/mysql/bin/mysql
Looking for ‘mysqlcheck’ as: /usr/local/mysql/bin/mysqlcheck
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/usr/local/mysql/data/mysql.sock’
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/usr/local/mysql/data/mysql.sock’
WP01.wp01_commentmeta OK
WP01.wp01_comments OK
WP01.wp01_geo_mashup_administrative_names OK
WP01.wp01_geo_mashup_location_relationships OK
WP01.wp01_geo_mashup_locations OK
WP01.wp01_links OK
WP01.wp01_mappress_maps OK
WP01.wp01_mappress_posts OK
WP01.wp01_options OK
WP01.wp01_postmeta OK
WP01.wp01_posts OK
WP01.wp01_searchmeter OK
WP01.wp01_searchmeter_recent OK
WP01.wp01_term_relationships OK
WP01.wp01_term_taxonomy OK
WP01.wp01_terms OK
WP01.wp01_usermeta OK
WP01.wp01_users OK
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.proxies_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’…
OK
[root@ip-xx-xxx-xx-xxx data]#

サービス起動ファイルの変更
※通常は、そのままにしてあるが前回はRPMでインストールして今回はTARから展開して利用しているので
 SocketやPIDを変更するのが手間なので、そのままコピー

[root@ip-xx-xxx-xx-xxx local]# cd support-files/
[root@ip-xx-xxx-xx-xxx support-files]# pwd
/usr/local/mysql/support-files
[root@ip-xx-xxx-xx-xxx support-files]# cp -p mysql.server /etc/init.d/mysqld

mysql> select @@version;
+———–+
| @@version |
+———–+
| 5.5.25a |
+———–+
1 row in set (0.00 sec)

mysql> select version();
+———–+
| version() |
+———–+
| 5.5.25a |
+———–+
1 row in set (0.00 sec)

mysql>

MYSQL5.5.25a

アップグレードでinnodb_buffer_pool_sizeが多くなっていたので変更。

mysql> show variables like ‘%buffer%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+——————————+———–+
14 rows in set (0.00 sec)

mysql> show variables like ‘%buffer%’;
+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 16777216 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16384 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 2048 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 65536 |
| sql_buffer_result | OFF |
+——————————+———-+
14 rows in set (0.00 sec)

mysql>

今回は久々にRPMでインストールしてあったMYSQLでアップグレードしたので、
/etc/init.d/mysqlの入れ替え、/etc/my.cnf、/etc/php.iniで少し時間かけてしまった。

PHP Socketの場所指定
MySQLに接続するときにエラー発生

パフォーマンスがいまいちなので、Wordpress用にQuery Cacheだけ設定しておいた。

mysql> SHOW VARIABLES LIKE ‘%query%’;
+——————————+————————————————-+
| Variable_name | Value |
+——————————+————————————————-+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/ip-xx-xxx-xx-xxx-slow.log |
+——————————+————————————————-+


MYSQLを暫くアップグレードしていなかったので、
BugFix含めて対応する事にした。

既存の環境を確認
[root@colinux local]$ ls -l | grep mysql
lrwxrwxrwx 1 root root 38 2009-12-26 01:40 mysql -> mysql-5.5.0-m2-linux-i686-icc-glibc23/
drwxr-xr-x 14 mysql mysql 4096 2009-12-22 00:23 mysql-5.1.41-linux-i686-icc-glibc23
drwxr-xr-x 14 mysql mysql 4096 2009-12-26 01:37 mysql-5.5.0-m2-linux-i686-icc-glibc23
[root@colinux local]$

古いバージョンはもう1年以上利用していないので削除。(2世代前のファイル)
[root@colinux local]# rm -rf mysql-5.1.41-linux-i686-icc-glibc23/

ディスクスペースが空いたので最新のMYSQL GAバージョンをダウンロード
http://dev.mysql.com/downloads/mysql/5.5.html#downloads

32bit (mysql-5.5.25-linux2.6-i686.tar.gz)
http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-i686.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-i686.tar.gz/from/http://ftp.jaist.ac.jp/pub/mysql/

64bit(mysql-5.5.25-linux2.6-x86_64.tar.gz)
http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-x86_64.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-x86_64.tar.gz/from/http://ftp.jaist.ac.jp/pub/mysql/

wgetに-Oでファイル名指定しないと、index.htmlでダウンロードしてしまうのでファイル名をオプションで指定。

[root@colinux src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-i686.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/ -O mysql-5.5.25-linux2.6-i686.tar.gz
–2012-06-23 07:11:41– http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-i686.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
dev.mysql.com をDNSに問いあわせています… 156.151.63.11
dev.mysql.com|156.151.63.11|:80 に接続しています… 接続しました。
HTTP による接続要求を送信しました、応答を待っています… 302 Found
場所: http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-i686.tar.gz [続く]
–2012-06-23 07:11:42– http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.5/mysql-5.5.25-linux2.6-i686.tar.gz
ftp.iij.ad.jp をDNSに問いあわせています… 202.232.140.141, 202.232.140.135, 202.232.140.136, …
ftp.iij.ad.jp|202.232.140.141|:80 に接続しています… 接続しました。
HTTP による接続要求を送信しました、応答を待っています… 200 OK
長さ: 179403866 (171M) [application/x-gzip]
`mysql-5.5.25-linux2.6-i686.tar.gz’ に保存中

100%[=============================================================================================================================>] 179,403,866 2.64M/s 時間 72s

2012-06-23 07:12:54 (2.38 MB/s) – `mysql-5.5.25-linux2.6-i686.tar.gz’ へ保存完了 [179403866/179403866]

[root@colinux src]#

[root@colinux src]# ls -lh
合計 216M
-rw-r–r– 1 root root 123K 2009-04-14 11:40 DBD-mysql-4.011.tar.gz
-rw-r–r– 1 root root 499K 2009-06-08 19:29 DBI-1.609.tar.gz
-rw-r–r– 1 root root 44M 2010-12-07 18:51 MySQL-embedded-5.5.8-1.linux2.6.i386.rpm
-rwxrwxr-x 1 root mysql 2.7K 2010-04-27 00:10 my.cnf.20110109
-rw-r–r– 1 root root 172M 2012-05-15 22:16 mysql-5.5.25-linux2.6-i686.tar.gz

ダウンロードしたtarを展開して、任意のフォルダーに移動して利用。

[root@colinux src]# tar zxvf mysql-5.5.25-linux2.6-i686.tar.gz
[省略]
mysql-5.5.25-linux2.6-i686/INSTALL-BINARY
mysql-5.5.25-linux2.6-i686/README
mysql-5.5.25-linux2.6-i686/data/test/.empty
mysql-5.5.25-linux2.6-i686/data/mysql/.empty
mysql-5.5.25-linux2.6-i686/COPYING
[root@colinux src]#

[root@colinux src]# ls -l
合計 221008
-rw-r–r– 1 root root 125653 2009-04-14 11:40 DBD-mysql-4.011.tar.gz
-rw-r–r– 1 root root 510309 2009-06-08 19:29 DBI-1.609.tar.gz
-rw-r–r– 1 root root 46019462 2010-12-07 18:51 MySQL-embedded-5.5.8-1.linux2.6.i386.rpm
-rwxrwxr-x 1 root mysql 2714 2010-04-27 00:10 my.cnf.20110109
drwxr-xr-x 13 root root 4096 2012-06-23 07:15 mysql-5.5.25-linux2.6-i686
-rw-r–r– 1 root root 179403866 2012-05-15 22:16 mysql-5.5.25-linux2.6-i686.tar.gz
[root@colinux src]#

[root@colinux src]# mv mysql-5.5.25-linux2.6-i686 /usr/local/
[root@colinux src]#

[root@colinux local]# pwd
/usr/local
[root@colinux local]# ls -l | grep mysql
lrwxrwxrwx 1 root root 38 2009-12-26 01:40 mysql -> c-m2-linux-i686-icc-glibc23/
drwxr-xr-x 14 mysql mysql 4096 2009-12-26 01:37 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 13 root root 4096 2012-06-23 07:15 mysql-5.5.25-linux2.6-i686
[root@colinux local]#

MYSQLを停止してシンボリックリンクを削除する前にMYSQLにログインしてバージョンを再確認。

mysql version confirmation


1.確認が終わったので、MYSQLを停止してシンボリックリンク削除と再作成
2.データファイルのコピー(古いDATAフォルダーを新しいDATAフォルダーへ)

※失敗した場合も古いバージョンにシンボリックリンクの変更のみでロールバック出来るので楽です。

既存MYSQLの停止
[root@colinux local]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@colinux local]#

[root@colinux local]# ls -l | grep mysql
lrwxrwxrwx 1 root root 38 2009-12-26 01:40 mysql -> mysql-5.5.0-m2-linux-i686-icc-glibc23/
drwxr-xr-x 14 mysql mysql 4096 2009-12-26 01:37 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 13 root root 4096 2012-06-23 07:15 mysql-5.5.25-linux2.6-i686
[root@colinux local]#

シンボリックリンクの削除
[root@colinux local]# rm -rf mysql
[root@colinux local]# ls -l | grep mysql
drwxr-xr-x 14 mysql mysql 4096 2009-12-26 01:37 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 13 root root 4096 2012-06-23 07:15 mysql-5.5.25-linux2.6-i686
[root@colinux local]#

MYSQLを停止したので、データファイルを新しいバージョンにコピー
[root@colinux local]# ls -l mysql-5.5.0-m2-linux-i686-icc-glibc23/data/ | wc -l
47
[root@colinux local]# ls -l mysql-5.5.25-linux2.6-i686/data/ | wc -l
3
[root@colinux local]#

データを移動したのでファイル数の確認
[root@colinux local]# mv -f mysql-5.5.0-m2-linux-i686-icc-glibc23/data mysql-5.5.25-linux2.6-i686/
[root@colinux local]# ls -l mysql-5.5.25-linux2.6-i686/data/ | wc -l
47
[root@colinux local]#

※mvコマンドだとバックアップが無くなってしまうので、cpコマンドでコピーする方が良い。
今回はmvしてしまったので、バージョンアップする前にコピーしておきました。
ロールバックもシンボリックリンク変更して起動するだけなので楽です。
———————————————————————————-
[root@colinux bin]# cp -rp /usr/local/mysql-5.5.25-linux2.6-i686/data /usr/local/mysql-5.5.0-m2-linux-i686-icc-glibc23/
[root@colinux bin]#
———————————————————————————-

移動し終わったので、新しいバージョンでシンボリックリンクの作成。
[root@colinux local]# ln -s mysql-5.5.25-linux2.6-i686/ mysql
[root@colinux local]# ls -l | grep mysql
lrwxrwxrwx 1 root root 27 2012-06-23 07:26 mysql -> mysql-5.5.25-linux2.6-i686/
drwxr-xr-x 13 mysql mysql 4096 2012-06-23 07:25 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 13 root root 4096 2012-06-23 07:25 mysql-5.5.25-linux2.6-i686
[root@colinux local]#

フォルダーをMYSQLユーザが変更出来るようにオーナーを変更。
[root@colinux local]# ls -l | grep mysql
lrwxrwxrwx 1 root root 27 2012-06-23 07:26 mysql -> mysql-5.5.25-linux2.6-i686/
drwxr-xr-x 13 mysql mysql 4096 2012-06-23 07:25 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 13 root root 4096 2012-06-23 07:25 mysql-5.5.25-linux2.6-i686
[root@colinux local]# chown -R mysql:mysql mysql-5.5.25-linux2.6-i686/
[root@colinux local]# ls -l | grep mysql
lrwxrwxrwx 1 root root 27 2012-06-23 07:26 mysql -> mysql-5.5.25-linux2.6-i686/
drwxr-xr-x 13 mysql mysql 4096 2012-06-23 07:25 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 13 mysql mysql 4096 2012-06-23 07:25 mysql-5.5.25-linux2.6-i686
[root@colinux local]#

フォルダー権限、シンボリックリンク、データコピーが終わったので起動してみる。

[root@colinux data]# /etc/init.d/mysql.server start
Starting MySQL.. ERROR! Manager of pid-file quit without updating file.
[root@colinux data]#
案の定エラーになった。
毎度の事だが、MYSQLのバージョンアップに伴いオプションが変わっているかと。

先ずは、エラーログを確認して以下のエラーから対応
——————————————————————————
120623 08:06:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
120623 08:06:12 mysqld_safe mysqld from pid file /usr/local/mysql/data/colinux.pid ended
——————————————————————————
[root@colinux data]# yum install libaio-devel -y
fedora 100% |=========================| 2.1 kB 00:00
updates 100% |=========================| 2.3 kB 00:00
Setting up Install Process
Parsing package install arguments

libaio-devel

再度サービス起動したが、エラーが残っていて起動しない。
[root@colinux data]# /etc/init.d/mysql.server start
my.confが新しいバージョンに対応していないようなので調整してみる。

——————————————————————————–
120623 08:08:24 mysqld_safe mysqld from pid file /usr/local/mysql/data/colinux.pid ended
120623 08:11:04 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
120623 8:11:05 [Note] Plugin ‘FEDERATED’ is disabled.
120623 8:11:05 InnoDB: The InnoDB memory heap is disabled
120623 8:11:05 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
120623 8:11:05 InnoDB: Compressed tables use zlib 1.2.3
120623 8:11:05 InnoDB: Using Linux native AIO
120623 8:11:06 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
120623 8:11:06 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
120623 8:11:06 InnoDB: Warning: Linux Native AIO disabled.
120623 8:11:06 InnoDB: Initializing buffer pool, size = 16.0M
120623 8:11:06 InnoDB: Completed initialization of buffer pool
120623 8:11:06 InnoDB: highest supported file format is Barracuda.
120623 8:11:07 InnoDB: Warning: allocated tablespace 17, old maximum was 0
InnoDB: 127 rollback segment(s) active.
120623 8:11:07 InnoDB: Waiting for the background threads to start
120623 8:11:08 InnoDB: 1.1.8 started; log sequence number 33765719
120623 8:11:08 [ERROR] /usr/local/mysql/bin/mysqld: unknown option ‘–skip-locking’
120623 8:11:08 [ERROR] Aborting

120623 8:11:08 InnoDB: Starting shutdown…
120623 8:11:09 InnoDB: Shutdown completed; log sequence number 35322070
120623 8:11:09 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

120623 08:11:09 mysqld_safe mysqld from pid file /usr/local/mysql/data/colinux.pid ended
[root@colinux data]#
——————————————————————————–

my.confを編集する前に、念の為バックアップ
[root@colinux data]# cp -p /etc/my.cnf /etc/my.cnf.old.20120623

こちらのオプションをコメントする。
——————————————————————————–
120623 8:11:08 [ERROR] /usr/local/mysql/bin/mysqld: unknown option ‘–skip-locking’
120623 8:11:08 [ERROR] Aborting
——————————————————————————–
[root@colinux data]# vi /etc/my.cnf
# Comment skip-locking at version up to 5.5.25
#skip-locking

こちらの文字コードの設定も設定の仕方が、character-set-serverに変わっているので変更。
——————————————————————————–
120623 8:22:00 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable ‘default-character-set=utf8’
120623 8:22:00 [ERROR] Aborting
——————————————————————————–
[root@colinux data]# vi /etc/my.cnf
# Comment skip-locking at version up to 5.5.25
#default-character-set=utf8
character-set-server=utf8

my.cnfの編集が終わったので起動してみる。
[root@colinux data]# /etc/init.d/mysql.server start
Starting MySQL…. SUCCESS!
[root@colinux data]#

起動したので、先ずはMYSQLのシステムテーブルをアップグレード。

[root@colinux data]# /usr/local/mysql/bin/mysql_upgrade -u root -p
Enter password:
Looking for ‘mysql’ as: /usr/local/mysql/bin/mysql
Looking for ‘mysqlcheck’ as: /usr/local/mysql/bin/mysqlcheck
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
DB01.TABLE000 OK
DB01.TABLE007 OK
DB01.TABLE008 OK
DB01.TABLE009 OK
DB01.TABLE100 OK
DB01.TABLE101 OK
DB01.TABLE911 OK
DB01.TABLE_FILEPATH OK
DB01.TABLE_FILEPATH2 OK
DB01.T_FULLTEXT OK
DB02.animals OK
DB02.animals02 OK
DB02.animals03 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
mysql.user_info OK
Running ‘mysql_fix_privilege_tables’…
OK
[root@colinux data]#

システムテーブルのアップグレードが問題無く終わったので念の為再起動

[root@colinux data]# /etc/init.d/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL…. SUCCESS!
[root@colinux data]#

エラーログの確認してみると、”Native AIO is not supported on tmpdir”というログがある。
——————————————————————————
Version: ‘5.5.25-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
120623 8:52:55 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
120623 8:52:55 [Note] Event Scheduler: Purging the queue. 0 events
120623 8:52:55 InnoDB: Starting shutdown…
120623 8:52:56 InnoDB: Shutdown completed; log sequence number 35322070
120623 8:52:56 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
120623 08:52:56 mysqld_safe mysqld from pid file /usr/local/mysql/data/colinux.pid ended
120623 08:52:58 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
120623 8:52:58 [Note] Plugin ‘FEDERATED’ is disabled.
120623 8:52:58 InnoDB: The InnoDB memory heap is disabled
120623 8:52:58 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
120623 8:52:58 InnoDB: Compressed tables use zlib 1.2.3
120623 8:52:58 InnoDB: Using Linux native AIO
120623 8:52:58 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
120623 8:52:58 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
120623 8:52:58 InnoDB: Warning: Linux Native AIO disabled.
120623 8:52:58 InnoDB: Initializing buffer pool, size = 16.0M
120623 8:52:58 InnoDB: Completed initialization of buffer pool
120623 8:52:59 InnoDB: highest supported file format is Barracuda.
120623 8:52:59 InnoDB: Warning: allocated tablespace 17, old maximum was 0
120623 8:52:59 InnoDB: Waiting for the background threads to start
120623 8:53:00 InnoDB: 1.1.8 started; log sequence number 35322070
120623 8:53:00 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
120623 8:53:00 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
120623 8:53:00 [Note] Server socket created on IP: ‘0.0.0.0’.
120623 8:53:00 [Note] Event Scheduler: Loaded 0 events
120623 8:53:00 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.25-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
[root@colinux data]#
——————————————————————————


以下のエラーが出ているので、tempファイルシステムを移動してみる。
現状は、/usr/local/tmpに設定しているの/tmpに変更してみる。

—————————————————————————–
120623 8:52:58 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
—————————————————————————–
[root@colinux mysql]# vi /etc/my.cnf
# Comment skip-locking at version up to 5.5.25
# tmpdir
# tmpdir=/usr/local/tmp
tmpdir=/tmp

fstabの内容は以下のような感じにしてました。
[root@colinux data]# cat /etc/fstab
LABEL=/ / ext3 defaults 1 1
#LABEL=/ / ext3 defaults,noatime 1 1
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
#LABEL=swap none swap defaults 0 0
/dev/cobd1 none swap defaults 0 0
#additional temp fs
/dev/shm /usr/local/tmp tmpfs defaults,size=32m 0 0
[root@colinux data]#

変更したので、再度MYSQLを再起動してみる。

[root@colinux mysql]# /etc/init.d/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL…. SUCCESS!
[root@colinux mysql]#

ログを確認してみてエラーが解消されたか確認。

—————————————————————————-
MYSQLのログからエラーが消えた事を確認。
—————————————————————————-
120623 9:03:00 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
120623 9:03:00 [Note] Event Scheduler: Purging the queue. 0 events
120623 9:03:00 InnoDB: Starting shutdown…
120623 9:03:00 InnoDB: Shutdown completed; log sequence number 35322070
120623 9:03:00 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
120623 09:03:01 mysqld_safe mysqld from pid file /usr/local/mysql/data/colinux.pid ended
120623 09:03:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
120623 9:03:02 [Note] Plugin ‘FEDERATED’ is disabled.
120623 9:03:02 InnoDB: The InnoDB memory heap is disabled
120623 9:03:02 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
120623 9:03:02 InnoDB: Compressed tables use zlib 1.2.3
120623 9:03:02 InnoDB: Using Linux native AIO
120623 9:03:03 InnoDB: Initializing buffer pool, size = 16.0M
120623 9:03:03 InnoDB: Completed initialization of buffer pool
120623 9:03:03 InnoDB: highest supported file format is Barracuda.
120623 9:03:03 InnoDB: Warning: allocated tablespace 17, old maximum was 0
120623 9:03:03 InnoDB: Waiting for the background threads to start
120623 9:03:04 InnoDB: 1.1.8 started; log sequence number 35322070
120623 9:03:05 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
120623 9:03:05 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
120623 9:03:05 [Note] Server socket created on IP: ‘0.0.0.0’.
120623 9:03:05 [Note] Event Scheduler: Loaded 0 events
120623 9:03:05 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.25-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
[root@colinux data]#
—————————————————————————-

エラーが解消されたので、ログインしてバージョンを確認してみる。
いつの間にか、コピーライトが”Copyright (c) 2000, 2011, Oracle and/or its affiliates.”
になっていますね。

[root@colinux data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> select @@version;
+————+
| @@version |
+————+
| 5.5.25-log |
+————+
1 row in set (0.01 sec)

バージョンも無事に更新されてました。

my.cnfでオプション変更したが、文字コードもきちんとUTF-8でした。
文字コード

以上でMYSQLのバージョンアップ完了。


MYSQL5.1.41がリリースされていたので、アップグレード
C.1.2. Changes in MySQL 5.1.41 (05 November 2009)

■InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of
 Release Candidate (RC) quality.
■The InnoDB buffer pool is divided into two sublists: A new sublist containing blocks
 that are heavily used by queries, and an old sublist containing less-used blocks and from
 which candidates for eviction are taken.
 innodb_old_blocks_pct / innodb_old_blocks_time
■Important Change: Security Fix
Bug#32167, CVE-2008-2079 / Bug#47320, CVE-2009-4028 / Bug#46922
Partitioning: Bug#44059
Replication: Bug#48297 / Bug#48216 / Bug#47678 / Bug#47323 / Bug#47287/ Bug#46640
Bug#42829 / Bug#34582 / Bug#48370 / Bug#48295 / Bug#48293
その他多数……………………….

1) 現在のバージョン確認
original_20091221

http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

2) 最新版のダウンロード
[root@colinux src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
–00:06:43– http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-icc-glibc23.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

wget_5141

3) ダウンロードしたtarを解凍
[root@colinux src]# tar zxvf mysql-5.1.41-linux-i686-icc-glibc23.tar.gz
tar_zxvf

4) 展開したtarを自分が希望するパスへ移動 (これはオプション)
mv_mysql5141

5) 新しく展開したフォルダーのパーミッションの変更
[root@colinux local]# chown -R mysql:mysql mysql-5.1.41-linux-i686-icc-glibc23/
[root@colinux local]# chmod -R 755 mysql-5.1.41-linux-i686-icc-glibc23/

6) 現在稼動している、MYSQLサービスを停止します。
mysql_5141_migration

7) シンボリックリンクの削除
[root@colinux local]# pwd
[root@colinux local]# rm mysql

8) アップグレード前のまで利用していた、MYSQLのdataフォルダーを新しいバージョンのMYSQLにコピー

[root@colinux local]# cp -rp mysql-5.1.40-linux-i686-icc-glibc23/data mysql-5.1.41-linux-i686-icc-glibc23/

9) シンボリックリンクの作成とサービスの再開
[root@colinux local]# ln -s mysql-5.1.41-linux-i686-icc-glibc23/ mysql

mysql5141_symbolic

[root@colinux local]# /etc/init.d/mysql.server start
Starting MySQL…. SUCCESS!
[root@colinux local]#

10) システムデータベースのアップグレード(information_schema / mysql )

[root@colinux local]# /usr/local/mysql/bin/mysql_upgrade -u root -p

mysql_5141_sqlupgrade

11) バージョンアップの確認

※ mysql_upgrade_infoファイルの確認
[root@colinux data]# cat /usr/local/mysql/data/mysql_upgrade_info
upgrade_info

※ エラーログの確認
mysql_5141_log_confirmation

※ information_schemaとmysqlスキーマの確認
select TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_TIME,UPDATE_TIME
from information_schema.tables where
TABLE_SCHEMA = 'information_schema' or TABLE_SCHEMA = 'mysql'
and CREATE_TIME is not NULL and UPDATE_TIME is not null
order by CREATE_TIME desc;

system_tables

※ バージョン確認 (mysql> select @@version;)
version_5141


[データベースのアップグレード]

ダウンタイムが2~3分程度(データサイズによる)で済むデータベースのマイナーアップグレードのメモ書きです。


①  mysql-5.1.32-linux-i686-icc-glibc23からmysql-5.1.34-linux-i686-icc-glibc23へのアップグレード

② 最新のMYSQLをダウンロードしてきて、/usr/localに展開する。
  http://dev.mysql.com/downloads/mysql/5.1.html
Linux (non RPM, Intel C/C++ compiled, glibc-2.3) downloads
Linux (x86) 5.1.36 113.3M
Linux (AMD64 / Intel EM64T) 5.1.36 116.3M
Linux (IA64) 5.1.36 139.6M

③ lsコマンドで確認した状態。mysqlはシンボリックリンクなので最新のバイナリーをシンボリックリンクの切り替えで更新する。

[root@MYSQL01 local]# ls -l /usr/local/mysql*
lrwxrwxrwx 1 root root 35 7月 14 14:42 /usr/local/mysql -> mysql-5.1.34-linux-i686-icc-glibc23

/usr/local/mysql-5.1.32-linux-i686-icc-glibc23:
合計 148
-rwxrwxr-x 1 root mysql 19071 2月 14 19:22 COPYING
-rwxrwxr-x 1 root mysql 5139 2月 14 19:22 EXCEPTIONS-CLIENT
-rwxrwxr-x 1 root mysql 8769 2月 14 19:22 INSTALL-BINARY
-rwxrwxr-x 1 root mysql 1410 2月 14 19:22 README
drwxrwxr-x 2 root mysql 4096 2月 14 19:22 bin
drwxrwxr-x 4 root mysql 4096 3月 18 10:57 data
drwxrwxr-x 2 root mysql 4096 2月 14 19:20 docs
drwxrwxr-x 2 root mysql 4096 2月 14 19:20 include
drwxrwxr-x 3 root mysql 4096 2月 14 19:21 lib
drwxrwxr-x 4 root mysql 4096 2月 14 19:21 man
drwxrwxr-x 9 root mysql 4096 2月 14 19:22 mysql-test
drwxrwxr-x 2 root mysql 4096 2月 14 19:22 scripts
drwxrwxr-x 27 root mysql 4096 2月 14 19:22 share
drwxrwxr-x 5 root mysql 4096 2月 14 19:22 sql-bench
drwxrwxr-x 2 root mysql 4096 2月 14 19:22 support-files

/usr/local/mysql-5.1.34-linux-i686-icc-glibc23:
合計 156
-rwxrwxr-x 1 mysql mysql 19071 4月 1 08:52 COPYING
-rwxrwxr-x 1 mysql mysql 5139 4月 1 08:52 EXCEPTIONS-CLIENT
-rwxrwxr-x 1 mysql mysql 8769 4月 1 08:52 INSTALL-BINARY
-rwxrwxr-x 1 mysql mysql 1410 4月 1 08:52 README
drwxrwxr-x 2 mysql mysql 4096 7月 14 14:38 bin
drwxrwxr-x 11 root mysql 4096 7月 14 14:42 data
drwxrwxr-x 4 mysql mysql 4096 7月 14 08:53 data_original
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:51 docs
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:51 include
drwxrwxr-x 3 mysql mysql 4096 4月 1 08:51 lib
drwxrwxr-x 4 mysql mysql 4096 4月 1 08:51 man
drwxrwxr-x 10 mysql mysql 4096 4月 1 08:52 mysql-test
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:52 scripts
drwxrwxr-x 27 mysql mysql 4096 4月 1 08:52 share
drwxrwxr-x 5 mysql mysql 4096 4月 1 08:52 sql-bench
drwxrwxr-x 2 mysql mysql 4096 4月 1 08:52 support-files
[root@MYSQL01 local]#

④ 既存で動作しているmysqlのサービスを一時停止する。

[root@MYSQL01 local]# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]

⑤ /usr/local/mysql-5.1.32-linux-i686-icc-glibc23/dataをフォルダー毎
新しいMYSQLのデータフォルダーにコピーする。
※データフォルダーやログが別にある時はそちらもコピーしておく。(my.cnf確認)

⑥ データコピーが終わったら既存のMYSQLはサービス停止時間を最小限に抑える為に再開しておく。

[root@MYSQL01 local]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
[root@MYSQL01 local]# ls -l

⑦ 新しいデータベース用に準備したオプションファイルを明示的に指定して、新しいMYSQLも起動する。
./bin/mysqld_safe --defaults-file=/etc/my_05.1.34.cnf &

-------------------my_05.1.34.cnf---------------
port = 3307
socket = /tmp/mysql5.1.34.sock
basedir=/usr/local/mysql-5.1.34-linux-i686-icc-glibc23
datadir=/usr/local/mysql-5.1.34-linux-i686-icc-glibc23/data
slow_query_log=/usr/local/mysql-5.1.34-linux-i686-icc-glibc23/data/slow_query.log
------------------------------------------------

※事前に新しいMYSQLのフォルダーオーナーをmysqlなどのユーザーに変更しておく。

⑧  ローカルソケットとportを指定して、MYSQLに接続する。
何も指定しないとDefaultのオプションファイルが読み込まれ3306に古いMYSQLに接続されてしまう。(/etc/my.cnf)

./mysql -u root -p -P 3307 --socket=/tmp/mysql5.1.34.sock

⑨ 接続出来る事が確認出来たら、以下のように新規導入したMYSQLのアップグレード状況を"mysql_upgrade"
にて確認。不足している、システムテーブルはこのスクリプトにて補完される。

---------------------------------------------------------------------------------------
[root@MYSQL01 bin]# ./mysql_upgrade -u root -p -P 3307 --socket=/tmp/mysql5.1.34.sock
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck'...
Running 'mysqlcheck'...
WINDOWS.TABLE004_Brokerage OK
WINDOWS.TABLE004_Channel OK
WINDOWS.TABLE004_ConfidenceTerm OK
WINDOWS.TABLE004_DTCompany OK
WINDOWS.TABLE004_DTForeignExchange OK
WINDOWS.TABLE004_DTForeignstock OK
WINDOWS.TABLE004_HandlingItem OK
WINDOWS.TABLE004_HowToOrder OK
WINDOWS.TABLE004_InfoService OK
WINDOWS.TABLE004_InitialPublicOffering OK
WINDOWS.TABLE004_SearchTerm OK
WINDOWS.TABLE004_SecuritiesCompany OK
WINDOWS.TABLE004_Support OK
WINDOWS.TABLE004_TabSort OK
WINDOWS.TABLE007DTOfBank OK
WINDOWS.TABLE007InfoOfBank OK
WINDOWS.TABLE007ItemDT OK
WINDOWS.TABLE005CommodityExchange OK
WINDOWS.TABLE005DTOfCompany OK
WINDOWS.TABLE005FinancialScale OK
WINDOWS.TABLE005GuaranteeMoney OK
WINDOWS.TABLE005HandlingCommodity OK
WINDOWS.TABLE005HowToOrder OK
WINDOWS.TABLE005InformationSupport OK
WINDOWS.TABLE005Item OK
WINDOWS.TABLE005Margin OK
WINDOWS.TABLE005MobileService OK
WINDOWS.TABLE005Others OK
WINDOWS.TABLE005SearchTerm OK
WINDOWS.TABLE005Sort OK
WINDOWS.T_TEST OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : 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
Error : You can't use locks with log tables.
status : 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
test.M01 OK
test.STUDY OK
test.STUDY2 OK
test.T01 OK
test.T_SiteResponse OK
test.company OK
test.innodb_monitor OK
Running 'mysql_fix_privilege_tables'...
OK
[root@MYSQL01 bin]#

⑩ 特にエラーが無ければ、古いMYSQLと入れ替える為にシンボリックリンクを切り替えて
MYSQLを停止 → シンボリックリンク切り替え → MYSQLを起動。

[root@MYSQL01 local]# /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]
[root@MYSQL01 local]# ls -l

[root@MYSQL01 local]# ls -l
lrwxrwxrwx 1 root root 35 7月 14 14:42 mysql -> mysql-5.1.32-linux-i686-icc-glibc23
drwxrwxr-x 13 root mysql 4096 2月 14 19:22 mysql-5.1.32-linux-i686-icc-glibc23
drwxrwxr-x 14 mysql mysql 4096 7月 14 14:12 mysql-5.1.34-linux-i686-icc-glibc23

[root@MYSQL01 local]# rm mysql

[root@MYSQL01 local]# ln -s mysql-5.1.34-linux-i686-icc-glibc23 mysql

lrwxrwxrwx 1 root root 35 7月 14 14:42 mysql -> mysql-5.1.34-linux-i686-icc-glibc23
drwxrwxr-x 13 root mysql 4096 2月 14 19:22 mysql-5.1.32-linux-i686-icc-glibc23
drwxrwxr-x 14 mysql mysql 4096 7月 14 14:12 mysql-5.1.34-linux-i686-icc-glibc23

[root@MYSQL01 local]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
[root@MYSQL01 local]# ls -l

⑪ MYSQLのオプションファイルは指定しなくても、オリジナル(/etc/my.cnf)が読み込まれport 3306にて接続
出来る。この時点でデータベース基本アップグレードは終了。

[root@MYSQL01 local]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.34-log MySQL Community Server (GPL)

⑫ 最終確認として、Version確認、アカウント確認、接続確認などを行う。
最終的にエラーログにエラーが書き込まれて無ければ無事終了。

mysql> select version();
+------------+
| version() |
+------------+
| 5.1.34-log |
+------------+
1 row in set (0.00 sec)

mysql> select user,host,password from mysql.user;
+-----------------+-------------------------+-------------------------------------------+
| user | host | password |
+-----------------+-------------------------+-------------------------------------------+
| root | localhost | *D5B72F886E3918D73A1AAAAD8F6C62EC6C4DFFFF |
| root | MYSQL01.local | *D5B72F886E3918D73A1AAAAD8F6C62EC6C4DFFFF |
| root | 127.0.0.1 | *D5B72F886E3918D73A1AAAAD8F6C62EC6C4DFFFF |
| my_user | 192.168.0.72 | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
| my_user | 192.168.0.115 | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
| my_user | PC08001.local | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
| my_user | PC06013.local | *16320B8A6B1F40A0D6924311E03EEEE6379AAAA2 |
+-----------------+-------------------------+-------------------------------------------+
20 rows in set (0.00 sec)

mysql> show full processlist;
+----+-----------------+-----------+--------------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+------+-------+-----------------------+
| 10 | root | localhost | information_schema | Query | 0 | NULL | show full processlist |
| 28 | my_user | localhost | TEST | Sleep | 5414 | | NULL |
+----+-----------------+-----------+--------------------+---------+------+-------+-----------------------+
2 rows in set (0.01 sec)

mysql>

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.34, for pc-linux-gnu (i686) using readline 5.1

Connection id: 10
Current database: information_schema
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.34-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 3 hours 13 min 56 sec

Threads: 2 Questions: 8919 Slow queries: 936 Opens: 619 Flush tables: 1 Open tables: 64 Queries per second avg: 0.766
--------------

mysql> exit