Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

2.10.1.1 MySQL Upgrade Strategies

Upgrade Methods
  • 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. For the in-place upgrade procedure, see In-Place Upgrade.

  • Logical Upgrade: Involves exporting SQL from the old MySQL version using a backup or export utility such as mysqldump or mysqlpump, installing the new MySQL version, and applying the SQL to the new MySQL version.

    Note

    Applying SQL extracted from a previous MySQL release to a new MySQL release may result in errors due to incompatibilities introduced by new, changed, deprecated, or removed features and capabilities. Consequently, SQL extracted from a previous MySQL release may require modification to enable a logical upgrade.

    To identify incompatibilities before upgrading to the latest MySQL 8.0 release, check your MySQL installation using the MySQL Shell checkForServerUpgrade utility. For more information, see MySQL Shell Utilities. The checks performed on a MySQL 5.7 installation by the MySQL Shell checkForServerUpgrade utility are described in Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.

  • 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.3, “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.4, “Upgrading MySQL with the MySQL APT Repository”.

Upgrade Paths
  • Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.

  • Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.7 release before upgrading to MySQL 8.0.

  • Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.

  • Once a release series reaches General Availability (GA) status, upgrade within a release series from one GA version to another GA version is supported. For example, upgrading from MySQL 8.0.x to 8.0.y is supported. (Upgrade involving development-status releases is not supported.) Skipping a release is also supported. For example, upgrading from MySQL 8.0.x to 8.0.z is supported. MySQL 8.0.11 is the first GA status release within the MySQL 8.0 release series.

Before You Begin

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

  • 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”.

    Important

    Downgrade from MySQL 8.0 to MySQL 5.7 (or from a MySQL 8.0 release to a previous MySQL 8.0 release) is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.

  • 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. Upgrading from MySQL 5.7 to MySQL 8.0 upgrades the data directory from the file-based structure to the data-dictionary structure.

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

  • Review Section 2.10.1.2, “Changes Affecting Upgrades to MySQL 8.0” to identify changes in MySQL 8.0 that may affect your MySQL installation and applications. Some changes may require action before or after upgrading.

  • 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.

  • Review Features Removed in MySQL 8.0 for MySQL server features that have been removed in MySQL 8.0. An upgrade requires changes with respect to those features if you use any of them.

  • Review Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0” for MySQL server variables and options that have been added, deprecated, or removed in MySQL 8.0. If you use any of these, an upgrade requires configuration changes.

  • If you use replication, review Section 17.4.3, “Upgrading a Replication Setup”.

  • 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.

Note

In the instructions that follow, 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).

Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation

Before upgrading to MySQL 8.0, it is necessary 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.

Note

The same checks can be performed using the MySQL Shell checkForServerUpgrade utility. For more information, see MySQL Shell Utilities.

Preliminary checks:

  1. There must be no tables that use obsolete data types, obsolete functions, orphan .frm files, InnoDB tables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context (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.

  2. 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;

    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;
  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:

    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE LOWER(TABLE_SCHEMA) = 'mysql'
    and LOWER(TABLE_NAME) IN
    (
    'catalogs',
    'character_sets',
    '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.

  4. 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);

    Any tables reported by the query must be altered to have constraint names no longer than 64 characters (use ALTER TABLE).

  5. There must be no tables or stored procedures with individual ENUM or SET column elements that exceed 255 characters or 1020 bytes in length. Prior to MySQL 8.0, the maximum combined length of ENUM or SET column elements was 64K. In MySQL 8.0, the maximum character length of an individual ENUM or SET column element is 255 characters, and the maximum byte length is 1020 bytes. (The 1020 byte limit supports multitibyte character sets). Before upgrading to MySQL 8.0, modify any ENUM or SET column elements that exceed the new limits. Failing to do so causes the upgrade to fail with an error.

  6. 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. For 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, 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.)

In-Place Upgrade
Note

If you are upgrading 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:

  1. Review the information in Before You Begin.

  2. Ensure the upgrade readiness of your installation by completing the preliminary checks in Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.

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

  4. 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;
  5. If you normally run your MySQL 5.7 server configured with innodb_fast_shutdown set to 2 (cold shutdown), configure it to perform a fast or slow shutdown by executing either of these statements:

    SET GLOBAL innodb_fast_shutdown = 1; -- fast shutdown
    SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown

    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.

  6. Shut down the old MySQL server. For example:

    mysqladmin -u root -p shutdown
  7. 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.3, “Upgrading MySQL with the MySQL Yum Repository”, Section 2.10.1.4, “Upgrading MySQL with the MySQL APT Repository”, or Upgrading MySQL with the MySQL SLES Repository for instructions.

  8. 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.

    When you start the MySQL 8.0 server, it 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.

  9. 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 does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.14, “Server-Side Help”.

  10. 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 messages written to the 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.

Upgrade Troubleshooting
  • A schema mismatch in a MySQL 5.7 instance between the frm file of a table and the InnoDB data dictionary can cause an upgrade to MySQL 8.0 to fail. Such mismatches may be due to frm file corruption. To address this issue, dump and restore affected tables before attempting the upgrade again.

  • 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.20 to libmysqlclient.so.21).

  • 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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.