Mysql charset issue after migration

[M.Azzalin]

Hi,
I’m a web developer and I work with php on systems with WHM and CPANEL.
Recently our agency is migrating CPANEL accounts of many customers, into older machines working with Centos 5.x to Centos 7, so with recent versions of apache, php and mysql.
We have three machines to migrate. This is the set up:
1) Apache 2.2.23 - PHP 5.2.9 - MySQL 5.1.73
2) Apache 2.2.24 - PHP 5.2.9 - MySQL 5.1.73
3) Apache 2.2.23 - PHP 5.3.21 - MySQL 5.1.73
For the migration we are using the transfer tool.
In another machine we migrated, we found some problems of charset enconding inside the migrated databases.
The database was regenerated with records with special characters codified with another encoding.
In my opinion the problem seems to come from the fact that the previous version of mysql uses the charset encoding latin1 while the new machine uses mysql 5.6.35 with a preset charset encoding set on utf8.
I use the CPANEL platform from a little time and I'm wondering how to migrate the database and whether there is some problem with the old default encoding value of mysql (latin1) migrating to the new (utf8).
On these servers we have many CPANEL accounts with relevant mysql databases so during the migration we would like to put ourselves in the situation of not having to check all customer databases one by one in search of these problems.
Has anyone ever had such problems?
Can we migrate CPANEL accounts without experiencing this problem?

 

[cPanelMichael]

Hello,

You'd need to set the default MySQL character set on the new server before copying the accounts over to it if you want a specific character set utilized. You can review MySQL documentation on this topic for more information on how this works:

MySQL :: MySQL 5.6 Reference Manual :: 10.5 Character Set Configuration
MySQL :: MySQL 5.6 Reference Manual :: 10.1.3.3 Database Character Set and Collation

MariaDB also provides a useful document if you plan to switch to MariaDB in the future:

Setting Character Sets and Collations

Also, here's a URL you may find helpful if you want to convert the character set used by a script/database:

Converting Database Character Sets « WordPress Codex

Thank you.