Bash script to convert MySQL database that contains UTF-8 data inside tables with latin1 encoding

convert_latin1_to_utf8.sh

#!/bin/bash

# based on http://www.whitesmith.co/blog/latin1-to-utf8/

set -e

if [ "$#" -ne 2 ]
then
  echo "Usage: $0 database_name /path/to/database_dump.sql"
  exit 1
fi

read -s -p "Enter MySQL root password: " mysql_password

# create options file for MySQL
mysql_options_file="$(mktemp -q --tmpdir "${inname}.XXXXXX")"
trap 'rm -f "$mysql_options_file"' EXIT
chmod 0600 "$mysql_options_file"
cat >"$mysql_options_file" <<EOF
[client]
password="${mysql_password}"
EOF

echo -e "\n\nConverting..."

database_name=$1
temp_database_name="$1_temp"
original_dump=$2
schema_dump="/tmp/$1_schema.sql"
data_dump="/tmp/$1_data.sql"

# remove NULL characters from dump
sed -i 's/\x0//g' $original_dump

# create the databases
mysql --defaults-extra-file="$mysql_options_file" -u root -e "CREATE SCHEMA $temp_database_name DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;"
mysql --defaults-extra-file="$mysql_options_file" -u root -e "CREATE SCHEMA $database_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"

# import the latin1 dump
mysql --defaults-extra-file="$mysql_options_file" -u root --default-character-set=latin1 $temp_database_name -e "SOURCE $original_dump;"

# export the schema, replace the charset and import the schema as utf8mb4
mysqldump --defaults-extra-file="$mysql_options_file" -u root --no-data --skip-set-charset --default-character-set=latin1 $temp_database_name -r $schema_dump
sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g' $schema_dump
mysql --defaults-extra-file="$mysql_options_file" -u root --default-character-set=utf8mb4 $database_name -e "SET names 'utf8mb4'; SOURCE $schema_dump;"

# export the data and import the data as utf8mb4
mysqldump --defaults-extra-file="$mysql_options_file" -u root --no-create-db --no-create-info --insert-ignore --skip-set-charset --default-character-set=latin1 $temp_database_name -r $data_dump
mysql --defaults-extra-file="$mysql_options_file" -u root --default-character-set=utf8mb4 $database_name -e "SET names 'utf8mb4'; SOURCE $data_dump;"

# cleanup
mysql --defaults-extra-file="$mysql_options_file" -u root -e "DROP DATABASE $temp_database_name;"
rm $schema_dump $data_dump