Web Projects Consulting

MySQL latin1 Content Upgrade to an UTF-8 Database

The below is pretty useful if you have some databases in other than UTF-8 charset and simply importing dumps does not help.

Look at the conversion shell script:

#!/bin/bash
DBFROM="$1"
DBTO="$2"
LOGIN="$3"
PASS="$4"

mysqldump  --extended-insert=FALSE --default-character-set=latin1  -u $LOGIN -p$PASS $DBFROM > dp.sql

cat dp.sql |sed  -e 's/DEFAULT CHARSET=latin1;/DEFAULT CHARSET=utf8 COLLATE utf8_bin;/' > dp2.sql

cat dp2.sql |sed  -e 's/SET NAMES latin1/SET NAMES utf8/' > dp3.sql

echo "drop database $DBTO; create database $DBTO character set utf8 collate utf8_bin;"|mysql -u $LOGIN -p$PASS
mysql -u $LOGIN -p$PASS $DBTO < dp3.sql

Save to ~/bin/mysql_migrate, run:

mysql_migrate [database_name] [database_name] [database_user] [database_password]

Based on this article

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.