mysql_secure_installation_Ansible

✋ Wait a second !

This module has been refactored to get rid of the dependencies issue across distributions, And it's much simpler now.

Updates

• use pymysql lib instead of MySQLdb 👎
• Enable authentication with unix_socket
• Add an option to disable unix_socket
• make the output more understandable
• run different commands based on different MySQL versions (Due to changes in newer MySQL versions)

To do,

• Update the module doc
• Test with more distributions
• Validate that disable_unix_socket works well in MySQl version >= 1.4

Features

An Idempotent Ansible Module that provides the functions of mysql_secure_installation

• Change MySQL Root Password - for a list of hosts i.e localhost, 127.0.0.1, ::1, .etc.
• Remove Anonymous User
• Disallow Root Login Remotely
• Remove Test Database
• disable unix_socket

💎 The Module is Idempotent Means that when you run it again, will not re-execute the commands If the desired state meets the current state

Dependencies

This is NOT something to worry about, It is something to make sure it's meet if you faced an error

1. mysqladmin command (already installed with MySQL/Mariadb) -- Needed to get information such as unix_socket location & MySQL version
2. python-pymysql which can be easily installed using the pkg manager e.g: apt, yum
   • The only caveat is that this package name may differ between distributions e.g: python3-pymysql or python36-pymysql (Trying to cover all the possible differences in the example provided)

Usage

💎 A full sample is provided at sample-playbook.yml which installs & secures MySQL -- Workes on the tested distributions below

# Modify the hosts
ansible-playbook sample-playbook.yml

• To use a custom Ansible Module:
  • create a directory called library in your playbook or your role's directory

cd my_playbook_folder
# OR
# cd my_role_folder
mkdir library
cp mysql_secure_installation.py library/

• 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']

Test

Below, is a list of the tested distributions

📌 The tests are done by an automated Jenkins pipeline

Distribution	Test result	Comment
Centos 7
Centos 8
fedora-34
Debian 10
Debian 11
Ubuntu 16.04		Make sure you're not using an old version (Related to example syntax)
Ubuntu 18.04
Ubuntu 20.04

💎 I'll be more than happy when you let me know if you faced an error !

Updates / News

as of Mariadb v10.4+ we can not use update mysql.user > Currently that affects disable_unix_socket option for Mariadb versions above 10.4 (need some investigation & will update the module)

Error produced

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

• https://stackoverflow.com/a/64841540

Input

Debug Output

• Note: The Module throws a Warning instead of an Error if the both the login_password & new_password are incorrect

Sample output

Known issues

No package matching 'python*-pymysql' is available

If you face this, don't worry it's NOT an issue, the problem is that python-pymysql might has a different name on the distro you're using

You'll probably face this if you are using a non tested distribution

Fix

Just Update pymysql package name with the correct name in the playbook

Extra mile

It is much appreciated ♥️ if you share package name on your tested distribution either with opening an issue or a pull request.

Read the Module’s Documentation

cd playbook_directory
# OR
cd role_directory
ansible-doc -M library mysql_secure_installation -v

Please leave a ⭐ if you found it useful

Thank you

Maintainer: Eslam Gomaa