Migrating Drupal to Mysql utf8mb4

There exists a Drupal module https://www.drupal.org/project/utf8mb4_convert to take care of this migration but as a database developer and administrator I wanted more control over how exactly the data and tables are migrated. So I have outlined the steps below, which bypasses using this module completely and take care of migrating to uft8mb4 offline. Replace the placeholders enclosed in <> according to your database.

Important: Take backup of your database and webserver files before you attempt the below steps. Incorrectly executing the below steps can result in data loss. You have been warned.

1. Take Mysql dump of the database structure alone i.e. database creation and table creation statements only.

mysqldump --no-data --routines --databases <db name> -u <db user> -p > <db name>_DDL_dump.sql

2. Now manipulate the above dump file as below.

sed -i 's/\/\*!40100 DEFAULT CHARACTER SET latin1 \*\///g' <db name>_DDL_dump.sql
sed -i 's/ utf8 / utf8mb4 /g' <db name>_DDL_dump.sql
sed -i 's/ latin1 / utf8mb4 /g' <db name>_DDL_dump.sql
sed -i 's/ DEFAULT CHARSET=\(utf8\|utf8mb4\) / /g' <db name>_DDL_dump.sql
sed -i 's/ DEFAULT CHARSET=\(utf8\|utf8mb4\);/;/g' <db name>_DDL_dump.sql
sed -i 's/ COLLATE[ =]\(utf8_bin\|utf8mb4_bin\) / /g' <db name>_DDL_dump.sql
sed -i 's/ COLLATE[ =]\(utf8_bin\|utf8mb4_bin\);/;/g' <db name>_DDL_dump.sql
sed -i 's/^\/\*!.*\*\/;$//g' <db name>_DDL_dump.sql

3. The above statements will work incase Drupal was installed using default language 'English'. You can adjust the above according to the character set in which Drupal was installed if default language was not used during installation.

4. Now add the below statements in Mysql configuration file.

Under [mysqld]

innodb_large_prefix            = true
innodb_file_format            = barracuda
innodb_file_per_table        = true

character_set_server        = utf8mb4
collation_server            = utf8mb4_unicode_ci

Under [client]

default_character_set        = utf8mb4

Under [mysql]

default_character_set        = utf8mb4

5. Now take Mysql dump of all data in the database

mysqldump --no-create-info --no-create-db --complete-insert --quick --databases <db name> -u <db user> -p > <db name>_DML_dump.sql

6. If the above dump was created successfully, then login to Mysql as root user and execute the below SQL statement.

ALTER DATABASE <db name> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

7. Now import the DDL and DML dump files using mysql client in sequence as shown below.

mysql -u <db user> -p < <db name>_DDL_dump.sql
mysql -u <db user> -p < <db name>_DML_dump.sql

8. Once the DDL and DML files have been imported successfully in above step, execute the below statement to update database to tell Drupal that it can now issue SQL statements inline with utf8mb4 requirement.

echo "USE <db name>; INSERT INTO variable SET name='drupal_all_databases_are_utf8mb4', value='b:1;'; COMMIT;" | mysql -u <db user> -p

9. Now add the below parameters to the databases array in Drupal settings.php file.

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

10. Restart Mysql server. Restart webserver and/or php-fpm server.