Change MySQL 5.5 default character-set to UTF8

Last night cPanel upgraded the version of MySQL on one of our servers to the latest MySQL 5.5. Unfortunately this results in over 4000 emails from that server, crying that various websites couldn’t connect to the MySQL database. The error found in /var/log/mysqld.log was

InnoDBErrorlog file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB
than specified in the .cnf file 0 26214400 bytes!
131218 23:06:21 [ERROR] Plugin 'InnoDB' init function returned error.
131218 23:06:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131218 23:06:21 [ERROR] /usr/sbin/mysqldunknown variable 'default-character-set=utf8'
131218 23:06:21 [ERROR] Aborting 

Interstingly enough, the first error:

InnoDBErrorlog file ./ib_logfile0 is of different size 0 5242880 bytes 

Was a side effect of the second error:

/usr/sbin/mysqldunknown variable 'default-character-set=utf8' 

Initially we had set the Innodb log file size to 25M in our my.cnf file. When cPanel had issues with the upgrade, at some point our log files reverted to the MySQL default 5M size. A fast fix for both these issues is to mv /etc/my.cnf to /etc/my.cnf.old. Restart with MySQL with “/scripts/restartsrv mysql”. This gets us up and running, but doesn’t fix any of the actual errors.

Be sure to make backups of all your databases before doing any of these steps.

Fixing the “default-character-set=utf8” error

To fix the default-character-set error, we found that MySQL deprecated this variable in MySQL 5.0.

Doing a quick search in Google found this great post with the proper way to change the default character set in MySQL 5.5. Simply put, edit /etc/my.cnf with your favourite editor:

Remove this line wherever it may appear:

default-character-set=utf8 

Add under [mysqld] the following code snippet:

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation
-server=utf8_unicode_ci
skip
-character-set-client-handshake 

Restart MySQL with /etc/init.d/mysqld restart or on cPanel using /scripts/restartsrv mysql. Now let’s verify that our default character set is UTF8:

mysql
show variables like 
'%character%';
show variables like '%collation%'
mysqlshow variables like '%character%';
+--------------------------+----------------------------+
Variable_name            Value                      |
+--------------------------+----------------------------+
character_set_client     utf8                       |
character_set_connection utf8                       |
character_set_database   utf8                       |
character_set_filesystem binary                     |
character_set_results    utf8                       |
character_set_server     utf8                       |
character_set_system     utf8                       |
character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysqlshow variables like '%collation%'
+----------------------+-----------------+
Variable_name        Value           |
+----------------------+-----------------+
collation_connection utf8_unicode_ci |
collation_database   utf8_unicode_ci |
collation_server     utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec

Looks good!

To fix the “/ib_logfile0 is of different size” error, we simply shut down MySQL, move the ib_logfile0 and ib_logfile1 to ib_logfile0.old and ib_logfile1.old, then restart MySQL.

Be sure to make backups of all your databases before doing any of these steps.