Upgrading to MySQL 8.0

MySQL 8 uses a global data dictionary containing information about database objects in transactional tables. In previous versions, the dictionary data was stored in metadata files and non-transactional system tables. You need to upgrade your data directory  from the file-based structure to the data-dictionary structure.

Just like a downgrade, you can upgrade using two methods:

• In-place upgrade
• Logical upgrade

You also should check a few prerequisites before the upgrade.

Getting ready

1. Check for obsolete datatypes or triggers that have a missing or empty definer or an invalid creation context:

shell> sudo mysqlcheck -u root -p --all-databases --check-upgrade

2. There must be no partitioned tables that use a storage engine that does not have native partitioning support. To identify these tables, execute this query:

shell> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

If there are any of these tables, change them to InnoDB:

mysql> ALTER TABLE table_name ENGINE = INNODB;

Or remove the partitioning:

mysql> ALTER TABLE table_name REMOVE PARTITIONING;

3. 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:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql' and LOWER(TABLE_NAME) IN ('catalogs', 'character_sets', 'collations', 'column_type_elements', 'columns', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'routines', 'schemata', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'version', 'view_routine_usage', 'view_table_usage');

4. There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with constraint names that are too long, execute this query:

mysql> SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE LENGTH(CONSTRAINT_NAME) > 64;

5. Tables not supported by MySQL 8.0, such as ndb, should be moved to InnoDB:

mysql> ALTER TABLE tablename ENGINE=InnoDB;

How to do it...

Just like the previous recipe, the following subsections will take you through the details with various systems, bundles, and so on. 

In-place upgrades

Here is an outline of the steps:

1. Shut down the old MySQL version.
2. Replace the old MySQL binaries or packages with the new ones (detailed steps for different types of installation methods are covered).
3. Restart MySQL on the existing data directory.
4. Run the mysql_upgrade utility.
5. In the MySQL 5.7 server, if there are encrypted InnoDB tablespaces, rotate the keyring master key by executing this statement:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

Here are the detailed steps:

1. Configure your MySQL 5.7 server to perform a slow shutdown. With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that the undo logs are empty and the data files are fully prepared in case of file format differences between releases.
   This step is the most important because, without it, you will end up with the following error:

[ERROR] InnoDB: Upgrade after a crash is not supported. 

This redo log was created with MySQL 5.7.18. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

2. Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.

Upgrade the MySQL binaries or packages.

YUM-based systems

1. Switch the repositories:

shell> sudo yum-config-manager --disable mysql57-community
shell> sudo yum-config-manager --enable mysql80-community

2. Verify that mysql80-community is enabled:

shell> sudo yum repolist all | grep mysql8
mysql80-community/x86_64             MySQL 8.0 Community Server  enabled:     16
mysql80-community-source             MySQL 8.0 Community Server  disabled

3. Run the yum update:

shell> sudo yum update mysql-server

APT-based systems

1. Reconfigure the apt to switch to MySQl 8.0:

shell> sudo dpkg-reconfigure mysql-apt-config

2. Run apt-get update:

shell> sudo apt-get update

3. Remove the current version:

shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -y
shell> sudo apt-get autoremove

4. Install MySQL 8:

shell> sudo apt-get update
shell> sudo apt-get install mysql-server
shell> sudo apt-get install libmysqlclient21

Using RPM or APT bundles

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from MySQL Downloads (refer to the Installing MySQL 8.0 using RPM or DEB files section).

Using Generic Binaries

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux using generic binaries section).

Start the MySQL 8.0 server (refer to the Starting or Stopping MySQL 8 to start MySQL section). If there are encrypted InnoDB tablespaces, use the --early-plugin-load option to load the keyring plugin.

The server automatically detects whether data dictionary tables are present. If not, the server creates them in the data directory , populates them with metadata, and then proceeds with its normal startup sequence. During this process, the server upgrades metadata for all database objects, including databases, tablespaces, system and user tables, views, and stored programs (stored procedures and functions, triggers, event scheduler events). The server also removes files that previously were used for metadata storage. For example, after upgrading, you will notice that your tables no longer have .frm files.

The server creates a directory named backup_metadata_57 and moves the files used by MySQL 5.7 into it. The server renames the event and proc tables to event_backup_57 and proc_backup_57. If this upgrade fails, the server reverts all changes to the data directory. In this case, you should remove all redo log files, start your MySQL 5.7 server on the same data directory, and fix the cause of any errors. Then, perform another slow shutdown of the 5.7 server and start the MySQL 8.0 server to try again.

Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

mysql_upgrade examines all tables in all databases for incompatibilities with the 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 the performance schema, INFORMATION_SCHEMA, and sys schema objects up to date for MySQL 8.0.

Restart the MySQL server (refer to the Starting or Stopping MySQL 8 to start MySQL section).

Logical Upgrades

Here is an outline of the steps:

1. Export existing data from the old MySQL version using mysqldump
2. Install the new MySQL version
3. Load the dump file into the new MySQL version
4. Run the mysql_upgrade utility

Here are the detailed steps:

1. You need to take a logical backup of the database (refer to Chapter 7, Backups for a quicker backup called mydumper):

shell> mysqldump -u root -p --add-drop-table --routines --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > data-for-upgrade.sql

2. Shut down the MySQL server (refer to the Starting or Stopping MySQL 8 section).
3. Install the new MySQL version (refer to the methods mentioned in the In-place upgrades section).
4. Start the MySQL server (refer to the Starting or Stopping MySQL 8 section).
5. Reset the temporary root password:

shell> mysql -u root -p
Enter password: **** (enter temporary root password from error log)

mysql> ALTER USER USER() IDENTIFIED BY 'your new password';

6. Restore the backup (this may take a long time depending up on the size of the backup). Refer to Chapter 8, Restoring Data for a quick restoration method called myloader:

shell> mysql -u root -p --force < data-for-upgrade.sql

7. Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

8. Restart the MySQL server (refer to the Starting or Stopping MySQL 8 section).