MySQLdump with coding issues

Although today everything is in UTF-8 (even in UTF-8 mb4) it has not always been like this, and when there are migrations with mysqldump it is possible to find that when restoring “rare letters” appear.

Those rare letters usually correspond to letters “no ascii”, that is, letters like “á” or “ñ” and that look strange.

In most cases this comes because the old databases are not configured in UTF-8 by default, so when exporting we will see coding problems that are dragged until the restoration.

This tutorial has been created on a Clouding.io VPS. You can create your own VPS from 3€/month.

In addition, you have the possibility to create your VPS with the WordPress image in one click.

PARTNERSHIP

We will have to follow 3 steps when making the backup and restoring it.

Backup

The first of the steps is to make the mysqldump, but with significant changes of what is usual.

mysqldump --default-character-set=latin1 basededatos -r copiadeseguridad.sql

On the one hand, we will define the character set in Latin1 (--default-character-set=latin1) which is what by default comes with the old databases.

On the other hand we will not make the usual “>” but a “-r” to save the file.

Removing “latin1”

The next step will be to remove the line that added the download. We can do it manually, simply by opening the file and looking in the first lines for the text, and deleting it:

/*!40101 SET NAMES latin1 */;

Or we can launch a command to delete it (for example if the file is very large).

sed -i 's//*!40101 SET NAMES latin1 */;/ /' copiadeseguridad.sql

Restoring the copy

To restore the copy we will access the new database server and enter the database forcing the new character set.

mysql --default-character-set=utf8mb4 basededatos

Once inside, we will set the set:

SET names 'utf8';

And then we will load the new file from the database.

SOURCE copiadeseguridad.sql;

We should now be able to access our WordPress and correctly view the characters of the site.


About this document

This document is regulated by the EUPL v1.2 license, published in WP SysAdmin and created by Javier Casares. Please, if you use this content in your website, your presentation or any material you distribute, remember to mention this site or its author, and having to put the material you create under EUPL license.