1) Why change collation?

MySQL collation decides how text is compared and sorted. The collation utf8mb4_0900_ai_ci is commonly seen on MySQL 8 environments, while utf8mb4_unicode_ci is widely compatible across many MySQL and MariaDB versions. If you migrate databases between servers (for example, from MySQL 8 to MariaDB), or if some applications/plugins do not behave well with newer collations, converting to utf8mb4_unicode_ci can reduce compatibility issues.

In shared hosting and WHM/cPanel environments, you may also want a consistent collation across all user databases to avoid random sorting differences, index errors during migration, or application-level search/sort mismatches.

Internal link: If you run cPanel and need to list databases created by cPanel users, see: How to list all MySQL databases created by cPanel users.

2) Before you start (backup + checks)

Collation conversion touches database metadata and table structures. On large databases, it can take time and may lock tables during conversion. Do these basic checks first:

2.1 Take a backup

• Always keep a full MySQL backup before bulk conversions.
• If you host client sites, schedule a maintenance window if needed.

2.2 Confirm which databases/tables are using utf8mb4_0900_ai_ci

You can quickly check how many databases and tables still have utf8mb4_0900_ai_ci:

mysql -u root -p -e "
SELECT schema_name, default_collation_name
FROM information_schema.schemata
WHERE default_collation_name='utf8mb4_0900_ai_ci';

SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_collation='utf8mb4_0900_ai_ci'
AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND table_type='BASE TABLE';
"

This gives you a preview of what will be changed and helps you estimate workload.

3) One-command method to convert all databases and tables

Below is the exact command sequence to generate and execute ALTER DATABASE and ALTER TABLE ... CONVERT TO statements for every database/table that currently uses utf8mb4_0900_ai_ci, excluding system schemas.

read -s -p "MySQL Root Password: " P; echo; MYSQL_PWD="$P" mysql -u root -Nse "SELECT CONCAT('ALTER DATABASE \`',schema_name,'\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') FROM information_schema.schemata WHERE default_collation_name='utf8mb4_0900_ai_ci' AND schema_name NOT IN ('mysql','information_schema','performance_schema','sys'); SELECT CONCAT('ALTER TABLE \`',table_schema,'\`.\`',table_name,'\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') FROM information_schema.tables WHERE table_collation='utf8mb4_0900_ai_ci' AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys') AND table_type='BASE TABLE';" | MYSQL_PWD="$P" mysql -u root
unset P

This approach is simple: it automatically creates the correct ALTER statements from MySQL’s own metadata tables, then pipes them back into MySQL to apply the changes.

4) What the command does (explained simply)

4.1 Reads password securely

The command starts by asking for your MySQL root password using read -s, which hides input while you type. It stores the password in a temporary shell variable named P.

4.2 Generates ALTER DATABASE statements

It queries information_schema.schemata and creates an ALTER DATABASE ... COLLATE ... statement for every database where the default collation is currently utf8mb4_0900_ai_ci, excluding system databases: mysql, information_schema, performance_schema, and sys.

Changing the database default collation helps new tables use the preferred collation automatically (unless an application explicitly forces something else).

4.3 Generates ALTER TABLE ... CONVERT statements

Next, it scans information_schema.tables and generates a conversion statement for each table that currently has utf8mb4_0900_ai_ci. The key part is:

ALTER TABLE `db`.`table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This converts the table’s character set and collation, and it also updates text columns so they inherit the new collation.

4.4 Executes everything automatically

The generated SQL is piped into a second mysql call, so the ALTER statements execute immediately. Finally, the password variable is removed using unset P.

5) Important notes and edge cases

5.1 Large tables can take time

Table conversion can rebuild table data, especially for InnoDB, and may lock tables depending on the storage engine and MySQL version. On production servers, run this during low-traffic hours.

5.2 MyISAM tables

Some environments still contain MyISAM tables (for example, certain older plugins or applications). The conversion statement still works for MyISAM, but the table may lock fully during the operation.

5.3 If you want a “dry run” first

If you want to only print the ALTER statements (without executing them), run just the generator part:

read -s -p "MySQL Root Password: " P; echo; MYSQL_PWD="$P" mysql -u root -Nse "
SELECT CONCAT('ALTER DATABASE \`',schema_name,'\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.schemata
WHERE default_collation_name='utf8mb4_0900_ai_ci'
AND schema_name NOT IN ('mysql','information_schema','performance_schema','sys');

SELECT CONCAT('ALTER TABLE \`',table_schema,'\`.\`',table_name,'\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables
WHERE table_collation='utf8mb4_0900_ai_ci'
AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND table_type='BASE TABLE';
"
unset P

5.4 Application-level overrides

Some apps explicitly set collation in queries, table definitions, or migrations. Even after conversion, an application might later recreate tables with a different collation. If that happens, enforce your preferred collation in your app config or migrations too.

5.5 Database defaults vs existing tables

Setting the database default collation does not automatically change existing tables. That is why the command includes both: ALTER DATABASE and ALTER TABLE ... CONVERT TO.

6) How to verify conversion

After running the conversion, confirm no databases/tables remain on utf8mb4_0900_ai_ci:

mysql -u root -p -e "
SELECT COUNT(*) AS dbs_still_0900
FROM information_schema.schemata
WHERE default_collation_name='utf8mb4_0900_ai_ci'
AND schema_name NOT IN ('mysql','information_schema','performance_schema','sys');

SELECT COUNT(*) AS tables_still_0900
FROM information_schema.tables
WHERE table_collation='utf8mb4_0900_ai_ci'
AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND table_type='BASE TABLE';
"

If both counts return 0, the bulk conversion is complete.

7) FAQ

Is utf8mb4_unicode_ci safe for WordPress and most PHP apps?

Yes. It is widely used and generally compatible across many MySQL/MariaDB versions. It is a common choice for web hosting servers that host multiple CMS applications.

Will this change break my website?

In most cases, no. But any bulk change should be done with a backup and ideally during a low-traffic window. If you have a custom application that relies on collation-specific comparisons, test first on staging.

Do I need to restart MySQL?

Usually not. Collation conversion is applied live with ALTER statements. Restart is not required unless you also change server defaults in MySQL configuration and want them applied.

What about only converting a single database?

If you only want one database, run:

ALTER DATABASE `your_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then convert its tables individually or generate statements filtered by table_schema='your_db'.