Upgrade from MySQL 5.7 to MySQL 8.0

MySQL server’s can be upgraded by performing either an INPLACE upgrade or LOGICAL upgrade. The Inplace upgrade involves shutting down the 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. The Logical upgrade involves exporting SQL from the MySQL 5.7 version using a backup or export utility such as mysqldump or mysqlpump, installing the MySQL 8.0 binaries, and then applying the SQL to the new MySQL version.MySQL_upgrade8

The Inplace upgrade is faster than the LOGICAL upgrade since it does not require loading of the databases after installing MySQL 8.0 version. Also while loading the databases during Logical upgrade, errors might be encountered due to the incompatibilities which would require modifying the exported SQL file.

Why upgrade MySQL installation?

  • Security concerns
  • Bug fixes
  • Performance and Scalability
  • New features
  • Reduce tech debt for installation ━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0)

What is new in version 8.0 ?

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

Preliminary checks:

  • Data types like old style decimals, old style varchar, old style TIME/DATETIME and TIMESTAMP types which became obsolete in MySQL 5.1, MySQL 5.0 and MySQL 5.6 respectively, that have persisted up until MySQL 5.7 due to the binary upgrade will not be supported in MySQL 8.0.  These tables can be identified by running CHECK TABLE…FOR UPGRADE or mysqlcheck with check-upgrade option in MySQL 5.7 prior to upgrade.

To check for these issues, execute this command:

mysqlcheck -u root -p --all-databases --check-upgrade
 mysql> check table 41_decimal for upgrade;
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
| Table           | Op    | Msg_type | Msg_text                                                                            |
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
| test.41_decimal | check | error    | Table upgrade required for `test`.`41_decimal`. Please dump/reload table to fix it! |
+-----------------+-------+----------+-------------------------------------------------------------------------------------+

Tables using such data types cannot be upgraded and should be fixed through REPAIR TABLE and dump/reload for old style varchar

  • There must be no partitioned tables that use a storage engine that does not have native partitioning support. To identify such tables, execute this query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';

Such tables should be altered to use an engine which supports native partitioning like InnoDB or the partitions should be removed.

ALTER TABLE <tablename> ENGINE = INNODB;
OR
ALTER TABLE <tablename> REMOVE PARTITIONING;
  • There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary. To identify tables with those names, execute this query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'check_constraints',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);

Any tables reported by the query must be renamed (use RENAME TABLE). This may also entail changes to applications that use the affected tables.

  • There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with too-long constraint names, execute this query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
  (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
               INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
   FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
   WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

These tables should be altered by dropping the constraint and adding the constraint with an explicit constraint name by ensuring the foreign key constraint name does not exceed 64 chars.

mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` DROP FOREIGN KEY `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк_ibfk_1`;
mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` ADD CONSTRAINT FOREIGN KEY FK1 (fld2) REFERENCES t1(fld1);
  • Prior to MySQL 8.0, users could create views with explicit column name up to 255 chars. To adhere to the maximum length of column name, views having explicit column name greater than 64 chars is not supported in MySQL 8.0. Currently these views can be identified only by doing a SHOW CREATE VIEW  in MySQL 5.7.
mysql> SHOW CREATE VIEW v1;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                  | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a123456789012345678901234567890123456789012345678901234567890123456789` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

These views should be altered to fix the column name prior to upgrade.

  • CREATE TRIGGER prior to MySQL 5.0.17 did not support definer attribute. Such trigger definitions with missing/empty definer  attribute or an invalid creation context (i.e character_set_client, collation_collection, database collation attributes) which have persisted until MySQL 5.7 cannot be upgraded. These triggers can be identified by running mysqlcheck with check-upgrade option or CHECK TABLE in MySQL 5.7.
mysqlcheck --user=root --socket=/data/mysql/mysql.sock --databases triggers --check-upgrade
mysql> select definer, trigger_name from INFORMATION_SCHEMA.TRIGGERS where definer='';

Such triggers should be dumped/reloaded to fix the issue.

  • Prior to MySQL 8.0, the total length of all enum elements could go up to approximately 64k since it was stored in 2 bytes in the FRM file.  Hence tables and stored procedures could be created with enum elements greater than 255 chars. However in MySQL 8.0, tables or stored procedures containing too long enum literals(i.e greater 255 chars) are not supported.
mysql> show create table t_long_enum;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_long_enum | CREATE TABLE `t_long_enum` (
  `i` int(11) NOT NULL,
  `b` enum('\n         1         2         3         4         5         6         7         8         9         0\n                                                                                                   0\n                                                                                                   0') DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Such tables or stored procedures should be altered before upgrading to MySQL 8.0

mysql> ALTER TABLE t_long_enum MODIFY b enum('\n         1         2         3         4\n');
  • Before upgrade, the MySQL 5.7 server must be shut down with innodb_fast_shutdown value set as 0 or 1. This will make sure that InnoDB redo logs are empty. Upgrading to MySQL 8.0 with MySQL 5.7 redo logs which are not empty will result in an error.
  • Upgrade from MySQL 5.7 which contains encrypted data files requires an additional step: ALTER INSTANCE ROTATE INNODB MASTER KEY should be executed to ensure it works well with replication.
  • The default/preferred authentication plugin has been changed to ‘caching_sha2_password’ since it provides more secure password encryption ‘mysql_native_password’ plugin and better performance than the ‘sha256_password’. This may cause some of the applications to throw errors related to ‘caching_sha2_password’ plugin after upgrade to MySQL 8.0. This maybe due to the fact the clients/connectors are yet to support ‘caching_sha2_password’ plugin.
  • Several of the defaults have been changed in MySQL 8.0. The most important change in defaults has been the change in the default value of ‘character_set_server’ and ‘character_set_database’ from ‘latin1’ to ‘utf8mb4’. Also the default value of ‘collation_server’ and ‘collation_database’ has been changed from ‘latin1_swedish_ci’ to ‘utf8mb4_0900_ai_ci’.

pic6.jpg

This blog post describes the simple steps to be followed for performing an INPLACE upgrade to MySQL 8.0.

Step 1 : Checking the upgrade preparedness using Upgrade Checker

The first step in upgrading to MySQL 8.0 is checking the upgrade preparedness of the existing MySQL 5.7 server. Upgrade Checker describes how simple it is to run the utility and check if there are any actions that needs to be taken prior to upgrade.We need to install MySQL shell from here

 mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();”

You can also specify connection options directly as function arguments – as with many others Shell’s functions,  the simplest form of which is an URI:

mysql-js> util.checkForServerUpgrade("root@localhost:3306");

pic1.JPG

The utility did not report any errors to be fixed. So I can proceed with the upgrade of the server to MySQL 8.0 by replacing the old binaries.

Step 2 : Take full backup of databases

  • Data is present in below path
     mysql> show global variables like 'datadir' ;
    +---------------+--------------+
    | Variable_name | Value        |
    +---------------+--------------+
    | datadir | /data/mysql/       |
    +---------------+--------------+
    1 row in set (0.00 sec)
  • Make sure that application is stopped and there are no connections hitting database using SHOW PROCESSLIST command.
  • 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.Also take backup of my.cnf file.
 mysqldump --all-databases  --single-transaction  --triggers --routines --events -hex-blob --port=3306 --user=root  --password=XXXXXXXX > mysqlfullbackup_dump.sql

Step 3 : Shutdown the MySQL Server

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.

 SET GLOBAL innodb_fast_shutdown = 1;

Shut down the MySQL server.

mysqladmin -u root -p shutdown

Step 4 : Remove the existing MySQL binaries

Create backup of my.cnf file

# cp /etc/my.cnf /etc/my.cnf_backup
# ls -la /etc/my.cnf_backup

Find the existing MySQL binaries

# rpm -qa | grep mysql
mysql-community-common-5.7.10-1.el7.x86_64
mysql-community-libs-compat-5.7.10-1.el7.x86_64
mysql-community-minimal-debuginfo-5.7.10-1.el7.x86_64
mysql-community-embedded-5.7.10-1.el7.x86_64
mysql-community-libs-5.7.10-1.el7.x86_64
mysql-community-devel-5.7.10-1.el7.x86_64
mysql-community-client-5.7.10-1.el7.x86_64
mysql-community-server-5.7.10-1.el7.x86_64
mysql-community-embedded-compat-5.7.10-1.el7.x86_64
mysql-community-embedded-devel-5.7.10-1.el7.x86_64

Remove the existing MySQL binaries

rpm -e --nodeps mysql-community-embedded-devel-5.7.10-1.el7.x86_64 
rpm -e --nodeps mysql-community-devel-5.7.10-1.el7.x86_64
rpm -e --nodeps mysql-community-embedded-5.7.10-1.el7.x86_64
rpm -e --nodeps mysql-community-embedded-compat-5.7.10-1.el7.x86_64
rpm -e --nodeps mysql-community-minimal-debuginfo-5.7.10-1.el7.x86_64
rpm -e --nodeps mysql-community-libs-compat-5.7.10-1.el7.x86_64 
rpm -e --nodeps mysql-community-server-5.7.10-1.el7.x86_64	
rpm -e --nodeps mysql-community-client-5.7.10-1.el7.x86_64
rpm -e --nodeps mysql-community-libs-5.7.10-1.el7.x86_64
rpm -e --nodeps mysql-community-common-5.7.10-1.el7.x86_64

Step 5 : Download Install MySQL 8.0 binaries

Download latest MySQL binary package from here 

# cd /home/ec2-user
# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.16-2.el7.x86_64.rpm-bundle.tar
# tar -xvf mysql-8.0.16-2.el7.x86_64.rpm-bundle.tar

If we need the archived versions click here

Install the rpm packages

rpm -ivh mysql-community-common-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-compat-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.16-2.el7.x86_64.rpm
rpm -ivh mysql-community-test-8.0.16-2.el7.x86_64.rpm

If there are failed dependency packages, we can find the required binaries using yum provides command. Please find the example below

yum provides '*/libncurses.so.5'

Verify the packages installed

pic2

Step 6 : Start MySQL instance with new binaries

Swap the existing my.cnf with old my.cnf file which we have taken backup before upgrade.

# cd /etc
# ls -la my.cnf
-rw-r--r-- 1 root root 1243 May  2 21:11 my.cnf
# mv /etc/my.cnf /etc/my.cnf_old
# mv /etc/my.cnf_backup /etc/my.cnf

Start the MySQL instance

 sudo service mysqld start

Verify the server version

pic3.jpg

Check the log file to ensure there are no errors reported.

pic7

Step 7 : Run mysql_upgrade utility

mysql_upgrade checks for all tables in all databases for incompatibilities with the current version of MySQL Server, it also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

Note : mysql_upgrade is essential only for versions before 8.0.16

mysql_upgrade  --socket=/data/mysql/mysql.sock --port=3306 --user=root

As of 8.0.16, the mysql_upgrade binary is deprecated, but its functionality is moved into the server. Let’s call this functionality the “server upgrade”. This is added alongside the Data Dictionary upgrade (DD Upgrade), which is a process to update the data dictionary table definitions.

pic4.JPG

Step 8 : Verify the database connections and applications

Since we have already started MySQL in previous steps, all that needs to be done is to verify that our sites load and work correctly.

pic 5.JPG

This marks the completion of the INPLACE upgrade process. If the upgrade fails due to some errors, the server reverts all changes to the data directory. An attempt to upgrade again to MySQL 8.0 involves:

  • If any redo logs are present in the data directory, remove all of them.
  • Start the MySQL 5.7 server on the same data directory.
  • Fix all the errors that were reported during upgrade.
  • Perform an INPLACE upgrade to MySQL 8.0 again.

Reference :

https://mysqlserverteam.com/mysql-8-0-16-mysql_upgrade-is-going-away/

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: