You also should check a few prerequisites before the upgrade.
- 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
- 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;
- 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');- 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;
- Tables not supported by MySQL 8.0, such as
ndb, should be moved to InnoDB:
mysql> ALTER TABLE tablename ENGINE=InnoDB;
Just like the previous recipe, the following subsections will take you through the details with various systems, bundles, and so on.
Here is an outline of the steps:
- Shut down the old MySQL version.
- Replace the old MySQL binaries or packages with the new ones (detailed steps for different types of installation methods are covered).
- Restart MySQL on the existing
data directory. - Run the
mysql_upgrade utility. - 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:
- 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;
- Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.
Upgrade the MySQL binaries or packages.
shell> sudo yum-config-manager --disable mysql57-community
shell> sudo yum-config-manager --enable mysql80-community
- 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
shell> sudo yum update mysql-server
- Reconfigure the
apt to switch to MySQl 8.0:
shell> sudo dpkg-reconfigure mysql-apt-config
shell> sudo apt-get update
- 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
shell> sudo apt-get update
shell> sudo apt-get install mysql-server
shell> sudo apt-get install libmysqlclient21
Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded fromMySQL Downloads (refer to the Installing MySQL 8.0 using RPM or DEB files section).
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).
Here is an outline of the steps:
- Export existing data from the old MySQL version using
mysqldump - Install the new MySQL version
- Load the dump file into the new MySQL version
- Run the
mysql_upgrade utility
Here are the detailed steps:
- 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
- Shut down the MySQL server (refer to the Starting or Stopping MySQL 8 section).
- Install the new MySQL version (refer to the methods mentioned in the In-place upgrades section).
- Start the MySQL server (refer to the Starting or Stopping MySQL 8 section).
- 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';
- 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
- Run the
mysql_upgrade utility:
shell> sudo mysql_upgrade -u root -p
- Restart the MySQL server (refer to the Starting or Stopping MySQL 8 section).
United States
United Kingdom
India
Germany
France
Canada
Russia
Spain
Brazil
Australia
Argentina
Austria
Belgium
Bulgaria
Chile
Colombia
Cyprus
Czechia
Denmark
Ecuador
Egypt
Estonia
Finland
Greece
Hungary
Indonesia
Ireland
Italy
Japan
Latvia
Lithuania
Luxembourg
Malaysia
Malta
Mexico
Netherlands
New Zealand
Norway
Philippines
Poland
Portugal
Romania
Singapore
Slovakia
Slovenia
South Africa
South Korea
Sweden
Switzerland
Taiwan
Thailand
Turkey
Ukraine
