Plesk MySQL upgrade 5.1 to 5.5

When trying to restore a database backup taken on MariaDB 5.5.56 on a Plesk server running MySQL 5.1, i get error

[root@xap ~]# mysql admin_dollsweb < admin_dollsweb.sql ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_unicode_ci' [root@xap ~]#

This is because MySQL 5.1 do not support utf8mb4_unicode_ci.

Solution 1

You can find and replace utf8mb4_unicode_ci with utf8_general_ci

sed -i 's/utf8mb4_unicode_ci/utf8_general_ci/g' DB_BACKUP.sql

One problem with this is you may lose some characters if the DB use the new charsets supported by utf8mb4_unicode_ci. utf8mb4_unicode_ci For most sites these won’t be a problem. utf8mb4_unicode_ci support latest emoji chars.

Solution 2

Upgrade MySQL to version 5.5 or newer.

On Plesk server, do the following steps

wget -q -O - http://www.atomicorp.com/installers/atomic | sh

When asked, type “yes” to enable the repo.

Upgrade MySQL with

yum upgrade mysql

Restart MySQL

service mysqld restart

Upgrade MySQL database with

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`