Change MySQL Characterset using ansible

Ansible’ MySQL_db module doesn’t support changing Character set and collation once the DB is created. So i am using a shell script to achieve this.

This role will work with AWS aurora MySQL and standard MySQL installations.

Below is the shell script. and i will explain each variables


database='{{ mysql_db_name }}'
user='{{ vault_mysql_login_user }}'
pass='{{ vault_mysql_login_password }}'
{% if mysql_db_type_aurora %}
host='{{ vault_mysql_login_host }}'
{% endif %}
charset='{{ mysql_character_set }}'
collate='{{ mysql_collation }}'

echo "Changing charset of database: $database"
mysql -u$user -p$pass {% if mysql_db_type_aurora %} -h $host {% endif %} $database -s -e "ALTER DATABASE $database CHARACTER SET = $charset COLLATE = $collate;"

for table in $(mysql -u$user -p$pass {% if mysql_db_type_aurora %} -h $host {% endif %} $database -s --skip-column-names -e 'show tables')
  echo "Converting charset of table: $table"
  mysql -u$user -p$pass {% if mysql_db_type_aurora %} -h $host {% endif %} $database -s -e "ALTER TABLE $table CONVERT TO CHARACTER SET $charset COLLATE $collate"
  • database : Name of the DB to be updated
  • user : MySQL user with permission to alter DB
  • pass: MySQL user password
  • host : You need to specify the host if ansible cannot ssh to host
  • charset : new character set
  • collate : new collate

Clone my repo to your ansible roles folder [repo url]

Update the parameters as per the Readme.

mysql_db_type_aurora the parameter will decide how the playbook will be executed(in your ansible server or MySQL instance itself ).

Example playbook

- hosts: mysql
  gather_facts: false
    - mysql-db-update
ansible-playbook -i <inventory file> mysql-update.yml -e mysql_db_type_aurora=true

Leave a Reply