How to do mysql_secure_installation via ansible playbook?

Question

I managed to install Apache Mysql/Mariadb and PHP using playbook. How can I do mysql_secure_installation using ansible?

I am a beginner in Ansible. I want to set a new password to MySQL server and complete all security questions via playbook.

score 3 · Answer 1 · answered Jul 24 '20 at 12:33

3

I implemented this myself for my MariaDB installations some time back, and before I trusted anyone else to do it correctly. These are the steps I performed:

  # mysql_secure_installation
- name: Update MariaDB root password
  mysql_user: name=root host={{item}} password={{mysql_root_password}}
  with_items:
    - 127.0.0.1
    - ::1
    - localhost

- name: Set ~/.my.cnf file
  template: src=dotmy.cnf.j2 dest=/root/.my.cnf mode=0600

  # mysql_secure_installation
- name: Delete anonymous MySQL user
  mysql_user: name="" host={{item}} state=absent
  with_items:
    - localhost
    - "{{ansible_nodename}}"

  # mysql_secure_installation
- name: Delete Hostname based MySQL user
  mysql_user: name=root host="{{ansible_nodename}}" state=absent

  # mysql_secure_installation
- name: Remove MySQL test database
  mysql_db: name=test state=absent

You'll have to decide how to create mysql_root_password yourself.

answered Jul 24 '20 at 12:33

Michael Hampton

244,070
43
506
972

[Ansible: How to change MySQL server root password by reprovisioning the server](https://dba.stackexchange.com/questions/102066/ansible-how-to-change-mysql-server-root-password-by-reprovisioning-the-server/) can be trusted, I think. Especially in the part `login_password="{{ mysql_old_root_password }}"` is needed to change root's password. – Vladimir Botka Jul 24 '20 at 12:43
Here is set the initial password when the MariaDB has just been installed. You can of course have a play which changes the password. – Michael Hampton Jul 24 '20 at 12:46
The question was " to set a new password". – Vladimir Botka Jul 24 '20 at 12:48
That is because the root user has no password when it is originally installed. – Michael Hampton Jul 24 '20 at 13:22
1

It's more secure when he has one, of course. See [2.10.4 Securing the Initial MySQL Account](https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html). – Vladimir Botka Jul 24 '20 at 13:30
@VladimirBotka That is the whole point! He is trying to secure the server after installing it. – Michael Hampton Jul 24 '20 at 13:45
(I proceed here despite the "avoid extended discussions in comments" warning because it's important, I think.). Some installations create by default random root password which must be changed in a certain very short amount of time. See the last section "Post-Initialization root Password Assignment" in [2.10.1 Initializing the Data Directory](https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html). FWIW, see [secret.yml](https://github.com/vbotka/ansible-freebsd-mysql/blob/master/tasks/secret.yml) how to grab the random password on installation and change it. – Vladimir Botka Jul 24 '20 at 14:14

score 2 · Answer 2 · answered Aug 03 '22 at 14:55

You can do this with Ansible's Expect Module. Here is an example:

- name: secure mariadb
  become: yes
  expect:
    command: mysql_secure_installation
    responses:
      'Enter current password for root': ''
      'Set root password': 'n'
      'Remove anonymous users': 'y'
      'Disallow root login remotely': 'y'
      'Remove test database': 'y'
      'Reload privilege tables now': 'y'
    timeout: 1
  register: secure_mariadb
  failed_when: "'... Failed!' in secure_mariadb.stdout_lines"

Be aware: This configuration works for me but you may have to adjust the values according to your needs!

score 1 · Answer 3 · edited Mar 02 '21 at 10:31

I wrote a custom ansible module to do this: https://github.com/eslam-gomaa/mysql_secure_installation_Ansible .

Example

- 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

Henrik Pingel · Answer 4 · 2020-07-24T15:39:30.213

For idempotency reasons it is a good practice to implement the hardening steps done by mysql_secure_installation with dedicated Ansible tasks rather than executing mysql_secure_installation via Ansible directly.

mysql_secure_installation does the following:

set the root password
remove anonymous users
remove root remote access
remove the test database

Theses hardening tasks can be implemented with Ansible like this:

- name: test database is absent
  mysql_db: name=test state=absent
  when: mysql_remove_test_database

Check out dev-sec's MySQL hardening repository for an complete example how to implement the mysql_secure_installation steps with Ansible tasks.

How to do mysql_secure_installation via ansible playbook?

4 Answers4

Linked