Description:
The MIC AdminAPI properly configures a cluster and creates the metadata schema, however, it does not persist any configuration changes in my.cnf at all.
Why would I want to use MySQL InnoDB Cluster if it is not configuring my.cnf variables (or with SET PERSIST in mysql 8.0) for me and I have to do them manually myself? What's the point?
[rant]
Other than having a metadata schema, which is mostly only useful for mysql router at this moment (which I wouldn't recommend using anyway), why would I use MIC?
[/rant]
How to repeat:
mysql> show global variables like 'grou%';
+-----------------------------------------------------+--------------------------------------+
| Variable_name | Value |
+-----------------------------------------------------+--------------------------------------+
| group_concat_max_len | 1024 |
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_bootstrap_group | OFF |
| group_replication_communication_debug_options | GCS_DEBUG_NONE |
| group_replication_components_stop_timeout | 31536000 |
| group_replication_compression_threshold | 1000000 |
| group_replication_enforce_update_everywhere_checks | OFF |
| group_replication_flow_control_applier_threshold | 25000 |
| group_replication_flow_control_certifier_threshold | 25000 |
| group_replication_flow_control_hold_percent | 10 |
| group_replication_flow_control_max_quota | 0 |
| group_replication_flow_control_member_quota_percent | 0 |
| group_replication_flow_control_min_quota | 0 |
| group_replication_flow_control_min_recovery_quota | 0 |
| group_replication_flow_control_mode | QUOTA |
| group_replication_flow_control_period | 1 |
| group_replication_flow_control_release_percent | 50 |
| group_replication_force_members | |
| group_replication_group_name | 40edb723-3f7b-11e8-b900-08002789cd2e |
| group_replication_group_seeds | 192.168.70.2:13306 |
| group_replication_gtid_assignment_block_size | 1000000 |
| group_replication_ip_whitelist | AUTOMATIC |
| group_replication_local_address | 192.168.70.3:13306 |
| group_replication_member_weight | 50 |
| group_replication_poll_spin_loops | 0 |
| group_replication_recovery_complete_at | TRANSACTIONS_APPLIED |
| group_replication_recovery_get_public_key | OFF |
| group_replication_recovery_public_key_path | |
| group_replication_recovery_reconnect_interval | 60 |
| group_replication_recovery_retry_count | 10 |
| group_replication_recovery_ssl_ca | |
| group_replication_recovery_ssl_capath | |
| group_replication_recovery_ssl_cert | |
| group_replication_recovery_ssl_cipher | |
| group_replication_recovery_ssl_crl | |
| group_replication_recovery_ssl_crlpath | |
| group_replication_recovery_ssl_key | |
| group_replication_recovery_ssl_verify_server_cert | OFF |
| group_replication_recovery_use_ssl | ON |
| group_replication_single_primary_mode | ON |
| group_replication_ssl_mode | REQUIRED |
| group_replication_start_on_boot | ON |
| group_replication_transaction_size_limit | 150000000 |
| group_replication_unreachable_majority_timeout | 0 |
+-----------------------------------------------------+--------------------------------------+
45 rows in set (0.00 sec)
mysql> ^DBye
[vagrant@node2 ~]$ sudo systemctl stop mysqld
2018-04-14T02:05:51.976008Z 0 [Warning] [MY-010909] /usr/sbin/mysqld: Forcing close of thread 57 user: 'root'.
2018-04-14T02:05:51.987247Z 0 [Warning] [MY-010909] /usr/sbin/mysqld: Forcing close of thread 18 user: 'root'.
2018-04-14T02:05:51.998199Z 0 [Warning] [MY-010909] /usr/sbin/mysqld: Forcing close of thread 27 user: 'root'.
2018-04-14T02:05:52.009046Z 0 [Warning] [MY-010909] /usr/sbin/mysqld: Forcing close of thread 61 user: 'root'.
2018-04-14T02:05:52.009197Z 0 [Warning] [MY-010909] /usr/sbin/mysqld: Forcing close of thread 46 user: 'root'.
2018-04-14T02:05:56.320430Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:05:57.320929Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:05:58.321336Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:05:59.321980Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:06:00.322945Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:06:01.323634Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:06:02.324252Z 33 [ERROR] [MY-011254] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine.'
2018-04-14T02:06:09.282983Z 0 [System] [MY-010910] /usr/sbin/mysqld: Shutdown complete.
[vagrant@node2 ~]$ sudo systemctl start mysqld
2018-04-14T02:06:12.996196Z 0 [Warning] [MY-010139] Changed limits: max_open_files: 5000 (requested 8161)
2018-04-14T02:06:12.996648Z 0 [Warning] [MY-010142] Changed limits: table_open_cache: 2419 (requested 4000)
2018-04-14T02:06:13.177894Z 0 [System] [MY-010116] /usr/sbin/mysqld (mysqld 8.0.4-rc-log) starting as process 8634 ...
2018-04-14T02:06:13.897014Z 1 [Warning] [MY-010005] Skip re-populating collations and character sets tables in read-only mode.
2018-04-14T02:06:13.902183Z 0 [ERROR] [MY-011254] Plugin group_replication reported: 'The group name option is mandatory'
2018-04-14T02:06:13.902210Z 0 [ERROR] [MY-011254] Plugin group_replication reported: 'Unable to start Group Replication on boot'
2018-04-14T02:06:13.976013Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-04-14T02:06:13.993909Z 0 [Warning] [MY-010604] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=node2-relay-bin' to avoid this problem.
2018-04-14T02:06:14.006024Z 0 [System] [MY-010931] /usr/sbin/mysqld: ready for connections. Version: '8.0.4-rc-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL).
[vagrant@node2 ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.4-rc-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 'grou%';
+-----------------------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------------------+----------------------+
| group_concat_max_len | 1024 |
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_bootstrap_group | OFF |
| group_replication_communication_debug_options | GCS_DEBUG_NONE |
| group_replication_components_stop_timeout | 31536000 |
| group_replication_compression_threshold | 1000000 |
| group_replication_enforce_update_everywhere_checks | OFF |
| group_replication_flow_control_applier_threshold | 25000 |
| group_replication_flow_control_certifier_threshold | 25000 |
| group_replication_flow_control_hold_percent | 10 |
| group_replication_flow_control_max_quota | 0 |
| group_replication_flow_control_member_quota_percent | 0 |
| group_replication_flow_control_min_quota | 0 |
| group_replication_flow_control_min_recovery_quota | 0 |
| group_replication_flow_control_mode | QUOTA |
| group_replication_flow_control_period | 1 |
| group_replication_flow_control_release_percent | 50 |
| group_replication_force_members | |
| group_replication_group_name | |
| group_replication_group_seeds | |
| group_replication_gtid_assignment_block_size | 1000000 |
| group_replication_ip_whitelist | AUTOMATIC |
| group_replication_local_address | |
| group_replication_member_weight | 50 |
| group_replication_poll_spin_loops | 0 |
| group_replication_recovery_complete_at | TRANSACTIONS_APPLIED |
| group_replication_recovery_get_public_key | OFF |
| group_replication_recovery_public_key_path | |
| group_replication_recovery_reconnect_interval | 60 |
| group_replication_recovery_retry_count | 10 |
| group_replication_recovery_ssl_ca | |
| group_replication_recovery_ssl_capath | |
| group_replication_recovery_ssl_cert | |
| group_replication_recovery_ssl_cipher | |
| group_replication_recovery_ssl_crl | |
| group_replication_recovery_ssl_crlpath | |
| group_replication_recovery_ssl_key | |
| group_replication_recovery_ssl_verify_server_cert | OFF |
| group_replication_recovery_use_ssl | OFF |
| group_replication_single_primary_mode | ON |
| group_replication_ssl_mode | DISABLED |
| group_replication_start_on_boot | ON |
| group_replication_transaction_size_limit | 150000000 |
| group_replication_unreachable_majority_timeout | 0 |
+-----------------------------------------------------+----------------------+
45 rows in set (0.01 sec)
Suggested fix:
I would like to see MySQL InnoDB Cluster use 'SET PERSIST' for the configuration of the group replication variables as needed, that at least solves this usability issue with MySQL 8.0
(This is slightly related to https://bugs.mysql.com/bug.php?id=90438)
