Convert latin1 to UTF-8 in MySQL

Well moving sites from one hosting to another can cause some series problems. Well i got two websites that are in my native language and when i move mysql base there i saw some garbled, weird and funny characters. Anyway i try to google some answers and this problem is because old mysql server version and some changes in coding (in many cases in WordPress platform). Anyway i don’t want to go with details about this but i found solution that helps me…. well in 99% because i still see some ??? (question marks) but not so many.

So i found this tweak that helps me:
TIP_Convert_latin1_to_UTF-8_in_MySQL.

I use this code:

Drop and create

Now it’s time to drop the old database and create a new one with UTF-8 support.

$ mysql --user=frog -p --execute="DROP DATABASE dbname;
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"

Import dump to databse

Last but not least, we need to import the converted data back to the new database.

$ mysql --user=frog --max_allowed_packet=16M -p --default-character-set=utf8 dbname < dump_utf8.sql

The max_allowed_packet-option is sometimes important. If your import ends up with a “ERROR 1153 at line 42: Got a packet bigger than ‘max_allowed_packet’”, you need to increase the packet size. Please mention, that you also need to update /etc/mysql/my.cnf and set max_allowed_packet=16M under the [mysqld] directive

… and that is it… simple, short and works for me !

If you enjoyed this post, make sure you subscribe to my RSS feed!