Shell script to change and convert mysql databases charset and collate.

mysql-convert-charset.sh

#!/bin/bash

username='username'
password='password'
database='database'

charset='utf8mb4'
collate='utf8mb4_unicode_ci'

backup=1

set -- `getopt -u -o u:p::d: -l user:,password:,database:,nobackup -- "$@"`

while true; do
    case $1 in
        -u|--user)
            username=$2
            shift 2
            ;;
        -p|--password)
            password=$2
            shift 2
            ;;
        -d|--database)
            database=$2
            shift 2
            ;;
        --nobackup)
            backup=0
            shift 1
            ;;
        *)
            break
            ;;
    esac
done

if [ "$backup" == 1 ]; then
    backupFile="${database}-`date '+%Y%m%d%H%M%S'`.sql"
    echo ''
    echo "Backuping $backupFile ..."
    mysqldump -u $username -p$password $database > $backupFile
fi

echo "Changing charset of database: $database"
mysql -u $username -p$password $database -s -e "ALTER DATABASE $database CHARACTER SET = $charset COLLATE = $collate"

for table in $(mysql -u $username -p$password $database -s --skip-column-names -e 'show tables')
do
    echo ''
    echo "Changing charset of table: $table"
    mysql -u $username -p$password $database -s -e "ALTER TABLE \`$table\` CHARACTER SET $charset COLLATE $collate"

    echo "Converting charset of table: $table"
    mysql -u $username -p$password $database -s -e "ALTER TABLE \`$table\` CONVERT TO CHARACTER SET $charset COLLATE $collate"
done

echo ''
echo 'Conversion done!'
echo ''
echo 'Optimizing tables...'
echo ''

mysqlcheck -u $username -p$password $database --auto-repair --optimize

echo ''
echo 'Done! Have a nice day! ;)'