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.


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


Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 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...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more ...
Read more