ansible answers to mysql_secure_installation

I can't realize how to write a task, that answers mysql_secure_installation script questions.

I only have

shell: mysql_secure_installation  <<< '1111' executable=/bin/bash

and no ideas on how to continue answering. What would be the best way to solve this? Thanks in advance!

Solution 1:

I think you best bet is to write a playbook (or better, change your mysql role) that will reproduce mysql_secure_installation script. There are several reasons for this :

• the script will always return 'changed', everytime you run your playbook, which is not something you want
• writing tasks is more flexible : you can add, remove, change and adapt what you want to do according to your setup
• you can learn in the process

Basically, mysql_secure_installation does this :

1. sets the root password
2. removes anonymous users
3. removes root remote access
4. removes the test database

Assuming you have set up mysql_root_password, and added python-mysqldb like so :

    - name: Adds Python MySQL support on Debian/Ubuntu
      apt: pkg="python-mysqldb" state=present
      when: ansible_os_family == 'Debian'

    - name: Adds Python MySQL support on RedHat/CentOS
      yum: name=MySQL-python state=present
      when: ansible_os_family == 'RedHat'

this can be accomplished like this :

• Setting the root password

    - name: Sets the root password 
      mysql_user: user=root password="{{ mysql_root_password }}" host=localhost
      no_log: yes
  

• Removing anonymous users

    - name: Deletes anonymous MySQL server user for ansible_fqdn
      mysql_user: user="" host="{{ ansible_fqdn }}" state="absent"
  
    - name: Deletes anonymous MySQL server user for localhost
      mysql_user: user="" state="absent"
  

• Removing root remote access

    - name: Secures the MySQL root user for IPV6 localhost (::1)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="::1"
      no_log: yes
  
    - name: Secures the MySQL root user for IPV4 localhost (127.0.0.1)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="127.0.0.1"
      no_log: yes 
  
    - name: Secures the MySQL root user for localhost domain (localhost)
      mysql_user: user="root" password="{{ mysql_root_password }}" host="localhost"
      no_log: yes 
  
    - name: Secures the MySQL root user for server_hostname domain
      mysql_user: user="root" password="{{ mysql_root_password }}" host="{{ ansible_fqdn }}"
      no_log: yes
  

• Removing the test database

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

This should do it. Note that I took a quick glance à the mysql_secure_installation on my system. I might have skipped something or there might be other steps included in other versions. YMMV !

Solution 2:

This is what worked for me:

- name: Adds Python MySQL support on Debian/Ubuntu
  apt: pkg="python-mysqldb" state=present
  when: ansible_os_family == 'Debian'

- name: Adds Python MySQL support on RedHat/CentOS
  yum: name=MySQL-python state=present
  when: ansible_os_family == 'RedHat'

- name: Set the root password 
  mysql_user: login_user=root login_password="{{ root_password }}" user=root password="{{ root_password }}"

- name: Secure the root user for IPV6 localhost (::1)
  mysql_user: login_user=root login_password="{{ root_password }}" user=root password="{{ root_password }}" host="::1"

- name: Secure the root user for IPV4 localhost (127.0.0.1)
  mysql_user: login_user=root login_password="{{ root_password }}" user=root password="{{ root_password }}" host="127.0.0.1"

- name: Secure the root user for localhost domain
  mysql_user: login_user=root login_password="{{ root_password }}" user=root password="{{ root_password }}" host="localhost"

- name: Secure the root user for server_hostname domain
  mysql_user: login_user=root login_password="{{ root_password }}" user=root password="{{ root_password }}" host="{{ ansible_fqdn }}"

- name: Deletes anonymous server user
  mysql_user: login_user=root login_password="{{ root_password }}" user="" host_all=yes state=absent

- name: Removes the test database
  mysql_db: login_user=root login_password="{{ root_password }}" db=test state=absent

Solution 3:

Take a look at this Ansible Module, it provides an easy and idempotent way for mysql_secure_installation in Ansible

Example - with a fresh MySQL Installation

- name: test mysql_secure_installation
  mysql_secure_installation:
    login_password: ''
    new_password: password22
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']
    change_root_password: true
    remove_anonymous_user: true
    disallow_root_login_remotely: true
    remove_test_db: true
  register: mysql_secure

# To see detailed output
- debug:
    var: mysql_secure

Example - Change an existing root password

- name: test mysql_secure_installation
  mysql_secure_installation:
    login_password: password22
    new_password: password23
    user: root
    login_host: localhost
    hosts: ['localhost', '127.0.0.1', '::1']

For usage: All you have to do is create a dir called library in your playbooks or role's dir and copy the mysql_secure_installation.py to it,

you can find a Full example in the following Link

https://github.com/eslam-gomaa/mysql_secure_installation_Ansible