To upgrade MySQL versions we can use below method:-
- 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.
- 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.
Upgrading MySQL :- https://dev.mysql.com/doc/refman/8.0/en/upgrading.html
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.
More info can be found here:-https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
- The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7.
- 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.
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
Shutdown old mysql server
mysqladmin -uroot -p shutdown
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
Install MySQL fresh binaries
rpm -Uhv /opt/packages/mysql/*.rpm
Here-/opt/packages are my location of MySQL Enterprise Binaries
Start MySQL latest version with existing data directory
mysqld --user=root --datadir=/path/to/existing-data-directory
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_upgrademysql_upgrade -uroot -pStep 9:-Shutdown and restart the MySQL Server to ensure that any changes made to system tables takes effect.mysqladmin -uroot -p shutdownmysqld --user=root --datadir=/path/to/existing-data-directoryStep 10:-Login to MySQL latest versionmysql -uroot -pStep 11 :-This Marks Completion of INPLACE upgrade Process.If the upgrade fails due to some errors, the server revert all changes to data directory.CONCLUSION:-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 IndexWhat's New in MySQL 8.0 can be avail in documentation page:-https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html Disclaimer:This is my personal blog. The views expressed on these pages are mine alone and not those of my Current Employer.