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


db tech showcase 2012
に参加してきました。DBエンジニアとしては、非常に有意義で楽しいセミナーでした。

DB Tech Showcase

前職ではDBのコンサルやDB Teamのマネージャーしていましたが、
現職ではマネージメントに専念していてここ2~3年程きちんと触れていませんでしたが、
久々にDB技術の変化と触れ合ってきました。
DBのパフォーマンスは、CPU、メモリー、ディスク、ネットワークがメインですが、
最近はCPU、64bit化によるメモリー効率改善、SSDなどの影響でI/Oパフォーマンス向上して、
ネットワークに影響が出ているようで、InfinibandがMS SQL,MYSQL,Oracleでも盛り上がっている感じでした。

DB Tech Showcaseメモ

SQL Server

○AlwaysON
━Availability Group
データベース単位
  共有ディスク無し
  複数DBフェイルオーバー
  複数のセカンダリーサーバー(Max:4台―但しACKが遅いのでレスポンス注意)
CheckPoint単位で同期(1分デフォルト) Primary & Secondary同時
   ①ログ→②メモリー→③Checkpoint→④データファイル
   MSベンチマークでは代替63M/秒
━FailOver Cluster
 インスタンス単位
  共有ディスク
  Windows2008R2+SQL 2012 複数サイト(サブネットを跨いだクラスタリング2012から可能)
Windows Server FailOver Cluster

━FailOver Cluster+Availability Groupの構成もあり。
 ━Availability Group  (同期レプリカ、自動フェールオーバー、リーダブルセカンダリー)
━Availability Group DR (複数レプリカー同期(SYNC)は2台まで。
  非同期(A-SYNC)レプリカ (A-SYNCなのでコミットを待たない)
━マルチサブネット対応
 DBCC TRACEON(3499, -1) Availability Groupの書き込みパフォーマンス調整。

 ━パフォーマンス確認(SQL Server: SQL Statistics オブジェクト)
http://msdn.microsoft.com/ja-jp/library/ms190911.aspx
http://msdn.microsoft.com/ja-jp/library/ms190732%28v=sql.90%29.aspx
http://www.confio.com/db-resources/sql-wait-types/writelog/

Batch Requests/sec
Log write waits
Logical Disk Read Bytes/sec (バッファーメモリー、ディスクI/O)

注意: 同期レプリカの場合、2台のDBログに書き込んでからACKをサーバーに返す為パフォーマンス要確認。

○InfiniBand and SSD
 ━Windows2012 SQL2012からSMB3.0に対応
  SMB3.0対応
  SMB Direct (RDMAを持つNetwork Adapterをサポート)→Infinibandディフォルト対応。
  今まで ①L1:NIC→L2→L3:CPU→L7 これから L1->L7へダイレクト(高速)
SMB Multi Channel対応(Auto Failover,Auto Detect)
http://www.atmarkit.co.jp/fnetwork/tokusyuu/61ib02/01.html
http://msdn.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx
http://www.mellanox.co.jp/
http://www.atmarkit.co.jp/fnetwork/tokusyuu/61ib02/01.html

デモ検証実測値)
Windows2012 6.5GB/Sec
SQL2012 4.2GB/Sec
IB Switch 3.8GB/Sec
SSD 2.5GB/Sec
  注:PCI Busの制限は要確認。(PCI-E: 4GB/Sec)
○SQL ServerとIndexの進化
 Column Store Index
特定行のカラムだけを取る。→ DistinctやGroup byが高速化
テーブルに対して1つだけ付与する事が出来る。(全ての列を含む1つインデックスを作成する事も可能)
 全てのデータをintに変換してHASHにして圧縮をかける(blobで保存)
 対応しているデータ型は特定されているので要確認。(int, real,datetime,money等) varcharは不可
CREATE COLUMNSTORE INDEX (Transact-SQL)
 http://msdn.microsoft.com/en-us/library/gg492153.aspx

 検証)
  Column Store Indexを利用しない場合
  41秒(Clustered Indexで処理)
(MAXDOP =0)
Column Store Indexを利用した場合
37秒 (MAXDOP =1)
Column Store Indexを利用した場合
7秒 (MAXDOP =0)
 注:ReadOnlyなので必要な場合はバッチで処理する。(tableはReadになる)
   パーティショニングに対応しているので、パーティション毎に作成して直近のデータのみバッチ作成等

○SQL Server Tuning Point
━SQL2012 Lock変更
SQL2012までは…….
(A) select * from T SCH-S
(B) alter table T SCH-M
(C) select * from T with(NOLOCK) SCH-S
(A) -> (C) -> (B) の順になりAとCの処理をBが待ち続ける。(DDL Starvation)
SQL2012以降は ……
FIFOアルゴリズム採用してSCH-SはSCH-Mをスキップしない。
  但しTraceFlag 617を設定する事でNOLOCKの処理を優先する事が出来る。(再起動不要)
━統計情報変更
自動更新の為の閾値
  テーブル基数(データ量)<=500件 500回のデータ変更で統計を更新   テーブル基数(データ量)> 500件 500回のデータ変更+20%の変更で統計更新
  (例) 10億件のデータがある場合
     10億X0.2+500= 200,000,500件の変更発生で統計情報更新
  Changes to automatic update statistics in SQL Server ? traceflag 2371 -> SQRT(データ件数 * 1000)
  http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
  http://sqlserverpedia.com/blog/sql-server-bloggers/new-sql-2008-r2-sp1-trace-flag-adjusts-autostats-threshold/
  (例) 1,000,000,000件データがある場合は、SQRT(1,000,000,000X1000) = 1,000,000の変更で統計更新
  注:SQL2012, SQL2008R2 SP1から対応
  sys.dm_db_stats_properties を利用してサンプル数、更新日時、更新回数などを確認可能。(次回の統計更新タイミングが予測可能)
  http://msdn.microsoft.com/en-us/library/jj553546%28v=sql.105%29.aspx
DBCC SHOW_STATISTICSでも確認出来たが利便性向上の為に動的VIEWに変更された。
  http://sasuke.main.jp/sqrt.html

━FORCESEEK
  オプティマイザーをオーバーライト
  データへのアクセス パスとしてインデックスのシーク操作のみを使用するようにクエリ オプティマイザを設定可能。
  クラスター化、非クラスター化インデックスに有効。
  http://msdn.microsoft.com/ja-jp/library/bb510478%28v=sql.105%29.aspx
forceseekにインデックスの指定は出来無かったが、2008R2からは指定出来るようになった。
 select * from table with (forceseek) -> select * from table with (forceseek(ix(c1,c2))

━クエリープラン拡張
クエリープラン収集機能拡張
  ~SQL2008 R2 Profiler, SQL Trace, SET STATISTICS PROFILE
条件:収集する or 収集しない
  収集負荷が高い、収集出来ない事が多い
SQL2008 R2 ~
  拡張イベントとして設定可能(条件設定可能:実行時間、CPUtime 2秒以上のクエリーなどのみを取る事が出来る)
  絞り込み可能

 ━ページ割り当て確認
  SQL Server 2012では、DBCC INDやDBCC EXTENTINFOといったアンドキュメンテッドのコマンドにとって代わる
  sys.dm_db_database_page_allocationsという名前の新しい動的管理関数が導入されました。
  基本8K
~ SQL2008 R2までは DBCC EXTENTINFO
SQL2012 ~
  sys.dm_db_database_page_allocation
  http://enterprisezine.jp/dbonline/detail/3950?p=2

 ━Cardinality Estimation
  Estimate Framework
クエリー実行に時間がかかる場合
  → カージナリティ見積もり不正、統計不正、インデックス不正
  推定実行プラン(estimate)は、実際の実行プランと違う場合があるので何が実歳に違うか確認する事が出来る。(1回実行後)
統計情報の不正を確認出来るが、FALSE POSITIVEも拾ってしまうので注意
  実行中は負荷が上る、過剰見積もり、並列クエリーのノイズ(スレッド)などに注意しておく。
  Troubleshooting Poor Query Performance: Cardinality Estimation
  http://msdn.microsoft.com/en-us/library/ms181034%28v=sql.105%29.aspx

━高パフォーマンスの負荷で実行されている SQL Server 2005 および SQL Server 2008 のチューニング オプション
トレース フラグ 834: バッファ プールに対する Microsoft Windows large-page allocations の使用 
http://support.microsoft.com/kb/920093/ja
トレース フラグ 2301: 詳細な意思決定サポートの最適化を有効にする
http://support.microsoft.com/kb/920093/ja

━sys.dm_os_wait_stats (Transact-SQL)
  http://msdn.microsoft.com/ja-jp/library/ms179984.aspx
order by wait_time_ms descで思い処理を調査出来る。
○SQL ServerとNUMA

━MAXDOPは物理コア数に合わせる。(AMDだけ注意 16->8 8->4で設定)
 最近のHYPER-THREADは改善されていて、ONにしておくとOLTPでのパフォーマンスが10%程度上がる。
 OLTPとNUMA
 8Socket + Hyper Thread ON 2400ユーザー/秒
 4Socket + Hyper Thread ON 1400ユーザー/秒
ディスク構成例:Data (SSD),Index(SSD),T-Log(Disk), tempDB(DISK)
 NUMA設定で0~3はNetworksにCPUが利用される。
 sys.dm_os_memory_node_access_stats
* Cross NUMA node memory access statistics
* Trace Flag 842 is needed
 Page life Expectancyは30分程度はあった方が望ましい。(無い場合はメモリー不足を確認)
 http://www.atmarkit.co.jp/fdb/rensai/10_drk/03/drk02.html
 How to Identify Microsoft SQL Server Memory Bottlenecks
 http://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/
 Finding Implicit Column Conversions in the Plan Cache
 http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx
 Coreinfo v3.1(mapping between logical processors and the physical processor)
 http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx

その他、Always On
https://skydrive.live.com/?cid=DEC1C75BCA0DCEB2&id=DEC1C75BCA0DCEB2!11496

MYSQL

○MariaDB
http://mariadb.org/
http://enterprisezine.jp/dbonline/detail/4220
 better than MYSQL
Full Compatible with MYSQL
Bug-Free? (MYSQLのバグを見てFixしてからリリース)
 GPLv2
XtraDB = InnoDB
MYSQLと比較するとCheckpointの頻度が少ない。(DiskへのFlashが少ない)
OptimizerはMYSQL5.68より優れている。
 MariaDB5.3 GIS support
MyISAMを利用しているユーザーにはKey Cacheが1~64セグメント設定出来るのでお勧め。
 基本的には1つのメジャーバージョンは5年サポートする。
About XtraDB
https://kb.askmonty.org/en/about-xtradb/

○MYSQL5.6新機能
セミナー資料by 奥野さん
━レプリケーション
 GTIDを保持してどのスレーブが最新か確認し自動昇格可能
 change master to master_host=’~~~’, master_auto_position =1;
 mysqlfailoverツール(workbenchに付属→マスターの自動フェイルオーバー実行)
 http://b.l0g.jp/mysql/mysql565-gtid/
 http://dev.mysql.com/doc/workbench/en/mysqlfailover.html
 http://yoku0825.blogspot.jp/2012/10/mysqlfailovermysql-utilitieslinux.html

 クラッシュセーフスレーブ(ファイル→InnoDBにレプリケーション情報を持つ事が可能)
 mysqlbinlogコマンドによるバイナリーログバックアップ
 スレーブ利用するNIC指定
 マルチスレッドスレーブ

━InnoDB
 インデックス追加・削除がオンライン可能(Clustered Indexは別)
 *.idbファイルのImport/Export可能
 http://dev.mysql.com/doc/refman/5.6/en/flush.html
インデックス統計改善
 memcached Interface追加
 ログMAXサイズが512GBまで拡張可能
 FULLTEXTインデックスサポート
 ページサイズ指定(4KB,8KB,16KB)
バッファープールの内容を保存、再起動時の暖機運転不要
http://nippondanji.blogspot.jp/2012/10/mysql-56.html

━Optimizer
 Semi-Joinの最適化
 Explainの改善(select以外のDMLも確認可能)

 Order by ~ Limit句の改善
 オプティマイザートレース
 MRR (RowIDをバッファーに入れてソート処理)

━パーティショニング
 パーティション間でのJOIN可能
 パーティション作成可能数1024→8192へ。
 ロック改善(テーブルロックの対象がパーティション単位へ)

━その他
 セキュリティ (slaveのスタートにパスワード入力可能=パラメーターへの書き込み不要)
 OpenGIS
パフォーマンス確認、小数点以下へ対応
スケーラビリティ(R/W: MYSQL5.5比 → 151%改善 Read: NYSQL5.5比で234%改善)
Kernel_Mutexによる処理の分散
Flush改善(リソース状況により詳細調整可能)
http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/


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

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

Bugs Fixed

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

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

ファイルをダウンロード

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

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

バックアップ

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

Tarファイルの展開

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

[省略]

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

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

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

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

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

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

MYSQL SYMBOLIC LINK

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

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

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

aws$ /usr/local/mysql/bin/mysql_upgrade -p
Enter password:
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/usr/local/mysql/data/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/usr/local/mysql/data/mysql.sock'
WP01.wp01_commentmeta                              OK
WP01.wp01_comments                                 OK
WP01.wp01_geo_mashup_administrative_names          OK
WP01.wp01_geo_mashup_location_relationships        OK
WP01.wp01_geo_mashup_locations                     OK
WP01.wp01_links                                    OK
WP01.wp01_mappress_maps                            OK
WP01.wp01_mappress_posts                           OK
WP01.wp01_options                                  OK
WP01.wp01_postmeta                                 OK
WP01.wp01_posts                                    OK
WP01.wp01_posts_0929                               OK
WP01.wp01_searchmeter                              OK
WP01.wp01_searchmeter_recent                       OK
WP01.wp01_term_relationships                       OK
WP01.wp01_term_taxonomy                            OK
WP01.wp01_terms                                    OK
WP01.wp01_usermeta                                 OK
WP01.wp01_users                                    OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
OK
aws$

mysql upgrade

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

mysql version

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

Installing and Upgrading MySQL


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

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


MySQL 5.6.7-RC in tpcc-mysql benchmark


Review of MySQL 5.6 Defaults Changes