Convert a database from utf8 to utf8mb4

An older application may require converting its tables from utf8 to utf8mb4. Do not do global replacements on a dump or drop the production tables: a text replacement can change user data, non-equivalent collation names or serialised content.

1. Create and verify a protected copy

Work from a private folder outside public_html, with a new dump name. Temporarily stop the application's writes and generate a consistent dump. -p asks for the password interactively and avoids leaving it in the history or the process list:

umask 077
mysqldump --single-transaction --routines --triggers -u USUARIO_MYSQL -p BASE_DE_DATOS > base-antes-utf8mb4.sql &&
test -s base-antes-utf8mb4.sql &&
sha256sum base-antes-utf8mb4.sql > base-antes-utf8mb4.sql.sha256 &&
chmod 400 base-antes-utf8mb4.sql base-antes-utf8mb4.sql.sha256 &&
sha256sum -c base-antes-utf8mb4.sql.sha256

If mysqldump returns an error, do not continue or treat the partial file as valid. Exporting routines requires permissions; if it fails, check which objects the application needs before leaving them out. Events, if there are any, must also be inventoried and exported with the right options and permissions. The checksum detects later changes to the file, but it does not prove the copy is complete. Read-only permissions do not make it immutable either.

Also download both files to storage other than the hosting. Do not edit this dump: it will be your recovery point.

2. Restore into a new database

Create an empty database, with a different user and name, and restore the copy there:

mysql -u USUARIO_NUEVO -p BASE_NUEVA < base-antes-utf8mb4.sql

In the clone, turn off production emails, payments, webhooks and automatic tasks. Set up a clone of the application to use BASE_NUEVA and confirm it opens, logs in and runs its main operations before converting anything.

3. Preview and convert the clone

First list the tables whose default is still utf8 or utf8mb3:

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND (
      TABLE_COLLATION LIKE 'utf8mb3\_%'
      OR TABLE_COLLATION LIKE 'utf8\_%'
  );

A column's encoding may differ from its table's default. Check the columns too; a table with a utf8mb4 default could still contain old columns:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

The following generator is a starting point for the tables shown in the first list. CONVERT TO transforms all their text columns, including those with a different encoding; do not run it without reviewing those exceptions. If only some columns need changing, prepare an ALTER TABLE ... MODIFY that keeps their full definition.

Generate the exact statements you would need, review them one by one and run them only on BASE_NUEVA:

SELECT CONCAT(
    'ALTER TABLE `', REPLACE(TABLE_SCHEMA, '`', '``'),
    '`.`', REPLACE(TABLE_NAME, '`', '``'),
    '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS sentencia_a_revisar
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND TABLE_TYPE = 'BASE TABLE'
  AND (
      TABLE_COLLATION LIKE 'utf8mb3\_%'
      OR TABLE_COLLATION LIKE 'utf8\_%'
  );

MySQL 8 normally shows the old utf8 collations under the name utf8mb3; the second pattern keeps compatibility with versions that still show the old alias. The specific collation must be compatible with the MySQL version and with the application. Check that the chosen collation exists on the destination. Changing comparison rules can create new matches in unique indexes; the conversion can also widen text types or clash with foreign keys. Check text column indexes beforehand, because when the maximum size per character increases, some old indexes may exceed the supported limit.

If you want new tables to inherit utf8mb4, also change the cloned database's default:

ALTER DATABASE `BASE_NUEVA`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This does not convert existing tables. Also set utf8mb4 in the application's connector; changing only the schema does not guarantee it can write four-byte characters. --single-transaction provides a consistent snapshot for transactional tables, but it does not protect MyISAM tables or schema changes during the dump.

4. Validate and switch the application

Compare between the original database and the clone:

• schema, indexes, views, triggers and routines;
• row count of each table;
• samples with accents, four-byte characters and serialised content;
• logging in, searches, editing and the application's scheduled tasks.

When all the checks pass, stop writes again, repeat on the clone any changes made since the first dump if necessary, and change the application's credentials to point to the new database. Keep the original database intact and with no writes during the observation period.

Reverting

If the later validation fails, stop writes and identify the operations received in the new database. Decide how to keep them before going back to the original: changing only the credentials would lose those changes. Then restore the previous credentials and validate the application. Do not delete the original database or the verified dump until you have explicitly closed the recovery period.

If you are unsure about the right collation or how to sync the final writes, contact support before the change.