Restore MySQL backup data with UTF8 encoding

[Backup with mysqldump]

# mysqldump --default-character-set=latin1 -u root -p dbname > /home/
backup.sql

I specified “latin1” as the default character set because the default environment was so.

After executing the above command, check Japanese characters are displayed in the backup file.

[Check the backup file with an editor]
Open the file with a text editor which can convert the character set, and then save the file in UTF8 format. Add the following statement to the top of backup.sql file.

SET NAMES utf8;

This allows input to MySQL with UTF8 UTF8 encoding.

[Restore data]

When restoring data, you may have seen an error message:

Got a packet bigger than 'max_allowed_packet' bytes

In this case, use an option “–max_allowed_packet”.

mysql --max_allowed_packet=128M -u root -p dbname < /home/backup.sql

Set the memory size accordingly. If this does not work, edit /etc/my.cnf file like this:

[mysqld]
・・・
・・
・
max_allowed_packet=128M

[mysql.server]

Restart MySQL and back up the file.

# service mysqld restart
# mysql -u root -p dbname < /home/backup.sql

This finishes the process of the data backup in UTF8. I used phpmyadmin and confirmed Japanese characters were displayed in the backup file.

Copy the content of the file and paste it onto a text editor which can convert the character set. Then, save the file in UTF8 format.

This post is also available in other languages.