![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
ダウンロード用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>
その他: 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>
その他セキュリティ強化
■パスワードの複雑性チェック
# 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>
/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>
参考
What’s New in MySQL 5.6
Writing Password-Validation Plugins
The Password Validation Plugin
メモ：
— Incase of uninstall Plugin —
mysql> UNINSTALL PLUGIN validate_password;