TOP>database>MySQL

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.

 add this entry to hatena bookmark add this entry to livedoor clip add this entry to yahoo bookmark add this entry to del.icio.us add this entry to google bookmark
[ Create | Edit | Delete | Freeze ]    [ Recent | History | Help ]