先日、MYSQLを5.5.27に更新したのでinformation_schemaを確認すると同時に
information_schema.tablesを改めて確認してみる。

MYSQL Version 5.5.27のinformation_schema

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

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_CMP_RESET                      |
| INNODB_TRX                            |
| INNODB_CMPMEM_RESET                   |
| INNODB_LOCK_WAITS                     |
| INNODB_CMPMEM                         |
| INNODB_CMP                            |
| INNODB_LOCKS                          |
+---------------------------------------+
37 rows in set (0.00 sec)

mysql>

information_schema

information_schema.tablesの基本構造

mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

information_schema_tables

DBインスタンスで利用されているENGINEの種類

mysql> select distinct engine from information_schema.tables;
+--------------------+
| engine             |
+--------------------+
| MEMORY             |
| MyISAM             |
| InnoDB             |
| CSV                |
| PERFORMANCE_SCHEMA |
+--------------------+
5 rows in set (0.00 sec)

mysql>

InnoDBは自分で作成したテーブルがメインなので今回はあえて、
MYSQL、MEMORYエンジンなどを確認してみる。

MyISAMを利用しているTABLE

mysql> select table_schema, table_name
    -> from information_schema.tables
    -> where engine='MyISAM';
+--------------------+---------------------------+
| table_schema       | table_name                |
+--------------------+---------------------------+
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | columns_priv              |
| mysql              | db                        |
| mysql              | event                     |
| mysql              | func                      |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | host                      |
| mysql              | ndb_binlog_index          |
| mysql              | plugin                    |
| mysql              | proc                      |
| mysql              | procs_priv                |
| mysql              | proxies_priv              |
| mysql              | servers                   |
| mysql              | tables_priv               |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| mysql              | user                      |
+--------------------+---------------------------+
31 rows in set (0.01 sec)

CSVを利用しているテーブル

mysql> select table_schema, table_name
    -> from information_schema.tables
    -> where engine='CSV';
+--------------+-------------+
| table_schema | table_name  |
+--------------+-------------+
| mysql        | general_log |
| mysql        | slow_log    |
+--------------+-------------+
2 rows in set (0.01 sec)

mysql>

information_schema_tables_csv

MEMORYを利用しているテーブル

mysql> select table_schema, table_name
    -> from information_schema.tables
    -> where engine='MEMORY';
+--------------------+---------------------------------------+
| table_schema       | table_name                            |
+--------------------+---------------------------------------+
| information_schema | CHARACTER_SETS                        |
| information_schema | COLLATIONS                            |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMN_PRIVILEGES                     |
| information_schema | ENGINES                               |
| information_schema | FILES                                 |
| information_schema | GLOBAL_STATUS                         |
| information_schema | GLOBAL_VARIABLES                      |
| information_schema | KEY_COLUMN_USAGE                      |
| information_schema | PROFILING                             |
| information_schema | REFERENTIAL_CONSTRAINTS               |
| information_schema | SCHEMATA                              |
| information_schema | SCHEMA_PRIVILEGES                     |
| information_schema | SESSION_STATUS                        |
| information_schema | SESSION_VARIABLES                     |
| information_schema | STATISTICS                            |
| information_schema | TABLES                                |
| information_schema | TABLESPACES                           |
| information_schema | TABLE_CONSTRAINTS                     |
| information_schema | TABLE_PRIVILEGES                      |
| information_schema | USER_PRIVILEGES                       |
| information_schema | INNODB_CMP_RESET                      |
| information_schema | INNODB_TRX                            |
| information_schema | INNODB_CMPMEM_RESET                   |
| information_schema | INNODB_LOCK_WAITS                     |
| information_schema | INNODB_CMPMEM                         |
| information_schema | INNODB_CMP                            |
| information_schema | INNODB_LOCKS                          |
+--------------------+---------------------------------------+
28 rows in set (0.01 sec)

mysql>

詳細はまた次回再確認確認


MYSQL5.5.27がリリースされていたのでバージョンアップ

D.1.2. Changes in MySQL 5.5.27 (2012-08-02)
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-27.html

InnoDB、Partitioning、Replicationなどに関するBug Fixなど。
その他以下のように、YEAR(2)対応が含まれているようです。
Important Change: The YEAR(2) data type is now deprecated because it is problematic.
Support for YEAR(2) will be removed in a future release of MySQL.

1) MYSQLバックアップ

[root@aws ec2-user]# mysqldump --all-databases --single-transaction --flush-logs > /home/ec2-user/mysql_dump20120826.sql  -u root -p
Enter password:
[root@aws ec2-user]# ls -l mysql_dump20120826.sql
-rw-r--r-- 1 root root 1100643 Aug 26 12:00 mysql_dump20120826.sql

2)ダウンロードと展開
[root@aws src]# tar zxvf mysql-5.5.27-linux2.6-x86_64.tar.gz

[省略…]

[root@aws src]# mv mysql-5.5.27-linux2.6-x86_64 /usr/local/
[root@aws src]# ls -l /usr/local/
total 48
drwxr-xr-x 2 root root 4096 May 27 09:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 2012 etc
drwxr-xr-x 2 root root 4096 Jan 6 2012 games
drwxr-xr-x 3 root root 4096 May 27 09:00 include
drwxr-xr-x 2 root root 4096 May 26 23:12 lib
drwxr-xr-x 3 root root 4096 Mar 24 17:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 2012 libexec
lrwxrwxrwx 1 root root 30 Jul 7 00:30 mysql -> mysql-5.5.25a-linux2.6-x86_64/
drwxrwxr-x 13 mysql mysql 4096 Jul 7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 13 root root 4096 Aug 26 12:03 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 2012 sbin
drwxr-xr-x 6 root root 4096 Mar 24 17:06 share
drwxr-xr-x 2 root root 4096 Aug 26 12:04 src
[root@aws src]#

3)サービス停止→データコピー→シンボリックリンク張り直し

[root@aws local]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@aws local]#

[root@aws local]# ls -l
total 48
drwxr-xr-x 2 root root 4096 May 27 09:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 2012 etc
drwxr-xr-x 2 root root 4096 Jan 6 2012 games
drwxr-xr-x 3 root root 4096 May 27 09:00 include
drwxr-xr-x 2 root root 4096 May 26 23:12 lib
drwxr-xr-x 3 root root 4096 Mar 24 17:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 2012 libexec
lrwxrwxrwx 1 root root 30 Jul 7 00:30 mysql -> mysql-5.5.25a-linux2.6-x86_64/
drwxrwxr-x 13 mysql mysql 4096 Jul 7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 13 root root 4096 Aug 26 12:07 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 2012 sbin
drwxr-xr-x 6 root root 4096 Mar 24 17:06 share
drwxr-xr-x 2 root root 4096 Aug 26 12:04 src
[root@aws local]#

[root@aws local]# cp -rp /usr/local/mysql/data/ /usr/local/mysql-5.5.27-linux2.6-x86_64
[root@aws local]# rm mysql
rm: remove symbolic link `mysql’? y
[root@aws local]# ln -s /usr/local/mysql-5.5.27-linux2.6-x86_64 mysql
[root@aws local]#

[root@aws local]# ls -l
total 48
drwxr-xr-x 2 root root 4096 May 27 09:00 bin
drwxr-xr-x 2 root root 4096 Jan 6 2012 etc
drwxr-xr-x 2 root root 4096 Jan 6 2012 games
drwxr-xr-x 3 root root 4096 May 27 09:00 include
drwxr-xr-x 2 root root 4096 May 26 23:12 lib
drwxr-xr-x 3 root root 4096 Mar 24 17:06 lib64
drwxr-xr-x 2 root root 4096 Jan 6 2012 libexec
lrwxrwxrwx 1 root root 39 Aug 26 12:09 mysql -> /usr/local/mysql-5.5.27-linux2.6-x86_64
drwxrwxr-x 13 mysql mysql 4096 Jul 7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 14 root root 4096 Aug 26 12:09 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x 2 root root 4096 Jan 6 2012 sbin
drwxr-xr-x 6 root root 4096 Mar 24 17:06 share
drwxr-xr-x 2 root root 4096 Aug 26 12:04 src
[root@aws local]#

Symbolic Link

4)権限設定
[root@aws local]# chown -R mysql:mysql mysql-5.5.27-linux2.6-x86_64
[root@aws local]#

5)新しいバイナリーでサービスの起動
[root@aws data]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@aws data]#

6)MYSQL_UPGRADEの実行とシステムテーブルの更新
[root@aws bin]# /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@aws bin]#

7)バージョンの確認
[root@aws data]# cat mysql_upgrade_info
5.5.27
[root@aws data]#

5.5.27

参考サイト:
D.1.2. Changes in MySQL 5.5.27 (2012-08-02)