Upgrade MySQL InnoDB Cluster

Upgrade MySQL InnoDB Cluster 

Database upgrade are day-to-day activity which DBA perform on regular basic either due to EOS or application demand. In today blog , We will discuss about upgrading MySQL Inno DB.

Upgrade in case of the MySQL InnoDB cluster can be divided primarily into three steps:

1) Upgrade MyShell on all Cluster nodes.

2) Upgrade all the MySQL Routers configured with InnoDB Cluster.

3) Upgrade InnoDB metadata /Innodb metadata schema.

4) Upgrade Individual MySQL Server running on all Cluster nodes.

Below is the details about all above steps:

1) Upgrade MySQL Shell on all Cluster node or server used to manage Cluter:

cd /tmp
curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb

dpkg -i mysql-apt-config*
apt update
apt-get update
apt-get install mysql-shell

Repeate above steps on all the server having MySQL-SHELL.

Connect to Cluster using mysqlsh and run:

var cluster = dba.getCluster()
cluster.status()

cluster.listRouters()    ==> Find the list of Router.(Need for the next Step)

dba.upgradeMetadata({dryRun:true})  ==> Shows details if cluster admin lack privileges or any router needed to upgraded be upgraded before metadata upgrade

Provide Below grants to Cluster admin user:
GRANT CLONE_ADMIN, EXECUTE, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, SYSTEM_VARIABLES_ADMIN ON *.* TO <em>'admin'@'%'</em> 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 <em>'admin'@'%'</em> 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 <em>'admin'@'%'</em> WITH GRANT OPTION;

NOTE :  At the same time, Do all the prechecked against DB required when doing standalone upgrade.

2. Upgarde MySQL router on all the application servers:

2.1 Stop MySQL Router

2.2 Upgrade/Replace MySQL Router installation with version 8.0.19

cd /tmp
curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb

dpkg -i mysql-apt-config*

apt update
apt-get update
apt-get install mysql-router

2.3 Restart MySQL Router

2.4 MySQL Router 8.0.19 can work normally with the old version of the metadata. You can also bootstrap new instances of Router 8.0.19 against the
old metadata. Thus, there should be no problems in case the Shell and Metadata can’t be upgraded right after the Router.

3. Upgrade MySQL Innodb Meta data:

Once all routers are upgraded, the metadata can be upgraded.  Running with dryRun mode

var cluster = dba.getCluster()
cluster.status()
dba.upgradeMetadata({dryRun:true})

Upgarde metadata :
dba.upgradeMetadata()

4. Upgrade individual Server from 8.0.18 to 8.0.19

The upgrade should start with Secondary Server and the Primary Server should be upgrade as the Last Server.

By default, MySQL Server when it is configured as member node with MySQL Shell (<cluster>.addInstance...)  is started with
group_replication_start_on_boot=true.  This tells the server to join InnoDB Cluster when it is started.
During the upgrade process, it might worth to change this setting (group_replication_start_on_boot) to false so that
we can validate the UPGRADE before it rejoins the InnoDB Cluster.

Shutdown MySQL server instance using innodb_fast_shutdown option.

With a fast or slow shutdown, InnoDB leaves its undo logs and data files in a
state that can be dealt with in case of file format differences between releases.
Connect to secondry Server:

mysql>select * from performance_schema.persisted_variables where variable_name like 'group_replication_start_on_boot%';
mysql>set persist group_replication_start_on_boot=false;
mysql>SET GLOBAL innodb_fast_shutdown = 1;

4.1 .Upgarde individual server using our blog Upgrade MySQ Server .

4.2 Persist the group_replication_start_on_boot=true so that the server with New Version 8.0.19 can rejoin on next startup

mysql>set persist group_replication_start_on_boot=true;

Restart Mysql:

mysql > restart

After restrat check cluster status:

mysqlsh>var cluster = dba.getCluster()
mysqlsh>cluster.status()

You will see mixed version of server :

Repeat above steps for above other secondry server.

When the Primary Server is shutdown, the new Primary Server is elected where the 2 Servers have the server version of MySQL 8.0.19.
When the Server is upgraded and rejoined, it has the member role as Secondary.

you can learn more about MySql and InnoDB from below books:

MySQL Replication Simplified: Easy step-by-step examples to establish, troubleshoot and monitor replication

Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

MySQL Cookbook: Solutions for Database Developers and Administrators

Effective MySQL Backup and Recovery

=================================================================

You can also , Visit our Blogs for related topic on MySQL InnoDB Cluster:
1)MySQL InnoDB Cluster node Addition Issue: - Cluster.addInstance: : MySQL server has gone away (RuntimeError)
2)Install & Configure MySQL Router -  MySQL InnoDB Cluster

3)Restoring a Failed member in InnoDB Cluster