Hi Ferran:
On Thu, 14 May 2009, Ferran Jorba wrote:
> iconv -c -f utf8 -t windows-1252 $dbdump >$dbdump.utf8
You don't need to do any iconv business, you can let MySQL convert the
charsets for you while loading. Here are some old raw notes to give you
an idea: (supposing you have a big Latin-1 database that you want to
dump and load as UTF-8)
1. dump DB struct only:
$ time mysqldump -u root -p --databases cdsweb20080403
--default-character-set=latin1 --no-data > cdsweb20080403-struct-only.sql
# takes 6 secs
2. dump DB data:
$ time mysqldump -u root -p cdsweb20080403 --default-character-set=latin1
--no-create-info > cdsweb20080403-data-only.sql
# full DB: DB files are 9.3G worth, dump takes 26 mins, dump file is 9G
long
# without those idx* and rnk*: DB files are worth 6.5G, dump takes 11
mins, dump file is 6.1G long
3. edit DB struct dump to change latin1 into utf8:
$ cp cdsweb20080403-struct-only.sql cdsdev-struct-only.sql
$ perl -pi -e 's,latin1,utf8,g' cdsdev-struct-only.sql
$ perl -pi -e 's,cdsweb20080403,cdsdev,g' cdsdev-struct-only.sql
4. load new DB structure:
$ echo "DROP DATABASE cdsdev" | mysql -u root -p
$ time cat cdsdev-struct-only.sql | mysql --default-character-set=utf8 -u
root -p
# takes 20 secs
5. create symlink for the new DB:
# /etc/init.d/mysqld stop
# mv /var/lib/mysql/cdsdev /opt2/mysql-data
# ln -s /opt2/mysql-data/cdsdev /var/lib/mysql/cdsdev
# /etc/init.d/mysqld start
6. split DB data file in order to edit its start (only one latin1 occurrence
there):
$ time split -b 100m cdsweb20080403-data-only.sql cdsdev-data-only-parts-
# takes 3 minutes
$ vim cdsdev-data-only-parts-aa # change latin1 to utf8
$ time cat cdsdev-data-only-parts-?? > cdsdev-data-only.sql
# takes 3 minutes
$ \rm cdsdev-data-only-parts-??
7. load new DB data: (note: maybe we can already apply ALTER TABLE
statements here in order to enlarge varchar() for UTF-8 heavy sites)
$ time cat cdsdev-data-only.sql | mysql --default-character-set=utf8 -u root
-p cdsdev
# takes 25 minutes
[...]
Best regards
--
Tibor Simko ** CERN Document Server ** <http://cds.cern.ch/>
