ダウンロード用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;

Comments are closed.

Post Navigation