MySQL :: MySQL 8.0 Reference Manual :: 2.10.1 Upgrading MySQL

MySQL 8.0 Reference Manual / ... / Upgrading MySQL

Pre-General Availability Draft: 2017-07-22

2.10.1 Upgrading MySQL

2.10.1.1 Changes Affecting Upgrades to MySQL 8.0
2.10.1.2 Upgrading MySQL with the MySQL Yum Repository
2.10.1.3 Upgrading MySQL with the MySQL APT Repository

MySQL Server 8.0 incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL series, dictionary data was stored in metadata files and nontransactional system tables. This section describes how to upgrade your data directory from the file-based structure to the data-dictionary structure.

Important

The process described here requires that you upgrade to MySQL 8.0 from a MySQL 5.7 GA version (5.7.9 or higher). Upgrades from non-GA versions of MySQL 5.7 or from MySQL series preceding 5.7 are not supported.

After you upgrade, a data dictionary-enabled server entails some general operational differences; see Section 14.6, “Data Dictionary Usage Differences”.

Note

In the following discussion, MySQL commands that must be run using a MySQL account with administrative privileges include -u root on the command line to specify the MySQL root user. Commands that require a password for root also include a -p option. Because -p is followed by no option value, such commands prompt for the password. Type the password when prompted and press Enter.

SQL statements can be executed using the mysql command-line client (connect as root to ensure that you have the necessary privileges).

Upgrade Methods

Supported upgrade methods include:

In-Place Upgrade: Involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.
Logical Upgrade: Involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.

For in-place and logical upgrade procedures, see Performing an In-Place Upgrade, and Performing a Logical Upgrade.

If you run MySQL Server on Windows, refer to the upgrade procedure described in Section 2.3.8, “Upgrading MySQL on Windows”.

If your current MySQL installation was installed on an Enterprise Linux platform or Fedora using the MySQL Yum Repository, see Section 2.10.1.2, “Upgrading MySQL with the MySQL Yum Repository”.

If your current MySQL installation was installed on Ubuntu using the MySQL APT repository, see Section 2.10.1.3, “Upgrading MySQL with the MySQL APT Repository”.

Upgrade Paths

Upgrades from one version to the next are supported. For example, upgrading from MySQL 5.7 to 8.0 is supported. Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MyQL 5.7 release before upgrading to MySQL 8.0.

Important

For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA version (5.7.9 or higher). Upgrades from non-GA versions of MySQL 5.7 are not supported.
Upgrades that skip a version are not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.
Upgrades from one GA status release to the next are supported. Skipping a release is also supported. The are currently no GA status releases available for MySQL 8.0.
Upgrades between milestone releases (or from milestone releases to GA releases) are not supported. For example, upgrading from MySQL 8.0.0 to 8.0.1 is not supported, as neither are GA status releases.

For upgrades between versions of a MySQL release series that has reached GA status, MySQL format and data files may be moved between different versions on systems with the same architecture. This is not necessarily true for upgrades between milestone releases. Use of milestone releases is at your own risk.

For information about the MySQL release naming scheme, see Section 2.1.1, “Which MySQL Version and Distribution to Install”.

Before You Begin

Before upgrading, review the following information and perform the recommended steps:

Before upgrading, protect your data by creating a backup of your current databases and log files. The backup should include the mysql system database, which contains the MySQL data dictionary tables and system tables. See Section 7.2, “Database Backup Methods”.
Review the Release Notes which provide information about features that are new in the MySQL 8.0 or differ from those found in earlier MySQL releases. Some of these changes may result in incompatibilities.
For a description of MySQL server features that have been removed in MySQL 8.0, see Features Removed in MySQL 8.0. An upgrade requires changes with respect to those features if you use any of them.
For listings of MySQL server variables and options that have been added, deprecated, or removed in MySQL 8.0, see Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0”. If you use any of these items, an upgrade requires configuration changes.
Review Section 2.10.1.1, “Changes Affecting Upgrades to MySQL 8.0”. This section describes changes that may require action before or after upgrading.
If you use replication, review Section 18.4.3, “Upgrading a Replication Setup”.
If you use XA transactions with InnoDB, run XA RECOVER before upgrading to check for uncommitted XA transactions. If results are returned, either commit or rollback the XA transactions by issuing an XA COMMIT or XA ROLLBACK statement.
If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a “dummy” database instance for assessing what conversions might be needed and the work involved to perform them. Make a copy of your MySQL instance that contains a full copy of the mysql database, plus all other databases without data. Run your upgrade procedure on this dummy instance to see what actions might be needed so that you can better evaluate the work involved when performing actual data conversion on your original database instance.
Rebuilding and reinstalling MySQL language interfaces is recommended whenever you install or upgrade to a new release of MySQL. This applies to MySQL interfaces such as PHP mysql extensions, the Perl DBD::mysql module, and the Python MySQLdb module.

Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation

Before starting the MySQL 8.0 server to upgrade the data directory, it is very important to ensure the upgrade readiness of your installation by using your MySQL 5.7 server to perform several preliminary checks. The upgrade process may fail otherwise.

To perform the preliminary checks:

There must be no tables that use obsolete data types, InnoDB tables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context. (This context is indicated by the character_set_client, collation_connection, Database Collation attributes displayed by SHOW TRIGGERS or the INFORMATION_SCHEMA TRIGGERS table.) To identify tables and triggers that fail these requirements, execute this command:
```
mysqlcheck -u root -p --all-databases --check-upgrade
```
If mysqlcheck reports any errors, correct the issues.
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%';
```
Any table reported by the query must be altered to use InnoDB or be made nonpartitioned. To change a table storage engine to InnoDB, execute this statement:
```
ALTER TABLE table_name ENGINE = INNODB;
```
Note

For information about converting MyISAM tables to InnoDB, see Section 15.8.1.4, “Converting Tables from MyISAM to InnoDB”.

To make a partitioned table nonpartitioned, execute this statement:
```
ALTER TABLE table_name 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',
'collations',
'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',
'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 CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE LENGTH(CONSTRAINT_NAME) > 64;
```
Any tables reported by the query must be altered to have constraint names no longer than 64 characters (use ALTER TABLE).
Your MySQL 5.7 installation must not use features that are not supported by MySQL 8.0. Any changes here are necessarily installation specific, but the following examples illustrate the kind of things to look for:
- Tables that use a storage engine not supported in MySQL 8.0 must be altered to use a supported engine.
  Example: MySQL 8.0 does not yet support MySQL Cluster, so NDB tables must be altered to use a different storage engine.
- Some server startup options and system variables have been removed in MySQL 8.0. See Features Removed in MySQL 8.0, and Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0”. If you use any of these items, an upgrade requires configuration changes.
  Example: Because the data dictionary provides information about database objects, the server no longer checks directory names in the data directory to find databases. Consequently, the --ignore-db-dir option is extraneous and has been removed. To handle this, remove any instances of --ignore-db-dir from your startup configuration. In addition, remove or move the named data directory subdirectories before upgrading to MySQL 8.0. (Alternatively, let the 8.0 server add those directories to the data dictionary as databases, then remove each of those databases using DROP DATABASE.)

Performing an In-Place Upgrade

This section describes how to perform an in-place upgrade. Before proceeding, review Before You Begin, and ensure that your installation is upgrade-ready by following the procedure given in Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.

Note

If you upgrade an installation originally produced by installing multiple RPM packages, upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.

For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_safe is not installed. In such cases, use systemd for server startup and shutdown instead of the methods used in the following instructions. See Section 2.5.9, “Managing MySQL Server with systemd”.

To perform an in-place upgrade:

Review the changes described in Section 2.10.1.1, “Changes Affecting Upgrades to MySQL 8.0” for steps to be performed before upgrading.
With your MySQL 5.7 server, if there are encrypted InnoDB tablespaces, rotate the keyring master key by executing this statement:
```
ALTER INSTANCE ROTATE INNODB MASTER KEY;
```
Configure your MySQL 5.7 server to perform a slow shutdown by executing this statement:
```
SET GLOBAL innodb_fast_shutdown = 0;
```
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 data files are fully prepared in case of file format differences between releases.
Shut down the old MySQL server. For example:
```
mysqladmin -u root -p shutdown
```
Upgrade the MySQL binary installation or packages. If upgrading a binary installation, unpack the new MySQL binary distribution package. See Obtain and Unpack the Distribution. For package-based installations, replace the old packages with the new ones.

Note

For supported Linux distributions, the preferred method for replacing the MySQL packages is to use the MySQL software repositories; see Section 2.10.1.2, “Upgrading MySQL with the MySQL Yum Repository”, Section 2.10.1.3, “Upgrading MySQL with the MySQL APT Repository”, or Upgrading MySQL with the MySQL SLES Repository for instructions.
Start the MySQL 8.0 server, using the existing data directory. For example:
```
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir
```
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.
If this step succeeds, the server performs a cleanup:
- In the data directory, the server creates a directory named backup_metadata_57 and moves into it files named db.opt and files with a suffix of .frm, .par, .TRG, .TRN, or .isl. (These are files previously used for metadata storage.)
  Files in the backup_metadata_57 directory retain the original file system hierarchy. For example, if t1.frm was located in the my_schema1 directory under the data directory, the server moves it to the backup_metadata_57/my_schema1 directory.
- In the mysql database, the server renames the event and proc tables to event_backup_57 and proc_backup_57.
If this step 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.
After the MySQL 8.0 server starts successfully, execute mysql_upgrade:
```
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.

Note

mysql_upgrade should not be used when the server is running with --gtid-mode=ON. See GTID mode and mysql_upgrade for more information.
mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.10, “Server-Side Help”.
Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:
```
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir
```
The first time you started the MySQL 8.0 server (in an earlier step), you may have noticed that it wrote several messages to its error log regarding nonupgraded tables. If mysql_upgrade has been run successfully, there should be no such messages the second time you start the server.

Performing a Logical Upgrade

This section describes how to perform a logical upgrade. Before proceeding, review Before You Begin, and ensure that your installation is upgrade-ready by following the procedure given in Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.

Note

To perform a logical upgrade:

Review the changes described in Section 2.10.1.1, “Changes Affecting Upgrades to MySQL 8.0” for steps to be performed before upgrading.
With your MySQL 5.7 server, use mysqldump to export your data:
```
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
```
The --routines and --events options must be given explicitly to include stored routines and events as object definitions in the dump file. Otherwise, they will appear only as rows in the mysql.proc and mysql.event tables, which will not be used in MySQL 8.0 when the dump file is reloaded (definitions for the corresponding objects are expected to be stored in data dictionary tables instead).
You can omit the --ignore-table options; the dump file will still reload later if you include the --force option on the load command. However, in that case, be prepared to see several warnings when those two tables load, which you should ignore.
Shut down the old MySQL server. For example:
```
mysqladmin -u root -p shutdown
```
Install MySQL 8.0. For installation instructions, see Chapter 2, Installing and Upgrading MySQL.
Initialize a new data directory, as described at Section 2.9.1, “Initializing the Data Directory”. For example:
```
mysqld --initialize --datadir=/path/to/8.0-datadir
```
Copy the temporary 'root'@'localhost' password displayed to your screen or written to your error log for later use.
Start the MySQL 8.0 server, using the new data directory. For example:
```
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir
```
If there are encrypted InnoDB tablespaces, use the --early-plugin-load option to load the keyring plugin.

Reset the temporary root password:

shell> mysql -u root -p
Enter password: ****  <- enter temporary root password
mysql> ALTER USER USER() IDENTIFIED BY 'your new password';

Load the previously created dump file into the new MySQL server. For example:
```
mysql -u root -p --force < data-for-upgrade.sql
```
If you did not specify --ignore-table options to suppress dumping the mysql.event and mysql.proc tables for the earlier mysqldump command, they will reappear when you reload the dump file.
After loading the dump file, execute mysql_upgrade:
```
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.

Note

mysql_upgrade should not be used when the server is running with --gtid-mode=ON. See GTID mode and mysql_upgrade for more information.
mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.10, “Server-Side Help”.
Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:
```
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir
```

Upgrade Troubleshooting

If problems occur, such as that the new mysqld server does not start, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation.
If, after an upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed (for example, from libmysqlclient.so.15 to libmysqlclient.so.16).
If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name. The same is true if the new version of MySQL implements a built-in function with the same name as an existing stored function. See Section 9.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

PREV HOME UP NEXT

User Comments