Script para alterar o schema e collation do banco de dados de Latin1 para UTF-8

linux_migrate_db_latin1_to_utf8.sh

#!/bin/bash
echo 'Migrar banco de dados de Latin1 para UTF-8'
echo
echo '===================='
echo
echo 'Realizando backup do banco na pasta atual'

now="$(date +'%Y%m%d-%H%M')"

# Old DB
old_db_name=my_old_database_name
old_db_host=my_old_hostname
old_db_user=my_old_database_user
old_db_password=123456

# New DB
new_db_name=my_new_database_name
new_db_host=my_new_hostname
new_db_user=my_new_database_user
new_db_password=123456

# Backup path
backup='~/db_backup_'$now'.sql'

mysqldump -h $old_host -u $old_db_user -p$old_db_password -v --single-transaction $old_db_name > './'$backup

# Se já existir o banco de dados ainda não existir, pode descomentar a as linhas abaixo
# echo 'DROP DATABASE IF EXISTS '$new_db_name'; ' > ./create_utf8_db.sql
# echo 'CREATE DATABASE `'$new_db_name'` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ' >> ./create_utf8_db.sql
# echo 'CREATE USER '$new_db_user'@'$new_db_host' IDENTIFIED BY '$new_db_password'; ' >> ./create_utf8_db.sql
# echo 'FLUSH PRIVILEGES;'
# mysql -h $new_db_host -u $new_db_user -p$new_db_password < ./create_utf8_db.sql
# rm create_utf8_db.sql

echo
echo '===================='
echo
echo 'Copiando o schema do banco de dados atual para o novo já alterando o charset de Latin1 para utf8'

mysqldump -h $old_db_host -u $old_db_user -p$old_db_password $old_db_name -v --single-transaction --no-data --skip-set-charset --default-character-set=latin1 | sed 's/CHARSET=latin1/CHARSET=utf8/g' | mysql -h $new_db_host -u $new_db_user -p$new_db_password $new_db_name --default-character-set=utf8

echo
echo '===================='
echo
echo 'Passando os dados do banco atual para o novo'

mysqldump -h $old_db_host -u $old_db_user -p$old_db_password $old_db_name -v --no-create-db --single-transaction --no-create-info --skip-set-charset --default-character-set=latin1 | mysql -h $new_db_host -u $new_db_user -p$new_db_password $new_db_name --default-character-set=utf8

echo
echo 'Finalizado. Pressione qualquer tecla para sair...'
read
exit