Further Notes on Configuring MySQL in Ansible

  • Tue 13 September 2016
  • misc

A few months ago I wrote about changing MySQL's password in Ansible.

A few more notes about the surrounding ecosystem are probably in order if the time I wasted tonight is any indication (even though I'd already solved this problem once and in fact had the recipe in another ansible role's task list).

It's only natural to copy in some MySQL configuration files, restart MySQL, and then get down to business with configuring users. But if you don't wait for the restart to finish, Ansible is quite likely to try to set the user's password before there's actually a database listener. It'll throw an error, and leave you scratching your head since it works fine from the command line.

Moreover, the next thing you should do afterwards is a .my.cnf file so that your subsequent commands will actually execute properly against the database. Can't put it in first though since the passwords will be wrong and could cause you grief even if the MySQL root user's password isn't set yet.

The sequence of events needs to go something like this:

{% raw %}

# necessary for the mysql functions in ansible:

- pkgin: name="build-essential" state=present

- pkgin: name="py27-pip" state=present

- pip: name="MySQL-python" state=present

# blah blah stuff stuff

- service: name=mysql enabled=yes state=restarted

- name:  letting mysql finish restarting...
  pause:  seconds=10

- mysql_user: name=root password=obviouspw priv=*.*:ALL,GRANT host={{ item }} state=present
     - "{{ ansible_hostname }}"
     - ::1
     - localhost

- copy: src="my.cnf" dest="/root/.my.cnf" owner=root group=root mode=0600

- mysql_db: name=somedatabase state=present

- mysql_db: name=somedatabase state=import target=/home/junk/somedatabase.sql
  ignore_errors: yes

{% endraw %}

Note too that (notwithstanding the Ansible docs), the semantics of "state=import" don't seem to imply "state=present".

As I said before, you could also decide to not run MySQL...