MySQL5.7.7RCの次のリリース、MySQL5.7.8が8月3日にリリースされました。
多くの機能追加や変更が行われています。RCの次のリリースですが、GAでは無いのでRC2という事になるかと思います。
RCにしては、少々機能追加が多いかと思いますが、色々と便利な機能が追加されています。
詳細は、以下のURLを参照下さい。

Changes in MySQL 5.7.8 (2015-08-03)
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html

—————————————
Backup Notes
Configuration Notes
JSON Notes
Optimizer Notes
Packaging Notes
Performance Schema Notes
Plugin Notes
Security Notes
Spatial Data Support
SQL Mode Notes
Functionality Added or Changed
Bugs Fixed

本日は、MySQL5.6.26とMySQL5.7.8のパラメータの違いを確認してみました。
パラメータの拡張、新規パラメータの追加があります。

【パラメータの変更については、こちらを参照下さい。】
binlog_error_action
binlog_format
binlog_gtid_simple_recovery
eq_range_index_dive_limit
innodb_buffer_pool_dump_at_shutdown
その他。。。
change

変更されたパタメータを取りまとめた資料
MySQL5.7.8における変更されたパラメータ(pdf)

【新規追加されたパラメータについては此方を参照下さい。】
binlog_group_commit_sync_delay
binlog_group_commit_sync_no_delay_count
check_proxy_users
default_authentication_plugin
default_password_lifetime
その他。。。
new

新規追加されたパラメータ簡易取りまとめPDF
MySQL5.7.8において、新規追加されたパラメータ(pdf)

※新規追加されたパラメータに関しては、一部、Defaultの値から変更しています。
 Default値の確認は、5.1.4 Server System Variables を参照してください。

その他、以下のパラメータ等は、Removeされているようでした。
———————————————————
binlogging_impossible_mode
innodb_additional_mem_pool_size
innodb_mirrored_log_groups
innodb_use_sys_malloc
sha256_password_private_key_path
sha256_password_public_key_path
simplified_binlog_gtid_recovery
storage_engine
thread_concurrency
timed_mutexes

その他参考
Improved Server Defaults in 5.7

此方で5.7GA版と5.6のパラメータ比較資料がダウンロード可能です。
http://downloads.mysql.com/presentations/20151030_05_MySQL-Parameter-comparison.pdf


LockはRDBMSには不可欠ですが、Dead Lockは出来るだけ回避したいLock。
SHOW ENGINE INNODB STATUS以外でもMySQL5.6からログに記録出来るようになってます。

mysql> show variables like 'innodb_print_all_deadlocks';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)


mysql> set innodb_print_all_deadlocks=ON;

ERROR 1229 (HY000): Variable 'innodb_print_all_deadlocks' is a GLOBAL variable and should be set with SET GLOBAL

mysql> set global innodb_print_all_deadlocks=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_print_all_deadlocks';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+

1 row in set (0.00 sec)

 
mysql>

参考: http://thinkit.co.jp/story/2013/11/19/4670/page/0/1


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]#

ダウンロード用URL

【32bit】
http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.7-rc-linux2.6-i686.tar.gz
【64bit】
http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.7-rc-linux2.6-x86_64.tar.gz

ダウンロードと展開


[root@192-168-11-36 src]# wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.7-rc-linux2.6-i686.tar.gz
--2012-10-26 15:09:24--  http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.7-rc-linux2.6-i686.tar.gz
Resolving cdn.mysql.com... 210.149.135.77, 210.149.135.22
Connecting to cdn.mysql.com|210.149.135.77|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 284584217 (271M) [application/x-tar-gz]
Saving to: `mysql-5.6.7-rc-linux2.6-i686.tar.gz'

100%[==========================================================================>] 284,584,217  917K/s   in 5m 45s

2012-10-26 15:15:10 (805 KB/s) - `mysql-5.6.7-rc-linux2.6-i686.tar.gz' saved [284584217/284584217]

[root@192-168-11-36 src]#

[root@192-168-11-36 src]# tar zxvf mysql-5.6.7-rc-linux2.6-i686.tar.gz
mysql-5.6.7-rc-linux2.6-i686/data/mysql/dummy.bak
mysql-5.6.7-rc-linux2.6-i686/data/test/dummy.bak
mysql-5.6.7-rc-linux2.6-i686/lib/libmysqld-debug.a
mysql-5.6.7-rc-linux2.6-i686/lib/plugin/validate_password.so
mysql-5.6.7-rc-linux2.6-i686/lib/plugin/qa_auth_server.so

[省略]

mysql-5.6.7-rc-linux2.6-x86_64/man/man1/innochecksum.1
mysql-5.6.7-rc-linux2.6-x86_64/man/man1/mysqltest_embedded.1
mysql-5.6.7-rc-linux2.6-x86_64/man/man1/resolve_stack_dump.1
mysql-5.6.7-rc-linux2.6-x86_64/man/man1/mysqltest.1
mysql-5.6.7-rc-linux2.6-x86_64/man/man8/mysqld.8
[sugiyama_s@192-168-11-36 mysql]$ 

インストール先への移動とシンボリックリンク作成

[root@192-168-11-36 src]# ls -lh
total 272M
drwxr-xr-x  2 root root 4.0K May 11  2011 debug
drwxr-xr-x  2 root root 4.0K May 11  2011 kernels
drwxr-xr-x 13 root root 4.0K Oct 26 15:17 mysql-5.6.7-rc-linux2.6-i686
-rw-r--r--  1 root root 272M Sep 19 16:46 mysql-5.6.7-rc-linux2.6-i686.tar.gz
drwxr-xr-x  7 root root 4.0K Jul 20 14:45 redhat
[root@192-168-11-36 src]# mv mysql-5.6.7-rc-linux2.6-i686 /usr/local/
[root@192-168-11-36 src]# cd /usr/local/
[root@192-168-11-36 local]# ls -lh
total 44K
drwxr-xr-x  2 root   root   4.0K Jun 11 15:42 bin
drwxr-xr-x  2 root   root   4.0K Dec  7  2011 etc
drwxr-xr-x  2 root   root   4.0K May 11  2011 games
drwxr-xr-x  2 root   root   4.0K May 11  2011 include
drwxr-xr-x  2 root   root   4.0K May 11  2011 lib
drwxr-xr-x  2 root   root   4.0K May 11  2011 libexec
drwxr-xr-x 13 root   root   4.0K Oct 26 15:17 mysql-5.6.7-rc-linux2.6-i686
drwxrwxr-x  9 nagios nagios 4.0K Aug  1 14:54 nagios
drwxr-xr-x  2 root   root   4.0K May 11  2011 sbin
drwxr-xr-x  7 root   root   4.0K Jun 11 15:42 share
drwxr-xr-x  2 root   root   4.0K May 11  2011 src
[root@192-168-11-36 local]#


[root@192-168-11-36 local]# ln -s mysql-5.6.7-rc-linux2.6-i686/ mysql
[root@192-168-11-36 local]# ls -l
total 44
drwxr-xr-x  2 root   root   4096 Jun 11 15:42 bin
drwxr-xr-x  2 root   root   4096 Dec  7  2011 etc
drwxr-xr-x  2 root   root   4096 May 11  2011 games
drwxr-xr-x  2 root   root   4096 May 11  2011 include
drwxr-xr-x  2 root   root   4096 May 11  2011 lib
drwxr-xr-x  2 root   root   4096 May 11  2011 libexec
lrwxrwxrwx  1 root   root     29 Oct 26 15:18 mysql -> mysql-5.6.7-rc-linux2.6-i686/
drwxr-xr-x 13 root   root   4096 Oct 26 15:17 mysql-5.6.7-rc-linux2.6-i686
drwxrwxr-x  9 nagios nagios 4096 Aug  1 14:54 nagios
drwxr-xr-x  2 root   root   4096 May 11  2011 sbin
drwxr-xr-x  7 root   root   4096 Jun 11 15:42 share
drwxr-xr-x  2 root   root   4096 May 11  2011 src
[root@192-168-11-36 local]#

設定ファイルのコピーと編集。権限設定

[root@192-168-11-36 mysql]# cp -p support-files/my-medium.cnf /etc/my.cnf
[root@192-168-11-36 mysql]# cp -p support-files/mysql.server /etc/init.d/mysql
[root@192-168-11-36 mysql]# vi /etc/init.d/mysql
[root@192-168-11-36 mysql]# vi /etc/my.cnf
[root@192-168-11-36 mysql]# chmod 755 /etc/init.d/mysql
[root@192-168-11-36 mysql]# chmod -R 775 /usr/local/mysql

編集項目(my.cnf)

[root@192-168-11-36 mysql]# cat /etc/my.cnf | egrep -i -b6 utf8
676-
677-# The following options will be passed to all MySQL clients
737-[client]
746-#password = your_password
772-port = 3306
785-socket = /tmp/mysql.sock
811:default-character-set = utf8
840-
841-# Here follows entries for some specific programs
891-
892-# The MySQL server
911-[mysqld]
920-port = 3306
933-socket = /tmp/mysql.sock
959:character-set-server = utf8
987-skip-external-locking
1009-key_buffer_size = 16M
1031-max_allowed_packet = 1M
1055-table_open_cache = 64
1077-sort_buffer_size = 512K
1101-net_buffer_length = 8K

4446-# dictionary file for validate_password plugin
4493-loose-validate_password_dictionary_file = /usr/local/mysql/share/dictionary.txt
4573-
4574-[mysqldump]
4586-quick
4592-max_allowed_packet = 16M
4617:default-character-set = utf8
4646-
4647-
4648-[mysql]
4656-no-auto-rehash
4671-# Remove the next comment character if you are not familiar with SQL
4740-#safe-updates
4754:default-character-set = utf8
4783-
4784-
4785-[myisamchk]
4797-key_buffer_size = 20M
4819-sort_buffer_size = 20M
4842-read_buffer = 2M
[root@192-168-11-36 mysql]#

編集項目(/etc/init.d/mysql)

[root@192-168-11-36 mysql]# cat /etc/init.d/mysql | egrep “basedir=|datadir=”
# basedir= basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
datadir=”$basedir/data”
# *not* set inside of the –basedir= handler.)
–basedir=*) basedir=`echo “$arg” | sed -e ‘s/^[^=]*=//’`
datadir=”$basedir/data”
–datadir=*) datadir=`echo “$arg” | sed -e ‘s/^[^=]*=//’`
$bindir/mysqld_safe –datadir=”$datadir” –pid-file=”$mysqld_pid_file_path” $other_args >/dev/null 2>&1 &
[root@192-168-11-36 mysql]#

権限設定とシステムデータベース作成



[root@192-168-11-36 local]# chmod -R 755 mysql-5.6.7-rc-linux2.6-i686/

[root@192-168-11-36 mysql]# scripts/mysql_install_db
Installing MySQL system tables...
121026 15:24:45 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
121026 15:24:45 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.
121026 15:24:45 InnoDB: The InnoDB memory heap is disabled
121026 15:24:45 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121026 15:24:45 InnoDB: Compressed tables use zlib 1.2.3
121026 15:24:45 InnoDB: Using Linux native AIO
121026 15:24:45 InnoDB: CPU does not support crc32 instructions
121026 15:24:45 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.
121026 15:24:45 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
121026 15:24:45 InnoDB: Warning: Linux Native AIO disabled.
121026 15:24:45 InnoDB: Initializing buffer pool, size = 64.0M
121026 15:24:45 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file /usr/local/mysql/data/ibdata1 did not exist:
InnoDB: a new database to be created!
121026 15:24:45 InnoDB: Setting file /usr/local/mysql/data/ibdata1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
121026 15:24:49 InnoDB: Log file /usr/local/mysql/data/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /usr/local/mysql/data/ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
121026 15:24:49 InnoDB: Log file /usr/local/mysql/data/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /usr/local/mysql/data/ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
121026 15:24:49 InnoDB: Doublewrite buffer not found: creating new
121026 15:24:49 InnoDB: Doublewrite buffer created
121026 15:24:49 InnoDB: 128 rollback segment(s) are active.
121026 15:24:49 [Warning] InnoDB: Creating foreign key constraint system tables.

121026 15:24:49 [Note] InnoDB: Foreign key constraint system tables created

121026 15:24:49 [Note] InnoDB: Creating tablespace and datafile system tables.

121026 15:24:49 [Note] InnoDB: Tablespace and datafile system tables created

121026 15:24:49 InnoDB: Waiting for the background threads to start
121026 15:24:49 InnoDB: 1.2.7 started; log sequence number 0
121026 15:24:50 [Note] Binlog end
121026 15:24:50 InnoDB: FTS optimize thread exiting.
121026 15:24:50 InnoDB: Starting shutdown...
121026 15:24:51 InnoDB: Shutdown completed; log sequence number 1629432
OK
Filling help tables...
121026 15:24:51 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
121026 15:24:51 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.
121026 15:24:51 InnoDB: The InnoDB memory heap is disabled
121026 15:24:51 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121026 15:24:51 InnoDB: Compressed tables use zlib 1.2.3
121026 15:24:51 InnoDB: Using Linux native AIO
121026 15:24:51 InnoDB: CPU does not support crc32 instructions
121026 15:24:51 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.
121026 15:24:51 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
121026 15:24:51 InnoDB: Warning: Linux Native AIO disabled.
121026 15:24:51 InnoDB: Initializing buffer pool, size = 64.0M
121026 15:24:51 InnoDB: Completed initialization of buffer pool
121026 15:24:51 InnoDB: highest supported file format is Barracuda.
121026 15:24:51 InnoDB: 128 rollback segment(s) are active.
121026 15:24:51 InnoDB: Waiting for the background threads to start
121026 15:24:51 InnoDB: 1.2.7 started; log sequence number 1629432
121026 15:24:51 [Note] Binlog end
121026 15:24:51 InnoDB: FTS optimize thread exiting.
121026 15:24:51 InnoDB: Starting shutdown...
121026 15:24:52 InnoDB: Shutdown completed; log sequence number 1629442
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h 192-168-11-36.vm.variable.jp password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

[root@192-168-11-36 local]# chown -R mysql:mysql mysql-5.6.7-rc-linux2.6-i686/

パスワードを設定していないので、パスワード無しでログイン後に直ぐにパスワード設定。

[root@192-168-11-36 mysql]# ./bin/mysqld_safe &
[1] 11748
[root@192-168-11-36 mysql]# 121026 15:27:15 mysqld_safe Logging to '/usr/local/mysql/data/192-168-11-36.vm.variable.jp.err'.
121026 15:27:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@192-168-11-36 mysql]# ps -ef | grep mysql
root     11748  6104  0 15:27 pts/0    00:00:00 /bin/sh ./bin/mysqld_safe
mysql    12107 11748  1 15:27 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/192-168-11-36.vm.variable.jp.err --pid-file=/usr/local/mysql/data/192-168-11-36.vm.variable.jp.pid --socket=/tmp/mysql.sock --port=3306
root     12137  6104  0 15:27 pts/0    00:00:00 grep mysql
[root@192-168-11-36 mysql]#  ./bin/mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'AWS-SQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@127.0.0.1 IDENTIFIED BY 'AWS-SQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@`192-168-11-36.vm.variable.jp` IDENTIFIED BY 'AWS-SQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

不要なアカウントを削除

mysql> use mysql
Database changed
mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.01 sec)


mysql>  delete from user where user='';
Query OK, 2 rows affected (0.00 sec)



mysql> select Host,User,Password from user;
+--------------------------------+------+-------------------------------------------+
| Host                           | User | Password                                  |
+--------------------------------+------+-------------------------------------------+
| localhost                      | root | *56B03CBC2122BA53BF892D59404D4D8333117364 |
| 192-168-11-36.vm.variable.jp   | root | *56B03CBC2122BA53BF892D59404D4D8333117364 |
| 127.0.0.1                      | root | *56B03CBC2122BA53BF892D59404D4D8333117364 |
| ::1                            | root |                                           |
+--------------------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql>  delete from user where Password='';
Query OK, 1 row affected (0.00 sec)

mysql> select Host,User,Password from user;
+--------------------------------+------+-------------------------------------------+
| Host                           | User | Password                                  |
+--------------------------------+------+-------------------------------------------+
| localhost                      | root | *56B03CBC2122BA53BF892D59404D4D8333117364 |
| 192-168-11-36.vm.variable.jp   | root | *56B03CBC2122BA53BF892D59404D4D8333117364 |
| 127.0.0.1                      | root | *56B03CBC2122BA53BF892D59404D4D8333117364 |
+--------------------------------+------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql>



mysql> select Host,Db,User from db;
+------+---------+------+
| Host | Db      | User |
+------+---------+------+
| %    | test    |      |
| %    | test\_% |      |
+------+---------+------+
2 rows in set (0.00 sec)

mysql> delete from db where User='';
Query OK, 2 rows affected (0.00 sec)

mysql> select Host,Db,User from db;
Empty set (0.00 sec)

mysql> exit
Bye
[root@192-168-11-36 mysql]#

MYSQLから一度出てプロセスを一度停止

[root@192-168-11-36 mysql]# ps -ef | grep mysql
root 11748 6104 0 15:27 pts/0 00:00:00 /bin/sh ./bin/mysqld_safe
mysql 12107 11748 0 15:27 pts/0 00:00:00 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/usr/local/mysql/data/192-168-11-36.vm.variable.jp.err –pid-file=/usr/local/mysql/data/192-168-11-36.vm.variable.jp.pid –socket=/tmp/mysql.sock –port=3306
root 12580 6104 0 15:37 pts/0 00:00:00 grep mysql
[root@192-168-11-36 mysql]# kill 12107
[root@192-168-11-36 mysql]# 121026 15:37:18 mysqld_safe mysqld from pid file /usr/local/mysql/data/192-168-11-36.vm.variable.jp.pid ended

[1]+ Done ./bin/mysqld_safe
[root@192-168-11-36 mysql]# ps -ef | grep mysql
root 12600 6104 0 15:37 pts/0 00:00:00 grep mysql
[root@192-168-11-36 mysql]#

パス設定と自動起動

[root@192-168-11-36 mysql]# vi /etc/profile
# MySQL
PATH=$PATH:/usr/local/mysql/bin
[root@192-168-11-36 mysql]#
[root@192-168-11-36 mysql]# /sbin/chkconfig mysql on
[root@192-168-11-36 mysql]# /sbin/chkconfig –list mysql
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@192-168-11-36 mysql]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
[root@192-168-11-36 mysql]#

Information_Schema確認

[root@192-168-11-36 mysql]# 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.6.7-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

mysql>


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                      |
| OPTIMIZER_TRACE                       |
| 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_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_LOCK_WAITS                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_FT_INSERTED                    |
| INNODB_CMPMEM                         |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_FT_CONFIG                      |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
+---------------------------------------+
60 rows in set (0.00 sec)

mysql> 

performance_schema確認

mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
| accounts                                           |
| cond_instances                                     |
| events_stages_current                              |
| events_stages_history                              |
| events_stages_history_long                         |
| events_stages_summary_by_account_by_event_name     |
| events_stages_summary_by_host_by_event_name        |
| events_stages_summary_by_thread_by_event_name      |
| events_stages_summary_by_user_by_event_name        |
| events_stages_summary_global_by_event_name         |
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| events_waits_current                               |
| events_waits_history                               |
| events_waits_history_long                          |
| events_waits_summary_by_account_by_event_name      |
| events_waits_summary_by_host_by_event_name         |
| events_waits_summary_by_instance                   |
| events_waits_summary_by_thread_by_event_name       |
| events_waits_summary_by_user_by_event_name         |
| events_waits_summary_global_by_event_name          |
| file_instances                                     |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |
| host_cache                                         |
| hosts                                              |
| mutex_instances                                    |
| objects_summary_global_by_type                     |
| performance_timers                                 |
| rwlock_instances                                   |
| session_account_connect_attrs                      |
| session_connect_attrs                              |
| setup_actors                                       |
| setup_consumers                                    |
| setup_instruments                                  |
| setup_objects                                      |
| setup_timers                                       |
| socket_instances                                   |
| socket_summary_by_event_name                       |
| socket_summary_by_instance                         |
| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |
| threads                                            |
| users                                              |
+----------------------------------------------------+
52 rows in set (0.00 sec)

mysql>

MYSQL5.6からの新しいテーブル
パフォーマンス関連

mysql> desc INNODB_METRICS;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| NAME            | varchar(193) | NO   |     |         |       |
| SUBSYSTEM       | varchar(193) | NO   |     |         |       |
| COUNT           | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT       | double       | YES  |     | NULL    |       |
| COUNT_RESET     | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT_RESET | double       | YES  |     | NULL    |       |
| TIME_ENABLED    | datetime     | YES  |     | NULL    |       |
| TIME_DISABLED   | datetime     | YES  |     | NULL    |       |
| TIME_ELAPSED    | bigint(21)   | YES  |     | NULL    |       |
| TIME_RESET      | datetime     | YES  |     | NULL    |       |
| STATUS          | varchar(193) | NO   |     |         |       |
| TYPE            | varchar(193) | NO   |     |         |       |
| COMMENT         | varchar(193) | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
17 rows in set (0.00 sec)

mysql>

バッファプール情報

mysql> desc INNODB_BUFFER_PAGE;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID             | bigint(21) unsigned | NO   |     | 0       |       |
| BLOCK_ID            | bigint(21) unsigned | NO   |     | 0       |       |
| SPACE               | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_NUMBER         | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_TYPE           | varchar(64)         | YES  |     | NULL    |       |
| FLUSH_TYPE          | bigint(21) unsigned | NO   |     | 0       |       |
| FIX_COUNT           | bigint(21) unsigned | NO   |     | 0       |       |
| IS_HASHED           | varchar(3)          | YES  |     | NULL    |       |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO   |     | 0       |       |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO   |     | 0       |       |
| ACCESS_TIME         | bigint(21) unsigned | NO   |     | 0       |       |
| TABLE_NAME          | varchar(1024)       | YES  |     | NULL    |       |
| INDEX_NAME          | varchar(1024)       | YES  |     | NULL    |       |
| NUMBER_RECORDS      | bigint(21) unsigned | NO   |     | 0       |       |
| DATA_SIZE           | bigint(21) unsigned | NO   |     | 0       |       |
| COMPRESSED_SIZE     | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_STATE          | varchar(64)         | YES  |     | NULL    |       |
| IO_FIX              | varchar(64)         | YES  |     | NULL    |       |
| IS_OLD              | varchar(3)          | YES  |     | NULL    |       |
| FREE_PAGE_CLOCK     | bigint(21) unsigned | NO   |     | 0       |       |
+---------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

mysql> desc INNODB_BUFFER_PAGE_LRU;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID             | bigint(21) unsigned | NO   |     | 0       |       |
| LRU_POSITION        | bigint(21) unsigned | NO   |     | 0       |       |
| SPACE               | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_NUMBER         | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_TYPE           | varchar(64)         | YES  |     | NULL    |       |
| FLUSH_TYPE          | bigint(21) unsigned | NO   |     | 0       |       |
| FIX_COUNT           | bigint(21) unsigned | NO   |     | 0       |       |
| IS_HASHED           | varchar(3)          | YES  |     | NULL    |       |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO   |     | 0       |       |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO   |     | 0       |       |
| ACCESS_TIME         | bigint(21) unsigned | NO   |     | 0       |       |
| TABLE_NAME          | varchar(1024)       | YES  |     | NULL    |       |
| INDEX_NAME          | varchar(1024)       | YES  |     | NULL    |       |
| NUMBER_RECORDS      | bigint(21) unsigned | NO   |     | 0       |       |
| DATA_SIZE           | bigint(21) unsigned | NO   |     | 0       |       |
| COMPRESSED_SIZE     | bigint(21) unsigned | NO   |     | 0       |       |
| COMPRESSED          | varchar(3)          | YES  |     | NULL    |       |
| IO_FIX              | varchar(64)         | YES  |     | NULL    |       |
| IS_OLD              | varchar(3)          | YES  |     | NULL    |       |
| FREE_PAGE_CLOCK     | bigint(21) unsigned | NO   |     | 0       |       |
+---------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

mysql> desc INNODB_BUFFER_POOL_STATS;
+----------------------------------+---------------------+------+-----+---------+-------+
| Field                            | Type                | Null | Key | Default | Extra |
+----------------------------------+---------------------+------+-----+---------+-------+
| POOL_ID                          | bigint(21) unsigned | NO   |     | 0       |       |
| POOL_SIZE                        | bigint(21) unsigned | NO   |     | 0       |       |
| FREE_BUFFERS                     | bigint(21) unsigned | NO   |     | 0       |       |
| DATABASE_PAGES                   | bigint(21) unsigned | NO   |     | 0       |       |
| OLD_DATABASE_PAGES               | bigint(21) unsigned | NO   |     | 0       |       |
| MODIFIED_DATABASE_PAGES          | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_DECOMPRESS               | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_READS                    | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_FLUSH_LRU                | bigint(21) unsigned | NO   |     | 0       |       |
| PENDING_FLUSH_LIST               | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_MADE_YOUNG                 | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_NOT_MADE_YOUNG             | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_MADE_YOUNG_RATE            | double              | NO   |     | 0       |       |
| PAGES_MADE_NOT_YOUNG_RATE        | double              | NO   |     | 0       |       |
| NUMBER_PAGES_READ                | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_PAGES_CREATED             | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_PAGES_WRITTEN             | bigint(21) unsigned | NO   |     | 0       |       |
| PAGES_READ_RATE                  | double              | NO   |     | 0       |       |
| PAGES_CREATE_RATE                | double              | NO   |     | 0       |       |
| PAGES_WRITTEN_RATE               | double              | NO   |     | 0       |       |
| NUMBER_PAGES_GET                 | bigint(21) unsigned | NO   |     | 0       |       |
| HIT_RATE                         | bigint(21) unsigned | NO   |     | 0       |       |
| YOUNG_MAKE_PER_THOUSAND_GETS     | bigint(21) unsigned | NO   |     | 0       |       |
| NOT_YOUNG_MAKE_PER_THOUSAND_GETS | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_PAGES_READ_AHEAD          | bigint(21) unsigned | NO   |     | 0       |       |
| NUMBER_READ_AHEAD_EVICTED        | bigint(21) unsigned | NO   |     | 0       |       |
| READ_AHEAD_RATE                  | double              | NO   |     | 0       |       |
| READ_AHEAD_EVICTED_RATE          | double              | NO   |     | 0       |       |
| LRU_IO_TOTAL                     | bigint(21) unsigned | NO   |     | 0       |       |
| LRU_IO_CURRENT                   | bigint(21) unsigned | NO   |     | 0       |       |
| UNCOMPRESS_TOTAL                 | bigint(21) unsigned | NO   |     | 0       |       |
| UNCOMPRESS_CURRENT               | bigint(21) unsigned | NO   |     | 0       |       |
+----------------------------------+---------------------+------+-----+---------+-------+
32 rows in set (0.00 sec)

mysql>

ディクショナリ情報

mysql> desc INNODB_SYS_TABLES;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| TABLE_ID      | bigint(21) unsigned | NO   |     | 0       |       |
| NAME          | varchar(655)        | NO   |     |         |       |
| FLAG          | int(11)             | NO   |     | 0       |       |
| N_COLS        | int(11)             | NO   |     | 0       |       |
| SPACE         | int(11)             | NO   |     | 0       |       |
| FILE_FORMAT   | varchar(10)         | YES  |     | NULL    |       |
| ROW_FORMAT    | varchar(12)         | YES  |     | NULL    |       |
| ZIP_PAGE_SIZE | int(11) unsigned    | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> desc INNODB_SYS_TABLESTATS;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| TABLE_ID          | bigint(21) unsigned | NO   |     | 0       |       |
| NAME              | varchar(193)        | NO   |     |         |       |
| STATS_INITIALIZED | varchar(193)        | NO   |     |         |       |
| NUM_ROWS          | bigint(21) unsigned | NO   |     | 0       |       |
| CLUST_INDEX_SIZE  | bigint(21) unsigned | NO   |     | 0       |       |
| OTHER_INDEX_SIZE  | bigint(21) unsigned | NO   |     | 0       |       |
| MODIFIED_COUNTER  | bigint(21) unsigned | NO   |     | 0       |       |
| AUTOINC           | bigint(21) unsigned | NO   |     | 0       |       |
| REF_COUNT         | int(11)             | NO   |     | 0       |       |
+-------------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> desc INNODB_SYS_INDEXES;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(21) unsigned | NO   |     | 0       |       |
| NAME     | varchar(193)        | NO   |     |         |       |
| TABLE_ID | bigint(21) unsigned | NO   |     | 0       |       |
| TYPE     | int(11)             | NO   |     | 0       |       |
| N_FIELDS | int(11)             | NO   |     | 0       |       |
| PAGE_NO  | int(11)             | NO   |     | 0       |       |
| SPACE    | int(11)             | NO   |     | 0       |       |
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> desc INNODB_SYS_COLUMNS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(21) unsigned | NO   |     | 0       |       |
| NAME     | varchar(193)        | NO   |     |         |       |
| POS      | bigint(21) unsigned | NO   |     | 0       |       |
| MTYPE    | int(11)             | NO   |     | 0       |       |
| PRTYPE   | int(11)             | NO   |     | 0       |       |
| LEN      | int(11)             | NO   |     | 0       |       |
+----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc INNODB_SYS_FIELDS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(21) unsigned | NO   |     | 0       |       |
| NAME     | varchar(193)        | NO   |     |         |       |
| POS      | int(11) unsigned    | NO   |     | 0       |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc INNODB_SYS_FOREIGN;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID       | varchar(193)     | NO   |     |         |       |
| FOR_NAME | varchar(193)     | NO   |     |         |       |
| REF_NAME | varchar(193)     | NO   |     |         |       |
| N_COLS   | int(11) unsigned | NO   |     | 0       |       |
| TYPE     | int(11) unsigned | NO   |     | 0       |       |
+----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc INNODB_SYS_FOREIGN_COLS;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ID           | varchar(193)     | NO   |     |         |       |
| FOR_COL_NAME | varchar(193)     | NO   |     |         |       |
| REF_COL_NAME | varchar(193)     | NO   |     |         |       |
| POS          | int(11) unsigned | NO   |     | 0       |       |
+--------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

パフォーマンス関連(INNODB_METRICS)からBuffer関連のデータを抽出

mysql> select NAME,SUBSYSTEM,COUNT,MAX_COUNT,MIN_COUNT,AVG_COUNT,STATUS from INNODB_METRICS where name like 'buffer%';
+------------------------------------------+----------------+----------+-----------+-----------+-----------------------+----------+
| NAME                                     | SUBSYSTEM      | COUNT    | MAX_COUNT | MIN_COUNT | AVG_COUNT             | STATUS   |
+------------------------------------------+----------------+----------+-----------+-----------+-----------------------+----------+
| buffer_pool_size                         | server         | 67108864 |  67108864 |  67108864 |                  NULL | enabled  |
| buffer_pool_reads                        | buffer         |      163 |       163 |      NULL |    0.0589938472674629 | enabled  |
| buffer_pool_read_requests                | buffer         |      600 |       600 |      NULL |   0.21715526601520088 | enabled  |
| buffer_pool_write_requests               | buffer         |        1 |         1 |      NULL | 0.0003619254433586681 | enabled  |
| buffer_pool_wait_free                    | buffer         |        0 |         0 |      NULL |                     0 | enabled  |
| buffer_pool_read_ahead                   | buffer         |        0 |         0 |      NULL |                     0 | enabled  |
| buffer_pool_read_ahead_evicted           | buffer         |        0 |         0 |      NULL |                     0 | enabled  |
| buffer_pool_pages_total                  | buffer         |     4096 |      4096 |      4096 |                  NULL | enabled  |
| buffer_pool_pages_misc                   | buffer         |        0 |         0 |         0 |                  NULL | enabled  |
| buffer_pool_pages_data                   | buffer         |      162 |       162 |       162 |                  NULL | enabled  |
| buffer_pool_pages_dirty                  | buffer         |        0 |         0 |         0 |                  NULL | enabled  |
| buffer_pool_pages_free                   | buffer         |     3934 |      3934 |      3934 |                  NULL | enabled  |
| buffer_pages_created                     | buffer         |        0 |         0 |      NULL |                     0 | enabled  |
| buffer_pages_written                     | buffer         |        1 |         1 |      NULL | 0.0003619254433586681 | enabled  |
| buffer_pages_read                        | buffer         |      162 |       162 |      NULL |   0.05863192182410423 | enabled  |
| buffer_data_reads                        | buffer         |  4853760 |   4853760 |      NULL |     1756.699239956569 | enabled  |
| buffer_data_written                      | buffer         |    34304 |     34304 |      NULL |    12.415490408975751 | enabled  |
| buffer_flush_batch_scanned               | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_batch_num_scan              | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_batch_scanned_per_call      | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_batch_total_pages           | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_batches                     | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_batch_pages                 | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_neighbor_total_pages        | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_neighbor                    | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_neighbor_pages              | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_n_to_flush_requested        | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_avg_page_rate               | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_lsn_avg_rate                | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_pct_for_dirty               | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_pct_for_lsn                 | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_sync_waits                  | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_adaptive_total_pages        | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_adaptive                    | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_adaptive_pages              | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_sync_total_pages            | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_sync                        | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_sync_pages                  | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_background_total_pages      | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_background                  | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_flush_background_pages            | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_batch_scanned                 | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_batch_num_scan                | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_batch_scanned_per_call        | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_batch_total_pages             | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_batches                       | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_batch_pages                   | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_single_flush_scanned          | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_single_flush_num_scan         | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_single_flush_scanned_per_call | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_single_flush_failure_count    | Buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_get_free_search               | Buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_search_scanned                | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_search_num_scan               | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_search_scanned_per_call       | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_unzip_search_scanned          | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_unzip_search_num_scan         | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_LRU_unzip_search_scanned_per_call | buffer         |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_index_leaf              | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_index_non_leaf          | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_index_ibuf_leaf         | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_index_ibuf_non_leaf     | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_undo_log                | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_index_inode             | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_ibuf_free_list          | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_ibuf_bitmap             | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_system_page             | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_trx_system              | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_fsp_hdr                 | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_xdes                    | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_blob                    | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_zblob                   | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_zblob2                  | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_read_other                   | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_index_leaf           | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_index_non_leaf       | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_index_ibuf_leaf      | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_index_ibuf_non_leaf  | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_undo_log             | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_index_inode          | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_ibuf_free_list       | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_ibuf_bitmap          | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_system_page          | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_trx_system           | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_fsp_hdr              | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_xdes                 | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_blob                 | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_zblob                | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_zblob2               | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
| buffer_page_written_other                | buffer_page_io |        0 |      NULL |      NULL |                  NULL | disabled |
+------------------------------------------+----------------+----------+-----------+-----------+-----------------------+----------+
90 rows in set (0.00 sec)

mysql>

mysql5-6-7

その他: TIME、DATETIME、TIMESTAMPにおいて小数点以下の秒(ミリ秒やマイクロ秒)を指定可能。


mysql> CREATE TABLE T1 (T TIME(3), DT DATETIME(6));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into T1(T,DT) values('19:33','2012/10/29 19:33');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T1;
+--------------+----------------------------+
| T            | DT                         |
+--------------+----------------------------+
| 19:33:00.000 | 2012-10-29 19:33:00.000000 |
+--------------+----------------------------+
1 row in set (0.00 sec)
mysql>

■GTIDをONにしてレプリケーション設定を有効化

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) –
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master’s port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables’ values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)

log-bin=mysql-bin
log-slave-update
gtid-mode = ON
disable-gtid-unsafe-statements


mysql> show binlog events IN 'mysql-bin.000011'  limit 6;
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| mysql-bin.000011 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.7-rc-log, Binlog ver: 4                             |
| mysql-bin.000011 | 120 | Previous_gtids |         1 |         151 |                                                                     |
| mysql-bin.000011 | 151 | Gtid           |         1 |         199 | SET @@SESSION.GTID_NEXT= '2F704FE6-1F36-11E2-890D-0203598073C8:1'   |
| mysql-bin.000011 | 199 | Query          |         1 |         278 | BEGIN                                                               |
| mysql-bin.000011 | 278 | Query          |         1 |         407 | use `test`; insert into T1(T,DT) values('15:51','2012/10/29 15:51') |
| mysql-bin.000011 | 407 | Xid            |         1 |         438 | COMMIT /* xid=7 */                                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql>

mysql56-resplication

その他セキュリティ強化

■パスワードの複雑性チェック
# dictionary file for validate_password plugin
loose-validate_password_dictionary_file = /usr/local/mysql/share/dictionary.txt

[root@192-168-11-36 data]# cat /usr/local/mysql/share/dictionary.txt
0000
1111
123456
12345
password
password1
123456789
12345678
1234567890
abc123
computer
tigger
1234
qwerty
money
carmen
mickey
secret
summer
internet
a1b2c3
[省略]

上記の段階ではパスワード強化が有効になって無いので以下の対応を行う。

mysql> SHOW VARIABLES LIKE 'validate_password%';
Empty set (0.00 sec)

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy_number      | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)
mysql>

validate_password

/etc/my.cnfの編集
[root@192-168-11-36 data]# vi /etc/my.cnf
# loose-validate_password_dictionary_file = /usr/local/mysql/share/dictionary.txt
validate_password_dictionary_file=/usr/local/mysql/share/dictionary.txt

MYSQLの再起動して有効化

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+---------------------------------------+
| Variable_name                        | Value                                 |
+--------------------------------------+---------------------------------------+
| validate_password_dictionary_file    | /usr/local/mysql/share/dictionary.txt |
| validate_password_length             | 8                                     |
| validate_password_mixed_case_count   | 1                                     |
| validate_password_number_count       | 1                                     |
| validate_password_policy_number      | MEDIUM                                |
| validate_password_special_char_count | 1                                     |
+--------------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON test.* TO varidate@localhost IDENTIFIED BY '1234567890';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> GRANT ALL PRIVILEGES ON test.* TO varidate@localhost IDENTIFIED BY 'computer';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> GRANT ALL PRIVILEGES ON test.* TO varidate@localhost IDENTIFIED BY 'computer-2012';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> GRANT ALL PRIVILEGES ON test.* TO varidate@localhost IDENTIFIED BY 'Computer-2012';
Query OK, 0 rows affected (0.00 sec)

mysql>

validate_password_dic

参考
What’s New in MySQL 5.6
Writing Password-Validation Plugins
The Password Validation Plugin

メモ:

— Incase of uninstall Plugin —
mysql> UNINSTALL PLUGIN validate_password;


奥野さんがMYSQL5.6について紹介されているページがあります。
ザックリと読んだところ、Oracleに近くなる部分が多々見受けられて興味深い内容でした。
是非、色々と試してみたいと思います。

開発スピードアクセル全開ぶっちぎり!日本よ、これがMySQL 5.6だッ!!


MySQL 5.6.7-RC in tpcc-mysql benchmark


Review of MySQL 5.6 Defaults Changes