mysql

CiS Compliant Apache License GitHub release (latest by date)

OSM: MySQL

A high end ansible role to setup standalone or a cluster MySQL with best practices in terms of security and performance tunning.

Key Features

  • CIS compliant
  • Best Practices(Performance Tunning)
  • Database and User Management

Requirements

No special requirement, only root access of the server is required.

Role Variables

We have categorized variables into two part i.e. Manadatory and Optional

Mandatory Variables

Variable Default Value Possible Values Description
mysql_root_username root Root Username Name of the admin user of MySQL
mysql_root_password N0Tweak$_@123! Any Strong Password A strong password for MySQL root user
mysql_replication_user.user slave Any Username Name of the slave user
mysql_replication_user.password N0Tweak$_@123! Any Strong Password A strong password for MySQL Slave
mysql_install_packages true true or false Whether you want to install MySQL packages. Set the value false if you need only configuration part
replication true true or false If you don't want to setup slave, set the value false
users_creation true true or false Whether you want to include tasks for user creation or not
database_creation true true or false Whether you want to include tasks for database creation or not

Optional Variables

Variable Default Value Possible Values Description
mysql_config_file /etc/mysql/my.cnf Any Linux Path Configuration file location of MySQL
mysql_slow_query_log_file /var/log/mysql/mysql-slow.log Any Linux Path Log file location of MySQL slow query
mysql_log_error /var/log/mysql/mysql.err Any Linux Path Log file location of MySQL errors
mysql_max_binlog_size 100M Size in MB Maximum size of bin log files in MySQL
mysql_binlog_format MIXED ROW or COLOUMN or MIXED Binlog format of MySQL
debian_mysql_version 5.7 Any MySQL Version Which version of MySQL you want to install on Debian System
mysql_redhat_version el7 Any MySQL Version Which version of MySQL you want to install on RedHat System

The rest of the things are in defaults

Inventory

An example inventory could be like this:-

[master]
master_server1

[slave]
slave_server1

[mysql]
master_server1
slave_server1

[mysql_cluster:children]
mysql
master
slave

[mysql_cluster:vars]
ansible_user=ubuntu

Leave blank master and slave if you don't want Master-Slave setup.

Example Playbook

Here is an example of playbook to execute this role:-

---
- hosts: mysql_cluster
  roles:
    - role: osm_mysql
      become: yes

Usage

There are multiple ways of executing the playbook according to your environment

  • To run complete role
ansible-playbook -i hosts site.yml
  • To create users
ansible-playbook -i hosts site.yml --tags "create_user"
  • To create databases
ansible-playbook -i hosts site.yml --tags "create_database"

Running Test Cases for Setup and CIS Benchmarks

For running the test cases, we have a seperate folder named inspec. Inspec (https://www.inspec.io/) should be installed if you want to run the test cases.

Command which needs to be run

inspec exec . -t ssh://username@server_ip -i /path/to/keyfile

For further test results information, you can go through the README.md of inspec profile.

References

Here we do have some of our OpsTree blog's regarding MySQL.

https://blog.opstree.com/2019/03/26/stay-away-replication-lag/

https://blog.opstree.com/2019/07/23/mysql-monitoring/

https://blog.opstree.com/2019/09/24/mysql-data-at-rest-encryption/

https://blog.opstree.com/2018/12/11/setting-up-mysql-monitoring-with-prometheus/

Author

Abhishek Dubey

To Do

  • Enable SSL communication between master and slave
Install
ansible-galaxy install OT-OSM/MySQL
GitHub repository
License
Unknown
Downloads
56