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

:Param	:Description	:Default	:Type
login_password	Root's password to login to MySQL		String
new_password	New desired Root password		String
user	MySQL user	root	String
login_host	host to connect to	localhost	String
hosts	List of hosts for the provided user i.e ['localhost', '127.0.0.1', '::1'], Note: all will have the same new password	[‘localhost’]	List
change_root_password		True	Boolean
remove_anonymous_user		True	Boolean
disallow_root_login_remotely		False	Boolean
remove_test_db		True	Boolean
disable_unix_socket	Disable login with unix_socket	False	Boolean

---

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