Before undertaking such migration the first step is a lesson in understanding more about how latin1 and utf8 work and interact in MySQL. latin1 in a common and historical character set used in MySQL. utf8 first available in MySQL Version 4.1 is an encoding supporting multiple bytes and is the system default in MySQL 5.0
Migrating MySQL latin1 to utf8 – Character Set Options
Continuing on from preparation in our MySQL latin1 to utf8 migration let us first understand where MySQL uses character sets. MySQL defines the character set at 4 different levels for the structure of data.
Migrating MySQL latin1 to utf8 – The process
Having covered the preparation and character set options of performing a latin1 to utf8 MySQL migration, just how do you perform the migration correctly.
Complete story again at Migrating MySQL latin1 to utf8 – The process .
Migrating MySQL latin1 to utf8 – In House Version
Use this method if at all possible as it will attempt to recover non-English latin1 characters (accents, umlauts) in your existing data.
- Confirm your database is currently encoded in latin1.
- Make a fresh backup (ideally using mysqlhotcopy se notes below)
- Temporarily disable your cronjob so you don’t have anything trying to access the database.
- The steps below require the ‘iconv’ utility to ensure proper UTF8 encoding. Type ‘iconv –version’ at the shell to check if it’s installed. If not, install it from your platform’s package manager (e.g. apt-get, yum, rpm).
Dump the database schema (use your own user + password + database):
mysqldump -Q -d -u root -p
--default-character-set=latin1 --skip-set-charset
old_database | sed 's/latin1/utf8/gi' > dump.schema.sql
Dump the database data (use your own user + password + database):
mysqldump -Q --insert-ignore -t -u root -p
--default-character-set=latin1 --skip-set-charset
old_database | iconv -c -f utf8 -t utf8 > dump.data.sql
Create a new UTF-8 database:
CREATE DATABASE newdb_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;
Import into the new database:
mysql -u root -p --default-character-set=utf8
newdb_utf8 < dump.schema.sql;
mysql -u root -p --default-character-set=utf8
newdb_utf8 < dump.data.sql;
Notes:
Some helpfull hints
mysqlhotcopy:
Reference Here
Usage: (to local filesystem)
mysqlhotcopy -u backups -p`cat ~backups/.db.shadow`
--addtodest --noindices old_database ~backups/dbs/
Usage: (to SCP)
mysqlhotcopy -u backups -p`cat ~backups/.db.shadow`
--addtodest --noindices
--method='scp -c arcfour -C -2' old_database backups@remotehost:~backups/dbs/