MySQL5.7.6DMRが今月リリースされて、色々な機能追加や改善が施されているので、
DMRのうちに少しずつ検証してRCとGAに備えたいと思います。
とりあえず、本日は初期設定まで。

2015年3月現在
DRM

MySQL 5.7 Release Notes
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/index.html

ダウンロードサイトからDMRをダウンロードしてきてあります。
mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz

[root@misc01 admin]# cd /usr/local/
[root@misc01 local]# ls -l
合計 545048
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
-rw-rw-r--. 1 admin admin 558127440  3月 28 06:49 mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x. 2 root  root          6  6月 10  2014 sbin
drwxr-xr-x. 5 root  root         46  3月 28 07:02 share
drwxr-xr-x. 2 root  root          6  6月 10  2014 src
[root@misc01 local]# 
[root@misc01 local]# tar xzvf mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
[root@misc01 local]# groupadd mysql
[root@misc01 local]# useradd -r -g mysql mysql
[root@misc01 local]# ls -l
合計 545052
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
drwxr-xr-x. 11 root  root       4096  3月 28 08:30 mysql-5.7.6-m16-linux-glibc2.5-x86_64
-rw-rw-r--.  1 admin admin 558127440  3月 28 06:49 mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x.  2 root  root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root  root         46  3月 28 07:02 share
drwxr-xr-x.  2 root  root          6  6月 10  2014 src
[root@misc01 local]# ln -s mysql-5.7.6-m16-linux-glibc2.5-x86_64 mysql
[root@misc01 local]# ls -l
合計 545052
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  3月 28 08:32 mysql -> mysql-5.7.6-m16-linux-glibc2.5-x86_64
drwxr-xr-x. 11 root  root       4096  3月 28 08:30 mysql-5.7.6-m16-linux-glibc2.5-x86_64
-rw-rw-r--.  1 admin admin 558127440  3月 28 06:49 mysql-5.7.6-m16-linux-glibc2.5-x86_64.tar.gz
drwxr-xr-x.  2 root  root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root  root         46  3月 28 07:02 share
drwxr-xr-x.  2 root  root          6  6月 10  2014 src
[root@misc01 local]# 

展開して初期DBを設定します
5.7以降で設定方法が変わっていたのを忘れていました… 詳細は此方をご確認ください。
2.9.1.1 Initializing the Data Directory Using mysqld
http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
4.4.2 mysql_install_db — Initialize MySQL Data Directory
http://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html

初期root用のpasswordもこちらでランダムに作成されています。
初回ログイン時に変更してしまいましょう。

[root@misc01 local]# cd mysql
[root@misc01 mysql]# chown -R mysql .
[root@misc01 mysql]# chgrp -R mysql .
[root@misc01 mysql]# scripts/mysql_install_db --user=mysql
bash: scripts/mysql_install_db: そのようなファイルやディレクトリはありません
[root@misc01 mysql]# bin/mysqld --initialize --user=mysql
2015-03-27T23:40:52.379590Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-27T23:40:53.301410Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-03-27T23:40:53.421318Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-03-27T23:40:53.487611Z 0 [Warning] Failed to setup SSL
2015-03-27T23:40:53.487667Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-03-27T23:40:53.490120Z 1 [Warning] A temporary password is generated for root@localhost: Bfd,psIvR4*e
[root@misc01 mysql]# chown -R root .
[root@misc01 mysql]# chown -R mysql data

初期設定が終わったので、起動してログインしてみます。

[root@misc01 mysql]# bin/mysqld_safe --user=mysql &
[1] 4910
[root@misc01 mysql]# 150328 08:51:25 mysqld_safe Logging to '/usr/local/mysql/data/misc01.err'.
150328 08:51:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@misc01 mysql]#

[root@misc01 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.6-m16

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.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.6-m16 |
+-----------+
1 row in set (0.01 sec)

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

mysql> exit
Bye
[root@misc01 mysql]# 

[root@misc01 mysql]# ps -ef | grep mysql
root      4910  1883  0 08:51 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql     4992  4910  0 08:51 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/misc01.err --pid-file=/usr/local/mysql/data/misc01.pid
root      5024  1883  0 08:56 pts/0    00:00:00 grep --color=auto mysql
[root@misc01 mysql]# kill 4992
[root@misc01 mysql]# 150328 08:56:32 mysqld_safe mysqld from pid file /usr/local/mysql/data/misc01.pid ended

[1]+  終了                  bin/mysqld_safe --user=mysql
[root@misc01 mysql]# ps -ef | grep mysql
root      5030  1883  0 08:56 pts/0    00:00:00 grep --color=auto mysql
[root@misc01 mysql]# 

起動ファイルのBASE, DATAディレクトリーを記入してファイルをコピー

[root@misc01 support-files]# pwd
/usr/local/mysql/support-files
[root@misc01 support-files]# vi mysql.server 
[root@misc01 support-files]# cp -p mysql.server /etc/init.d/
[root@misc01 support-files]# /etc/init.d/mysql.server start
Starting MySQL. SUCCESS! 
[root@misc01 support-files]#

MySQLにroot以外の追加の管理者アカウントを追加してみます。
GRANTでアカウントを作成するのはやめて、CREATE USERで作成した方が良いですね。

[root@misc01 support-files]# /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.6-m16 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.

mysql> GRANT ALL PRIVILEGES  ON *.* TO admin@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.7からは、mysql.userテーブルからpasswordという列が無くなってますね。

mysql> select user,host,password from mysql.user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> select user,host,authentication_string from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | authentication_string                     |
+-------+-----------+-------------------------------------------+
| root  | localhost | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| admin | %         | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
+-------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

その他、アカウントのパスワード期限など、コンプライアンス対応に使えそうなフィールドが増えています。
update

※ CentOSなどでは,MariaDBさんの/etc/my.cnfがあるので初期設定時にエラーになったりするので初期設定時に削除しています。
  ここら辺、名前が被らない方がユーザー的には有難いです。


FLUSH TABLES WITH READ LOCKをバックグラウンドで実行する処理がある場合に、
長時間実行しているバッチなどの処理があると、後から実行されるQueryが待たされるケースがある。
そんな、話を多からず、少なからず質問頂くので一応メモとして動作を記録。

通常は、データベース側の処理はDurationは短いので問題無いですが。。。
長時間バッチが実行されるような処理がある場合を避けて、FLUSH TABLES WITH READ LOCKを含む処理を実行するのが良さそうです。
MyISAMが全て無くなればまた、少しだけ選択肢が増えそうです。

http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/backup-capacity-options.html#option_meb_no-locking

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/backup-partial-options.html#option_meb_only-innodb

処理1

select * from actor where (select sleep(30));

処理2

mysql> FLUSH TABLES WITH READ LOCK;

処理3

mysql> insert into sakila.city(city,contry_id) values('Tokyo',00);

処理1が終わるまで全てGlobalロックによって待たされる。
処理1をKillすると処理2が瞬時に完了するので。以下のコマンドでUnlockしてあげると待たされていた処理が全て完了する。

処理4

UNLOCK TABLES

WorkbenchとShow Processlistで確認
wait

waitquery

以下sysスキーマで待機時間の長いQueryを確認
sys_schema

参考: 13.3.5 LOCK TABLES and UNLOCK TABLES Syntax


MySQL SlapをNDB対して実行して、メモリー割り当て変化の簡単な確認

[root@misc bin]# /usr/local/mysql/bin/mysqlslap --no-defaults --create-schema=SLAP --engine=ndb --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=ndb --number-int-cols=3 --number-char-cols=5 --concurrency=10 --auto-generate-sql-write-number=10000 --auto-generate-sql-execute-number=10000 --auto-generate-sql-load-type=mixed -h 192.168.56.114 -u admin -p
Enter password: 
Benchmark
        Running for engine ndb
        Average number of seconds to run all queries: 135.323 seconds
        Minimum number of seconds to run all queries: 135.323 seconds
        Maximum number of seconds to run all queries: 135.323 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[root@misc bin]# 

ndb

Node, alloc_bytes,free_bytesを確認する限りだと、ノード間のデータは同期されているので均等に分かれている。
こちらに、追加でノードグループを増やしたときのテーブル分割を次回確認してみる。

mysql> select * from memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2129920 |         65 | 134217728 |        4096 |
|       1 | Index memory        |  475136 |         58 |  67371008 |        8224 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2129920 |         65 | 134217728 |        4096 |
|       2 | Index memory        |  475136 |         58 |  67371008 |        8224 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.02 sec)

mysql> select * from resources;
+---------+---------------------+----------+------+-------+
| node_id | resource_name       | reserved | used | max   |
+---------+---------------------+----------+------+-------+
|       1 | RESERVED            |     4113 | 5495 | 13703 |
|       1 | DISK_OPERATIONS     |        0 |    0 |     0 |
|       1 | DISK_RECORDS        |        0 |    0 |     0 |
|       1 | DATA_MEMORY         |     6152 | 2158 |  6152 |
|       1 | JOBBUFFER           |        0 |    0 |     0 |
|       1 | FILE_BUFFERS        |     1152 | 1096 |  1152 |
|       1 | TRANSPORTER_BUFFERS |        0 |    0 |     0 |
|       1 | DISK_PAGE_BUFFER    |     2240 | 2240 |  2240 |
|       1 | QUERY_MEMORY        |        0 |    0 |     0 |
|       1 | SCHEMA_TRANS_MEMORY |       64 |    1 |     0 |
|       2 | RESERVED            |     4113 | 5495 | 13703 |
|       2 | DISK_OPERATIONS     |        0 |    0 |     0 |
|       2 | DISK_RECORDS        |        0 |    0 |     0 |
|       2 | DATA_MEMORY         |     6152 | 2158 |  6152 |
|       2 | JOBBUFFER           |        0 |    0 |     0 |
|       2 | FILE_BUFFERS        |     1152 | 1096 |  1152 |
|       2 | TRANSPORTER_BUFFERS |        0 |    0 |     0 |
|       2 | DISK_PAGE_BUFFER    |     2240 | 2240 |  2240 |
|       2 | QUERY_MEMORY        |        0 |    0 |     0 |
|       2 | SCHEMA_TRANS_MEMORY |       64 |    1 |     0 |
+---------+---------------------+----------+------+-------+
20 rows in set (0.01 sec)

mysql> select * from memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 3735552 |        114 | 134217728 |        4096 |
|       1 | Index memory        |  524288 |         64 |  67371008 |        8224 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 3735552 |        114 | 134217728 |        4096 |
|       2 | Index memory        |  524288 |         64 |  67371008 |        8224 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       98304 |       6624 |
|    1 |    1 |       98304 |       4080 |
|    2 |    0 |       98304 |       6624 |
|    2 |    1 |       98304 |       4032 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |      524288 |      26928 |
|    1 |    1 |      524288 |      27984 |
|    2 |    0 |      524288 |      26880 |
|    2 |    1 |      524288 |      27984 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |      819200 |      27888 |
|    1 |    1 |      819200 |      30720 |
|    2 |    0 |      819200 |      27888 |
|    2 |    1 |      819200 |      30672 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |     1212416 |      22272 |
|    1 |    1 |     1212416 |      16080 |
|    2 |    0 |     1212416 |      22272 |
|    2 |    1 |     1212416 |      16032 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
Empty set (0.02 sec)

mysql> 

今回は、検証用に2台のノードにMySQL Clusterを導入してみました。
検証目的なので2台で設定してますが、本番ではデータノードは分けた方が良さそうです。

————————-
Node1: 192.168.56.114
Node2: 192.168.56.115
————————-

mysql> select * from ndbinfo.nodes;
+---------+--------+---------+-------------+-------------------+
| node_id | uptime | status  | start_phase | config_generation |
+---------+--------+---------+-------------+-------------------+
|       1 |   3994 | STARTED |           0 |                 1 |
|       2 |   3948 | STARTED |           0 |                 1 |
+---------+--------+---------+-------------+-------------------+
2 rows in set (0.05 sec)

mysql> 

mysql> select * from ndbinfo.arbitrator_validity_detail;
+---------+------------+------------------+---------------+-----------+
| node_id | arbitrator | arb_ticket       | arb_connected | arb_state |
+---------+------------+------------------+---------------+-----------+
|       1 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
|       2 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
+---------+------------+------------------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> 

既に、起動済みなのでNDBストレージエンジンでTABLEを作成してみる。
cluster74_02で作成

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_02 |
+--------------+
1 row in set (0.01 sec)

mysql> CREATE DATABASE NDB01 default character set utf8mb4;USE NDB01;
Query OK, 1 row affected (0.06 sec)

Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE T20150319(id int not null auto_increment, memo varchar(100), primary key(id) ) ENGINE=ndb;
Query OK, 0 rows affected (0.47 sec)

mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc T20150319;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> 

他のノードにてデータ確認
Node: cluster74_01

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_01 |
+--------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| NDB01              |
| mysql              |
| ndb_1_fs           |
| ndbinfo            |
| ndbmemcache        |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> show tables from NDB01;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

ここで一度バックアップを取得
※リストアすると上記の状態に戻る。

ndb_mgm> backup start

DBに追加でデータを追加すると同時にリソースアロケーションを確認。

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-4');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-5');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T20150319;
+----+--------------------------------------+
| id | memo                                 |
+----+--------------------------------------+
|  3 | This is MySQL Cluster 7.4.4 検証-3   |
|  5 | This is MySQL Cluster 7.4.4 検証-5   |
|  1 | This is MySQL Cluster 7.4.4 検証     |
|  2 | This is MySQL Cluster 7.4.4 検証-2   |
|  4 | This is MySQL Cluster 7.4.4 検証-4   |
+----+--------------------------------------+
5 rows in set (0.01 sec)

mysql> desc ndbinfo.memory_per_fragment;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| fq_name                | varchar(512)        | YES  |     | NULL    |       |
| parent_fq_name         | varchar(512)        | YES  |     | NULL    |       |
| type                   | varchar(512)        | YES  |     | NULL    |       |
| table_id               | int(10) unsigned    | YES  |     | NULL    |       |
| node_id                | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance         | int(10) unsigned    | YES  |     | NULL    |       |
| fragment_num           | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_bytes  | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_size_bytes  | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_count       | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_count  | decimal(16,0)       | YES  |     | NULL    |       |
| var_elem_alloc_bytes   | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_free_bytes    | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_count         | bigint(20) unsigned | YES  |     | NULL    |       |
| hash_index_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32608 |
|    1 |    1 |       32768 |      32512 |
|    2 |    0 |       32768 |      32608 |
|    2 |    1 |       32768 |      32512 |
+------+------+-------------+------------+
4 rows in set (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-6');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-7');
Query OK, 1 row affected (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-9');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-10');
Query OK, 1 row affected (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32480 |
|    1 |    1 |       32768 |      32480 |
|    2 |    0 |       32768 |      32480 |
|    2 |    1 |       32768 |      32480 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id,memory_type,used,used_pages,total,total_pages from ndbinfo.memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       1 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       2 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.01 sec)

mysql> 

ここからは、データのリストア
1. Clusterを停止(管理・データ)
2. 管理ノードを起動
3. データノードを–initial付きで起動
4.SQLノード停止
5. Restore作業1(1台目だけは-mオプション付き)
6. Restore作業2 (2台目は-mは不要)
7. データ確認して完了

[root@cluster74_01 BACKUP]# pwd
/usr/local/mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64/data/BACKUP
[root@cluster74_01 BACKUP]# ls -l
合計 0
drwxr-x---. 2 root root 72  3月 19 22:25 BACKUP-1
[root@cluster74_01 BACKUP]# 


[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
ndb_mgm> exit
[root@cluster74_01 cluster]# 

SQLノードも停止しました。

[root@cluster74_01 cluster]# /home/admin/cluster/start_management.sh 
Start MySQL Management Node
MySQL Cluster Management Server mysql-5.6.23 ndb-7.4.4
Management Prompt
/usr/local/mysql/bin/ndb_mgm
[root@cluster74_01 cluster]# 

[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:25:38 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:25:38 [ndbd] INFO     -- Angel allocated nodeid: 1
[root@cluster74_01 cluster]# 

[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:26:51 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:26:51 [ndbd] INFO     -- Angel allocated nodeid: 2
[root@cluster74_02 cluster]# 



[root@cluster74_01 BACKUP]# /usr/local/mysql/bin/ndb_restore -b 1 -n 1 -m -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 1
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Successfully restored table `NDB01/def/T20150319`
Successfully restored table event REPL$NDB01/T20150319
Successfully restored table `ndbmemcache/def/ndb_clusters`
Successfully restored table event REPL$ndbmemcache/ndb_clusters

省略....
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 0
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.log'
File size 52 bytes
Restored 15 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_01 BACKUP]# 



[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndb_restore -b 1 -n 2 -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 2
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1-0.2.Data'
File size 31068 bytes

省略...
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table(24) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.log'
File size 52 bytes
Restored 14 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_02 cluster]# 

SQLノードを起動してデータ確認

[root@cluster74_01 cluster]# ./start_mysql.sh 
Start MySQL Node
[root@cluster74_01 cluster]# 150319 23:45:00 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_01.err'.
150319 23:45:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_01 cluster]# 


[root@cluster74_02 cluster]# 150319 23:45:12 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_02.err'.
150319 23:45:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_02 cluster]# 


ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50 (not connected, accepting connect from 192.168.56.114)
id=51 (not connected, accepting connect from 192.168.56.115)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm> 



mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |           0 |          0 |
|    1 |    1 |       32768 |      32608 |
|    2 |    0 |           0 |          0 |
|    2 |    1 |       32768 |      32608 |
+------+------+-------------+------------+
4 rows in set (0.06 sec)

mysql> 

restore

参照:
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbinfo.html


MySQL 監査ログのローテション
MySQL Enterprise Auditで取得した監査ログをMySQL Utilitiesを利用して、
定期的にローテーション出来るかどうか確認してみました。

ログをローテーションした結果
audit

Audit Logのローテーションとしては、以下の2パターンがあります。

1) ログサイズによる自動ローテーション
sysvar_audit_log_rotate_on_size

If the audit_log_rotate_on_size value is greater than 0,
the audit log plugin closes and reopens its log file
if a write to the file causes its size to exceed this value.

2) mysqlutilitiesを利用したローテーション
MySQL Utilityを利用したローテーション

mysqluc> help utilities
Utility           Description
----------------  ---------------------------------------------------------
mysqlauditadmin   audit log maintenance utility
mysqlauditgrep    audit log search utility
mysqldbcompare    compare databases for consistency
mysqldbcopy       copy databases from one server to another
mysqldbexport     export metadata and data from databases
mysqldbimport     import metadata and data from files
mysqldiff         compare object definitions among objects where the
                  difference is how db1.obj1 differs from db2.obj2
mysqldiskusage    show disk usage for databases
mysqlfailover     automatic replication health monitoring and failover
mysqlfrm          show CREATE TABLE from .frm files
mysqlindexcheck   check for duplicate or redundant indexes
mysqlmetagrep     search metadata
mysqlprocgrep     search process information
mysqlreplicate    establish replication with a master
mysqlrpladmin     administration utility for MySQL replication
mysqlrplcheck     check replication
mysqlrplms        establish multi-source replication
mysqlrplshow      show slaves attached to a master
mysqlrplsync      replication synchronization checker utility
mysqlserverclone  start another instance of a running server
mysqlserverinfo   show server information
mysqluserclone    clone a MySQL user account to one or more new users

mysqluc>
mysqluc> help mysqlauditadmin
Usage: mysqlauditadmin.exe --server=user:pass@host:port --show-options

mysqlauditadmin - audit log maintenance utility

Options:
Option                     Description
-------------------------  ------------------------------------------------
--version                  show program's version number and exit
--help                     display this help message and exit
--license                  display program's license and exit
--server=SERVER            connection information for the server in the
                           form:
                           <user>[:<password>]@<host>[:<port>][:<socket>]
                           or <login-path>[:<port>][:<socket>] or <config-
                           path>[<&#91;group&#93;>].
--audit-log-name=LOG_NAME  full path and file name for the audit log file.
                           Used for stats and copy options.
--show-options             display the audit log system variables.
--remote-login=RLOGIN      user name and host to be used for remote login
                           for copying log files. Format:
                           <user>:<host_or_ip> Password will be prompted.
--file-stats               display the audit log file statistics.
--copy-to=COPY_LOCATION    the location to copy the audit log file
                           specified. The path must be locally accessible
                           for the current user.
--value=VALUE              value used to set variables based on the command
                           specified. See --help for list per command.
--ssl-ca=SSL_CA            The path to a file that contains a list of
                           trusted SSL CAs.
--ssl-cert=SSL_CERT        The name of the SSL certificate file to use for
                           establishing a secure connection.
--ssl-key=SSL_KEY          The name of the SSL key file to use for
                           establishing a secure connection.
-v, --verbose              control how much information is displayed. e.g.,
                           -v = verbose, -vv = more verbose, -vvv = debug
                           Available Commands: copy - copy the audit log to
                           a locally accessible path policy - set the audit
                           log policy Values = ALL, NONE, LOGINS, QUERIES,
                           DEFAULT rotate - perform audit log rotation
                           rotate_on_size - set the rotate log size limit
                           for auto rotation Values = 0, 4294967295

mysqluc> mysqlauditadmin --show-options --server=admin:password@192.168.56.113
WARNING: Using a password on the command line interface can be insecure.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+


mysqluc> mysqlauditadmin --show-options --server=admin:password@192.168.56.113 rotate
WARNING: Using a password on the command line interface can be insecure.
#
# Showing options before command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+

#
# Executing ROTATE command.
#

#
# Showing options after command.
#
# Audit Log Variables and Options
#
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| audit_log_buffer_size        | 1048576       |
| audit_log_connection_policy  | ALL           |
| audit_log_current_session    | ON            |
| audit_log_exclude_accounts   |               |
| audit_log_file               | audit.log     |
| audit_log_flush              | OFF           |
| audit_log_format             | OLD           |
| audit_log_include_accounts   |               |
| audit_log_policy             | ALL           |
| audit_log_rotate_on_size     | 0             |
| audit_log_statement_policy   | ALL           |
| audit_log_strategy           | ASYNCHRONOUS  |
+------------------------------+---------------+


mysqluc>

ログが以下のようにローテーションされて、
古いファイルがaudit.log.xxxxx.xmlというファイル名になっています。


[root@misc data]# ls -l audit.*
-rw-rw----. 1 mysql mysql  4600  3月 13 22:13 audit.log
-rw-rw----. 1 mysql mysql 23048  3月 13 22:13 audit.log.14262524122629864.xml
[root@misc data]#

追加で、負荷をかけた状態でローテーションしてみました。こちらがWorkbenchで確認したログです。
workbench

Workbenchでログも確認してみました。mysqlslapからアクセスがある事が確認出来ます。
log

mysqlauditadminについては此方を参照下さい
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqlauditadmin.html

https://docs.oracle.com/cd/E17952_01/mysql-utilities-1.3-en/mysqlauditadmin.html

補足;此方は、コマンドでログの中身をフィルターするUTILITYになります。
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqlauditgrep.html

Enterprise Auditはこちらから30日間トライアルがダウンロード可能です。
https://edelivery.oracle.com/

MySQL Utiliries
http://thinkit.co.jp /story/2014/02/10/4814 

SYSLOG: This function is also nice to have. (Percona)
http://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html


MySQL Fabricにて”Global” という名前でグループを作成し,その中にサーバを三台登録してあります。
本日は、Shardingの作成とShardingされたTableとグループ全体に対してDDLを実行しました。

【目的】 ShardingされたMySQL Fabric GroupへのCRATE,ALTERなどのDDLの実行と適用範囲の確認

■ Sharding作成前の状況

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
fd8f10c5-c3c1-11e4-84b2-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
fe41edb4-c3c1-11e4-84b2-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
fed03648-c3c1-11e4-84b2-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0

-bash-4.2$ 

■ Sharding Mapping定義作成
-bash-4.2$ mysqlfabric help sharding create_definition
sharding create_definition type_name group_id [–synchronous]

Define a shard mapping.


-bash-4.2$ mysqlfabric sharding create_definition RANGE global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
1e42e197-7b22-45a5-ba65-7432572e47ee        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425622316.87 Triggered by <mysql.fabric.events.Event object at 0x12fa310>.
    4       2 1425622316.94                     Executing action (_define_shard_mapping).
    5       2 1425622316.95                      Executed action (_define_shard_mapping).


-bash-4.2$ 

-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name global_group_id
---------- --------- ---------------
         1     RANGE          global


-bash-4.2$ 

■Sharding用にグループ作成とサーバ追加

-bash-4.2$ mysqlfabric group create shard1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
fe58c7b1-d881-49f1-be60-c6c456d17926        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425622571.12 Triggered by <mysql.fabric.events.Event object at 0x1b6ecd0>.
    4       2 1425622571.13                             Executing action (_create_group).
    5       2 1425622571.14                              Executed action (_create_group).


-bash-4.2$ mysqlfabric group add shard1 127.0.0.1:63304
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
0b058a45-a48b-4483-b405-0b8a645ba024        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425622571.32 Triggered by <mysql.fabric.events.Event object at 0x1bc30d0>.
    4       2 1425622571.32                               Executing action (_add_server).
    5       2 1425622571.33                                Executed action (_add_server).


-bash-4.2$ mysqlfabric group add shard1 127.0.0.1:63305
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
02446f80-1c38-41fb-a7bc-88f3593ee10a        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425622571.51 Triggered by <mysql.fabric.events.Event object at 0x1bc30d0>.
    4       2 1425622571.51                               Executing action (_add_server).
    5       2 1425622571.52                                Executed action (_add_server).


-bash-4.2$ mysqlfabric group promote shard1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
34ef4ed7-db5d-4b8d-a4a9-c86784181cd0        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425622571.66 Triggered by <mysql.fabric.events.Event object at 0x12fa410>.
    4       2 1425622571.66                      Executing action (_define_ha_operation).
    5       2 1425622571.66                       Executed action (_define_ha_operation).
    3       2 1425622571.66 Triggered by <mysql.fabric.events.Event object at 0x12fa3d0>.
    4       2 1425622571.66                      Executing action (_find_candidate_fail).
    5       2 1425622571.68                       Executed action (_find_candidate_fail).
    3       2 1425622571.68 Triggered by <mysql.fabric.events.Event object at 0x1b5a2d0>.
    4       2 1425622571.68                     Executing action (_check_candidate_fail).
    5       2 1425622571.69                      Executed action (_check_candidate_fail).
    3       2 1425622571.68 Triggered by <mysql.fabric.events.Event object at 0x1b5a350>.
    4       2 1425622571.69                          Executing action (_wait_slave_fail).
    5       2 1425622571.71                           Executed action (_wait_slave_fail).
    3       2 1425622571.71 Triggered by <mysql.fabric.events.Event object at 0x1b5a490>.
    4       2 1425622571.71                      Executing action (_change_to_candidate).
    5       2 1425622571.79                       Executed action (_change_to_candidate).


-bash-4.2$ mysqlfabric group activate shard1
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
77bc7aa8-8068-4d1d-a2da-4e2a5bd5da87        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425622571.95 Triggered by <mysql.fabric.events.Event object at 0x1bc3290>.
    4       2 1425622571.95                           Executing action (_activate_group).
    5       2 1425622571.96                            Executed action (_activate_group).


-bash-4.2$ 

■以下のグループとサーバも上記と同じくShardingの為にGroupとして追加
mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63306
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group promote shard2
mysqlfabric group activate shard2

mysqlfabric group create shard3
mysqlfabric group add shard3 127.0.0.1:63308
mysqlfabric group add shard3 127.0.0.1:63309
mysqlfabric group promote shard3
mysqlfabric group activate shard3

■ Add sharding to employee

Mapping IDは以下のコマンドで確認すると1になっているので1でシャーディングを作成する。

-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name global_group_id
---------- --------- ---------------
         1     RANGE          global


-bash-4.2$ mysqlfabric sharding add_table 1 test.employees emp_no
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
115ea959-bb38-4a2b-ab04-a59dad52da0e        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425623161.61 Triggered by <mysql.fabric.events.Event object at 0x12fa450>.
    4       2 1425623161.61                        Executing action (_add_shard_mapping).
    5       2 1425623161.69                         Executed action (_add_shard_mapping).


-bash-4.2$ mysqlfabric sharding add_shard 1 shard1/1,shard2/300,shard3/600 --state=enabled
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
794b2bb8-83a5-48c5-9a61-88c30b8d72a8        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425623182.84 Triggered by <mysql.fabric.events.Event object at 0x1bc9410>.
    4       2 1425623182.84                                Executing action (_add_shard).
    5       2 1425623183.45                                 Executed action (_add_shard).


-bash-4.2$ 

-bash-4.2$ mysqlfabric dump sharding_information
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
       test  employees      emp_no           1        1     RANGE   shard1       global
       test  employees      emp_no         300        2     RANGE   shard2       global
       test  employees      emp_no         600        3     RANGE   shard3       global


-bash-4.2$ 

■テーブルが全てのサーバに作成されている。
グループ全体 ”Global” を指定して、CREATE TABLEを実行しています。

-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
-bash-4.2$ python ./alter_shared_create.py 
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees      |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no     | int(11)  | NO   | PRI | NULL    |       |
| first_name | char(40) | YES  |     | NULL    |       |
| last_name  | char(40) | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees      |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no     | int(11)  | NO   | PRI | NULL    |       |
| first_name | char(40) | YES  |     | NULL    |       |
| last_name  | char(40) | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63306 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees      |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63306 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no     | int(11)  | NO   | PRI | NULL    |       |
| first_name | char(40) | YES  |     | NULL    |       |
| last_name  | char(40) | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63308 -e "show tables from test"
Warning: Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| employees      |
+----------------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63308 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| emp_no     | int(11)  | NO   | PRI | NULL    |       |
| first_name | char(40) | YES  |     | NULL    |       |
| last_name  | char(40) | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
-bash-4.2$ 

上記で実行したスクリプト


import mysql.connector 
from mysql.connector import fabric

import time

def connect():
    try:
       conn=mysql.connector.connect(
          fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
          user="root", database="test", password="root",
          autocommit=True
       )
    except mysql.connector.Error as e: 
        print "Error trying to get a new database connection"
        print "Error code:", e.errno 
        print "SQLSTATE value:", e.sqlstate
        print "Error message:", e.msg
        quit()
    return conn

conn = connect()
conn.set_property(group='global')
cur = conn.cursor()
cur.execute(
    "CREATE TABLE employees ("
    "   emp_no INT PRIMARY KEY, "
    "   first_name CHAR(40), "
    "   last_name CHAR(40)"
    ")"
    )

conn.commit()
conn.close()
conn = connect()

alter-table0

■テーブル定義の変更
上記で作成したテーブルに対して、Alter Tableで列を追加する。


-bash-4.2$ python ./alter_shared.py 
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   | PRI | NULL    |       |
| first_name | char(40)    | YES  |     | NULL    |       |
| last_name  | char(40)    | YES  |     | NULL    |       |
| nickname   | varchar(64) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   | PRI | NULL    |       |
| first_name | char(40)    | YES  |     | NULL    |       |
| last_name  | char(40)    | YES  |     | NULL    |       |
| nickname   | varchar(64) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63306 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   | PRI | NULL    |       |
| first_name | char(40)    | YES  |     | NULL    |       |
| last_name  | char(40)    | YES  |     | NULL    |       |
| nickname   | varchar(64) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63308 -e "desc  test.employees"
Warning: Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   | PRI | NULL    |       |
| first_name | char(40)    | YES  |     | NULL    |       |
| last_name  | char(40)    | YES  |     | NULL    |       |
| nickname   | varchar(64) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
-bash-4.2$ 

上記で実行したスクリプト


import mysql.connector 
from mysql.connector import fabric

import time

def connect():
    try:
       conn=mysql.connector.connect(
          fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
          user="root", database="test", password="root",
          autocommit=True
       )
    except mysql.connector.Error as e: 
        print "Error trying to get a new database connection"
        print "Error code:", e.errno 
        print "SQLSTATE value:", e.sqlstate
        print "Error message:", e.msg
        quit()
    return conn

conn = connect()
conn.set_property(group='global')
cur = conn.cursor()
cur.execute("ALTER TABLE test.employees ADD nickname VARCHAR(64)")


conn.commit()
conn.close()
conn = connect()

alter-table

こちらは、グローバルグループに複数のSharding定義を追加した場合(例)
グループにテーブル定義を追加してあげると、複数の定義を追加出来ます。

shardbefore

-bash-4.2$ mysqlfabric sharding create_definition RANGE global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
0d675538-8b4a-4c57-b4a8-a29b529f0c7c        1       1      2

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2  1425626498.5 Triggered by <mysql.fabric.events.Event object at 0x12fa310>.
    4       2  1425626498.5                     Executing action (_define_shard_mapping).
    5       2 1425626498.59                      Executed action (_define_shard_mapping).


-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name global_group_id
---------- --------- ---------------
         1     RANGE          global
         2     RANGE          global


-bash-4.2$ mysqlfabric sharding create_definition RANGE global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
5a3500cf-df2f-4219-a96b-9c9af166f33c        1       1      3

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425626647.47 Triggered by <mysql.fabric.events.Event object at 0x12fa310>.
    4       2 1425626647.47                     Executing action (_define_shard_mapping).
    5       2 1425626647.47                      Executed action (_define_shard_mapping).


-bash-4.2$ mysqlfabric sharding list_definitions
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

mapping_id type_name global_group_id
---------- --------- ---------------
         1     RANGE          global
         2     RANGE          global
         3     RANGE          global


-bash-4.2$ 


-bash-4.2$ mysqlfabric sharding add_table 2 test.employees2 emp_no
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
aadb02ef-c743-4839-b567-045723f7c3e8        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425626950.87 Triggered by <mysql.fabric.events.Event object at 0x12fa450>.
    4       2 1425626950.87                        Executing action (_add_shard_mapping).
    5       2 1425626950.88                         Executed action (_add_shard_mapping).


-bash-4.2$ mysqlfabric sharding add_shard 2 shard1/1,shard2/300,shard3/600 --state=enabled
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
32dd4f37-725c-4717-b6eb-073a26b814b7        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425626974.93 Triggered by <mysql.fabric.events.Event object at 0x1bc9410>.
    4       2 1425626974.94                                Executing action (_add_shard).
    5       2 1425626975.45                                 Executed action (_add_shard).


-bash-4.2$ mysqlfabric sharding add_table 3 test.employees3 emp_no
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
ae7c4c6c-342c-4d8e-b1c4-8ce534a91ffd        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425627239.67 Triggered by <mysql.fabric.events.Event object at 0x12fa450>.
    4       2 1425627239.72                        Executing action (_add_shard_mapping).
    5       2 1425627239.73                         Executed action (_add_shard_mapping).


-bash-4.2$ mysqlfabric sharding add_shard 3 shard1/1,shard2/300,shard3/600 --state=enabled
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
0ccb5df2-216c-4076-9966-708cd189b95a        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1425627253.49 Triggered by <mysql.fabric.events.Event object at 0x1bc9410>.
    4       2 1425627253.49                                Executing action (_add_shard).
    5       2 1425627253.94                                 Executed action (_add_shard).


-bash-4.2$ mysqlfabric dump sharding_information
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

schema_name table_name column_name lower_bound shard_id type_name group_id global_group
----------- ---------- ----------- ----------- -------- --------- -------- ------------
       test  employees      emp_no           1        1     RANGE   shard1       global
       test  employees      emp_no         300        2     RANGE   shard2       global
       test  employees      emp_no         600        3     RANGE   shard3       global
       test employees2      emp_no           1        4     RANGE   shard1       global
       test employees2      emp_no         300        5     RANGE   shard2       global
       test employees2      emp_no         600        6     RANGE   shard3       global
       test employees3      emp_no           1        7     RANGE   shard1       global
       test employees3      emp_no         300        8     RANGE   shard2       global
       test employees3      emp_no         600        9     RANGE   shard3       global


-bash-4.2$ 

shardadd

参照: 8.8.4. MySQL Fabric Configuration for Running Samples