Ansible idempotent MySQL installation Playbook

Question

I want to setup a MySQL server on AWS, using Ansible for the configuration management. I am using the default AMI from Amazon (ami-3275ee5b), which uses yum for package management.

When the Playbook below is executed, all goes well. But when I run it for a second time, the task Configure the root credentials fails, because the old password of MySQL doesn't match anymore, since it has been updated the last time I ran this Playbook.

This makes the Playbook non-idempotent, which I don't like. I want to be able to run the Playbook as many times as I want.

- hosts: staging_mysql
  user: ec2-user
  sudo: yes

  tasks:
    - name: Install MySQL
      action: yum name=$item
      with_items:
        - MySQL-python
        - mysql
        - mysql-server

    - name: Start the MySQL service
      action: service name=mysqld state=started

    - name: Configure the root credentials
      action: command mysqladmin -u root -p $mysql_root_password

What would be the best way to solve this, which means make the Playbook idempotent? Thanks in advance!

Answer 1

I know this is an old question, but I am sharing my working playbook for those, who are looking for it:

mysql.yml

---
 - name: Install the MySQL packages
   apt: name={{ item }} state=installed update_cache=yes
   with_items:
     - mysql-server-5.6
     - mysql-client-5.6
     - python-mysqldb
     - libmysqlclient-dev

 - name: Copy the configuration file (my.cnf)
   template: src=my.cnf.j2 dest=/etc/mysql/my.cnf
   notify:
     - Restart MySQL

 - name: Update MySQL root password for all root accounts
   mysql_user: name=root host={{ item }} password={{ mysql_root_pass }} state=present
   with_items:
     - "{{ ansible_hostname }}"
     - 127.0.0.1
     - ::1
     - localhost

 - name: Copy the root credentials as .my.cnf file
   template: src=root.cnf.j2 dest=~/.my.cnf mode=0600

 - name: Ensure Anonymous user(s) are not in the database
   mysql_user: name='' host={{ item }} state=absent
   with_items:
     - localhost
     - "{{ ansible_hostname }}"

 - name: Remove the test database
   mysql_db: name=test state=absent
   notify:
     - Restart MySQL

vars.yml

---
 mysql_port: 3306 #Default is 3306, please change it if you are using non-standard
 mysql_bind_address: "127.0.0.1" #Change it to "0.0.0.0",if you want to listen everywhere
 mysql_root_pass: mypassword #MySQL Root Password

my.cnf.j2

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = {{ mysql_port }}
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = {{ mysql_bind_address }}
key_buffer              = 16M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 64M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

root.cnf.j2

[client]
user=root
password={{ mysql_root_pass }}

Answer 2

This is an alternative solution to the one proposed by @LorinHochStein

One of my constraints was to ensure that no passwords are stored in plain text files anywhere on the server. Thus .my.cnf was not a practical proposition

Solution :

- name: update mysql root password for all root accounts from local servers
  mysql_user: login_user=root 
              login_password={{ current_password }} 
              name=root 
              host=$item 
              password={{ new_password }} 
              priv=*.*:ALL,GRANT
  with_items:
      - $ansible_hostname
      - 127.0.0.1
      - ::1
      - localhost

And in the vars file

current_password: foobar
new_password: "{{ current_password }}"

When not changing the mysql password run ansible playbook on command line as usual.

When changing the mysql password, add the following to the command line. Specifying it on the commandline allows the parameter set on the command line to take precedence over the one defaulted to in the vars file.

$ ansible-playbook ........ --extra-vars "new_password=buzzz"

After running the command change the vars file as follows

current_password=buzzz
new_password={{ current_password }}

Answer 3

For ansible 1.3+ :

- name: ensure mysql local root password is zwx123
  mysql_user: check_implicit_admin=True login_user=root login_password="zwx123" name=root password="zwx123" state=present

Answer 4

Ansible version for a secure MySQL installation.

mysql_secure_installation.yml

- hosts: staging_mysql
  user: ec2-user
  sudo: yes

  tasks:
    - name: Install MySQL
      action: yum name={{ item }}
      with_items:
        - MySQL-python
        - mysql
        - mysql-server

    - name: Start the MySQL service
      action: service name=mysqld state=started

    # 'localhost' needs to be the last item for idempotency, see
    # http://ansible.cc/docs/modules.html#mysql-user
    - name: update mysql root password for all root accounts
      mysql_user: name=root host={{ item }} password={{ mysql_root_password }}
      with_items:
        - "{{ ansible_hostname }}"
        - 127.0.0.1
        - ::1
        - localhost

    - name: copy .my.cnf file with root password credentials
      template: src=templates/root/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

    - name: delete anonymous MySQL server user for $server_hostname
      action: mysql_user user="" host="{{ server_hostname }}" state="absent"

    - name: delete anonymous MySQL server user for localhost
      action: mysql_user user="" state="absent"

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

templates/root/my.cnf.j2

[client]
user=root
password={{ mysql_root_password }}

References

• The original answer by Lorin Hochstein
• https://github.com/gaspaio/ansible-devbox/blob/master/roles/mysql/tasks/server.yml

Answer 5

The following will Work (Insert my.cnf in between 2 mysql_user calls)

---

- name: 'Install MySQL'
    yum: name={{ item }} state=present
    with_items:
    - MySQL-python
    - mysql
    - mysql-server
    notify:
     - restart-mysql
- name: 'Start Mysql Service'
  action: service name=mysqld state=started enabled=yes
- name: 'Update Mysql Root Password'
  mysql_user: name=root host=localhost password={{ mysql_root_password }} state=present
- name: 'Copy Conf file with root password credentials'
  template: src=../templates/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600
- name: 'Update Rest-Mysql Root Password'
  mysql_user: name=root host={{ item }} password={{ mysql_root_password }} state=present
    with_items:
    - "{{ ansible_hostname }}"
    - "{{ ansible_eth0.ipv4.address }}"
    - 127.0.0.1
    - ::1
- name: 'Delete anonymous MySQL server user from server'
  mysql_user: name="" host={{ ansible_hostname }} state="absent"