MySQL5.6でONLINE DDLが強化されて、テーブルの変更も多くのケースではオンラインで処理出来る為、深夜のメンテナンスが必須では無くなりました。
もちろんサーバーの負荷やユーザーの利用状況によっては、やはり深夜の時間帯でのメンテナンスが安心だというケースもまだあるとは思いますが、
オンラインでユーザーへのサービスを停止せずに、メンテナンスが出来るという選択肢は非常に重要だと思います。

ONLINE DDL 5.6での拡張(参考)
http://thinkit.co.jp/story/2013/11/19/4670/page/0/1

5.7ではまた追加で、ONLINEでのメンテナンスが出来る選択肢が増えています。
例えば、以下のようにオンラインでvarcharサイズを変更したりすることが出来ます。
新規サービス拡張の時に、サイズを増やしたいというニーズは自分も今まで何回も経験してきた事なので、
この機能追加もとても良い改良かと思います。

例) VARCHARサイズ拡張 (例) varchar(100) → varchar(255)


ALTER TABLE T_ONLINE_DDL ALGORITHM=INPLACE, CHANGE COLUMN text text VARCHAR(255);


5.6で上記コマンドを実行した場合
ONLINE_56

5.7で上記コマンドを実行した場合
ONLINE57

参考: 14.10.1 Overview of Online DDL


MySQL5.7にてレプリケーションに新たなオプションが加わったので、
ここで軽くご紹介させて頂きます。

今回は、以下の2点のみ検証してみました。
1. マルチスレッドスレーブ(同一スキーマ)
– 全体的なパフォーマンス次第ですが、トランザクションを並列して実行可能。
レプリケーションのパフォーマンスが向上します。
2. 動的フィルタリング
– オンライン処理増えると、メンテンナンス時間の制限が減らせてますね。

検証、MySQL Version


root@localhost [REPLI]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.7-rc-log |
+--------------+

以下は、また時間見てご紹介させて頂きます。

■ SEMI-SYNC(準同期)に関しては、Loss-Lessが選択できるようになりました。
AFTER_SYNCかAFTER_COMMITを選択
SET rpl_semi_sync_master_wait_point= [AFTER_SYNC|AFTER_COMMIT]
MySQL5.7.2で準同期が改良され、データ同期時にマスター障害が発生しても
データ損失が発生しない、 Loss-less 準同期レプリケーションに改良されました。
[AFTER_SYNC = Loss Less]

■ ACK待ちするスレーブの数(Default = 1)の変更
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N [Default = 1]

■ GTIDのローリングアップグレード
MySQL 5.7.6でGTIDのローリング有効化ができるようになったので、システム全体を一度にシャットダウンしなくてもOK
GTIDのローリングアップグレードに関しては、こちらのサイトが参考になります。
http://yoku0825.blogspot.jp/2015/03/mysql-576gtidok.html

1) My.cnfの変更(マスター&スレーブ)
こちらは、従来通りserver_id,log_bin,gtid-mode, enforce-gtid-consistency,
log-slave-updates,master_info_repository,relay_log_info_repository,relay_log_recoveryなどを設定しています。

2) アカウント作成

root@localhost [(none)]> CREATE USER 'repl_user'@'192.168.56.109' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.56.109';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> 

3)バックアップとコピー
今回は、検証用のREPLI DBのみ対象なのでREPLIのみダンプしてコピー&リストアしてます。

[root@misc01 admin]# /usr/local/mysql/bin/mysqldump -uroot -p --databases REPLI --single-transaction --triggers --routines --events > GTID_REPLI.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@misc01 admin]# scp GTID_REPLI.sql admin@192.168.56.109:/home/admin/
The authenticity of host '192.168.56.109 (192.168.56.109)' can't be established.
ECDSA key fingerprint is 34:a2:3f:a8:a5:16:6a:35:1c:d6:9c:5f:30:1e:c8:79.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.109' (ECDSA) to the list of known hosts.
admin@192.168.56.109's password: 
GTID_REPLI.sql                                    100% 2705     2.6KB/s   00:00    
[root@misc01 admin]#

4)スレーブにてデータリストア

5)スレーブの設定
※スレーブをスタートする前に、
ここでは、REPLICATION FILTER,SLAVE_PARALLEL_TYPE,SLAVE_PARALLEL_WORKERS
などを設定変更してスレーブをスタートしています。

root@localhost [REPLI]> change master to
    -> master_host = '192.168.56.113',
    -> master_port=3306,
    -> master_user='repl_user',
    -> master_password='password',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [REPLI]> CHANGE REPLICATION FILTER REPLICATE_DO_DB=(REPLI);
Query OK, 0 rows affected (0.00 sec)

root@localhost [REPLI]> SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)

root@localhost [REPLI]> SET GLOBAL SLAVE_PARALLEL_WORKERS=5;
Query OK, 0 rows affected (0.01 sec)

root@localhost [REPLI]> start slave;
Query OK, 0 rows affected (0.02 sec)

root@localhost [REPLI]>

replication_filter

フィルターの設定とマルチスレッドスレーブが反映されているか確認

root@localhost [REPLI]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 822
               Relay_Log_File: misc02-relay-bin.000002
                Relay_Log_Pos: 1035
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: REPLI
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 822
              Relay_Log_Space: 1243
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:1-3
            Executed_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
1 row in set (0.01 sec)

root@localhost [REPLI]> show variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| init_slave                   |                       |
| log_slave_updates            | ON                    |
| log_slow_slave_statements    | OFF                   |
| pseudo_slave_mode            | OFF                   |
| rpl_stop_slave_timeout       | 31536000              |
| slave_allow_batching         | OFF                   |
| slave_checkpoint_group       | 512                   |
| slave_checkpoint_period      | 300                   |
| slave_compressed_protocol    | OFF                   |
| slave_exec_mode              | STRICT                |
| slave_load_tmpdir            | /tmp                  |
| slave_max_allowed_packet     | 1073741824            |
| slave_net_timeout            | 60                    |
| slave_parallel_type          | LOGICAL_CLOCK         |
| slave_parallel_workers       | 5                     |
| slave_pending_jobs_size_max  | 16777216              |
| slave_preserve_commit_order  | OFF                   |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors            | OFF                   |
| slave_sql_verify_checksum    | ON                    |
| slave_transaction_retries    | 10                    |
| slave_type_conversions       |                       |
| sql_slave_skip_counter       | 0                     |
+------------------------------+-----------------------+
23 rows in set (0.01 sec)

root@localhost [REPLI]> 

※ 動的に設定した、replicate-do-db, slave_parallel_type, slave_parallel_workersは,
Instanceの再起動で消えてしまうので必要に応じてオプションファイルに追加設定する必要があります。

root@localhost [REPLI]> system cat /etc/my.cnf | grep replicate-do-db
replicate-do-db=REPLI
root@localhost [REPLI]> 

マルチスレッドスレーブ
slave_para
フィルター
do_db

■INSERTの確認
マスター

root@localhost [REPLI]> select @@hostname;
+------------+
| @@hostname |
+------------+
| misc01     |
+------------+
1 row in set (0.00 sec)

root@localhost [REPLI]>  select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
| 70010 | 2015-04-27 13:42:41.794 | 2015-04-27 13:42:41.794726 |
+-------+-------------------------+----------------------------+
5 rows in set (0.01 sec)

root@localhost [REPLI]> INSERT INTO T_REPLI01(n_time,s_time) values(now(3),sysdate(6));
Query OK, 1 row affected (0.00 sec)

root@localhost [REPLI]>  select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70015 | 2015-04-27 13:47:42.973 | 2015-04-27 13:47:42.973535 |
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
+-------+-------------------------+----------------------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> 

Slave

root@localhost [REPLI]> select @@hostname;
+------------+
| @@hostname |
+------------+
| misc02     |
+------------+
1 row in set (0.00 sec)

root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
| 70010 | 2015-04-27 13:42:41.794 | 2015-04-27 13:42:41.794726 |
+-------+-------------------------+----------------------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70015 | 2015-04-27 13:47:42.973 | 2015-04-27 13:47:42.973535 |
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
+-------+-------------------------+----------------------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> 

master_Slave

■Processlistでスレッドの確認

root@localhost [REPLI]> show processlist;
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
|  3 | root        | localhost | REPLI | Query   |    0 | starting                                               | show processlist |
|  9 | system user |           | NULL  | Connect | 3775 | Waiting for master to send event                       | NULL             |
| 10 | system user |           | NULL  | Connect |  115 | Slave has read all relay log; waiting for more updates | NULL             |
| 11 | system user |           | NULL  | Connect |  115 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
8 rows in set (0.00 sec)

root@localhost [REPLI]> select thd_id,conn_id,user,db,command,current_statement from sys.processlist where user = 'sql/slave_worker';
+--------+---------+------------------+------+---------+-------------------+
| thd_id | conn_id | user             | db   | command | current_statement |
+--------+---------+------------------+------+---------+-------------------+
|     34 |      12 | sql/slave_worker | NULL | Connect | NULL              |
|     35 |      13 | sql/slave_worker | NULL | Connect | NULL              |
|     36 |      14 | sql/slave_worker | NULL | Connect | NULL              |
|     37 |      15 | sql/slave_worker | NULL | Connect | NULL              |
|     33 |      11 | sql/slave_worker | NULL | Connect | NULL              |
+--------+---------+------------------+------+---------+-------------------+
5 rows in set (0.09 sec)

root@localhost [REPLI]> 

Show_full_Process_List

参考:
13.4.2.2 CHANGE REPLICATION FILTER Syntax

MySQLのセミナーなどで、デモも含めてご紹介させて頂いているので参加して頂くのも良いかもしれません。
セミナーサイト
http://events.oracle.com/search/search?start=1&pageHitCount=10&group=Events&keyword=japan=


先日は、共通テーブルスペース(Generated Tablespace)の紹介をさせて頂いたので、
5.7でより細かく設定出来るようになった
undo tablespaceとInnoDB temporary table tablespaceの設定
を紹介してみます。インスタンスの初期設定のタイミングで設定する必要がある為、
出来るだけインスタンスを構築する前にファイルパスなどの設計も行って頂ければ宜しいかと思います。

※まだ、GAになっていないのでRC(リリース候補)での確認のみなので、実際に本番環境で利用する場合は事前検証お願い致します。
datafile

■ Separate UNDO tablespace
–自動オンラインUNDOログ切り捨て(MySQL 5.7.5~)
–UNDOログファイルサイズの増加を回避する事が可能
※ログを切り捨てている時は、対象のテーブルスペースはOFFLINEになる為、
最低2個はテーブルスペースが必要となります。

■ 一時テーブル専用の表領域を新規追加
–CREATE/DROPのパフォーマンスを改善
–DDLによる変更が短縮され,一部ディスクI/Oも削減

インスタンスのインストールを行う為に、TARは現状最新版のMySQL5.7.7 RCを使いました。
ファイル: mysql-5.7.7-rc-linux-glibc2.5-x86_64.tar.gz

以下、インストールにおける変更点
※ InstallerがCとC++に代わって、Perlが不要になったので今回は、perlとperl-Data-Dumperはインストールせずに、
  以下のlibaioとlibaio-develのみインストールしました。
[root@misc02 mysql]# yum install libaio
[root@misc02 mysql]# yum install libaio-devel

※ INSTANCEの初期設定
MySQL5.6まで: mysql_install_db  
MySQL5.7から: mysqld –initialize か mysqld –initialize-insecure

mysqlアカウント追加とシンボリックリンク作成

[root@misc02 local]# groupadd mysql
[root@misc02 local]# useradd -r -g mysql mysql
[root@misc02 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.7-rc-linux-glibc2.5-x86_64  sbin  share  src
[root@misc02 local]# ln -s mysql-5.7.7-rc-linux-glibc2.5-x86_64/ mysql
[root@misc02 local]# ls -l
合計 4
drwxr-xr-x. 2 root root     6  6月 10  2014 bin
drwxr-xr-x. 2 root root     6  6月 10  2014 etc
drwxr-xr-x. 2 root root     6  6月 10  2014 games
drwxr-xr-x. 2 root root     6  6月 10  2014 include
drwxr-xr-x. 2 root root     6  6月 10  2014 lib
drwxr-xr-x. 2 root root     6  6月 10  2014 lib64
drwxr-xr-x. 2 root root     6  6月 10  2014 libexec
lrwxrwxrwx. 1 root root    37  4月 25 16:29 mysql -> mysql-5.7.7-rc-linux-glibc2.5-x86_64/
drwxr-xr-x. 9 7161 wheel 4096  3月 30 22:10 mysql-5.7.7-rc-linux-glibc2.5-x86_64
drwxr-xr-x. 2 root root     6  6月 10  2014 sbin
drwxr-xr-x. 5 root root    46  4月 25 15:56 share
drwxr-xr-x. 2 root root    56  4月 25 16:27 src
[root@misc02 local]# 
[root@misc02 local]# cd mysql
[root@misc02 mysql]# rm /etc/my.cnf
rm: 通常ファイル `/etc/my.cnf' を削除しますか? y
[root@misc02 mysql]# mkdir /home/mysql/
[root@misc02 mysql]# mkdir /home/mysql/data
[root@misc02 mysql]# chown mysql:mysql /home/mysql
[root@misc02 mysql]# chown -R mysql:mysql /home/mysql

my.cnfは、innodb_data_home_dir, innodb_temp_data_file_path, innodb_undo_tablespacesだけ変更しています。
※ちなみに、PAGESIZEを32K, 64Kにしたい場合はこの段階で行った方が良いかもしれません。

[root@misc02 mysql]# vi /etc/my.cnf
[root@misc02 mysql]# cat /etc/my.cnf | grep innodb
innodb_buffer_pool_size        = 128M                                # Go up to 80% of your available RAM
innodb_buffer_pool_chunk_size  =  64M                                # Used when change size innodb_buffer Online 
# innodb_buffer_pool_instances = <n>                                 # Bigger if huge InnoDB Buffer Pool or high concurrency
innodb_file_per_table          = 1                                   # Is the recommended way nowadays
innodb_data_home_dir           = /home/mysql/data                    # directory path for all InnoDB data files in the system tablespace. 
innodb_temp_data_file_path     = ibtmp1:16M:autoextend               # size for InnoDB temporary table tablespace data files. 
innodb_undo_tablespaces        = 3                                   # When an undo tablespace is truncated, it is temporarily taken offline.
innodb_flush_method            = O_DIRECT                            # O_DIRECT is sometimes better for direct attached storage
# innodb_write_io_threads        = 8                                 # If you have a strong I/O system or SSD
# innodb_read_io_threads         = 8                                 # If you have a strong I/O system or SSD
# innodb_io_capacity             = 1000                              # If you have a strong I/O system or SSD
innodb_flush_log_at_trx_commit = 1                                   # 1 for durability, 0 or 2 for performance
innodb_log_buffer_size         = 8M                                  # Bigger if innodb_flush_log_at_trx_commit= 0
innodb_log_file_size           = 256M                                # Bigger means more write throughput but longer recovery time
[root@misc02 mysql]# 

my.cnfも設定が終わったので、インスタンを初期化します。
コマンドで指定しても問題ありません。

パスワードの変更方法は以下のコマンドを推奨しています。
MySQL 5.7.6 and later:
 ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;
MySQL 5.7.5 and earlier:
 SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’);

[root@misc02 mysql]# bin/mysqld --initialize --user=mysql
2015-04-25T08:19:31.323405Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2015-04-25T08:19:31.323953Z 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2015-04-25T08:19:31.323967Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-04-25T08:19:31.324019Z 0 [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap.
2015-04-25T08:19:31.324052Z 0 [Note] bin/mysqld (mysqld 5.7.7-rc-log) starting as process 2556 ...
2015-04-25T08:19:31.331134Z 0 [Note] Creating the data directory /usr/local/mysql-5.7.7-rc-linux-glibc2.5-x86_64/data/
[root@misc02 mysql]# chown -R root .
[root@misc02 mysql]# chown -R mysql data
[root@misc02 mysql]# bin/mysqld_safe --user=mysql &
[1] 2582
[root@misc02 mysql]# 150425 17:20:23 mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
150425 17:20:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@misc02 mysql]# 
[root@misc02 mysql]# cat ./data/error.log | grep password
2015-04-25T08:35:49.799907Z 1 [Warning] A temporary password is generated for root@localhost: lgwqmpDnK5/q

[root@misc02 mysql]# ./bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.7-rc-log

Copyright (c) 2000, 2015, 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.

root@localhost [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

root@localhost [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.01 sec)

root@localhost [(none)]> 

ファイルが意図した場所に出来ている事を確認
 UNDO TABLESPACE: : undo001 ~ undo003
 InnoDB TABLESPACEとSYSTEMとInnoDB Temp関連:/home/mysql/data/

[root@misc02 mysql]# ls -l data/
合計 555168
-rw-r-----. 1 mysql mysql        56  4月 25 17:38 auto.cnf
-rw-r-----. 1 mysql mysql      9652  4月 25 17:38 error.log
-rw-r-----. 1 mysql mysql 268435456  4月 25 17:38 ib_logfile0
-rw-r-----. 1 mysql mysql 268435456  4月 25 17:35 ib_logfile1
-rw-r-----. 1 mysql mysql         6  4月 25 17:38 misc02.pid
drwxr-x---. 2 mysql mysql      4096  4月 25 17:35 mysql
-rw-r-----. 1 mysql mysql     99540  4月 25 17:35 mysql-bin.000001
-rw-r-----. 1 mysql mysql       437  4月 25 17:39 mysql-bin.000002
-rw-r-----. 1 mysql mysql        38  4月 25 17:38 mysql-bin.index
-rw-rw----. 1 root  root          5  4月 25 17:38 mysqld_safe.pid
drwxr-x---. 2 mysql mysql      8192  4月 25 17:35 performance_schema
drwxr-x---. 2 mysql mysql      8192  4月 25 17:35 sys
-rw-r-----. 1 mysql mysql  10485760  4月 25 17:35 undo001
-rw-r-----. 1 mysql mysql  10485760  4月 25 17:38 undo002
-rw-r-----. 1 mysql mysql  10485760  4月 25 17:35 undo003
[root@misc02 mysql]# ls -l /home/mysql/data/
合計 28676
-rw-r-----. 1 mysql mysql      420  4月 25 17:35 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912  4月 25 17:38 ibdata1
-rw-r-----. 1 mysql mysql 16777216  4月 25 17:38 ibtmp1
[root@misc02 mysql]# 

起動ファイルの設定

[root@misc02 mysql]# cp -p ./support-files/mysql.server /etc/init.d/
[root@misc02 mysql]# chmod 755 /etc/init.d/mysql.server 
[root@misc02 mysql]# vi /etc/init.d/mysql.server 
[root@misc02 mysql]# /etc/init.d/mysql.server start
Starting MySQL. SUCCESS! 

UNDO TABLESPACEは、自動でTRUNCATE出来るので後程設定(innodb_undo_log_truncate)をONにしますが、基本的な設定が出来ているか確認してみます。

[root@misc02 mysql]# /usr/local/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.7-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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.

root@localhost [(none)]> show variables like 'innodb_undo_log_truncate';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_log_truncate | OFF   |
+--------------------------+-------+
1 row in set (0.01 sec)

root@localhost [(none)]> show variables like 'innodb_max_undo_log_size';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
+--------------------------+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 3     |
+-------------------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'innodb_undo_directory';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | .     |
+-----------------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'innodb_purge_rseg_truncate_frequency';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
+--------------------------------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> 

undo

InnoDB Temporary Tablespaceも意図した場所に出来ている事を確認

root@localhost [(none)]> show variables like 'innodb_temp%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:16M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  | /home/mysql/data       |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

root@localhost [(none)]> 

tmp

参照: 14.4.8 Truncating Undo Logs That Reside in Undo Tablespaces
innodb_temp_data_file_path


Resizing the InnoDB Buffer Pool Online

MySQL5.7.5DMRからオンラインで、InnoDB Buffer Poolのサイズを変更出来るようになっています。他の商用データベースでも同じ機能があり、運用において数回程度実行した事がありますが、
正直利用する頻度はそれ程多く無いかと思いますが, “Nice to Have”と言った機能になるでしょうか。いざという時に、インスタンスを再起動せずに変更出来れば色々と凌げる場面もあるかと思います。

―――― 留意点 ―――― 抜粋:8.9.1.1 Resizing the InnoDB Buffer Pool Online
When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress,
new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes.
The exception to this rule is that concurrent access to the buffer pool is permitted while the buffer pool is defragmented and pages are withdrawn during an operation to
decrease buffer pool size. A drawback of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn.
――――――――――――――――――――――――――

以下の2つの値をベースに適宜設定変更して頂ければと思います。
innodb_buffer_pool_chunk_size
innodb_buffer_pool_size

設定変更(其の1)
innodb_buffer_pool_sizeとinnodb_buffer_pool_chunk_sizeが共にDefaultの128Mの状態で、
innodb_buffer_pool_chunk_sizeを指定して小さい値を指定して変更しようとした場合。
エラーにはなりませんが、Bufferの値は変わりません。

root@localhost [performance_schema]> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

root@localhost [performance_schema]> SET GLOBAL innodb_buffer_pool_size=67108864;
Query OK, 0 rows affected (0.00 sec)

root@localhost [performance_schema]> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.01 sec)

chunk_small

設定変更(其の2)
既存のinnodb_buffer_pool_sizeサイズ(128M)にinnodb_buffer_pool_chunk_size(128M)を
オンラインで追加した場合は、問題無くInnoDB Buffer Poolが追加され256Mに変更されました。
また、その値からinnodb_buffer_pool_chunk_sizeの値である128Mを再度減らしてみると、
問題無く変更される事も確認出来ます。
innodb_buffer_pool_chunk_sizeは、Defaultで128MですがOptionFileで設定変更を変えるか、
起動時にコマンドで指定してあげる事によってより細かい設定に変更する事も可能です。


root@localhost [performance_schema]> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.01 sec)

root@localhost [performance_schema]> SET GLOBAL innodb_buffer_pool_size=268435456;
Query OK, 0 rows affected (0.00 sec)

root@localhost [performance_schema]> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 268435456      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

root@localhost [performance_schema]> SET GLOBAL innodb_buffer_pool_size=134217728;
Query OK, 0 rows affected (0.01 sec)

root@localhost [performance_schema]> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

root@localhost [performance_schema]> 

chunk_big


MySQL5.6ではinnodb_file_per_tableがDefaultでテーブル毎にテーブルスペース(ファイル)が作成されますが、MySQL5.7.6 DMRからは、CREATE TABLESPACEステートメントによって、
複数テーブルで共有出来るテーブルスペースが作成出来るようになりました。また、Defaultデータディレクトリーとは別のパスにテーブルスペースを作成出来るので、
負荷が高いテーブルなどをSSDなどに配置するなど柔軟に対応することが可能になります。Oracle(テーブルスペース)やMS SQL(ファイルグループ)に関しては、
以前から同様に指定出来ますが、オープンソースデータベースのMySQLは5.7になり、更にそれらの商用データベースと同様の機能も利用出来る汎用性を備えたデータベースになってきました。
MySQL5.7はまだ5.7.7RCですが、更に新たな機能が追加されているので適宜可能な範囲で、こちらにてご紹介したいとお思います。

参照: 13.1.15 CREATE TABLESPACE Syntax
CREATE TABLESPACE is supported with InnoDB as of MySQL 5.7.6.
A general tablespace is a shared tablespace, similar to the system tablespace.
It can hold multiple tables, and supports all table row formats.
General tablespaces can also be created in a location relative to or independent of the MySQL data directory.

Tablespaceの作成


root@localhost [USER01]> CREATE TABLESPACE U_TABLESPACE01 ADD DATAFILE '/home/mysql/user_tablespace01.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

root@localhost [USER01]> CREATE TABLESPACE U_TABLESPACE02_8K ADD DATAFILE '/home/mysql/user_tablespace02_8k.ibd' FILE_BLOCK_S8192 Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

テーブルスペースを指定してテーブルを作成

root@localhost [USER01]> CREATE TABLE `T_USER01` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    ->  PRIMARY KEY (`id`)
    ->  ) TABLESPACE = U_TABLESPACE01 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [USER01]> CREATE TABLE `T_USER02_8K` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) TABLESPACE = U_TABLESPACE02_8K ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOC=8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES where NAME like 'U_%';
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
| SPACE | NAME              | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE |
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
|   165 | U_TABLESPACE01    | 2048 | Any         | Any        |     16384 |             0 | General    |
|   166 | U_TABLESPACE02_8K | 2089 | Barracuda   | Compressed |     16384 |          8192 | General    |
+-------+-------------------+------+-------------+------------+-----------+---------------+------------+
2 rows in set (0.00 sec)

tablespace2

Defaultデータディレクトリーとは別にテーブルスペースが作成されている事を確認


root@localhost [USER01]> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

root@localhost [USER01]> system ls /home/mysql/
user_tablespace01.ibd  user_tablespace02_8k.ibd
root@localhost [USER01]> 

先程、T_USER01を作成してテーブルスペースに追加でテーブルを作成


root@localhost [USER01]> CREATE TABLE `T_USER02` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) TABLESPACE = U_TABLESPACE01 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> show tables;
+------------------+
| Tables_in_USER01 |
+------------------+
| T_USER01         |
| T_USER02         |
| T_USER02_8K      |
+------------------+
3 rows in set (0.00 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
3 rows in set (0.00 sec)

root@localhost [USER01]> 

tablespace_innodb

既存のテーブルを共通テーブルスペースへ移動する場合はAlter Tableコマンドにて対応

root@localhost [USER01]> CREATE TABLE `T_USER03` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `text` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
|      174 | USER01/T_USER03    |    1 |      5 |   167 | Antelope    | Compact    |             0 | Single     |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
4 rows in set (0.00 sec)

root@localhost [USER01]> ALTER TABLE T_USER03 TABLESPACE U_TABLESPACE01;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [USER01]> SELECT * FROM information_schema.INNODB_SYS_TABLES where NAME LIKE 'USER%';
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
|      171 | USER01/T_USER01    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      173 | USER01/T_USER02    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
|      172 | USER01/T_USER02_8K |  169 |      5 |   166 | Barracuda   | Compressed |          8192 | General    |
|      175 | USER01/T_USER03    |  129 |      5 |   165 | Antelope    | Compact    |             0 | General    |
+----------+--------------------+------+--------+-------+-------------+------------+---------------+------------+
4 rows in set (0.00 sec)

root@localhost [USER01]> 

tablespace_alter

メモ:
5.7でサポートされる以下のページサイズも対応していますが、圧縮機能はサポートされていません。
64K 64K (65536) Compression is not supported
32K 32K (32768) Compression is not supported

Tablespace Row Formatのサポートについて
General tablespaces support all table row formats (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)
with the caveat that compressed and uncompressed tables cannot exist in the same general tablespace due to different physical page sizes.


どのようなシステムにも完全が無いように、
Webサイトのセキュリティに気を付けてコーディングしたり、セキュリティ診断しながら運用しても、
どこかしら運用している中でセキュリティ対策漏れが出て来てしまう事が往々にしてあります。
また、それらのセキュリティ問題は、社内システムに関しても同様です。

個人情報を管理しているデータベースや企業秘密情報を管理するデータベースに関しては、
IPSやFW機能などのアプライアンスなどでカバーしてくれる製品もありますが、
非常に高価なものが多いです。本日、検証し共有させて頂くMySQL Enterprise Firewall機能に関しては、
White List方法(2015年4月現在)を用いたデータベース側で不正アクセスをブロックする
MySQLデータベースのセキュリティ対策追加モジュールです。
もし、ご興味を持たれたら、是非 Oracle Software Delivery Cloud から、
30日間無償で検証出来るトライアルを使って検証してみて頂ければと思います。

動作確認MySQL Version

admin@192.168.56.201 [(none)]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

インストールとPluginの状況確認

[admin@GA01 ~]$ mysql -u root -p mysql < /usr/local/mysql/share/linux_install_firewall.sql 
Enter password: 
[admin@GA01 ~]$ cat /usr/local/mysql/share/linux_install_firewall.sql
# Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved.
# Install firewall tables
USE mysql;
CREATE TABLE IF NOT EXISTS mysql.firewall_whitelist( USERHOST VARCHAR(80) NOT NULL, RULE text Nne= MyISAM;
CREATE TABLE IF NOT EXISTS mysql.firewall_users( USERHOST VARCHAR(80) PRIMARY KEY, MODE ENUM ('ING', 'PROTECTING', 'RESET') DEFAULT 'OFF') engine= MyISAM;

INSTALL PLUGIN mysql_firewall SONAME 'firewall.so';
INSTALL PLUGIN mysql_firewall_whitelist SONAME 'firewall.so';
INSTALL PLUGIN mysql_firewall_users SONAME 'firewall.so';

CREATE FUNCTION set_firewall_mode RETURNS STRING SONAME 'firewall.so';
CREATE FUNCTION normalize_statement RETURNS STRING SONAME 'firewall.so';
CREATE AGGREGATE FUNCTION read_firewall_whitelist RETURNS STRING SONAME 'firewall.so';
CREATE AGGREGATE FUNCTION read_firewall_users RETURNS STRING SONAME 'firewall.so';
delimiter //
CREATE PROCEDURE sp_set_firewall_mode (IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
  SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE Fg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
  SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;

IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
  DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
  INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firew WHERE USERHOST=arg_userhost;
END IF;
END //
delimiter ;


[admin@GA01 ~]$ 


root@GA01 [(none)]> select PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_LIBRARY,PLUGIN_LICENSE,LOAD_OPTION
    -> from information_schema.plugins where PLUGIN_NAME like '%FIREWALL%';
+--------------------------+---------------+----------------+----------------+-------------+
| PLUGIN_NAME              | PLUGIN_STATUS | PLUGIN_LIBRARY | PLUGIN_LICENSE | LOAD_OPTION |
+--------------------------+---------------+----------------+----------------+-------------+
| MYSQL_FIREWALL           | ACTIVE        | firewall.so    | PROPRIETARY    | ON          |
| MYSQL_FIREWALL_WHITELIST | ACTIVE        | firewall.so    | PROPRIETARY    | ON          |
| MYSQL_FIREWALL_USERS     | ACTIVE        | firewall.so    | PROPRIETARY    | ON          |
+--------------------------+---------------+----------------+----------------+-------------+
3 rows in set (0.00 sec)

root@GA01 [(none)]> 

Install
install

Plugins
option

検証用ユーザー作成

root@GA01 [(none)]> GRANT ALL PRIVILEGES  ON test.* TO fw_user@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

root@GA01 [(none)]> 


root@GA01 [mysql]> select user,host,password from user;
+---------+-----------+-------------------------------------------+
| user    | host      | password                                  |
+---------+-----------+-------------------------------------------+
| root    | localhost | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| root    | 127.0.0.1 | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| admin   | %         | *B51ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| fw_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+---------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

root@GA01 [mysql]> 

Firewall基本設定オプション
sp
詳細: 5.14.4.2 MySQL Enterprise Firewall Procedures and Functions

自動学習機能を有効にして、ステートメントをWhite Listに記録します


--- First register this account with the Firewall. 
--- You do this by calling the stored proceedure we created earlier:

root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RECORDING');
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0   Imported rules: 0         |
+-----------------------------------------------+
1 row in set (0.01 sec)

+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@GA01 [mysql]> 

※ CALL sp_set_firewall_mode(‘ユーザー名’,’RECORDING’)は、運用開始後にWhite Listに値を追加する時にも使用します。

記録対象ユーザー(検証用ユーザー)でアクセスし、SQLステートメントを実際に登録してみます。

[admin@GA01 ~]$ mysql -u fw_user -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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.

fw_user@localhost [(none)]> use test
Database changed

fw_user@localhost [test]> CREATE TABLE FW_DEMO
    -> (
    -> ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(100)
    -> ) Engine=InnoDB CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)

fw_user@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| FW_DEMO        |
+----------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> 

fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall',@@version));
Query OK, 1 row affected (0.00 sec)

fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall2',@@version));
Query OK, 1 row affected (0.00 sec)

fw_user@localhost [test]> insert into FW_DEMO(title) values(concat('test firewall3',@@version));
Query OK, 1 row affected (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 1;
+----+--------------------------------------------------------+
| ID | title                                                  |
+----+--------------------------------------------------------+
|  1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 2;
+----+---------------------------------------------------------+
| ID | title                                                   |
+----+---------------------------------------------------------+
|  2 | test firewall25.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> 

学習が終了したので、White Listを有効にして、リストにあるステートメントのみを許可します。

root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','PROTECTING');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 10 rows affected (0.00 sec)

root@GA01 [mysql]> 

fw_on

登録された、White Listの確認。

root@GA01 [mysql]> SELECT userhost, substr(rule,1,80) FROM mysql.firewall_whitelist WHERE userhost= 'fw_user@localhost';
+-------------------+----------------------------------------------------------------------------------+
| userhost          | substr(rule,1,80)                                                                |
+-------------------+----------------------------------------------------------------------------------+
| fw_user@localhost | SELECT SCHEMA ( )                                                                |
| fw_user@localhost | SHOW TABLES                                                                      |
| fw_user@localhost | INSERT INTO `FW_DEMO` ( `title` ) VALUES ( `concat` ( ? , @@version ) )          |
| fw_user@localhost | DESC `FW_DEMO`                                                                   |
| fw_user@localhost | SELECT * FROM `FW_DEMO` WHERE `id` = ?                                           |
| fw_user@localhost | CREATE TABLE `FW_DEMO` ( `ID` INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY K |
| fw_user@localhost | SHOW SCHEMAS                                                                     |
| fw_user@localhost | SELECT SYSTEM_USER ( )                                                           |
| fw_user@localhost | SELECT @@version_comment LIMIT ?                                                 |
| fw_user@localhost | SELECT * FROM SYSTEM_USER                                                        |
+-------------------+----------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

root@GA01 [mysql]> SELECT * FROM information_schema.mysql_firewall_users;
+-------------------+------------+
| USERHOST          | MODE       |
+-------------------+------------+
| fw_user@localhost | PROTECTING |
+-------------------+------------+
1 row in set (0.00 sec)

root@GA01 [mysql]> 

rule_status

実際に対象ユーザー(fw_user@localhost)でアクセスしてみて、データベースが防御されているか確認。


fw_user@localhost [test]> select * from FW_DEMO where id = 1;
+----+--------------------------------------------------------+
| ID | title                                                  |
+----+--------------------------------------------------------+
|  1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 2;
+----+---------------------------------------------------------+
| ID | title                                                   |
+----+---------------------------------------------------------+
|  2 | test firewall25.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 3;
+----+---------------------------------------------------------+
| ID | title                                                   |
+----+---------------------------------------------------------+
|  3 | test firewall35.6.24-enterprise-commercial-advanced-log |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> 


root@GA01 [mysql]> SHOW STATUS LIKE 'Firewall%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Firewall_access_denied  | 10    |
| Firewall_access_granted | 6     |
| Firewall_cached_entries | 10    |
+-------------------------+-------+
3 rows in set (0.00 sec)

root@GA01 [mysql]> 

※ 上記を確認頂くと、テーブル全体のデータを持っていこうとする処理はブロックされています。

protected

SQLのエラーログにも記録されている事が確認出来ます。


[root@GA01 data]# tail -n 2 error.log 
2015-04-13 22:40:21 3321 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fw_user@localhost. Reason: No match in whitelist. Statement: SELECT * FROM `FW_DEMO` '
2015-04-13 22:41:25 3321 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fw_user@localhost. Reason: No match in whitelist. Statement: SELECT * FROM `FW_DEMO` '
[root@GA01 data]# 

White Listの設定をOFFにしたい場合は、以下のコマンドでリセットする事が可能です。

root@GA01 [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RESET');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@GA01 [mysql]> SELECT * FROM information_schema.mysql_firewall_users;
+-------------------+------+
| USERHOST          | MODE |
+-------------------+------+
| fw_user@localhost | OFF  |
+-------------------+------+
1 row in set (0.00 sec)

root@GA01 [mysql]> 

リセット結果は、全部のデータをSELECTしてもブロックされて無い事で確認出来ます。
after_reset

補足:

 CALL sp_set_firewall_mode('fw_user@localhost','OFF'); 

でもWhite ListをOFFにしてSQLステートメントをブロックされないようにする事が可能です。

関連テーブルとProcedure
基本的には、以下のテーブルを利用してモジュールをコントロールしています。
今後、更にMySQL5.7との連携や機能拡張が行われて行く事になるかと思います。


root@GA01 [information_schema]> select TABLE_SCHEMA,TABLE_NAME from information_schema.tables
    -> where TABLE_NAME like '%fire%';
+--------------------+--------------------------+
| TABLE_SCHEMA       | TABLE_NAME               |
+--------------------+--------------------------+
| information_schema | MYSQL_FIREWALL_WHITELIST |
| information_schema | MYSQL_FIREWALL_USERS     |
| mysql              | firewall_users           |
| mysql              | firewall_whitelist       |
+--------------------+--------------------------+
4 rows in set (0.01 sec)

root@GA01 [information_schema]> 



root@GA01 [mysql]> show create procedure sp_set_firewall_mode\G
*************************** 1. row ***************************
           Procedure: sp_set_firewall_mode
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_set_firewall_mode`(IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
  SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE FW.userhost=arg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
  SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;

IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
  DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
  INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firewall_whitelist WHERE USERHOST=arg_userhost;
END IF;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

root@GA01 [mysql]> 


Case Sensitve
大切なデータを守る為には、コーディングルールを決める必要がありますね。

fw_user@localhost [test]> select * from FW_DEMO where ID = 1;
+----+--------------------------------------------------------+
| ID | title                                                  |
+----+--------------------------------------------------------+
|  1 | test firewall5.6.24-enterprise-commercial-advanced-log |
+----+--------------------------------------------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> select * from FW_DEMO where id = 1;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> 

case_sensitive

OverHead (抜粋)
Firewall takes those digests and compares them against an in-memory hash.
Matching a query against a whitelist of course adds a little extra processing
and our preliminary testing has shown under concurrent stress level loads
only a 2-3% performance impact added by running the firewall.

参照:
New MySQL Enterprise Firewall – Prevent SQL Injection Attacks

5.14.3 Using MySQL Enterprise Firewall

5.14.4.2 MySQL Enterprise Firewall Procedures and Functions
5.14.4.3 MySQL Enterprise Firewall System Variables


5.7.6 DMRではutf8mb4でのFTインデックスの作成が出来ませんでしたが、
5.7.7 RCで以下のバグ修正が行われたので、基本的なmecab設定をMySQL5.7.7RCにて確認。
今回のリリースで、5.7はRC(リリースキャンディデート)になったので、次のバージョンでGAになり色々な用途で使えるようになりそうです。

Changes in MySQL 5.7.7 (2015-04-08, Release Candidate)
InnoDB: The InnoDB MeCab full-text parser plugin now supports the eucjpms,
cp932, and utf8mb4 character sets. (Bug #20534096)

直接関係ないですが、5.7.7からはInnoDBのDefaultファイルフォーマットがBarracudaに変更されています。
Tableデータの圧縮を使う方には、手間が省けて良い変更かと思います。
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html
The innodb_file_format default value was changed to Barracuda.
The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.
bara

root@localhost [mecab]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.7-rc-log |
+--------------+
1 row in set (0.00 sec)

root@localhost [mecab]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_LICENSE from information_schema.plugins where PLUGIN_NAME IN ('ngram','mecab');
+-------------+----------------+----------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_LICENSE |
+-------------+----------------+----------------+
| ngram       | 0.1            | GPL            |
| mecab       | 0.1            | GPL            |
+-------------+----------------+----------------+
2 rows in set (0.00 sec)

root@localhost [mecab]> 

root@localhost [mecab]> CREATE TABLE M_DEMO_MB4
    -> (
    -> FTS_M_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(100),
    -> FULLTEXT INDEX mecab_idx(title) WITH PARSER mecab
    -> ) Engine=InnoDB CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.03 sec)

root@localhost [mecab]> 

table

テストデータを入れて、全文検索の基本動作が問題無い事を確認。

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('東京都は日本の首都です'),('京都と大阪は日本の府です');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('mysql');
Query OK, 1 row affected (0.01 sec)

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('MYSQL');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('MySQL');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO_MB4 (title) VALUES ('まいえすきゅーえる');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> 
root@localhost [mecab]> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
Empty set (0.01 sec)

root@localhost [mecab]> SET GLOBAL innodb_ft_aux_table="mecab/M_DEMO_MB4";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mecab]> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
+-----------------------------+--------------+-------------+-----------+--------+----------+
| WORD                        | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------------------------+--------------+-------------+-----------+--------+----------+
| 東京                        |            2 |           2 |         1 |      2 |        0 |
| 日本                        |            2 |           3 |         2 |      2 |       12 |
| 首都                        |            2 |           2 |         1 |      2 |       21 |
| です                        |            2 |           3 |         2 |      2 |       27 |
| 京都                        |            3 |           3 |         1 |      3 |        0 |
| 大阪                        |            3 |           3 |         1 |      3 |        9 |
| 日本                        |            2 |           3 |         2 |      3 |       18 |
| です                        |            2 |           3 |         2 |      3 |       30 |
| mysql                       |            4 |           6 |         3 |      4 |        0 |
| mysql                       |            4 |           6 |         3 |      5 |        0 |
| mysql                       |            4 |           6 |         3 |      6 |        0 |
| マイエスキューエル          |            7 |           7 |         1 |      7 |        0 |
| マイエスキューエル                   |            8 |           8 |         1 |      8 |        0 |
| いえ                        |            9 |           9 |         1 |      9 |        3 |
| すき                        |            9 |           9 |         1 |      9 |        9 |
| える                        |            9 |           9 |         1 |      9 |       21 |
+-----------------------------+--------------+-------------+-----------+--------+----------+
16 rows in set (0.00 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN NATURAL LANGUAGE MODE) AS score FROM M_DEMO_MB4;
+----------+--------------------------------------+--------------------+
| FTS_M_ID | title                                | score              |
+----------+--------------------------------------+--------------------+
|        1 | 東京都は日本の首都です               |  1.178047776222229 |
|        2 | 京都と大阪は日本の府です             | 0.3624762296676636 |
|        3 | mysql                                |                  0 |
|        4 | MYSQL                                |                  0 |
|        5 | MySQL                                |                  0 |
|        6 | マイエスキューエル                   |                  0 |
|        7 | マイエスキューエル                            |                  0 |
|        8 | まいえすきゅーえる                   |                  0 |
+----------+--------------------------------------+--------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN BOOLEAN MODE) AS score FROM M_DEMO_MB4;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> 


mecabプラグイン
MySQL5.7からmecabプラグインが利用出来るようになるので、基本的な動作確認をしました。
現状では、eucjpms (ujis), cp932 (sjis), and utf8 (utf8mb4)のみ対応している状態です。

mecab-pug

Mecabについて
http://ja.wikipedia.org/wiki/MeCab

参考: 
InnoDB Full-Text: MeCab Parser
http://planet.mysql.com/entry/?id=5989321

初期設定
Plugin Directoryにモジュールや設定ファイルがあります。

[root@misc01 mecab]# ls -l /usr/local/mysql/lib/
合計 800168
-rw-r--r--. 1 root mysql  21934486  2月 27 00:04 libmysqlclient.a
lrwxrwxrwx. 1 root mysql        20  2月 27 00:12 libmysqlclient.so -> libmysqlclient.so.20
lrwxrwxrwx. 1 root mysql        24  2月 27 00:12 libmysqlclient.so.20 -> libmysqlclient.so.20.0.0
-rwxr-xr-x. 1 root mysql  10071218  2月 27 00:04 libmysqlclient.so.20.0.0
lrwxrwxrwx. 1 root mysql        16  2月 27 00:12 libmysqlclient_r.a -> libmysqlclient.a
lrwxrwxrwx. 1 root mysql        17  2月 27 00:12 libmysqlclient_r.so -> libmysqlclient.so
lrwxrwxrwx. 1 root mysql        20  2月 27 00:12 libmysqlclient_r.so.20 -> libmysqlclient.so.20
lrwxrwxrwx. 1 root mysql        24  2月 27 00:12 libmysqlclient_r.so.20.0.0 -> libmysqlclient.so.20.0.0
-rw-r--r--. 1 root mysql 347907574  2月 27 00:03 libmysqld-debug.a
-rw-r--r--. 1 root mysql 438661070  2月 27 00:12 libmysqld.a
-rw-r--r--. 1 root mysql     27154  2月 27 00:03 libmysqlservices.a
-rw-r--r--. 1 root mysql    754332  2月 26 23:58 libtcmalloc_minimal.so
drwxr-xr-x. 4 root mysql        26  3月 28 08:30 mecab
drwxr-xr-x. 3 root mysql      4096  3月 28 08:30 plugin
[root@misc01 mecab]# ls -l
合計 0
drwxr-xr-x. 5 root mysql 63  3月 28 08:30 dic
drwxr-xr-x. 2 root mysql 20  3月 29 07:29 etc
[root@misc01 mecab]# pwd
/usr/local/mysql/lib/mecab
[root@misc01 mecab]# ls -l dic/
合計 12
drwxr-xr-x. 2 root mysql 4096  3月 28 08:30 ipadic_euc-jp
drwxr-xr-x. 2 root mysql 4096  3月 28 08:30 ipadic_sjis
drwxr-xr-x. 2 root mysql 4096  3月 28 08:30 ipadic_utf-8
[root@misc01 mecab]# ls -l etc/
合計 4
-rw-r--r--. 1 root mysql 379  3月 29 07:29 mecabrc
[root@misc01 mecab]# 

基本インストールと設定
mecab設定ファイル

[root@misc01 etc]# cat mecabrc 
; Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
;
; Configuration file of MeCab
;
; We have three ipadics in /path/to/mysql/lib/mecab/dic:
; ipadic_euc-jp, ipadic_sjis, and ipadic_utf8.
; you can choose any of them or use your own one.
dicdir =  /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp
[root@misc01 etc]# vi mecabrc 
[root@misc01 etc]# cat mecabrc 
; Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
;
; Configuration file of MeCab
;
; We have three ipadics in /path/to/mysql/lib/mecab/dic:
; ipadic_euc-jp, ipadic_sjis, and ipadic_utf8.
; you can choose any of them or use your own one.
; dicdir =  /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp
dicdir = /usr/local/mysql/lib/mecab/dic/ipadic_utf-8
[root@misc01 etc]# 


[root@misc01 plugin]# /usr/local/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.6-m16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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.

root@localhost [(none)]> show variables like 'plug%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
Query OK, 0 rows affected (0.29 sec)

root@localhost [(none)]> SHOW STATUS LIKE 'mecab_charset';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| mecab_charset | utf8  |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'innodb_ft_min_token_size';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 2     |
+--------------------------+-------+
1 row in set (0.00 sec)


root@localhost [(none)]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_LICENSE from information_schema.plugins
    -> where PLUGIN_NAME = 'meCab';
+-------------+----------------+----------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_LICENSE |
+-------------+----------------+----------------+
| mecab       | 0.1            | GPL            |
+-------------+----------------+----------------+
1 row in set (0.00 sec)

root@localhost [(none)]> 

install

検証用データベース、テーブル、データの作成

root@localhost [(none)]> CREATE DATABASE `mecab` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use mecab
Database changed
root@localhost [mecab]> CREATE TABLE M_DEMO
    ->  (
    ->   FTS_M_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(100),
    ->   FULLTEXT INDEX mecab_idx(title) WITH PARSER mecab
    ->   ) Engine=InnoDB CHARACTER SET utf8;
Query OK, 0 rows affected (0.15 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('東京都は日本の首都です'),('京都と大阪は日本の府です');
 
Query OK, 2 rows affected (0.69 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('mysql');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('MYSQL');
Query OK, 1 row affected (0.01 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('MySQL');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

root@localhost [mecab]> INSERT INTO M_DEMO (title) VALUES ('まいえすきゅーえる');
Query OK, 1 row affected (0.01 sec)

root@localhost [mecab]> 


root@localhost [mecab]> select * from M_DEMO;
+----------+--------------------------------------+
| FTS_M_ID | title                                |
+----------+--------------------------------------+
|        1 | 東京都は日本の首都です               |
|        2 | 京都と大阪は日本の府です             |
|        3 | mysql                                |
|        4 | MYSQL                                |
|        5 | MySQL                                |
|        6 | マイエスキューエル                   |
|        7 | マイエスキューエル                            |
|        8 | まいえすきゅーえる                   |
+----------+--------------------------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> SET GLOBAL innodb_ft_aux_table="mecab/M_DEMO";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mecab]> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
+-----------------------------+--------------+-------------+-----------+--------+----------+
| WORD                        | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------------------------+--------------+-------------+-----------+--------+----------+
| 東京                        |            2 |           2 |         1 |      2 |        0 |
| 日本                        |            2 |           3 |         2 |      2 |       12 |
| 首都                        |            2 |           2 |         1 |      2 |       21 |
| です                        |            2 |           3 |         2 |      2 |       27 |
| 京都                        |            3 |           3 |         1 |      3 |        0 |
| 大阪                        |            3 |           3 |         1 |      3 |        9 |
| 日本                        |            2 |           3 |         2 |      3 |       18 |
| です                        |            2 |           3 |         2 |      3 |       30 |
| mysql                       |            4 |           6 |         3 |      4 |        0 |
| mysql                       |            4 |           6 |         3 |      5 |        0 |
| mysql                       |            4 |           6 |         3 |      6 |        0 |
| マイエスキューエル          |            7 |           7 |         1 |      7 |        0 |
| マイエスキューエル                   |            8 |           8 |         1 |      8 |        0 |
| いえ                        |            9 |           9 |         1 |      9 |        3 |
| すき                        |            9 |           9 |         1 |      9 |        9 |
| える                        |            9 |           9 |         1 |      9 |       21 |
+-----------------------------+--------------+-------------+-----------+--------+----------+
16 rows in set (0.00 sec)

root@localhost [mecab]> 

mecab

■ IN NATURAL LANGUAGE MODE(Text Searches)
Text searched for is converted to a union of search tokens.
  For example, ‘日本の首都’ is converted to ‘日本 の 首都’.

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都' IN NATURAL LANGUAGE MODE);
+----------+--------------------------------------+
| FTS_M_ID | title                                |
+----------+--------------------------------------+
|        1 | 東京都は日本の首都です               |
|        2 | 京都と大阪は日本の府です             |
+----------+--------------------------------------+
2 rows in set (0.00 sec)

root@localhost [mecab]>  SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN NATURAL LANGUAGE MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+--------------------+
| FTS_M_ID | title                                | score              |
+----------+--------------------------------------+--------------------+
|        1 | 東京都は日本の首都です               |  1.178047776222229 |
|        2 | 京都と大阪は日本の府です             | 0.3624762296676636 |
|        3 | mysql                                |                  0 |
|        4 | MYSQL                                |                  0 |
|        5 | MySQL                                |                  0 |
|        6 | マイエスキューエル                   |                  0 |
|        7 | マイエスキューエル                            |                  0 |
|        8 | まいえすきゅーえる                   |                  0 |
+----------+--------------------------------------+--------------------+
8 rows in set (0.00 sec)

■ IN BOOLEAN MODE(Text Searches)
Text searched for is converted to a phrase search.
  For example, ‘日本の首都’ is converted to ‘”日本 の 首都”‘.

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都' IN BOOLEAN MODE);
+----------+-----------------------------------+
| FTS_M_ID | title                             |
+----------+-----------------------------------+
|        1 | 東京都は日本の首都です            |
+----------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)

mecab_cost

■ IN BOOLEAN MODE(Wildcard Searches)
No tokenization for the text of a wildcard search.
For example, for ‘日本の首都*’ we will search the prefix of ‘日本の首都’

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本*' IN BOOLEAN MODE);
+----------+--------------------------------------+
| FTS_M_ID | title                                |
+----------+--------------------------------------+
|        1 | 東京都は日本の首都です               |
|        2 | 京都と大阪は日本の府です             |
+----------+--------------------------------------+
2 rows in set (0.01 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本*' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+--------------------+
| FTS_M_ID | title                                | score              |
+----------+--------------------------------------+--------------------+
|        1 | 東京都は日本の首都です               | 0.3624762296676636 |
|        2 | 京都と大阪は日本の府です             | 0.3624762296676636 |
|        3 | mysql                                |                  0 |
|        4 | MYSQL                                |                  0 |
|        5 | MySQL                                |                  0 |
|        6 | マイエスキューエル                   |                  0 |
|        7 | マイエスキューエル                            |                  0 |
|        8 | まいえすきゅーえる                   |                  0 |
+----------+--------------------------------------+--------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都' IN BOOLEAN MODE);
+----------+-----------------------------------+
| FTS_M_ID | title                             |
+----------+-----------------------------------+
|        1 | 東京都は日本の首都です            |
+----------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [mecab]> SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)


root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('日本の首都*' IN BOOLEAN MODE);
Empty set (0.00 sec)

root@localhost [mecab]>  SELECT FTS_M_ID,title,MATCH (title) AGAINST('日本の首都*' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------+
| FTS_M_ID | title                                | score |
+----------+--------------------------------------+-------+
|        1 | 東京都は日本の首都です               |     0 |
|        2 | 京都と大阪は日本の府です             |     0 |
|        3 | mysql                                |     0 |
|        4 | MYSQL                                |     0 |
|        5 | MySQL                                |     0 |
|        6 | マイエスキューエル                   |     0 |
|        7 | マイエスキューエル                            |     0 |
|        8 | まいえすきゅーえる                   |     0 |
+----------+--------------------------------------+-------+
8 rows in set (0.00 sec)

root@localhost [mecab]> 

■ IN BOOLEAN MODE(Phrase Searches)
A phrase search is tokenized by mecab. For example,
“日本の首都” is converted to “日本 の 首都”.

root@localhost [mecab]> SELECT * FROM M_DEMO WHERE MATCH(title) AGAINST('"日本の首都"' IN BOOLEAN MODE);
+----------+-----------------------------------+
| FTS_M_ID | title                             |
+----------+-----------------------------------+
|        1 | 東京都は日本の首都です            |
+----------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [mecab]>  SELECT FTS_M_ID,title,MATCH (title) AGAINST('"日本の首都"' IN BOOLEAN MODE) AS score FROM M_DEMO;
+----------+--------------------------------------+-------------------+
| FTS_M_ID | title                                | score             |
+----------+--------------------------------------+-------------------+
|        1 | 東京都は日本の首都です               | 1.178047776222229 |
|        2 | 京都と大阪は日本の府です             |                 0 |
|        3 | mysql                                |                 0 |
|        4 | MYSQL                                |                 0 |
|        5 | MySQL                                |                 0 |
|        6 | マイエスキューエル                   |                 0 |
|        7 | マイエスキューエル                            |                 0 |
|        8 | まいえすきゅーえる                   |                 0 |
+----------+--------------------------------------+-------------------+
8 rows in set (0.00 sec)

root@localhost [mecab]> 

一つ前のPOSTで検証したデータでそのまま、NGRAMにてIN BOOLEAN MODEを使い検索してみました。

Natural Language
自然言語処理
By default or with the IN NATURAL LANGUAGE MODE modifier,
the MATCH() function performs a natural language search for a string against a text collection.
– text searched for is converted to a union of n-gram values. For example, ‘sql’ is converted to ‘sq ql’ (with a default token size of 2 or bigram).

natu

Boolean
真(true)と偽(false)の2種類の値だけを扱う型 (“+”、”-“で条件設定可能)
MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier.
With this modifier, certain characters have special meaning at the beginning or end of words in the search string.
– text searched for is converted to an n-gram phrase search. For example, ‘sql’ is converted to ‘”sq ql”‘:

bool

■ NATURAL LANGUAGE MODEとBOOLEAN MODEでの結果の違い

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
|       11 | ql    |
+----------+-------+
5 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('sql' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> 

compare

■以下,BOOLEANモードの挙動についての確認
– text searched
– wildcard searches
– phrase searches

text searched for is converted to an n-gram phrase search.
For example, ‘sql’ is converted to ‘”sq ql”‘:

12.9.2 Boolean Full-Text Searches
http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

基本設定と検証用データ

root@localhost [ngram]> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost [ngram]> select * from N_DEMO;
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        1 | mysql                       |
|        2 | MYSQL                       |
|        3 | MySQL                       |
|        4 | マイエスキューエル          |
|        5 | マイエスキューエル                   |
|        6 | まいえすきゅーえる          |
|        7 | まい                        |
|        8 | えす                        |
|        9 | sq                          |
|       10 | sl                          |
|       11 | ql                          |
+----------+-----------------------------+
11 rows in set (0.00 sec)

■ example)
‘s*’はsを含むすべてのデータ


root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('s*' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
|       10 | sl    |
+----------+-------+
5 rows in set (0.00 sec)

root@localhost [ngram]> 

‘sq*’ is converted to ‘”sq”‘

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('sq*' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
+----------+-------+
4 rows in set (0.00 sec)

root@localhost [ngram]> 

‘sql*’ is equivalent to ‘”sq ql”‘:

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('sql*' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"sq ql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> 

■ データを追加して日本語で確認
挙動は同じで、対象とするデータを抽出出来る事を確認しました。
データを増やして、もっと試してみる必要あり。

root@localhost [ngram]> select * from N_DEMO;
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        1 | mysql                          |
|        2 | MYSQL                          |
|        3 | MySQL                          |
|        4 | マイエスキューエル             |
|        5 | マイエスキューエル                      |
|        6 | まいえすきゅーえる             |
|        7 | まい                           |
|        8 | えす                           |
|        9 | sq                             |
|       10 | sl                             |
|       11 | ql                             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
13 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('ま*' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|        7 | まい                           |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
4 rows in set (0.01 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('まい*' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|        7 | まい                           |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
4 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('まいえ*' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.01 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"まい いえ"' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.01 sec)

root@localhost [ngram]> 

補足:AGAINST(‘まいえ*’ IN BOOLEAN MODE) = AGAINST(‘”まい いえ”‘ IN BOOLEAN MODE)である事をスコアで確認。

root@localhost [ngram]> SELECT FTS_N_ID,MATCH (title) AGAINST('まいえ*' IN BOOLEAN MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 |                  0 |
|        2 |                  0 |
|        3 |                  0 |
|        4 |                  0 |
|        5 |                  0 |
|        6 | 0.9228526949882507 |
|        7 |                  0 |
|        8 |                  0 |
|        9 |                  0 |
|       10 |                  0 |
|       11 |                  0 |
|       12 |                  0 |
|       13 | 0.9228526949882507 |
+----------+--------------------+
13 rows in set (0.00 sec)

root@localhost [ngram]> SELECT FTS_N_ID,MATCH (title) AGAINST('"まい いえ"' IN BOOLEAN MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 |                  0 |
|        2 |                  0 |
|        3 |                  0 |
|        4 |                  0 |
|        5 |                  0 |
|        6 | 0.9228526949882507 |
|        7 |                  0 |
|        8 |                  0 |
|        9 |                  0 |
|       10 |                  0 |
|       11 |                  0 |
|       12 |                  0 |
|       13 | 0.9228526949882507 |
+----------+--------------------+
13 rows in set (0.01 sec)

root@localhost [ngram]> 

score

その他、追加動作確認

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.01 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"sql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"きゅー"' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.00 sec)

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('"きゅーえ"' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|       12 | まいーえすきゅーえる           |
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.01 sec)

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('きゅー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.00 sec)

root@localhost [ngram]> 

mysql-boolean

MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier.
With this modifier, certain characters have special meaning at the beginning or
end of words in the search string. In the following query, the + and – operators indicate
that a word must be present or absent, respectively, for a match to occur.

In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which
+ stands for AND
– stands for NOT
[no operator] implies OR

参照: http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

root@localhost [ngram]> root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH(title) AGAINST('きゅー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('+きゅー -まいー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       13 | まいえーすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('+きゅー -えー' IN BOOLEAN MODE);
+----------+--------------------------------+
| FTS_N_ID | title                          |
+----------+--------------------------------+
|        6 | まいえすきゅーえる             |
|       12 | まいーえすきゅーえる           |
+----------+--------------------------------+
2 rows in set (0.00 sec)

root@localhost [ngram]> 
root@localhost [ngram]> SET GLOBAL innodb_ft_aux_table="ngram/N_DEMO";
Query OK, 0 rows affected (0.00 sec)

root@localhost [ngram]> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| いえ   |           15 |          15 |         1 |     15 |        3 |
| いー   |           14 |          14 |         1 |     14 |        3 |
| えす   |           14 |          14 |         1 |     14 |        9 |
| える   |           14 |          15 |         2 |     14 |       24 |
| える   |           14 |          15 |         2 |     15 |       24 |
| えー   |           15 |          15 |         1 |     15 |        6 |
| きゅ   |           14 |          15 |         2 |     14 |       15 |
| きゅ   |           14 |          15 |         2 |     15 |       15 |
| すき   |           14 |          15 |         2 |     14 |       12 |
| すき   |           14 |          15 |         2 |     15 |       12 |
| まい   |           14 |          15 |         2 |     14 |        0 |
| まい   |           14 |          15 |         2 |     15 |        0 |
| ゅー   |           14 |          15 |         2 |     14 |       18 |
| ゅー   |           14 |          15 |         2 |     15 |       18 |
| ーえ   |           14 |          15 |         2 |     14 |        6 |
| ーえ   |           14 |          15 |         2 |     14 |       15 |
| ーえ   |           14 |          15 |         2 |     15 |       21 |
| ーす   |           15 |          15 |         1 |     15 |        9 |
+--------+--------------+-------------+-----------+--------+----------+
18 rows in set (0.00 sec)

root@localhost [ngram]> 

filer2

参照: http://mysqlserverteam.com/innodb-full-text-n-gram-parser/


MySQL5.7から実装されるInnoDBの全文検索 in CJKモードの挙動について。
Natural Language Full-Text Searchsについて。

12.9.1 Natural Language Full-Text Searches
http://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html
By default or with the IN NATURAL LANGUAGE MODE modifier,
the MATCH() function performs a natural language search for a string against
a text collection. A collection is a set of one or more columns included in a FULLTEXT index.
The search string is given as the argument to AGAINST().

テストバージョン
version

検証用DB、テーブル、データの作成。
5.7のngramはDefaultのpluginになっているのでそのままインデックスまで作成して確認。

mysql>  CREATE DATABASE `ngram` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.00 sec)


mysql> CREATE TABLE N_DEMO
    -> (
    ->         FTS_N_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->         title VARCHAR(100),
    ->         FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram
    -> ) Engine=InnoDB CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('mysql');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('MYSQL');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('MySQL');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('マイエスキューエル');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO N_DEMO (title) VALUES ('まいえすきゅーえる');
Query OK, 1 row affected (0.00 sec)

mysql> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%ft_min%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| ft_min_word_len          | 4     |
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> SET GLOBAL innodb_ft_aux_table="ngram/N_DEMO";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| my     |            2 |           4 |         3 |      2 |        0 |
| my     |            2 |           4 |         3 |      3 |        0 |
| my     |            2 |           4 |         3 |      4 |        0 |
| ql     |            2 |           4 |         3 |      2 |        3 |
| ql     |            2 |           4 |         3 |      3 |        3 |
| ql     |            2 |           4 |         3 |      4 |        3 |
| sq     |            2 |           4 |         3 |      2 |        2 |
| sq     |            2 |           4 |         3 |      3 |        2 |
| sq     |            2 |           4 |         3 |      4 |        2 |
| ys     |            2 |           4 |         3 |      2 |        1 |
| ys     |            2 |           4 |         3 |      3 |        1 |
| ys     |            2 |           4 |         3 |      4 |        1 |
| いえ   |            7 |           7 |         1 |      7 |        3 |
| えす   |            7 |           7 |         1 |      7 |        6 |
| える   |            7 |           7 |         1 |      7 |       21 |
| きゅ   |            7 |           7 |         1 |      7 |       12 |
| すき   |            7 |           7 |         1 |      7 |        9 |
| まい   |            7 |           7 |         1 |      7 |        0 |
| ゅー   |            7 |           7 |         1 |      7 |       15 |
| イエ   |            5 |           5 |         1 |      5 |        3 |
| エス   |            5 |           5 |         1 |      5 |        6 |
| エル   |            5 |           5 |         1 |      5 |       21 |
| キュ   |            5 |           5 |         1 |      5 |       12 |
| スキ   |            5 |           5 |         1 |      5 |        9 |
| マイ   |            5 |           5 |         1 |      5 |        0 |
| ュー   |            5 |           5 |         1 |      5 |       15 |
| ーえ   |            7 |           7 |         1 |      7 |       18 |
| ーエ   |            5 |           5 |         1 |      5 |       18 |
| ュー     |            6 |           6 |         1 |      6 |       15 |
| ーエ     |            6 |           6 |         1 |      6 |       18 |
| イエ     |            6 |           6 |         1 |      6 |        3 |
| エス     |            6 |           6 |         1 |      6 |        6 |
| エル     |            6 |           6 |         1 |      6 |       21 |
| キュ     |            6 |           6 |         1 |      6 |       12 |
| スキ     |            6 |           6 |         1 |      6 |        9 |
| マイ     |            6 |           6 |         1 |      6 |        0 |
+--------+--------------+-------------+-----------+--------+----------+
36 rows in set (0.00 sec)

mysql> 

実際に全文検索(ngram)の動作を確認してみる。

mysql> select * from N_DEMO;
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        1 | mysql                       |
|        2 | MYSQL                       |
|        3 | MySQL                       |
|        4 | マイエスキューエル          |
|        5 | マイエスキューエル                   |
|        6 | まいえすきゅーえる          |
+----------+-----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('my' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT FTS_N_ID,MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 | 0.1812381148338318 |
|        2 | 0.1812381148338318 |
|        3 | 0.1812381148338318 |
|        4 |                  0 |
|        5 |                  0 |
|        6 |                  0 |
+----------+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('マイ' IN NATURAL LANGUAGE MODE);
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        5 | マイエスキューエル                   |
+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('マイ' IN NATURAL LANGUAGE MODE);
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        4 | マイエスキューエル          |
+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('まい' IN NATURAL LANGUAGE MODE);
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        6 | まいえすきゅーえる          |
+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

NGRAM

ngram_token_sizeがDefaultで2になっている時の挙動
2文字で設定されているので、検索結果も検索対象と同じ2文字を含むデータが検出されいる。
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_ngram_token_size

ngram-token-def

mysql> INSERT INTO N_DEMO (title) VALUES ('まい'), ('えす'), ('sq'), ('sl'), ('ql');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from N_DEMO;
+----------+-----------------------------+
| FTS_N_ID | title                       |
+----------+-----------------------------+
|        1 | mysql                       |
|        2 | MYSQL                       |
|        3 | MySQL                       |
|        4 | マイエスキューエル          |
|        5 | マイエスキューエル                   |
|        6 | まいえすきゅーえる          |
|        7 | まい                        |
|        8 | えす                        |
|        9 | sq                          |
|       10 | sl                          |
|       11 | ql                          |
+----------+-----------------------------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
|        9 | sq    |
|       11 | ql    |
+----------+-------+
5 rows in set (0.00 sec)

mysql> SELECT FTS_N_ID,MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE) AS score FROM N_DEMO;
+----------+---------------------+
| FTS_N_ID | score               |
+----------+---------------------+
|        1 | 0.38602644205093384 |
|        2 | 0.38602644205093384 |
|        3 | 0.38602644205093384 |
|        4 |                   0 |
|        5 |                   0 |
|        6 |                   0 |
|        7 |                   0 |
|        8 |                   0 |
|        9 | 0.19301322102546692 |
|       10 |                   0 |
|       11 | 0.19301322102546692 |
+----------+---------------------+
11 rows in set (0.01 sec)

mysql> 

ngram_token_sizeを2から3へ変更して再度検索した結果
– この値の変更には、SQLインスタンスの再起動が必要です。
– インデックスの再作成も必要になります。
先程と同じ検索をしても、結果が異なる事が確認出来る。

root@localhost [ngram]> system cat /etc/my.cnf | grep ngram_token_size
ngram_token_size=3
root@localhost [ngram]> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 3     |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

root@localhost [ngram]> alter table N_DEMO drop index ngram_idx;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [ngram]> alter table N_DEMO ADD FULLTEXT INDEX ngram_idx (title) WITH PARSER ngram;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [ngram]> SELECT * FROM N_DEMO WHERE MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE);
+----------+-------+
| FTS_N_ID | title |
+----------+-------+
|        1 | mysql |
|        2 | MYSQL |
|        3 | MySQL |
+----------+-------+
3 rows in set (0.00 sec)

root@localhost [ngram]> SELECT FTS_N_ID,MATCH (title) AGAINST ('sql' IN NATURAL LANGUAGE MODE) AS score FROM N_DEMO;
+----------+--------------------+
| FTS_N_ID | score              |
+----------+--------------------+
|        1 | 0.3184022605419159 |
|        2 | 0.3184022605419159 |
|        3 | 0.3184022605419159 |
|        4 |                  0 |
|        5 |                  0 |
|        6 |                  0 |
|        7 |                  0 |
|        8 |                  0 |
|        9 |                  0 |
|       10 |                  0 |
|       11 |                  0 |
+----------+--------------------+
11 rows in set (0.00 sec)

root@localhost [ngram]> 

alter_table