Saturday, May 16, 2020

Step by Step INPLACE Upgrade from MySQL 5.7 to MySQL 8.0.13 using Upgrade Checker


To upgrade MySQL versions  we can use below method:-
  1. INPLACE Upgrade :- It involves shutting down MySQL 5.7  server,replacing the old binaries  with MySQL 8.0 binaries and then starting the MySQL 8.0 server on the old data directory. 
  2. LOGICAL Upgrade:- It involves exporting SQL from MySQL 5.7 version using backup utility such as mysqldump or mysqlpump , installing the MySQL 8.0 binaries and then applying the SQL to the new version.

Before start upgrading the MySQL versions it is recommended to have deeper analysis by visiting MySQL Documentation page.
What is MySQL Upgrade Checker?
Utility to check MySQL 5.7 server instances for compatibility errors and issues for upgrading. there is function called , util.checkForServerUpdate() is an upgrade checker utility that enables you to verify whether MySQL Server instances are ready for upgrade.
  1. The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7.
  2. MySQL Server only supports upgrade between GA releases. Upgrades from non-GA releases or MySQL 5.7 or 8.0 are not supported.

Let's try Upgrade Process Step by Step:-
I am going to demonstrate my environment which is running with MySQL Enterprise Edition 5.7.19. wanted to upgrade with latest MySQL Enterprise Edition 8.0.13 version.
In case, if users wanted to know about what is MySQL Enterprise Edition, please visit below link to know more about features & value of it:-

At this point, take a backup of the data directory using a backup or export utility such as  mysqldump or mysqlpump
It is important to protect your data by creating a backup of your current databases which should include the mysql system database and log files.
Step 3:-
Go to MySQL Prompt and execute any of below commands to shutdown innodb.
SET GLOBAL innodb_fast_shutdown = 0; ~ slow shutdown
SET  GLOBAL innodb_fast_shutdown = 1; ~fast shutdown
Step 4:- 
Shutdown old mysql server
mysqladmin -uroot -p  shutdown 

Step 5:- 
Uninstall OLD MySQL Binaries
Get the list of mysql binaries to uninstall
yum list installed | grep -i MySQL

yum erase mysql-commercial-client.x86_64
yum erase mysql-commercial-common.x86_64
yum erase mysql-commercial-libs.x86_64
yum erase mysql-commercial-libs-compat.x86_64 
yum erase mysql-commercial-server.x86_64

Step 6:- 
Install MySQL fresh binaries
rpm -Uhv /opt/packages/mysql/*.rpm
Here-/opt/packages  are my location of MySQL Enterprise Binaries
Step 7:- 
Start MySQL latest version with existing data directory
mysqld --user=root --datadir=/path/to/existing-data-directory
Step 8:-
After MySQL8.0 successfully starts then, execute mysql_upgrade command, this will examines all tables in all databases for incompatibilities with current version of MySQL.
It makes any remaining changes required in the mysql system database between MySQL 5.7 and MySQL 8.0,so that you can take advantage of new privileges or capabilities.
mysql_upgrade also brings performance_schema,information_schema,sys schema objects up to date for MySQL 8.0. 
Below is command for mysql_upgrade
mysql_upgrade -uroot -p
Step 9:-
Shutdown and restart the MySQL Server to ensure that any changes made to system tables takes effect.
mysqladmin -uroot -p shutdown
mysqld --user=root --datadir=/path/to/existing-data-directory
Step 10:-
Login to MySQL latest version
mysql -uroot -p
Step 11 :-
This Marks Completion of INPLACE upgrade Process.
If the upgrade fails due to some errors, the server revert all changes to data directory.
MySQL 8.0 has come with new features including-
  • MySQL Document Store-SQL + NoSQL
  • Atomic DDL Support
  • CTE + Analytics functions
  • Security and Account Management
  • Invisible Index
What's New in MySQL 8.0 can be avail in documentation page:-  Disclaimer:This is my personal blog. The views expressed on these pages are mine alone and not those of my Current Employer.

No comments:

Post a Comment