7374756666

A script to convert an entire MySQL/MariaDB charset from one charset to another

I recently had to add emoji support to a site which used the utf8 charset on a MariaDB 10.1 server.

To do this I needed to change the charset of the database/tables/columns to utf8mb4.

The following script will do this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
#!/bin/sh
if [ -z "$1" ]
then
    echo "You must specify a database as the first argument" 1>&2
    exit 1
fi
DB=$1
mysql -e "ALTER DATABASE $DB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci"
mysql -N -e 'show tables' $DB | \
awk "{ print \"pt-online-schema-change --execute \
--alter 'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' D=$DB,t=\"\$1}" | \
sh

This uses the excellent Percona Toolkit to change the table schema online to prevent downtime due to the tables being locked.