convert mysql database to utf8

mysql convert database to utf8

#!/bin/bash
# Use this to convert a database from latin1 (or any other) encoding to utf8 enconding
# Written by [email protected]: http://www.ninjaconcept.com/
 
# MySQL Hostname
DBHOST='localhost'
 
# MySQL Username
DBUSER='root'
 
# MySQL Password
DBPASSWD='root'
 
# temp file location
TEMPFILELOC='/tmp/'

# temp file name
TEMPFILE='database-dump.txt'
TEMPFILE_UTF8='database-dump-utf8.txt'
 
#Verify the Arguments
if [ $# -ne 1 ];
then
echo "USAGE: mysqlconvertdbutf8.sh DATABASE"
exit 1
fi
#Grab the Args
# MySQL Database
DB=$1

# dump the original database
mysqldump -h ${DBHOST} --user=${DBUSER} -p${DBPASSWD} --default-character-set=latin1 -c --insert-ignore --skip-set-charset ${DB} > $TEMPFILELOC$TEMPFILE

# convert dump to utf8
iconv -f ISO8859-1 -t UTF-8 $TEMPFILELOC$TEMPFILE > $TEMPFILELOC$TEMPFILE_UTF8

# replace sql instructions
perl -pi -w -e 's/CHARSET=latin1/CHARSET=utf8/g;' $TEMPFILELOC$TEMPFILE_UTF8

# drop the database and recreate as utf8
mysql --user=${DBUSER} -p${DBPASSWD} --execute="DROP DATABASE $DB; CREATE DATABASE $DB CHARACTER SET utf8 COLLATE utf8_general_ci;"

# re import the converted sql file
mysql --user=${DBUSER} -p${DBPASSWD} --max_allowed_packet=16M --default-character-set=utf8 $DB < $TEMPFILELOC$TEMPFILE_UTF8


#Cleanup after yourself, delete the tempfile
rm -f $TEMPFILELOC$TEMPFILE
rm -f $TEMPFILELOC$TEMPFILE_UTF8
 
exit 0