KBEC-00515 - Converting a MySQL Database From utf8/utf8mb3 to utf8mb4

3 minute readKnowledge base

Issue

This document describes how to convert your MySQL database (DB) from the utf8/utf8mb3 character set to the utf8mb4. In this article we use utf8/utf8mb3 together because utf8 is an alias for utf8mb3.

It is highly recommended to do this. According to article in MySQL Documentation - https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset-unicode-utf8.html. The utf8mb3 character set is deprecated, and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. utf8 is currently an alias for utf8mb3, but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references.

Environment

Resolution

Changing the Default MySQL Character Set to utf8mb4

The procedures below cover making the utf8mb4 DB versions using mysqldump.

  1. Edit the client section of /etc/my.cnf (the MySQL configuration file) to read:

[client]
default-character-set=utf8mb4
  1. Edit the mysql section in /etc/my.cnf to read:

[mysql]
default-character-set=utf8mb4
  1. Edit the mysqld section in /etc/my.cnf to read:

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'

IMPORTANT! After you finish editing the file, restart the MySQL server.

Converting an Empty Database

If you created your DB schema and your DB is still empty, complete the following steps:

  1. Log in to your MySQL instance.

  2. Run the following query in your DB to convert it to utf8mb4:

ALTER DATABASE dbname charset=utf8mb4 collate utf8mb4_general_ci;

Converting a Database Containing Tables

If your CloudBees CD/RO (CloudBees Flow) server already has a populated MySQL DB and you are getting a warning about deprecation of utf8mb3, use the following procedures to convert your database.

Linux and MacOS

The following steps show how to create a DB dump, edit the DB dump so that the correct charset and collation are used, and then restore the new DB.

  1. Open a terminal window and move to a temporary directory.

  2. Dump out the DB:

mysqldump -h db_server_host_name -u username -p password -c -e --single-transaction -B dbname > dump.sql

The arguments in this command clean up the character sets and provide a dump that will not cause problems if you are moving the DB to a different DB server or need to restore the DB on a reverted system. For complete descriptions of these arguments, see https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html.

When you run this command, a DB dump is generated into dump.sql.

  1. Next, create a backup of dump.sql by entering:

cp dump.sql dump-fixed.sql

You will modify dump-fixed.sql and will keep dump.sql as a backup.

  1. Edit the dump file and correct the incorrect character that have been used. You can use any search-and-replace editor or program such as VIM:

sed -i 's/) ENGINE=InnoDB.*/) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;/' dump-fixed.sql

This command converts all tables to utf8mb4 by replacing all table suffixes with DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci.

  1. Run commands to drop and recreate database with new charset and collation. And to restore the DB structure and data from the dump file:

mysql  -h db_server_host_name -u username -p password -e "drop database dbname"
mysql -h db_server_host_name -u username -p password -e "create database dbname character set utf8mb4 collate utf8mb4_general_ci"
mysql -h db_server_host_name -u username -p password < dump-fixed.sql

Example

mysqldump -h db_server_host_name -u username -p password -c -e --single-transaction -B dbname > dump.sql
cp dump.sql dump-fixed.sql
sed -i 's/) ENGINE=InnoDB.*/) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;/' dump-fixed.sql
mysql -h db_server_host_name -u username -p password -e "drop database dbname"
mysql -h db_server_host_name -u username -p password -e  "create database dbname character set utf8mb4 collate utf8mb4_general_ci"
mysql -h db_server_host_name -u username -p password < dump-fixed.sql

Windows

The following steps show how to create a DB dump, edit it so that the correct character set (utf8mb4) and collation (utf8mb4_general_ci) are used, and then restore the new DB . For this, first download Super Sed (Win32 executable, zipped).

  1. Open a command window and move to a temporary folder.

  2. Enter:

\bin\mysqldump -h [db_server_host_name] -u [username] -p [password] -c -e --single-transaction -B [dbname] -r dbdump.sql
copy dbdump.sql dump-fixed.sql
ssed -i "s/) ENGINE=InnoDB.*/) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;/" dump-fixed.sql
[your mysql dir]\bin\mysql -h [db_server_host_name] -u [username] -p[password] -e "drop database dbname"
[your mysql dir]\bin\mysql -h [db_server_host_name] -u [username] -p[password] -e "create database dbname character set utf8mb4 collate utf8mb4_general_ci"
[your mysql dir]\bin\mysql -h [db_server_host_name] -u [username] -p[password] [dbname] < dump-fixed.sql

For complete descriptions of these arguments, see https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html.