lunes, 4 de febrero de 2013

MySQL convert latin1 database to utf8

On the necessity of migrating the character set on a MySQL database, it is possible to easily achieve following these steps.

1.- Exporting database structure, it is important exporting to original character set.

mysqldump --default-character-set=latin1 --skip-set-charset -d -u root -p bd1 > bd1_estructura.sql

2.- Export data to a dump file, also exporting to original character set.
mysqldump --default-character-set=latin1 --skip-set-charset -t -u root -p bd1 > bd1_data.sql
 

3.- On the first dump file, change the character set with the new desired character set, in this case utf-8.

DEFAULT CHARSET=latin1
to
DEFAULT CHARSET=utf8

4.- Create the new database with utf-8 database character set.
create database bd2 default character set utf8 collate utf8_general_ci;

5.- Import structure with utf-8 character set.
mysql --default-character-set=utf8 -u root -p bd2 < bd1_estructura.sql

6.- Finally import data, also with utf-8 character set.
mysql --default-character-set=utf8 -u root -p bd2 < bd1_data.sql