Restore backup data of MySQL with UTF8
<Backup by mysqldump>
# mysqldump --default-character-set=latin1 -u root -p dbname > /home/
backup.sql
This time, the default was latin1, so specify with option. When you open the file, you can see if it is displayed well in Japanese.
[Check the context of Sakura Editor]
Copy the mysqldump file which was opened with UTF8 to Sakura Editor and save as UTF8 style. Write the following on the top of backup.sql in order to make UTF8
SET NAMES utf8;
Now it will be input with UTF8.
<Restore the data>
When you restore the data, the following error often appears.
Got a packet bigger than 'max_allowed_packet' bytes
This is probably because the size of the data is too large when you input it. Use --max_allowed_packet as an option.
mysql --max_allowed_packet=128M -u root -p dbname < /home/backup.sql
Change the storage capacity properly. If still it does not work well, write the setting in /etc/my.cnf
[mysqld]
・・・
・・
・
max_allowed_packet=128M
[mysql.server]
Restart MySQL and restore.
# service mysqld restart
# mysql -u root -p dbname < /home/backup.sql
Now the backuped data is restored as UTF8, and you can the display even from phpmyadmin in Japanese.
Tags: UTF8 backup phpmyadmin character code transformed character MySQL

