mysqlfabric group add実行時の新規追加サーバーのデータ同期について。

1)マスターDBに必要なバイナリーログがある場合は、新規Slaveがログを読み込んでデータ同期。
2) マスターDBに必要なバイナリーログが無い場合は、新規Slaveにデータをリストアしてからmysqlfabric group addコマンドを実行。

■ mysqlfabric group addコマンドについての前回のブログ
mysqlfabricにてSlaveを追加

【通常時Fabric管理ノードが受け取っている処理】

+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2015-02-26 10:35:43 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:43 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:35:45 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:45 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:35:47 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:47 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:35:49 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:35:49 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

グループにサーバーを追加した時のバックグラウンド処理の確認。

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

                                uuid finished success result
------------------------------------ -------- ------- ------
b63f21d7-4b17-4602-a110-a6ad394c6438        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1424914567.27 Triggered by <mysql.fabric.events.Event object at 0x27465d0>.
    4       2 1424914567.33                               Executing action (_add_server).
    5       2 1424914567.46                                Executed action (_add_server).


-bash-4.2$

-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

【Slaveを追加した時に流れるQuery】 Fabric管理ノード(データストアDB)

+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | SELECT username, protocol, password FROM users WHERE username = 'admin' AND protocol = 'xmlrpc'                                                                                                                                                                                                                                                                                           |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | SELECT p.subsystem, p.component, p.function FROM users AS u LEFT JOIN user_roles AS ur USING (user_id) LEFT JOIN role_permissions AS rp USING (role_id) LEFT JOIN permissions AS p USING (permission_id) WHERE u.username = 'admin' AND protocol = 'xmlrpc'                                                                                                                               |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | INSERT INTO log (subject, reported, reporter, message, category, type) VALUES('group.add', '2015-02-26 01:36:07', 'mysql.fabric.command', 'Started command (group, add).', 1, 0)                                                                                                                                                                                                          |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | BEGIN                                                                                                                                                                                                                                                                                                                                                                                     |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | INSERT INTO checkpoints(proc_uuid, lockable_objects, job_uuid, sequence, action_fqn, param_args, param_kwargs) VALUES ('b63f21d7-4b17-4602-a110-a6ad394c6438', 'c__builtin__\nset\np0\n((lp1\nS\'lock\'\np2\natp3\nRp4\n.', '12fe6274-f21b-4706-83c6-9ad2c763cbb9', 0, 'mysql.fabric.services.server._add_server', '(S\'global\'\np0\nS\'127.0.0.1:63307\'\np1\nNI00\ntp2\n.', '(dp0\n.') |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | COMMIT                                                                                                                                                                                                                                                                                                                                                                                    |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | UPDATE checkpoints set started = 1424914567.333504 WHERE proc_uuid = 'b63f21d7-4b17-4602-a110-a6ad394c6438' and job_uuid = '12fe6274-f21b-4706-83c6-9ad2c763cbb9'                                                                                                                                                                                                                         |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | BEGIN                                                                                                                                                                                                                                                                                                                                                                                     |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE server_uuid = '4b2ed657-bd51-11e4-9ab3-08002766cefe'                                                                                                                                                                                                                                                |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT @@GLOBAL.SERVER_UUID                                                                                                                                                                                                                                                                                                                                                               |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | INSERT INTO servers(server_uuid, server_address, mode, status, weight, group_id) values('4b2ed657-bd51-11e4-9ab3-08002766cefe', '127.0.0.1:63307', 1, 2, 1.0, NULL)                                                                                                                                                                                                                       |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | UPDATE servers SET group_id = 'global' WHERE server_uuid = '4b2ed657-bd51-11e4-9ab3-08002766cefe'                                                                                                                                                                                                                                                                                         |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE server_uuid = '4790c170-bd51-11e4-9ab3-08002766cefe'                                                                                                                                                                                                                                                |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | UPDATE checkpoints set finished = 1424914567.455931 WHERE proc_uuid = 'b63f21d7-4b17-4602-a110-a6ad394c6438' and job_uuid = '12fe6274-f21b-4706-83c6-9ad2c763cbb9'                                                                                                                                                                                                                        |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | COMMIT                                                                                                                                                                                                                                                                                                                                                                                    |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        10 |         1 | Query        | DELETE FROM checkpoints WHERE proc_uuid = 'b63f21d7-4b17-4602-a110-a6ad394c6438'                                                                                                                                                                                                                                                                                                          |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        15 |         1 | Query        | INSERT INTO log (subject, reported, reporter, message, category, type) VALUES('group.add', '2015-02-26 01:36:07', 'mysql.fabric.command', 'Finished command (group, add).', 1, 1)                                                                                                                                                                                                         |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT group_id, description, master_uuid, master_defined, status FROM groups WHERE group_id = 'global'                                                                                                                                                                                                                                                                                   |
| 2015-02-26 10:36:07 | fabric[fabric] @ localhost [127.0.0.1] |        16 |         1 | Query        | SELECT server_uuid, server_address, mode, status, weight, group_id FROM servers WHERE group_id = 'global'                                                                                                                                                                                                                                                                                 |
| 2015-02-26 10:36:08 | root[root] @ localhost [127.0.0.1]     |        17 |         1 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                 |
+---------------------+----------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

【追加したSlave側のログ】

 
-----------------------------------------------------------------------------------------------------------------
-bash-4.2$ cat fabric_add_slave.sql | more
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Query   SELECT DATABASE()
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Init DB mysql
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Query   show databases
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Query   show tables
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      columns_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      db 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      event 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      func 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      general_log 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_category 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_keyword 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_relation 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      help_topic 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      innodb_index_stats 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      innodb_table_stats 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      ndb_binlog_index 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      plugin 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      proc 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      procs_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      proxies_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      servers 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slave_master_info 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slave_relay_log_info 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slave_worker_info 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      slow_log 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      tables_priv 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_leap_second 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_name 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_transition 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      time_zone_transition_type 
2015-02-26 10:48:54     root[root] @ localhost [127.0.0.1]      3       8       Field List      user 
2015-02-26 10:48:58     root[root] @ localhost [127.0.0.1]      3       8       Query   show databases
2015-02-26 10:49:21     root[root] @ localhost [127.0.0.1]      3       8       Query   select @@hostname,@@port
2015-02-26 10:49:24     [root] @ localhost [127.0.0.1]  4       8       Connect root@localhost on 
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Query   SELECT @@GLOBAL.SERVER_UUID
2015-02-26 10:49:24     [root] @ localhost [127.0.0.1]  5       8       Connect root@localhost on 
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.SERVER_UUID as SERVER_UUID
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.SERVER_ID as SERVER_ID
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.VERSION as VERSION
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.GTID_MODE as GTID_MODE
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.LOG_BIN as LOG_BIN
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      4       8       Quit
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.READ_ONLY as READ_ONLY
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW GRANTS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   STOP SLAVE
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'root' MASTER_PASSWORD = <secret> MASTER_PORT = 63301
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SET @@GLOBAL.READ_ONLY = ON
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SELECT @@GLOBAL.READ_ONLY as READ_ONLY
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   START SLAVE
2015-02-26 10:49:24     [] @  []        6       8       Connect Out     root@127.0.0.1:63301
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     root[root] @ localhost [127.0.0.1]      5       8       Query   SHOW SLAVE STATUS
2015-02-26 10:49:24     [] @  []        7       8       Query   SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
2015-02-26 10:49:24     [] @  []        7       8       Query   GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
2015-02-26 10:49:24     [] @  []        7       8       Query   DROP TABLE IF EXISTS `employees` /* generated by server */
2015-02-26 10:49:24     [] @  []        7       8       Query   CREATE TABLE employees (   emp_no INT,    first_name CHAR(40),    last_name CHAR(40))
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (0, 'MySQL Fabric:0', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (1, 'MySQL Fabric:1', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (2, 'MySQL Fabric:2', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */

省略.....

2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (298, 'MySQL Fabric:298', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:24     [] @  []        7       8       Query   BEGIN
2015-02-26 10:49:24     [] @  []        7       8       Query   INSERT INTO employees VALUES (299, 'MySQL Fabric:299', 'Scale Out and HA Demo')
2015-02-26 10:49:24     [] @  []        7       8       Query   COMMIT /* implicit, from Xid_log_event */
2015-02-26 10:49:26     [root] @ localhost [127.0.0.1]  8       8       Connect root@localhost on 
2015-02-26 10:49:26     root[root] @ localhost [127.0.0.1]      8       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:26     root[root] @ localhost [127.0.0.1]      8       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:26     root[root] @ localhost [127.0.0.1]      8       8       Quit
2015-02-26 10:49:28     [root] @ localhost [127.0.0.1]  9       8       Connect root@localhost on 
2015-02-26 10:49:28     root[root] @ localhost [127.0.0.1]      9       8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:28     root[root] @ localhost [127.0.0.1]      9       8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:28     root[root] @ localhost [127.0.0.1]      9       8       Quit
2015-02-26 10:49:30     [root] @ localhost [127.0.0.1]  10      8       Connect root@localhost on 
2015-02-26 10:49:30     root[root] @ localhost [127.0.0.1]      10      8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:30     root[root] @ localhost [127.0.0.1]      10      8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:30     root[root] @ localhost [127.0.0.1]      10      8       Quit
2015-02-26 10:49:32     [root] @ localhost [127.0.0.1]  11      8       Connect root@localhost on 
2015-02-26 10:49:32     root[root] @ localhost [127.0.0.1]      11      8       Query   SET NAMES 'utf8' COLLATE 'utf8_general_ci'
2015-02-26 10:49:32     root[root] @ localhost [127.0.0.1]      11      8       Query   SET @@session.autocommit = ON
2015-02-26 10:49:32     root[root] @ localhost [127.0.0.1]      11      8       Quit
2015-02-262015-02-26 10:54:48     root[root] @ localhost [127.0.0.1]      121     8       Query   select * into outfile 'fabric_add_slave.sql' from mysql.general_log
-bash-4.2$ 
-----------------------------------------------------------------------------------------------------------------

【マスターでバイバリーログを削除した場合に、サーバーを追加した場合の動作確認】

バイナリーログのPURGE


mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW MASTER LOGS;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| Fabric01-bin.000001 |     93831 |
| Fabric01-bin.000002 |       241 |
| Fabric01-bin.000003 |       191 |
+---------------------+-----------+
3 rows in set (0.00 sec)

mysql> PURGE MASTER LOGS TO 'Fabric01-bin.000002';
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW MASTER LOGS;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| Fabric01-bin.000002 |       241 |
| Fabric01-bin.000003 |       191 |
+---------------------+-----------+
2 rows in set (0.01 sec)

mysql> 

【マスターでバイバリーログを削除した場合はログが読み取れずスレーブ作成が失敗する】

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

ServerError: Error trying to configure server (4c6e5d9d-bd51-11e4-9ab3-08002766cefe) as slave: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'.

-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0
4c2e3c4e-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

【マスターからダンプして,新規Slaveにリストア後にGROUPにADD】
既に、上記でGROUP ADDが失敗していたので、@@GLOBAL.GTID_EXECUTEDが存在していたので情報をリセットしてからリストア。

-bash-4.2$ /usr/local/mysql/bin/mysqldump -uroot -proot -h127.0.0.1 -P63301 --all-databases --single-transaction --triggers --routines --events > GTID_63301.sql
Warning: Using a password on the command line interface can be insecure.
-bash-4.2$ ls
Fabric01-bin.000002  Fabric01-relay-bin.000001  Fabric01.pid    ib_logfile0  my.sock    mysql.out
Fabric01-bin.000003  Fabric01-relay-bin.index   GTID_63301.sql  ib_logfile1  mysql      performance_schema
Fabric01-bin.index   Fabric01.log               auto.cnf        ibdata1      mysql.err  test
-bash-4.2$ 


-bash-4.2$ /usr/local/mysql/bin/mysql -h127.0.0.1 -P63309 -u root -p < /home/mysql/fabric/mysql1/GTID_63301.sql
Enter password: 
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
-bash-4.2$ /usr/local/mysql/bin/mysql -h127.0.0.1 -P63309 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, 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> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+---------------------------------+------------------------------------------+
| Variable_name                   | Value                                    |
+---------------------------------+------------------------------------------+
| enforce_gtid_consistency        | ON                                       |
| gtid_executed                   | 4c6e5d9d-bd51-11e4-9ab3-08002766cefe:1-2 |
| gtid_mode                       | ON                                       |
| gtid_owned                      |                                          |
| gtid_purged                     |                                          |
| simplified_binlog_gtid_recovery | OFF                                      |
+---------------------------------+------------------------------------------+
6 rows in set (0.00 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye
-bash-4.2$ /usr/local/mysql/bin/mysql -h127.0.0.1 -P63309 -u root -p < /home/mysql/fabric/mysql1/GTID_63301.sql
Enter password: 
-bash-4.2$ 

&#91;/SHELL&#93;
※一度,group addに失敗している為、一度RESET MASTERを実行しています。


<strong>【ダンプからリストア後にSlaveをグループに追加】</strong> 
[SHELL]
-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0
4c2e3c4e-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0


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

                                uuid finished success result
------------------------------------ -------- ------- ------
7e90ab70-f52f-4e18-b7e6-2a009cddd828        1       1      1

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


-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
------------------------------------ --------------- --------- ---------- ------
4790c170-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
483d8d5a-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
48d8c44f-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4958fc6d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
49f47cf1-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
4a900296-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0
4b2ed657-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63307 SECONDARY  READ_ONLY    1.0
4c2e3c4e-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63308 SECONDARY  READ_ONLY    1.0
4c6e5d9d-bd51-11e4-9ab3-08002766cefe 127.0.0.1:63309 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63307 -se"select @@port,count(*) from test.employees"
Warning: Using a password on the command line interface can be insecure.
@@port  count(*)
63307   299
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63309 -se"select @@port,count(*) from test.employees"
Warning: Using a password on the command line interface can be insecure.
@@port  count(*)
63309   299
-bash-4.2$ 

【新規、ダンプ&リストアして追加したスレーブのログを確認してみる。】

mysql> select * from mysql.general_log;
+---------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------+
| event_time          | user_host                          | thread_id | server_id | command_type | argument                                                                                                       |
+---------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------+
| 2015-02-26 11:33:11 | [root] @ localhost [127.0.0.1]     |        24 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Query        | SELECT @@GLOBAL.SERVER_UUID                                                                                    |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |        24 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.SERVER_UUID as SERVER_UUID                                                                     |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.SERVER_ID as SERVER_ID                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.VERSION as VERSION                                                                             |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.GTID_MODE as GTID_MODE                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.LOG_BIN as LOG_BIN                                                                             |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.READ_ONLY as READ_ONLY                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW GRANTS                                                                                                    |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | STOP SLAVE                                                                                                     |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | CHANGE MASTER TO MASTER_HOST = '127.0.0.1' MASTER_USER = 'root' MASTER_PASSWORD = <secret> MASTER_PORT = 63301 |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SET @@GLOBAL.READ_ONLY = ON                                                                                    |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SELECT @@GLOBAL.READ_ONLY as READ_ONLY                                                                         |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | START SLAVE                                                                                                    |
| 2015-02-26 11:33:11 | [] @  []                           |        25 |         9 | Connect Out  | root@127.0.0.1:63301                                                                                           |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:11 | root[root] @ localhost [127.0.0.1] |         6 |         9 | Query        | SHOW SLAVE STATUS                                                                                              |
| 2015-02-26 11:33:13 | [root] @ localhost [127.0.0.1]     |        27 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:33:13 | root[root] @ localhost [127.0.0.1] |        27 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:33:13 | root[root] @ localhost [127.0.0.1] |        27 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:33:13 | root[root] @ localhost [127.0.0.1] |        27 |         9 | Quit         |                                                                                                                |

省略....
                                                                 |
| 2015-02-26 11:35:22 | [root] @ localhost [127.0.0.1]     |        91 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:35:22 | root[root] @ localhost [127.0.0.1] |        91 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:35:22 | root[root] @ localhost [127.0.0.1] |        91 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:35:22 | root[root] @ localhost [127.0.0.1] |        91 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:35:24 | [root] @ localhost [127.0.0.1]     |        92 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:35:24 | root[root] @ localhost [127.0.0.1] |        92 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:35:24 | root[root] @ localhost [127.0.0.1] |        92 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:35:24 | root[root] @ localhost [127.0.0.1] |        92 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:35:26 | [root] @ localhost [127.0.0.1]     |        93 |         9 | Connect      | root@localhost on                                                                                              |
| 2015-02-26 11:35:26 | root[root] @ localhost [127.0.0.1] |        93 |         9 | Query        | SET NAMES 'utf8' COLLATE 'utf8_general_ci'                                                                     |
| 2015-02-26 11:35:26 | root[root] @ localhost [127.0.0.1] |        93 |         9 | Query        | SET @@session.autocommit = ON                                                                                  |
| 2015-02-26 11:35:26 | root[root] @ localhost [127.0.0.1] |        93 |         9 | Quit         |                                                                                                                |
| 2015-02-26 11:35:27 | root[root] @ localhost [127.0.0.1] |        23 |         9 | Query        | select * from mysql.general_log                                                                                |
+---------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------+
291 rows in set (0.01 sec)

mysql> 

17.1.3.2 Setting Up Replication Using GTIDs
http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html
MySQL Fabric – Part 1 – Installing
https://blog.marceloaltmann.com/mysql-fabric-installing-instalar/


mysql fabricにて書き込みのRANGEシャーディングしたテーブルから、
データを読み込みの確認。

分割定義


# Create Sharding Grop1
mysqlfabric group create shard1
mysqlfabric group add shard1 127.0.0.1:63304
mysqlfabric group add shard1 127.0.0.1:63305
mysqlfabric group add shard1 127.0.0.1:63306
mysqlfabric group promote shard1
mysqlfabric group activate shard1

# Create Sharding Group2
mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group add shard2 127.0.0.1:63308
mysqlfabric group add shard2 127.0.0.1:63309
mysqlfabric group promote shard2
mysqlfabric group activate shard2

# Add sharding to employee
mysqlfabric sharding add_table 1 test.employees emp_no
mysqlfabric sharding add_shard 1 shard1/1,shard2/300 --state=enabled

select_shard

それぞれのグループに分散されたテーブルからデータを読み込み
0 ~ 299は、shard1
300以上はshard2


# coding: utf-8

import mysql.connector 
from mysql.connector import fabric

import time

def find_employee(conn, emp_no):
    try:
       conn.set_property(tables=["test.employees"], key=str(emp_no), mode=fabric.MODE_READONLY, scope=fabric.SCOPE_LOCAL)
       cur = conn.cursor()
       cur.execute(
           "SELECT concat('Hostname:',@@hostname),concat(': ',@@port,' ; '), first_name, last_name "
           "FROM employees WHERE emp_no = %s", (emp_no,)
        )
    except mysql.connector.Error: 
       print "find_employee: Database connection error, trying to reconnect ..." 
       conn=connect() 
    for row in cur:
        print row[0],row[1],row[2],row[3]

# Address of the Fabric, not the host we are going to connect to.
def connect():
    try:
       conn=mysql.connector.connect(
          fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
          user="app", database="test", password="app",
          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()
for x in range(295, 310):
    find_employee(conn, x)


実行結果
メモ:それぞれのFabric Groupのスレーブから分散してデータを持ってきている。
select_shard_range


MySQLFabricはOpenStack Novaと連携可能なので、
選択肢の一つとしてProviderとしてOpenStackを登録してみた。

詳細はこちらの資料を参照下さい。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/CF4F217F7E733EF6066ED14D5CB7371C/CON5636_Kindahl-MySQLFabricElasticityOOW2014.pdf

Provider登録用のコマンド

[admin@Fabric01 ~]$ mysqlfabric help provider register
provider register provider_id username password url  [--tenant=NONE] 
[--provider_type=OPENSTACK] [--default_image=NONE] [--default_flavor=NONE] 
[--extra=NONE] [--synchronous]  
Register a provider.

登録したProviderからサーバーをProvisioning

[admin@Fabric01 ~]$ mysqlfabric help server create
server create provider_id  [--image=NONE] [--flavor=NONE] [--number_machines=1] 
[--availability_zone=NONE] [--key_name=NONE] [--security_groups=NONE] 
[--private_network=NONE] [--public_network=NONE] [--userdata=NONE] [--swap=NONE] 
[--scheduler_hints=NONE] [--meta=NONE] [--datastore=NONE] 
[--datastore_version=NONE] [--size=NONE] [--databases=NONE] [--users=NONE] 
[--configuration=NONE] [--security=NONE] [--skip_store] [--wait_spawning] 
[--synchronous]  
Create a virtual machine instance:

コマンドを利用するにはOpenStackのNova Clientなどをインストールしておく必要があります。

[root@Fabric02 admin]# yum install gcc
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.iij.ad.jp
 * extras: mirror.vastspace.net

[root@Fabric01 mysql]# yum install python-devel python-pip
読み込んだプラグイン:fastestmirror
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
base                                                                                                | 3.6 kB  00:00:00     extras                                                                                              | 3.4 kB  00:00:00     rpmforge                                                                                            | 1.9 kB  00:00:00     updates                                                                                             | 3.4 kB  00:00:00     (1/2): extras/7/x86_64/primary_db                                                                   |  43 kB  00:00:00     (2/2): updates/7/x86_64/primary_db                                                                  | 6.1 MB  00:00:04     Determining fastest mirrors
 * base: ftp.nara.wide.ad.jp
 * extras: ftp.nara.wide.ad.jp
 * rpmforge: ftp.kddilabs.jp
 * updates: ftp.nara.wide.ad.jp
パッケージ python-pip は利用できません。
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ python-devel.x86_64 0:2.7.5-16.el7 を インストール
--> 依存性解決を終了しました。

[root@Fabric01 mysql]# 

[root@Fabric01 mysql]# curl https://bitbucket.org/pypa/setuptools/raw/bootstrap/ez_setup.py -o - | python
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 10655  100 10655    0     0   4164      0  0:00:02  0:00:02 --:--:--  4163
Downloading https://pypi.python.org/packages/source/s/setuptools/setuptools-12.0.5.zip
Extracting in /tmp/tmp3TYzVX
Now working in /tmp/tmp3TYzVX/setuptools-12.0.5
Installing Setuptools
...
Finished processing dependencies for setuptools==12.0.5

[root@Fabric01 mysql]# 


[root@Fabric01 mysql]# curl -kL https://raw.github.com/pypa/pip/master/contrib/get-pip.py | python
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:--  0:00:01 --:--:--     0
100 1567k  100 1567k    0     0   539k      0  0:00:02  0:00:02 --:--:-- 2945k
Collecting pip
  Downloading pip-6.0.7-py2.py3-none-any.whl (1.3MB)
    100% |################################| 1.3MB 282kB/s 
Installing collected packages: pip

Successfully installed pip-6.0.7
[root@Fabric01 mysql]# 

[root@Fabric01 mysql]# pip install python-novaclient
Collecting python-novaclient
  Using cached python_novaclient-2.20.0-py2.py3-none-any.whl
Collecting oslo.utils>=1.0.0 (from python-novaclient)
...
Successfully installed Babel-1.3 PrettyTable-0.7.2 argparse-1.3.0 iso8601-0.1.10 netifaces-0.10.4 oslo.utils-1.2.1 pbr-0.10.7 python-keystoneclient-1.0.0 python-novaclient-2.20.0 requests-2.5.1 simplejson-3.6.5 six-1.9.0

[root@Fabric01 mysql]# 

[root@Fabric01 mysql]# pip install python-neutronclient
Collecting python-neutronclient
  Downloading python_neutronclient-2.3.10-py2.py3-none-any.whl (180kB)
    100% |################################| 184kB 1.7MB/s 
Requirement already satisfied (use --upgrade to upgrade): oslo.utils>=1.0.0 in /usr/lib/python2.7/site-packages (from python-neutronclient)
...
Successfully installed cliff-1.9.0 cmd2-0.6.8 pyparsing-2.0.3 python-neutronclient-2.3.10
[root@Fabric01 mysql]# 

インストールが完了したら、以下のコマンドでOpenStackをProviderとして登録。
※2015年2月現在で確認出来るProviderは,OpenStackのみが登録可能でした。

-bash-4.2$ mysqlfabric provider register my_stack my_user my_password http://8.21.28.222:5000/v2.0/--tenant=my_user_role --provider_type=OPENSTACK
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
7c9e3185-a664-42c9-94e8-e9179fc0bab8        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422565944.03 Triggered by <mysql.fabric.events.Event object at 0x2e21a10>.
    4       2 1422565944.04                        Executing action (_register_provider).
    5       2  1422565944.1                         Executed action (_register_provider).

listコマンドで登録したProviderを確認

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

provider_id      type username                                                url tenant default_image default_flavor extra
----------- --------- -------- -------------------------------------------------- ------ ------------- -------------- -----
   my_stack OPENSTACK  my_user http://8.21.28.222:5000/v2.0/--tenant=my_user_role   None          None           None  None


-bash-4.2$ 

問題無く登録出来たので、他にも付加出来る情報を加えて再登録

-bash-4.2$ mysqlfabric provider register mysqlfabric01 my_user my_password http://8.21.28.222:5000/v2.0/ --tenant=my_user_role --provider_type=OPENSTACK --default_image=mysql_img --default_flavor=nova_mysql01
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
2b66b1a7-fc9c-4d5f-8178-bf865b2ca863        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422566557.81 Triggered by <mysql.fabric.events.Event object at 0x2e21a10>.
    4       2 1422566557.82                        Executing action (_register_provider).
    5       2 1422566557.83                         Executed action (_register_provider).


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

  provider_id      type username                                                url       tenant default_image default_flavor extra
------------- --------- -------- -------------------------------------------------- ------------ ------------- -------------- -----
mysqlfabric01 OPENSTACK  my_user                      http://8.21.28.222:5000/v2.0/ my_user_role     mysql_img   nova_mysql01  None
     my_stack OPENSTACK  my_user http://8.21.28.222:5000/v2.0/--tenant=my_user_role         None          None           None  None


-bash-4.2$ 

古い方のProvider情報をunregisterコマンドで登録削除

-bash-4.2$ mysqlfabric provider unregister my_stack
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
bb497878-3a54-4bf4-8cc4-36e5606200fd        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422566754.92 Triggered by <mysql.fabric.events.Event object at 0x2e21a90>.
    4       2 1422566754.92                      Executing action (_unregister_provider).
    5       2 1422566754.95                       Executed action (_unregister_provider).


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

  provider_id      type username                           url       tenant default_image default_flavor extra
------------- --------- -------- ----------------------------- ------------ ------------- -------------- -----
mysqlfabric01 OPENSTACK  my_user http://8.21.28.222:5000/v2.0/ my_user_role     mysql_img   nova_mysql01  None


-bash-4.2$ 

Fab

OpenStack NovaのAPIの登録まではここで完了。
あとは、mysqlfabric server createコマンドでProvisioingしてMySQL Fabric Groupに追加してスケールアウト対応。

参照:http://docs.openstack.org/ja/user-guide/content/install_clients.html


SYS Schema on MySQLの再確認
SYS Schemaは元々ps_helperという名前だったが、Oracle、MS SQLと同じように
分かり易くする為にSYSにしたとの事。

The MySQL SYS Schema

インストール方法
以下の2つのうちどちらか選択
1) Workbench6.1以上~
2) GitからScriptをダウンロード

root@localhost [sys]>select * from sys.version;
+-------------+-------------------------------------------+
| sys_version | mysql_version                             |
+-------------+-------------------------------------------+
| 1.1.0       | 5.6.22-enterprise-commercial-advanced-log |
+-------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+-------------+-------+
| db  | object_type | count |
+-----+-------------+-------+
| sys | FUNCTION    |    11 |
| sys | VIEW        |    78 |
| sys | PROCEDURE   |    22 |
+-----+-------------+-------+
3 rows in set, 3 warnings (0.40 sec)

root@localhost [sys]>

x$はraw view (for tools to poll)
formatted viewはhumans and command lineの為に準備してある。(成形してある)

■■ user / Host Summary View
IO Usage
Stages
Statement Details

root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
                 user: background
           statements: NULL
    statement_latency: NULL
statement_avg_latency: 0 ps
          table_scans: NULL
             file_ios: 1728
      file_io_latency: 2.10 s
  current_connections: 18
    total_connections: 21
         unique_hosts: 0
*************************** 2. row ***************************
                 user: root
           statements: 90
    statement_latency: 1.37 s
statement_avg_latency: 15.22 ms
          table_scans: 6
             file_ios: 72340
      file_io_latency: 2.59 s
  current_connections: 1
    total_connections: 1
         unique_hosts: 1
2 rows in set (0.01 sec)

root@localhost [sys]>

■5.7では、メモリーの確認も出来る。

root@localhost [sys]>select * from sys.version;
+-------------+---------------------+
| sys_version | mysql_version       |
+-------------+---------------------+
| 1.3.0       | 5.7.5-labs-http-log |
+-------------+---------------------+
1 row in set (0.00 sec)

root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
                  user: root
            statements: 171
     statement_latency: 569.90 ms
 statement_avg_latency: 3.33 ms
           table_scans: 5
              file_ios: 1987
       file_io_latency: 00:03:05.67
   current_connections: 1
     total_connections: 1
          unique_hosts: 1
        current_memory: 0 bytes
total_memory_allocated: 0 bytes
*************************** 2. row ***************************
                  user: background
            statements: 0
     statement_latency: 0 ps
 statement_avg_latency: 0 ps
           table_scans: 0
              file_ios: 1940
       file_io_latency: 1.59 s
   current_connections: 21
     total_connections: 23
          unique_hosts: 0
        current_memory: 0 bytes <--- 5.7.5以上で追加された。
total_memory_allocated: 0 bytes <--- 5.7.5以上で追加された。
2 rows in set (0.01 sec)

root@localhost &#91;sys&#93;>

■ユーザ毎にIOを確認出来る

root@localhost [sys]>select * from user_summary_by_file_io_type;
+------------+--------------------------------------+-------+-----------+-------------+
| user       | event_name                           | total | latency   | max_latency |
+------------+--------------------------------------+-------+-----------+-------------+
| background | wait/io/file/innodb/innodb_data_file |   608 | 798.30 ms | 37.70 ms    |
| background | wait/io/file/sql/FRM                 |   910 | 789.77 ms | 195.80 ms   |
| background | wait/io/file/innodb/innodb_log_file  |    18 | 304.28 ms | 172.62 ms   |
| background | wait/io/file/sql/slow_log            |     4 | 65.20 ms  | 65.18 ms    |
| background | wait/io/file/sql/binlog              |    32 | 45.77 ms  | 19.03 ms    |
| background | wait/io/file/myisam/kfile            |    67 | 27.08 ms  | 7.97 ms     |
| background | wait/io/file/mysys/cnf               |     5 | 21.57 ms  | 21.51 ms    |
| background | wait/io/file/sql/binlog_index        |    15 | 20.14 ms  | 14.04 ms    |
| background | wait/io/file/mysys/charset           |     3 | 13.95 ms  | 13.87 ms    |
| background | wait/io/file/sql/ERRMSG              |     5 | 10.38 ms  | 7.82 ms     |
| background | wait/io/file/myisam/dfile            |    42 | 4.91 ms   | 1.40 ms     |
| background | wait/io/file/sql/query_log           |     4 | 1.30 ms   | 1.26 ms     |
| background | wait/io/file/sql/casetest            |    10 | 473.97 us | 275.72 us   |
| background | wait/io/file/sql/pid                 |     3 | 197.44 us | 167.97 us   |
| background | wait/io/file/sql/global_ddl_log      |     2 | 13.67 us  | 11.35 us    |
| root       | wait/io/file/csv/data                | 69443 | 1.35 s    | 277.94 ms   |
| root       | wait/io/file/csv/metadata            |    63 | 535.14 ms | 414.93 ms   |
| root       | wait/io/file/sql/FRM                 |   676 | 379.57 ms | 69.12 ms    |
| root       | wait/io/file/myisam/kfile            |  1458 | 188.04 ms | 49.70 ms    |
| root       | wait/io/file/myisam/dfile            |   532 | 137.41 ms | 66.26 ms    |
| root       | wait/io/file/sql/file_parser         |   156 | 3.52 ms   | 117.48 us   |
| root       | wait/io/file/sql/dbopt               |    16 | 494.32 us | 132.02 us   |
| root       | wait/io/file/innodb/innodb_data_file |     2 | 24.08 us  | 13.14 us    |
+------------+--------------------------------------+-------+-----------+-------------+
23 rows in set (0.00 sec)

root@localhost [sys]>

■STATEMENT_LATENCYなどを確認出来る。FULL_SCANなども確認可能。

root@localhost [sys]>select * from user_summary_by_statement_latency\G
*************************** 1. row ***************************
         user: root
        total: 92
total_latency: 1.37 s
  max_latency: 952.59 ms
 lock_latency: 934.38 ms
    rows_sent: 203
rows_examined: 2968
rows_affected: 0
   full_scans: 8
1 row in set (0.00 sec)

root@localhost [sys]>

■ステートメントのタイプ毎に状況が確認出来る

root@localhost [sys]>select * from user_summary_by_statement_type\G
*************************** 1. row ***************************
         user: root
    statement: select
        total: 10
total_latency: 839.20 ms
  max_latency: 766.46 ms
 lock_latency: 803.75 ms
    rows_sent: 36
rows_examined: 3133
rows_affected: 0
   full_scans: 5
*************************** 2. row ***************************
         user: root
    statement: Field List
        total: 78
total_latency: 463.95 ms
  max_latency: 114.47 ms
 lock_latency: 130.78 ms
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0

■IO処理とProcesslist_idが確認出来る。(Current)

root@localhost [sys]>select * from io_by_thread_by_latency\G
*************************** 1. row ***************************
          user: root@localhost
         total: 72370
 total_latency: 2.59 s
   min_latency: 4.68 ns
   avg_latency: 1.19 ms
   max_latency: 414.93 ms
     thread_id: 21
processlist_id: 1
*************************** 2. row ***************************
          user: main
         total: 1710
 total_latency: 1.85 s
   min_latency: 24.96 ns
   avg_latency: 2.71 ms
   max_latency: 195.80 ms
     thread_id: 1
processlist_id: NULL

■どのファイル処理に時間がかかっているか確認出来る

root@localhost [sys]>select * from io_global_by_file_by_latency limit 2\G
*************************** 1. row ***************************
         file: /usr/local/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/data/mysql/general_log.CSV
        total: 64611
total_latency: 1.21 s
   count_read: 32253
 read_latency: 1.14 s
  count_write: 101
write_latency: 1.18 ms
   count_misc: 32257
 misc_latency: 66.27 ms
*************************** 2. row ***************************
         file: /usr/local/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/data/ibdata1
        total: 532
total_latency: 706.08 ms
   count_read: 525
 read_latency: 696.01 ms
  count_write: 2
write_latency: 87.71 us
   count_misc: 5
 misc_latency: 9.98 ms
2 rows in set (0.00 sec)

root@localhost [sys]>

■■ Analysis Views
Object Overview
Table Usage
Index Usage
Show users only
schema_*

root@localhost [sys]>select * from schema_table_statistics limit 1\G
*************************** 1. row ***************************
     table_schema: mysql
       table_name: plugin
    total_latency: 0 ps
     rows_fetched: 0
    fetch_latency: 0 ps
    rows_inserted: 0
   insert_latency: 0 ps
     rows_updated: 0
   update_latency: 0 ps
     rows_deleted: 0
   delete_latency: 0 ps
 io_read_requests: 10     <---↓ IOの状況も確認可能
          io_read: 1.46 KiB
  io_read_latency: 33.01 ms
io_write_requests: 0
         io_write: 0 bytes
 io_write_latency: 0 ps
 io_misc_requests: 14
  io_misc_latency: 101.93 us
1 row in set (0.02 sec)

root@localhost &#91;sys&#93;>

root@localhost [sys]>select * from schema_index_statistics limit 1\G
*************************** 1. row ***************************
  table_schema: sys
    table_name: sys_config
    index_name: PRIMARY
 rows_selected: 0
select_latency: 0 ps
 rows_inserted: 0
insert_latency: 0 ps
  rows_updated: 0
update_latency: 0 ps
  rows_deleted: 0
delete_latency: 0 ps
1 row in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from schema_tables_with_full_table_scans;
+---------------+-------------+-------------------+
| object_schema | object_name | rows_full_scanned |
+---------------+-------------+-------------------+
| test          | T01_BKUP    |                 5 |
| test          | demo_test   |                 5 |
+---------------+-------------+-------------------+
2 rows in set (0.00 sec)

root@localhost [sys]>

InnoDBはインスタンス、MyISAM I/Oはtemporary table IOでの待ちを示している

root@localhost [sys]>select * from waits_global_by_latency;
+--------------------------------------+-------+---------------+-------------+-------------+
| events                               | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/csv/data                | 69530 | 1.35 s        | 19.37 us    | 277.94 ms   |
| wait/io/file/sql/FRM                 |  1664 | 1.17 s        | 704.45 us   | 195.80 ms   |
| wait/io/file/innodb/innodb_data_file |   625 | 855.73 ms     | 1.37 ms     | 37.70 ms    |
| wait/io/file/csv/metadata            |    63 | 535.14 ms     | 8.49 ms     | 414.93 ms   |
| wait/io/file/innodb/innodb_log_file  |    24 | 327.86 ms     | 13.66 ms    | 172.62 ms   |
| wait/io/file/myisam/kfile            |  1591 | 215.86 ms     | 135.68 us   | 49.70 ms    |
| wait/io/file/myisam/dfile            |   604 | 143.02 ms     | 236.79 us   | 66.26 ms    |
| wait/io/file/sql/slow_log            |    10 | 68.33 ms      | 6.83 ms     | 65.18 ms    |
| wait/io/file/sql/binlog              |    46 | 65.30 ms      | 1.42 ms     | 19.03 ms    |
| wait/io/table/sql/handler            |    11 | 30.13 ms      | 2.74 ms     | 29.94 ms    |
| wait/io/file/sql/binlog_index        |    31 | 29.84 ms      | 962.71 us   | 14.04 ms    |
| wait/io/file/mysys/cnf               |     5 | 21.57 ms      | 4.31 ms     | 21.51 ms    |
| wait/io/file/mysys/charset           |     3 | 13.95 ms      | 4.65 ms     | 13.87 ms    |
| wait/io/file/sql/ERRMSG              |     5 | 10.38 ms      | 2.08 ms     | 7.82 ms     |
| wait/io/file/sql/query_log           |    10 | 2.13 ms       | 213.12 us   | 1.26 ms     |
| wait/io/file/sql/file_parser         |    78 | 653.66 us     | 8.38 us     | 21.38 us    |
| wait/io/file/sql/dbopt               |    16 | 494.32 us     | 30.89 us    | 132.02 us   |
| wait/io/file/sql/casetest            |    10 | 473.97 us     | 47.40 us    | 275.72 us   |
| wait/io/file/sql/pid                 |     3 | 197.44 us     | 65.81 us    | 167.97 us   |
| wait/io/file/sql/global_ddl_log      |     2 | 13.67 us      | 6.84 us     | 11.35 us    |
| wait/lock/table/sql/handler          |     8 | 4.71 us       | 588.12 ns   | 1.13 us     |
+--------------------------------------+-------+---------------+-------------+-------------+
21 rows in set (0.01 sec)

root@localhost [sys]>

ユーザー毎のファイルIOが確認出来る

root@localhost [sys]>root@localhost [sys]>select * from waits_by_user_by_latency;
+------+--------------------------------------+-------+---------------+-------------+-------------+
| user | event                                | total | total_latency | avg_latency | max_latency |
+------+--------------------------------------+-------+---------------+-------------+-------------+
| root | wait/io/file/csv/data                | 69536 | 1.35 s        | 19.37 us    | 277.94 ms   |
| root | wait/io/file/csv/metadata            |    63 | 535.14 ms     | 8.49 ms     | 414.93 ms   |
| root | wait/io/file/sql/FRM                 |   676 | 379.57 ms     | 561.49 us   | 69.12 ms    |
| root | wait/io/file/myisam/kfile            |  1524 | 188.78 ms     | 123.87 us   | 49.70 ms    |
| root | wait/io/file/myisam/dfile            |   562 | 138.12 ms     | 245.76 us   | 66.26 ms    |
| root | wait/io/table/sql/handler            |    11 | 30.13 ms      | 2.74 ms     | 29.94 ms    |
| root | wait/io/file/innodb/innodb_data_file |    10 | 29.85 ms      | 2.99 ms     | 29.62 ms    |
| root | wait/io/file/sql/binlog              |    14 | 19.53 ms      | 1.40 ms     | 12.48 ms    |
| root | wait/io/file/sql/binlog_index        |    16 | 9.70 ms       | 606.37 us   | 6.12 ms     |
| root | wait/io/file/innodb/innodb_log_file  |     2 | 3.96 ms       | 1.98 ms     | 3.94 ms     |
| root | wait/io/file/sql/file_parser         |   156 | 3.52 ms       | 22.54 us    | 117.48 us   |
| root | wait/io/file/sql/slow_log            |     6 | 3.13 ms       | 521.60 us   | 3.05 ms     |
| root | wait/io/file/sql/query_log           |     6 | 830.11 us     | 138.35 us   | 793.00 us   |
| root | wait/io/file/sql/dbopt               |    16 | 494.32 us     | 30.89 us    | 132.02 us   |
| root | wait/lock/table/sql/handler          |     8 | 4.71 us       | 588.12 ns   | 1.13 us     |
+------+--------------------------------------+-------+---------------+-------------+-------------+
15 rows in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from statement_analysis limit 2\G
*************************** 1. row ***************************
            query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
               db: sys
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 3
    total_latency: 766.46 ms
      max_latency: 766.46 ms
      avg_latency: 766.46 ms
     lock_latency: 760.44 ms
        rows_sent: 3
    rows_sent_avg: 3
    rows_examined: 374
rows_examined_avg: 374
       tmp_tables: 189 <--- Temp Tableの利用が確認出来る
  tmp_disk_tables: 33  <--- Temp Disk Tableの利用が確認出来る
      rows_sorted: 22
sort_merge_passes: 0
           digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
       first_seen: 2015-02-07 22:01:36
        last_seen: 2015-02-07 22:01:36
*************************** 2. row ***************************
            query: SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ...
               db: sys
        full_scan: 
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 114.47 ms
      max_latency: 114.47 ms
      avg_latency: 114.47 ms
     lock_latency: 106.12 ms
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 0
rows_examined_avg: 0
       tmp_tables: 5
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 46f73fffb370a0c58fe74b1f0c71f85b
       first_seen: 2015-02-07 21:55:46
        last_seen: 2015-02-07 21:55:46
2 rows in set (0.00 sec)

root@localhost &#91;sys&#93;>

■SQLステートメントのエラー回数などが確認出来る

root@localhost [sys]>select * from statements_with_errors_or_warnings limit 3\G
*************************** 1. row ***************************
      query: `switch` 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:30:18
  last_seen: 2015-02-07 22:30:18
     digest: 1a34fb3cdd7b61d8f9a688cff4d8ef1b
*************************** 2. row ***************************
      query: FLUSH `logsl` 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:30:43
  last_seen: 2015-02-07 22:30:43
     digest: ff6a6729c6f04b36d5f57c0cf1bacfd0
*************************** 3. row ***************************
      query: SELECT * FROM `statemanet_analysis` LIMIT ? 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:42:18
  last_seen: 2015-02-07 22:42:18
     digest: 577ed5b5d68f1e1cad11c030187cc58c
3 rows in set (0.00 sec)

root@localhost [sys]>

■ SQL StatementのTemp Tableの利用状況が確認出来る。

root@localhost [sys]>select * from statements_with_temp_tables limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
                      db: sys
              exec_count: 1
           total_latency: 766.46 ms
       memory_tmp_tables: 189
         disk_tmp_tables: 33
avg_tmp_tables_per_query: 189
  tmp_tables_to_disk_pct: 17
              first_seen: 2015-02-07 22:01:36
               last_seen: 2015-02-07 22:01:36
                  digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
1 row in set (0.00 sec)

root@localhost [sys]>

■SQLステートメントのFULLスキャンが確認出来る。

root@localhost [sys]>select * from statements_with_full_table_scans limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
                      db: sys
              exec_count: 1
           total_latency: 766.46 ms
     no_index_used_count: 1
no_good_index_used_count: 0
       no_index_used_pct: 100
               rows_sent: 3
           rows_examined: 374
           rows_sent_avg: 3
       rows_examined_avg: 374
              first_seen: 2015-02-07 22:01:36
               last_seen: 2015-02-07 22:01:36
                  digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
1 row in set (0.00 sec)

root@localhost [sys]>

■SHOW PROCESSLISTより多くの情報を確認出来る。

root@localhost [sys]>select * from processlist limit 2\G
*************************** 1. row ***************************
                thd_id: 1
               conn_id: NULL
                  user: sql/main
                    db: NULL
               command: NULL
                 state: System lock
                  time: 3410
     current_statement: INTERNAL DDL LOG RECOVER IN PROGRESS
          lock_latency: NULL
         rows_examined: NULL
             rows_sent: NULL
         rows_affected: NULL
            tmp_tables: NULL
       tmp_disk_tables: NULL
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
*************************** 2. row ***************************
                thd_id: 21
               conn_id: 1
                  user: root@localhost
                    db: sys
               command: Query
                 state: Sending data
                  time: 0
     current_statement: select * from processlist limit 2
          lock_latency: 283.00 us
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 2
       tmp_disk_tables: 0
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
2 rows in set (0.00 sec)

root@localhost [sys]>

■ Functionsでデータを自動的に読みやすく変換してくれる。

root@localhost [sys]>select format_time(232222345) as time
    -> union select format_time(8323232323)
    -> union select format_time(8323232323234566);
+-----------+
| time      |
+-----------+
| 232.22 us |
| 8.32 ms   |
| 2.31h     |
+-----------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select format_bytes(23456) as bytes
    -> union select format_bytes(23456789);
+-----------+
| bytes     |
+-----------+
| 22.91 KiB |
| 22.37 MiB |
+-----------+
2 rows in set (0.00 sec)

root@localhost [sys]>

Theadの情報をDUMPする事が可能

root@localhost [sys]>call ps_trace_thread(1,'/tmp/stack_1.dot',60,0.1,true,true,true);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

+--------------------------------------------+
| Info                                       |
+--------------------------------------------+
| Data collection starting for THREAD_ID = 1 |
+--------------------------------------------+
+-----------------------------------------+
| Info                                    |
+-----------------------------------------+
| Stack trace written to /tmp/stack_1.dot |
+-----------------------------------------+
1 row in set (4.07 sec)

+------------------------------------------------+
| Convert to PDF                                 |
+------------------------------------------------+
| dot -Tpdf -o /tmp/stack_1.pdf /tmp/stack_1.dot |
+------------------------------------------------+
1 row in set (4.07 sec)

+------------------------------------------------+
| Convert to PNG                                 |
+------------------------------------------------+
| dot -Tpng -o /tmp/stack_1.png /tmp/stack_1.dot |
+------------------------------------------------+
1 row in set (4.07 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (4.19 sec)

Query OK, 0 rows affected (4.19 sec)

root@localhost [sys]>

——メモ: GraphやPDF化するには以下のソフトが必要—–
PDF作成

[root@misc admin]# dot -Tpdf -o /tmp/stack_2.pdf /tmp/stack_2.dot
[root@misc admin]# ls -l /tmp/
合計 12
srwxrwxrwx. 1 mysql mysql   0  2月  7 21:55 mysql.sock
-rw-rw-rw-. 1 mysql mysql 293  2月  7 23:15 stack_1.dot
-rw-rw-rw-. 1 mysql mysql 293  2月  7 23:21 stack_2.dot
-rw-r--r--. 1 root  root  968  2月  7 23:30 stack_2.pdf
[root@misc admin]# 

以下、インストールログ


[root@misc admin]# wget http://www.graphviz.org/graphviz-rhel.repo
--2015-02-07 23:26:22--  http://www.graphviz.org/graphviz-rhel.repo
www.graphviz.org (www.graphviz.org) をDNSに問いあわせています... 204.178.9.49
www.graphviz.org (www.graphviz.org)|204.178.9.49|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1138 (1.1K) [text/plain]
`graphviz-rhel.repo' に保存中

100%[==============================================================================================================>] 1,138       --.-K/s 時間 0.003s  

2015-02-07 23:26:25 (321 KB/s) - `graphviz-rhel.repo' へ保存完了 [1138/1138]

[root@misc admin]# ls -l
合計 320
drwx------. 4 root  root     92  1月 24 22:59 backup
drwx------. 4 root  root     92  1月 25 21:15 backup0125manual
-rwxr-xr-x. 1 root  root 299008  1月  5 20:32 cpanm
-rw-r--r--. 1 root  root   1138  2月 17  2012 graphviz-rhel.repo
-rwxr-xr-x. 1 root  root    642  1月  5 14:18 memcached_fast.pl
-rwxr-xr-x. 1 root  root    316  1月  5 21:03 memcached_mysql.pl
drwxrwxrwx. 4 mysql root     92  1月 24 23:37 mysqlbackup
-rw-r--r--. 1 root  root  12582  3月 21  2013 rpmforge-release-0.5.3-1.el5.rf.x86_64.rpm

[root@misc admin]# yum install 'graphviz*'
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.riken.jp
 * extras: ftp.riken.jp
 * rpmforge: ftp.kddilabs.jp
 * updates: ftp.riken.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ graphviz.x86_64 0:2.30.1-18.el7 を インストール
--> 依存性の処理をしています: urw-fonts のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: librsvg-2.so.2()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: libpng15.so.15()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: libpangoft2-1.0.so.0()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64


[root@misc admin]# rpm -qa |grep -i graphviz
graphviz-2.30.1-18.el7.x86_64
graphviz-perl-2.30.1-18.el7.x86_64
graphviz-ocaml-2.30.1-18.el7.x86_64
graphviz-gd-2.30.1-18.el7.x86_64
graphviz-php-2.30.1-18.el7.x86_64
graphviz-guile-2.30.1-18.el7.x86_64
graphviz-graphs-2.30.1-18.el7.x86_64
graphviz-devel-2.30.1-18.el7.x86_64
graphviz-lua-2.30.1-18.el7.x86_64
graphviz-ruby-2.30.1-18.el7.x86_64
graphviz-doc-2.30.1-18.el7.x86_64
graphviz-tcl-2.30.1-18.el7.x86_64
graphviz-python-2.30.1-18.el7.x86_64
graphviz-java-2.30.1-18.el7.x86_64
[root@misc admin]# 

Sys Schema for MySQL 5.6 and MySQL 5.7

http://wiki.ducca.org/wiki/graphviz_%E3%81%AE%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB


MySQL Fabricを利用していて、Node障害が発生した場合の対応手順確認。

■ FAULTYの状態からの回復手順
今回は、MySQLのProcessをkillコマンドで停止して疑似障害を起こしていたので、
再度起動しなおしてから以下の操作を行いました。

1) 現状確認


-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
------------------------------------ --------------- --------- ---------- ------
4594fd2a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63301    FAULTY READ_WRITE    1.0
46317949-aa84-11e4-a01a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
470a3117-aa84-11e4-a01a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4783b13a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

2) 起動した状態ではFAULTのままになっているので、先ずはSET_STATUSコマンドでSPAREの状態にします。
以下のオプションから選択可能
Status (STANDBY). Possible values are (0, ‘FAULTY’), (1, ‘SPARE’), (2, ‘SECONDARY’), (3, ‘PRIMARY’)


-bash-4.2$ mysqlfabric server set_status 4594fd2a-aa84-11e4-a01a-08002766cefe SPARE
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
75fdf4e5-b87b-4f91-be74-25bf6e01a663        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422845850.53 Triggered by <mysql.fabric.events.Event object at 0x345a990>.
    4       2 1422845850.54                        Executing action (_set_server_status).
    5       2  1422845851.0                         Executed action (_set_server_status).


-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
------------------------------------ --------------- --------- ---------- ------
4594fd2a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63301     SPARE    OFFLINE    1.0
46317949-aa84-11e4-a01a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
470a3117-aa84-11e4-a01a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4783b13a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 

spare

■OFFLINEですが、この状態で既にデータが同期されているので、SECONDARYにしてユーザーのリクエストを処理開始します。
  – ユーザーに提供する為に、SPAREからSECONDARYにステータスを変更。

-bash-4.2$ mysqlfabric server set_status 4594fd2a-aa84-11e4-a01a-08002766cefe SECONDARY
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
dc804a2e-86b5-4694-bdd6-ddb3e3dd9461        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1422846492.45 Triggered by <mysql.fabric.events.Event object at 0x345a990>.
    4       2 1422846492.46                        Executing action (_set_server_status).
    5       2 1422846492.46                         Executed action (_set_server_status).


-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
------------------------------------ --------------- --------- ---------- ------
4594fd2a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63301 SECONDARY  READ_ONLY    1.0
46317949-aa84-11e4-a01a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
470a3117-aa84-11e4-a01a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
4783b13a-aa84-11e4-a01a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$