Install MySQL using Ansible, using an idempotent script

This Ansible role will install MySQL on a *nix and may be run multiple times without failure, even though root’s password is changed when running it.
The order is important and here are some tips:

  • The ‘etc.my.cnf’ template does not include user and password entries
  • The ‘.my.cnf’ template only includes user and password entries and is copied to root’s home directory (since my script runs as root), not the deploy’s home directory.
  • Root’s password is set for security reasons
  • Deploy’s only granted access to the application’s databases. I use db1 and db2 as examples here.

Put the below section in your /tasks/main.yml file.

  - name: Install MySQL packages
    apt: pkg={{item}} state=installed
      - bundler
      - mysql-server-core-5.5
      - mysql-client-core-5.5
      - libmysqlclient-dev
      - python-mysqldb
      - mysql-server
      - mysql-client
      - build-essential

- name: Remove the MySQL test database
action: mysql_db db=test state=absent

- name: Create global my.cnf
template: src=etc.my.cnf dest=/etc/mysql/my.cnf

- name: Create databases
mysql_db: name={{item}} state=present collation=utf8_general_ci encoding=utf8
- db1
- db2

- name: Add deploy DB user and allow access to news_* databases
mysql_user: name={{user}} password={{password}} host="%" priv=db1.*:ALL/db2.*:ALL,GRANT state=present

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

- name: Create local my.cnf for root user
template: src=my.cnf dest=/root/.my.cnf owner=root mode=0600

- name: Restart the MySQL service
action: service name=mysql state=restarted enabled=true

How to reconnect to a database when its connection was lost

One of my projects has a long-running task that constantly needs information from the database. I needed a mechanism to assure that the task will automatically reconnect to the database if and when that connection was broken.

I came up with this scheme using a trick with rescue blocks (code abbreviated for clarity) in this gist.

def my_task

    while(true) do
      rescue Exception => ex
          sleep 10
          retry # will retry the reconnect
          retry # will retry the database_access_here call

Here’s a line-by-line explanation:

Line 4: This is where your application’s database access logic would be.

Line 5: Catch a database access exception here

Here is where it gets interesting:

Line 7: Open a new block and retry the connection.

Line 10: This retry will retry the reconnect method and will loop as long as the database connection is still down.

Line 11: The else clause will execute if _no_ exception happened in line 10, and will retry the original database call in line 4.

In my case and example, I am not counting retries because I don’t care that I’ve failed – I must continue to retry. You may want to use “retry if retries < 3” as a break mechanism.

I have also removed some mailer code that notifies me when the reconnect fails so I can (manually) see what happened to the connection. The moment the connection is re-established, life goes on as normal within the infinite while loop.