mysql
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
To Do
- Enable SSL communication between master and slave
Install and configure MySQL with CIS best practices
ansible-galaxy install OT-OSM/MySQL