MySQL 5.7 => 8.0

How to upgrade MySQL with version 5.7 to 8.0.

Info

• INPLACE upgrade from MySQL 5.7 to MySQL 8.0
• MySQL Shell 8.0.4: Introducing “Upgrade checker” utility
• Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

Upgrade columns to utf8mb4

The folowing statement creates the statements to change all columns to utf8mb4:

SELECT
    /* Disable foreign key checks temporily to be able to make these changes */
    'SET FOREIGN_KEY_CHECKS = 0;' AS alter_statement 
UNION SELECT 
    /* Alter the default character set of each database */
    CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement 
FROM 
    information_schema.SCHEMATA 
WHERE 
    DEFAULT_CHARACTER_SET_NAME!='utf8mb4' AND 
    SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')
UNION SELECT
    /* Alter the default character set of each table .
      This also converts all text columns in the table,
      So there is no need to have a statement to alter each
      column individually */
    DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement 
FROM
(
    SELECT
        /* Find all tables with a text column that isn't utf8mb4 */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.COLUMNS 
    WHERE
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') AND 
        CHARACTER_SET_NAME IS NOT NULL AND 
        CHARACTER_SET_NAME!='utf8mb4'
    UNION SELECT
        /* Also find all tables that don't have the correct default character set */
        TABLE_SCHEMA, TABLE_NAME
    FROM 
        information_schema.TABLES AS T
    JOIN 
        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C ON C.collation_name = T.table_collation
    WHERE 
        CHARACTER_SET_NAME!='utf8mb4' AND 
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys')
) AS TABLE_UPDATES
UNION SELECT
    /* Re-enable forign key checks */
    'SET FOREIGN_KEY_CHECKS = 1;' AS alter_statement 
;