Wednesday, January 29, 2020

Upgrading MySQL InnoDB Cluster with MySQL5.7.25 to MySQL 8.0.19


This is the tutorial and serves as a sample ONLY.  Every environment can be different.  It is a test trial for the Upgrade MySQL InnoDB Cluster with version 5.7.25 to MySQL 8.0.19 where the MySQL InnoDB Cluster Metadata has changed from V1 to V2 in 8.0.19.
1.      Assuming the following SETUP
           i.              MySQL Server 5.7.25
          ii.              MySQL Shell : 8.0.15
        iii.              MySQL Router : 8.0.15
2.      3 nodes are running on the same machine for this tutorial
           i.              Port : 3310, 3320 and 3330
          ii.              Hostname : primary   (or node1)
3.      MySQL InnoDB Cluster Status as follows
MySQL [primary ssl] JS> x.status({extended:2})
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "groupName": "8561210d-4278-11ea-907d-0800277b31d3",
        "name": "default",
        "primary": "primary:3310",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "node1:3320": {
                "address": "node1:3320",
                "memberId": "9cdacfc4-4277-11ea-8435-0800277b31d3",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "node1:3330": {
                "address": "node1:3330",
                "memberId": "a18eb74f-4277-11ea-8595-0800277b31d3",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "primary:3310": {
                "address": "primary:3310",
                "memberId": "98f0e702-4277-11ea-81f1-0800277b31d3",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "transactions": {
                    "checkedCount": 0,
                    "committedAllMembers": "8561210d-4278-11ea-907d-0800277b31d3:1-16,
98f0e702-4277-11ea-81f1-0800277b31d3:1-10",
                    "conflictsDetectedCount": 0,
                    "inQueueCount": 0,
                    "lastConflictFree": ""
                }
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "primary:3310"
}


Configuration File (MySQL) – my1.cnf / my2.cnf / my3.cnf
The initial configuration as shown in the first part of the ROW.  The second part was appended during the dba.configureLocalInstance(…) after the MySQL InnoDB Cluster creation.
my1.cnf
my2.cnf
my3.cnf
[mysqld]
datadir=/home/mysql/data/3310
basedir=/usr/local/mysql5725
log-error=/home/mysql/data/3310/my.error
port=3310
socket=/home/mysql/data/3310/my.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=101

# enable gtid
gtid-mode=on
enforce-gtid-consistency = ON
log-slave-updates = ON

# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE
# Extraction Algorithm
transaction-write-set-extraction=XXHASH64

report-host=primary
report_port = 3310

[mysqld]
datadir=/home/mysql/data/3320
basedir=/usr/local/mysql5725
log-error=/home/mysql/data/3320/my.error
port=3320
socket=/home/mysql/data/3320/my.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=102

# enable gtid
gtid-mode=on
enforce-gtid-consistency = ON
log-slave-updates = ON

# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE
# Extraction Algorithm
transaction-write-set-extraction=XXHASH64

report-host=primary
report_port = 3320
[mysqld]
datadir=/home/mysql/data/3330
basedir=/usr/local/mysql5725
log-error=/home/mysql/data/3330/my.error
port=3330
socket=/home/mysql/data/3330/my.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=103

# enable gtid
gtid-mode=on
enforce-gtid-consistency = ON
log-slave-updates = ON

# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE
# Extraction Algorithm
transaction-write-set-extraction=XXHASH64

report-host=primary
report_port = 3330
INSERTED after dba.configureLocalInstance( … )
INSERTED after dba.configureLocalInstance( … )
INSERTED after dba.configureLocalInstance( … )
auto_increment_increment = 1
auto_increment_offset = 2
group_replication_allow_local_disjoint_gtids_join = OFF
group_replication_allow_local_lower_version_join = OFF
group_replication_auto_increment_increment = 7
group_replication_bootstrap_group = OFF
group_replication_components_stop_timeout = 31536000
group_replication_compression_threshold = 1000000
group_replication_enforce_update_everywhere_checks = OFF
group_replication_exit_state_action = READ_ONLY
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
group_replication_flow_control_mode = QUOTA
group_replication_force_members =
group_replication_group_name = 8561210d-4278-11ea-907d-0800277b31d3
group_replication_group_seeds = node1:13320,node1:13330
group_replication_gtid_assignment_block_size = 1000000
group_replication_ip_whitelist = 192.168.56.0/24
group_replication_local_address = node1:13310
group_replication_member_weight = 80
group_replication_poll_spin_loops = 0
group_replication_recovery_complete_at = TRANSACTIONS_APPLIED
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 = 0
group_replication_unreachable_majority_timeout = 0
auto_increment_increment = 1
auto_increment_offset = 2
group_replication_allow_local_disjoint_gtids_join = OFF
group_replication_allow_local_lower_version_join = OFF
group_replication_auto_increment_increment = 7
group_replication_bootstrap_group = OFF
group_replication_components_stop_timeout = 31536000
group_replication_compression_threshold = 1000000
group_replication_enforce_update_everywhere_checks = OFF
group_replication_exit_state_action = READ_ONLY
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
group_replication_flow_control_mode = QUOTA
group_replication_force_members =
group_replication_group_name = 8561210d-4278-11ea-907d-0800277b31d3
group_replication_group_seeds = node1:13310,node1:13330
group_replication_gtid_assignment_block_size = 1000000
group_replication_ip_whitelist = 192.168.56.0/24
group_replication_local_address = node1:13320
group_replication_member_weight = 70
group_replication_poll_spin_loops = 0
group_replication_recovery_complete_at = TRANSACTIONS_APPLIED
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 = 0
group_replication_unreachable_majority_timeout = 0
auto_increment_increment = 1
auto_increment_offset = 2
group_replication_allow_local_disjoint_gtids_join = OFF
group_replication_allow_local_lower_version_join = OFF
group_replication_auto_increment_increment = 7
group_replication_bootstrap_group = OFF
group_replication_components_stop_timeout = 31536000
group_replication_compression_threshold = 1000000
group_replication_enforce_update_everywhere_checks = OFF
group_replication_exit_state_action = READ_ONLY
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
group_replication_flow_control_mode = QUOTA
group_replication_force_members =
group_replication_group_name = 575f951a-427c-11ea-83d7-0800277b31d3
group_replication_group_seeds = node1:13310,node1:13320
group_replication_gtid_assignment_block_size = 1000000
group_replication_ip_whitelist = 192.168.56.0/24
group_replication_local_address = node1:13330
group_replication_member_weight = 60
group_replication_poll_spin_loops = 0
group_replication_recovery_complete_at = TRANSACTIONS_APPLIED
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 = 0
group_replication_unreachable_majority_timeout = 0


MySQL Router (8.0.15)
mysql> select * from mysql_innodb_cluster_metadata.routers;
+-----------+-------------+---------+------------+
| router_id | router_name | host_id | attributes |
+-----------+-------------+---------+------------+
|         1 |             |       3 | NULL       |
+-----------+-------------+---------+------------+
1 row in set (0.00 sec)

mysql> select * from mysql_innodb_cluster_metadata.hosts;
+---------+----------------------+------------+-------------------+----------+------------------------------------+--------------------+
| host_id | host_name            | ip_address | public_ip_address | location | attributes                         | admin_user_account |
+---------+----------------------+------------+-------------------+----------+------------------------------------+--------------------+
|       1 | primary              |            | NULL              |          | NULL                               | NULL               |
|       2 | node1                |            | NULL              |          | NULL                               | NULL               |
|       3 | virtual-41.localhost | NULL       | NULL              |          | {"registeredFrom": "mysql-router"} | NULL               |
+---------+----------------------+------------+-------------------+----------+------------------------------------+--------------------+
3 rows in set (0.00 sec)

Upgrade Steps
1.      With the Running MySQL InnoDB Cluster 5.7.25 (MySQL Shell 8.0.15 and MySQL Router 8.0.15)
           i.              Upgrade the MySQL Router First with MySQL 8.0.19
          ii.              Upgrade the MySQL Server to MySQL 8.0.19 (But still using MySQL Shell 8.0.15)
        iii.              With MySQL Server 8.0.19 Running for ALL Servers as MySQL InnoDB Cluster (using Shell MySQL 8.0.15)
        iv.              Upgrade MetaData to V2 : MySQL Shell 8.0.19


MySQL Router 8.0.19 Upgrade
For Simplicity with this tutorial, the upgrade is simply to do the bootstrap process with MySQL Router 8.0.19 new binary.   The new ROUTER configuration folder is created and the router is started.
After the MySQL Router 8.0.19 upgrade, checking the tables with mysql_innodb_cluster_metadata.routers :
mysql> select * from mysql_innodb_cluster_metadata.routers;
+-----------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| router_id | router_name | host_id | attributes                                                                                                                                                      |
+-----------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|         1 |             |       3 | NULL                                                                                                                                                            |
|         2 |             |       4 | {"version": "8.0.19", "ROEndpoint": "6447", "RWEndpoint": "6446", "ROXEndpoint": "64470", "RWXEndpoint": "64460", "MetadataUser": "mysql_router2_z7jvrcps73jm"} |
+-----------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Extra Row is created with attribute : 8.0.19 information.
Whereas the user table has new User due to the MySQL Router Bootstrapping.   This tutorial does not provide any details with re-using existing router account or managing/removing the OLD  router account(s).
mysql> select user,host from mysql.user;
+----------------------------------+----------------------------+
| user                             | host                       |
+----------------------------------+----------------------------+
| gradmin                          | %                          |
| mysql_router1_7d0dklmkh3n8       | %                          |
| mysql_router2_z7jvrcps73jm       | %                          |
| mysql_innodb_cluster_r0431831317 | 192.168.56.0/255.255.255.0 |
| mysql_innodb_cluster_r0431832209 | 192.168.56.0/255.255.255.0 |
| mysql_innodb_cluster_r0431833234 | 192.168.56.0/255.255.255.0 |
| mysql.session                    | localhost                  |
| mysql.sys                        | localhost                  |
| root                             | localhost                  |
+----------------------------------+----------------------------+
9 rows in set (0.00 sec)
1          Upgrade MySQL 5.7.25 to MySQL 8.0.19 for Secondary Server
1.1         Shutdown MySQL Secondary Server
[mysql@virtual-41 mysql57]$ mysql -uroot -h127.0.0.1 -P3330
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2019, 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> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql>
1.2         Backup the my[1|2|3].cnf
[mysql@virtual-41 config]$ mkdir backup
[mysql@virtual-41 config]$ cp my3.cnf backup
[mysql@virtual-41 config]$ cp my2.cnf backup
[mysql@virtual-41 config]$ cp my1.cnf backup

1.3         Change the my?.cnf for the Secondary Server
1.3.1    COMMENT all variables with group_replication% prefix from the configuration file
Those variables should be converted into persisted variables

[mysql@virtual-41 config]$ mkdir 8.0
[mysql@virtual-41 config]$ sed   "s/^group_replication/#group_replication/g" my3.cnf > 8.0/my3.cnf
[mysql@virtual-41 config]$ sed   "s/^group_replication/#group_replication/g" my2.cnf > 8.0/my2.cnf
[mysql@virtual-41 config]$ sed   "s/^group_replication/#group_replication/g" my1.cnf > 8.0/my1.cnf

1.4         Change the 8.0/my?.cnf with basedir pointing to MySQL 8.0.19 (MySQL Home Directory) and make change to mysqlx-port and mysqlx-socket for MySQL 8.0.  The mysqlx port /socket has default value.  For my tutorial on single machine, server(s) cannot be started for the same port/socket.
1.4.1    For 8.0/my3.cnf as example :
basedir=/usr/local/mysql8019
mysqlx-port=33300
mysqlx-socket=/home/mysql/data/3330/myx.sock

1.5         Convert the group_replication% variables into persisted variables statements
[mysql@virtual-41 config]$ awk '/^group_replication/   { if ($NF != "=") print "set persist " $0 ";"}' my3.cnf > 8.0/my3.sql
[mysql@virtual-41 config]$ awk '/^group_replication/   { if ($NF != "=") print "set persist " $0 ";"}' my2.cnf > 8.0/my2.sql
[mysql@virtual-41 config]$ awk '/^group_replication/   { if ($NF != "=") print "set persist " $0 ";"}' my1.cnf > 8.0/my1.sql

1.6         Modify the SQL files to
1.6.1    comment the deprecated (or not used) variables (group_replication_allow_local_disjoint_gtids_join)
#set persist group_replication_allow_local_disjoint_gtids_join = OFF;

1.6.2    Change the group_replication_exit_state_action = OFFLINE_MODE only if needed.  OFFLINE_MODE is available in new MySQL 8.0.19.
1.6.3    Change the group_replication_auto_increment_increment = 1 (for SINGLE PRIMARY MODE)
1.6.4    Change the SQL file with those value as STRING with quotation mark
E.g
set persist group_replication_group_name = "575f951a-427c-11ea-83d7-0800277b31d3";

1.6.5    Modify or append variables which you may want.  The following variables serve as example ONLY.   You can use the SHELL to modify after upgrade.
(e.g.
group_replication_unreachable_majority_timeout =,
group_replication_member_expel_timeout=
group_replication_autorejoin_tries
set persist group_replication_consistency = "BEFORE_ON_PRIMARY_FAILOVER"
)


1.7         Start Up MySQL Secondary Server as Standalone Server using MySQL 8.0.19 Binary
1.7.1    Make sure the PATH with the New Binary from MySQL 8.0.19 bin to be used to start the Database.
1.7.2    The startup process should automatically upgrade the MySQL 5.7 to MySQL 8.0.19.  Because ALL group_replication% prefix variables are commented, the startup will not bring the server to rejoin the MySQL InnoDB Cluster. 
1.7.3    Check the error log for any errors.   (This is supposed to be taken care for MySQL 5.7 to MySQL 8.0 process)   This tutorial assumes the upgrade process is finished without any errors.  
1.7.3.1   If there is any error, RESTORED the MySQL Database as MySQL 5.7.  
1.7.3.2   MySQL 5.7 to MySQL 8.0 should be taken care with separate process.  Backup the original MySQL 5.7 and clone the database as separate database.   Upgrade to MySQL 8.0 and check carefully.  Until all errors are fixed, the MySQL 5.7.x InnoDB Cluster to MySQL 8.0.x InnoDB Cluster can be rolled out with the upgrade process. 
1.7.3.3   Sample Error Log file for my3.cnf after the startup and Upgrade to 8.0.19.  The Group Replication Plugin is started but there is no group_replication% prefix variables.  The server does not rejoin the InnoDB Cluster.
2020-01-30T03:15:20.657436Z 0 [System] [MY-010116] [Server] /usr/local/mysql8019/bin/mysqld (mysqld 8.0.19-commercial) starting as process 15406
2020-01-30T03:15:20.705770Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-01-30T03:15:24.115723Z 0 [ERROR] [MY-011685] [Repl] Plugin group_replication reported: 'The group name option is mandatory'
2020-01-30T03:15:24.116032Z 0 [ERROR] [MY-011660] [Repl] Plugin group_replication reported: 'Unable to start Group Replication on boot'
2020-01-30T03:15:29.025526Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2020-01-30T03:15:31.721829Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' started.
2020-01-30T03:15:54.090860Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' completed.
2020-01-30T03:15:54.422628Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-30T03:15:54.604614Z 0 [System] [MY-010931] [Server] /usr/local/mysql8019/bin/mysqld: ready for connections. Version: '8.0.19-commercial'  socket: '/home/mysql/data/3330/my.sock'  port: 3330  MySQL Enterprise Server - Commercial.

1.7.3.4   Execute the script file for persisted variables which it includes those group_replication variables.
[mysql@virtual-41 config]$ mysql -uroot -h127.0.0.1 -P3330 < my3.sql
1.7.3.5   Check the persisted variables imported
1.7.3.6   As example for my3 Secondary Server (at this moment the server is still a standalone server)
[mysql@virtual-41 config]$ mysql -uroot -h127.0.0.1 -P3330 -e "select * from performance_schema.persisted_variables;"
+----------------------------------------------------+--------------------------------------+
| VARIABLE_NAME                                      | VARIABLE_VALUE                       |
+----------------------------------------------------+--------------------------------------+
| group_replication_consistency                      | BEFORE_ON_PRIMARY_FAILOVER           |
| group_replication_allow_local_lower_version_join   | OFF                                  |
| group_replication_auto_increment_increment         | 1                                    |
| group_replication_autorejoin_tries                 | 288                                  |
| group_replication_bootstrap_group                  | OFF                                  |
| group_replication_components_stop_timeout          | 31536000                             |
| group_replication_compression_threshold            | 1000000                              |
| group_replication_enforce_update_everywhere_checks | OFF                                  |
| group_replication_exit_state_action                | OFFLINE_MODE                         |
| group_replication_flow_control_applier_threshold   | 25000                                |
| group_replication_flow_control_certifier_threshold | 25000                                |
| group_replication_flow_control_mode                | QUOTA                                |
| group_replication_group_name                       | 575f951a-427c-11ea-83d7-0800277b31d3 |
| group_replication_group_seeds                      | node1:13310,node1:13320              |
| group_replication_gtid_assignment_block_size       | 1000000                              |
| group_replication_ip_whitelist                     | 192.168.56.0/24                      |
| group_replication_local_address                    | node1:13330                          |
| group_replication_member_expel_timeout             | 120                                  |
| group_replication_member_weight                    | 60                                   |
| group_replication_poll_spin_loops                  | 0                                    |
| group_replication_recovery_complete_at             | TRANSACTIONS_APPLIED                 |
| group_replication_recovery_reconnect_interval      | 60                                   |
| group_replication_recovery_retry_count             | 10                                   |
| 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           | 0                                    |
| group_replication_unreachable_majority_timeout     | 120                                  |
+----------------------------------------------------+--------------------------------------+
[mysql@virtual-41 config]$

1.7.3.7   Restart the server which should rejoin the MySQL InnoDB Cluster.
1.7.3.8   Check the MySQL Server Error Log for any error
1.7.3.9   Connect with MySQL Shell 8.0.15 (At this time, the shell has not yet been upgraded) and Check the Cluster Status.
[mysql@virtual-41 config]$ mysqlsh
MySQL Shell 8.0.15-commercial

Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.

MySQL JS> \connect gradmin:grpass@primary:3310
Creating a session to 'gradmin@primary:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 112498
Server version: 5.7.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
No default schema selected; type \use <schema> to set one.

MySQL [primary ssl] JS> var x = dba.getCluster()
MySQL [primary ssl] JS> x.status()
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "primary:3310",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "node1:3320": {
                "address": "node1:3320",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "node1:3330": {
                "address": "node1:3330",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "primary:3310": {
                "address": "primary:3310",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "primary:3310"
}

MySQL [primary ssl] JS>
1.7.3.10            The Secondary Server (with port 3330 is ONLINE and rejoined)
1.7.4    Repeat the process for the 2nd Secondary Server and then finally the Primary Server.
1.7.5    After ALL servers are upgraded, and check the status again.  The R/W node Primary Server should be switched to another node.  For this tutorial with Weighting being set, the Primary Server should be on 3320 as follows :
[mysql@virtual-41 mysql80]$ mysqlsh
MySQL Shell 8.0.15-commercial

Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.

MySQL JS> \connect gradmin:grpass@primary:3320
Creating a session to 'gradmin@primary:3320'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 236
Server version: 8.0.19-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.

MySQL [primary ssl] JS> var x = dba.getCluster()
MySQL [primary ssl] JS> x.status()
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "node1:3320",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "node1:3320": {
                "address": "node1:3320",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "node1:3330": {
                "address": "node1:3330",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "primary:3310": {
                "address": "primary:3310",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "primary:3320"
}

MySQL [primary ssl] JS>

2          Upgrade MySQL InnoDB Cluster Meta Data to V2 and Using MySQL Shell 8.0.19
2.2         Please do GRANT Statements for the clusterAdmin User following the dba.upgradeMetadata({dryRun:true}) in MySQL Shell 8.0.19
MySQL [primary ssl] JS> dba.upgradeMetadata({dryRun:true})
ERROR: The account 'gradmin'@'%' is missing privileges required to manage an InnoDB cluster:
GRANT EXECUTE, SELECT ON *.* TO 'gradmin'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'gradmin'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'gradmin'@'%' WITH GRANT OPTION;
Dba.upgradeMetadata: The account 'gradmin'@'%' is missing privileges required for this operation. (RuntimeError)

2.2.1    Execute the GRANT statements as following the steps
2.2.2    You need to RUN extra GRANT statement :
mysql> grant SELECT  on performance_schema.global_variables to gradmin@'%' with grant option;
2.2.2.1   Or else you may get error during the upgrade of meta data.
Step 1 of 1: upgrading from 1.0.1 to 2.0.0...
ERROR: SELECT command denied to user 'gradmin'@'primary.localhost' for column 'variable_value' in table 'global_variables'
Dba.upgradeMetadata: Unable to detect Metadata version. Please check account privileges. (RuntimeError)

Once all is completed, the Servers are upgraded to New Version 8.0.19 together with meta data V2.



No comments:

Post a Comment