Converting mySQL latin1 to utf8

The solution

What you need to do is actually convert the character set to utf8, in order to do this your going to need to run it through a program you could use iconv if you are already familiar with it, however if your system has python installed you can grab a copy of my sysadmin program which has iconv like functionality but is far more user friendly.

What you will need

• Text Editor (vi/nano/pico/emacs)


• Python 2.4 or higher


• SED package


• Sysadmin program


• mySQL

Preparing the file

This assumes the database is currently using latin1, in theory this could be any encoding.

Get a dump of the database:

# mysqldump --set-character-set=latin-1 --set-charset -u user -pPASSWORD databasename > databasename-latin1.sql

Now you have to be aware of what you need to replace using SED, you can’t just replace all instances of ‘latin1? as Murphy’s law being as it is means that somewhere there will be ‘latin1? in the physical content, especially for instance if I was using a mysql dump from this blog.

As such you need to replace the following:

#  /*!40101 SET NAMES latin1 */;

If your database dump is small enough (sub 100mb) you can edit this line directly in your text editor, alternatively you can do the following.

cat ./databasename-latin1.sql | sed 's/SET NAMES latin1/SET NAMES utf8/g' > tmp

cat ./tmp > ./databasename-latin1.sql

rm -f ./tmp

Now you need to replace all instances of ‘CHARSET=latin1?

cat ./databasename-latin1.sql | sed 's/CHARSET=latin1/CHARSET=utf8/g' > tmp

cat ./tmp > ./databasename-latin1.sql

rm -f ./tmp

Now we have to run the file through the charset converter

sysadmin -c iconv -d ./databasename-latin1.sql,latin-1,utf-8

If your sql dump is over 30mb, you will be prompted to confirm you wish to proceed, please remember that this will load the entire file into memory, so ensure you have enough available system memory before proceeding, I also suggest not running this on a production server.

If any characters could not be converted you will be alerted to their exact position within the file, from there you will either need to use sed to replace the character or use your text editor.

If all went well you now have ./databasename-latin1.sql.utf-8 (note the utf-8 extension), you now have a complete utf8 mySQL dump, all you need do now is import the dump.

cat ./databasename-latin1.sql | sed 's/SET NAMES latin1/SET NAMES utf8/g' > tmp

cat ./tmp > ./databasename-latin1.sql

rm -f ./tmp

Now you need to replace all instances of ‘CHARSET=latin1?

cat ./databasename-latin1.sql | sed 's/CHARSET=latin1/CHARSET=utf8/g' > tmp

cat ./tmp > ./databasename-latin1.sql

rm -f ./tmp

Now we have to run the file through the charset converter

sysadmin -c iconv -d ./databasename-latin1.sql,latin-1,utf-8

If your sql dump is over 30mb, you will be prompted to confirm you wish to proceed, please remember that this will load the entire file into memory, so ensure you have enough available system memory before proceeding, I also suggest not running this on a production server.

If any characters could not be converted you will be alerted to their exact position within the file, from there you will either need to use sed to replace the character or use your text editor.

If all went well you now have ./databasename-latin1.sql.utf-8 (note the utf-8 extension), you now have a complete utf8 mySQL dump, all you need do now is import the dump.