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
When applying SQL extracted from a previous MySQL version to a new MySQL version, you may encounter errors due to incompatibilities introduced by new, changed, deprecated, or removed features and capabilities. Before upgrading from MySQL 5.7 to MySQL 8.0, perform the preliminary checks described in Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation to identify issues. Alternatively. the same checks can be performed using the MySQL Shell
checkForServerUpgradeutility. For more information, see MySQL Shell Utilities. SQL exported from a previous MySQL version may require modification to enable 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 22.214.171.124, “Upgrading MySQL with the MySQL Yum Repository”.
If your current MySQL installation was installed on Ubuntu using the MySQL APT repository, see Section 126.96.36.199, “Upgrading MySQL with the MySQL APT Repository”.
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.
yis supported. (Upgrade involving development-status releases is not supported.) Skipping a release is also supported. For example, upgrading from MySQL 8.0.
zis supported. MySQL 8.0.11 is the first GA status release within the MySQL 8.0 release series.
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
mysqlsystem 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 188.8.131.52, “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
mysqldatabase, 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
mysqlextensions, the Perl
DBD::mysqlmodule, and the Python
In the instructions that follow, MySQL commands that must be
run using a MySQL account with administrative privileges
-u on the
command line to specify the MySQL
user. Commands that require a password for
root also include a
-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
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.
The same checks can be performed using the MySQL Shell
checkForServerUpgrade utility. For more
information, see MySQL Shell Utilities.
There must be no tables that use obsolete data types, obsolete functions, orphan
InnoDBtables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context (indicated by the
Database Collationattributes displayed by
SHOW TRIGGERSor the
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
InnoDBor be made nonpartitioned. To change a table storage engine to
InnoDB, execute this statement:
ALTER TABLE table_name ENGINE = INNODB;
For information about converting
InnoDB, see Section 184.108.40.206, “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
mysqlsystem 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.
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
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
NDBtables 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-diroption is extraneous and has been removed. To handle this, remove any instances of
--ignore-db-dirfrom 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
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:
Review the information in Before You Begin.
Ensure the upgrade readiness of your installation by completing the preliminary checks in Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.
If you use XA transactions with
XA RECOVERbefore upgrading to check for uncommitted XA transactions. If results are returned, either commit or rollback the XA transactions by issuing an
With your MySQL 5.7 server, if there are encrypted
InnoDBtablespaces, rotate the keyring master key by executing this statement:
ALTER INSTANCE ROTATE INNODB MASTER KEY;
If you normally run your MySQL 5.7 server configured with
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,
InnoDBleaves its undo logs and data files in a state that can be dealt with 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 220.127.116.11, “Upgrading MySQL with the MySQL Yum Repository”, Section 18.104.22.168, “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
InnoDBtablespaces, use the
--early-plugin-loadoption 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
If this step succeeds, the server performs a cleanup:
In the data directory, the server creates a directory named
backup_metadata_57and moves into it files named
db.optand files with a suffix of
.isl. (These are files previously used for metadata storage.)
Files in the
backup_metadata_57directory retain the original file system hierarchy. For example, if
t1.frmwas located in the
my_schema1directory under the data directory, the server moves it to the
mysqldatabase, the server renames the
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
mysqlsystem 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,
sysschema objects up to date for MySQL 8.0.
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.
If problems occur, such as that the new mysqld server does not start, verify that you do not have an old
my.cnffile from your previous installation. You can check this with the
--print-defaultsoption (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active
my.cnffile that affects server or client operation.
If, after an upgrade, you experience problems with compiled client programs, such as
Commands out of syncor unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, check the date for your
libmysqlclient.alibrary 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
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 FUNCTIONto drop the UDF, and then use
CREATE FUNCTIONto 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.