InnoDB Clusterを構築すると、MySQL Routerの設定ファイルが自動的に作成されます。基本的にはTTLによって構成はキャッシュされますが、metadata cache componentが常にMySQLに接続していて、Group Replicationの状況をモニタリングしています。それにより、構成変更や障害発生時には自動的に検知してアプリケーションを適切なデータベースに振り分けてくれます。

【以下、マニュアル抜粋】
MySQL Router keeps a cached list of the online MySQL servers, or the topology and state of the configured InnoDB cluster. Initially, the list is loaded from Router’s configuration file when Router is started. This list was generated with InnoDB cluster servers when Router was bootstrapped using the –bootstrap option.

To keep the cache updated, the metadata cache component keeps an open connection to one of the InnoDB cluster servers that contains metadata. It does so by querying the metadata database and live state information from MySQL’s performance schema. The cluster metadata is changed whenever the InnoDB cluster is modified, such as adding or removing a MySQL server using the MySQL Shell, and the performance_schema tables are updated in real-time by the MySQL server’s Group Replication plugin whenever a cluster state change is detected. For example, if one of the MySQL servers unexpectedly exits.

When Router detects that a connected MySQL server crashes, for example because the metadata cache has lost its connection and can not connect again, it attempts to connect to a different MySQL server to fetch metadata and InnoDB cluster state from the new MySQL server.

Application connections to a MySQL server that crashes are automatically closed. They must then reconnect to Router, which redirects them to an online MySQL server.

参照: https://dev.mysql.com/doc/mysql-router/2.1/en/mysql-router-general-metadata.html

MySQL Router2.1から, MySQLへの常時接続の確認 (mysql_router5_212qj4063dmw)


mysql> show processlist;
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User                       | Host            | db    | Command | Time | State                                                  | Info             |
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
| 11 | system user                |                 | NULL  | Connect | 4600 | executing                                              | NULL             |
| 14 | system user                |                 | NULL  | Connect | 4600 | Slave has read all relay log; waiting for more updates | NULL             |
| 42 | root                       | localhost       | mysql | Query   |    0 | System lock                                            | show processlist |
| 43 | mysql_router5_212qj4063dmw | localhost:48721 | NULL  | Sleep   |   66 |                                                        | NULL             |
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.01 sec)

mysql> select * from general_log;
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
| event_time                 | user_host                    | thread_id | server_id | command_type | argument                               |
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
| 2017-06-09 14:34:32.995672 | root[root] @ localhost []    |        42 | 944686911 | Query        | select * from general_log              |
| 2017-06-09 14:34:37.233924 | root[root] @ localhost []    |        42 | 944686911 | Query        | show processlist                       |
| 2017-06-09 14:34:41.252563 | [root] @ localhost [::1]     |        46 | 944686911 | Connect      | root@localhost on  using SSL/TLS       |
| 2017-06-09 14:34:41.253181 | root[root] @ localhost [::1] |        46 | 944686911 | Query        | select @@version_comment limit 1       |
| 2017-06-09 14:34:41.253872 | root[root] @ localhost [::1] |        46 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port |
| 2017-06-09 14:34:41.257997 | root[root] @ localhost [::1] |        46 | 944686911 | Quit         |                                        |
| 2017-06-09 14:34:44.344733 | root[root] @ localhost []    |        42 | 944686911 | Query        | select * from general_log              |
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
7 rows in set (0.00 sec)


PRIMARY(書き込み)は固定され、SECONDARYは参照専用でラウンドロビンされている

-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ 


SECONDARYの一台を停止してみる


mysql-js> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.killSandboxInstance(3320)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be killed


Killing MySQL instance...

Instance localhost:3320 successfully killed.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> 


MySQL Router2.1がトポロジーの変更を認識し、以下のコマンドをmysql_router5_212qj4063dmw@localhostから実行している事が確認出来る

mysql> select * from general_log;
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time                 | user_host                                                                      | thread_id | server_id | command_type | argument                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2017-06-09 14:34:57.700152 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| 2017-06-09 14:35:39.294567 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Connect      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.294983 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Query        | SELECT @@GLOBAL.gtid_executed                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 2017-06-09 14:35:39.295129 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.299868 | _gr_user[_gr_user] @ localhost []                                              |        49 | 944686911 | Connect      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.300055 | _gr_user[_gr_user] @ localhost []                                              |        49 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:41.755412 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | show processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.021036 | [root] @ localhost [::1]                                                       |        50 | 944686911 | Connect      | root@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.021898 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Query        | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.022239 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:54.025850 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:54.939557 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        43 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:55.225193 | [mysql_router5_212qj4063dmw] @ localhost [127.0.0.1]                           |        51 | 944686911 | Connect      | mysql_router5_212qj4063dmw@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:55.227370 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = 'testCluster' |
| 2017-06-09 14:35:55.229489 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | show status like 'group_replication_primary_member'                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 2017-06-09 14:35:55.232809 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'                                                                                                                                                                                                                                                                                          |
| 2017-06-09 14:35:55.764665 | [root] @ localhost [::1]                                                       |        52 | 944686911 | Connect      | root@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:55.765502 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Query        | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:55.767062 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:55.770798 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:36:00.277880 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | show processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:36:04.168023 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
33 rows in set (0.00 sec)

MySQL Router 2.1が実行しているコマンド
このコマンドで、PRIMARYと現状のGroup Replicationの構成を認識している。

mysql> show status like 'group_replication_primary_member';      
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 1c008db5-4cca-11e7-be2d-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = 'testCluster';
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| replicaset_name | mysql_server_uuid                    | role | weight | version_token | location | I.addresses->>'$.mysqlClassic' | I.addresses->>'$.mysqlX' |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| default         | 1c008db5-4cca-11e7-be2d-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3310                 | localhost:33100          |
| default         | 4acfab6c-4cca-11e7-848b-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3320                 | localhost:33200          |
| default         | 7b6cb3c3-4cca-11e7-8af6-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3330                 | localhost:33300          |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
3 rows in set (0.01 sec)

mysql> SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier';
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
| member_id                            | member_host  | member_port | member_state | @@group_replication_single_primary_mode |
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
| 1c008db5-4cca-11e7-be2d-080027d65c57 | replications |        3310 | ONLINE       |                                       1 |
| 7b6cb3c3-4cca-11e7-8af6-080027d65c57 | replications |        3330 | ONLINE       |                                       1 |
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> 

ノード3320は停止しているので、MySQL Router2.1が稼働しているノードに適切に割り振っている

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ 

停止していたインスタンスを再稼働させる


-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.startSandboxInstance(3320);
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be started


Starting MySQL instance...

Instance localhost:3320 successfully started.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.rejoinInstance('localhost:3320')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Please provide the password for 'root@localhost:3320': 
Rejoining instance to the cluster ...

The instance 'root@localhost:3320' was successfully rejoined on the cluster.

The instance 'localhost:3320' was successfully added to the MySQL Cluster.
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> 

MySQL Routerも認識して再度バランシングに追加されている事が確認出来る


mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+
-bash-4.2$ 

詳細は、此方を確認下さい
https://dev.mysql.com/doc/mysql-router/2.1/en/

サポート (HA & RouterはEEにてサポート)
https://www.mysql.com/jp/support/

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Post Navigation