This playbook installs MySQL using distro-aware package handling and applies a minimal service baseline with current best practices for DevOps environments.
- name: Install MySQL
hosts: mysql
become: true
vars:
mysql_packages_debian:
- mysql-server
- mysql-client
mysql_packages_redhat:
- mysql-server
- mysql
tasks:
- name: Install packages on Debian family
apt:
name: "{{ mysql_packages_debian }}"
state: present
update_cache: true
when: ansible_os_family == "Debian"
- name: Install packages on RedHat family
yum: # Using yum for broader compatibility, can use dnf on newer systems
name: "{{ mysql_packages_redhat }}"
state: present
when: ansible_os_family == "RedHat"
- name: Enable and start service
systemd:
name: "{{ 'mysql' if ansible_os_family == 'Debian' else 'mysqld' }}"
state: started
enabled: true
daemon_reload: yes
- name: Validate binary availability
command: mysql --version
register: mysql_version
changed_when: false
failed_when: false
- name: Show version/check output
debug:
var: mysql_version.stdout
For production environments, consider this more playbook:
- name: Install and Configure MySQL with Security Hardening
hosts: mysql
become: true
vars:
mysql_packages_debian:
- mysql-server
- mysql-client
mysql_packages_redhat:
- mysql-server
- mysql
mysql_root_password: "{{ vault_mysql_root_password }}" # Store in Ansible Vault
mysql_bind_address: "127.0.0.1" # Restrict to localhost by default
mysql_max_connections: 100
pre_tasks:
- name: Wait for system to become reachable
wait_for_connection:
- name: Gather package facts
package_facts:
manager: auto
tasks:
# Install MySQL packages
- name: Install packages on Debian family
apt:
name: "{{ mysql_packages_debian }}"
state: present
update_cache: true
when: ansible_os_family == "Debian"
- name: Install packages on RedHat family
yum:
name: "{{ mysql_packages_redhat }}"
state: present
when: ansible_os_family == "RedHat"
# Ensure MySQL service is started and enabled
- name: Enable and start MySQL service
systemd:
name: "{{ 'mysql' if ansible_os_family == 'Debian' else 'mysqld' }}"
state: started
enabled: true
daemon_reload: yes
# Wait for MySQL to be ready
- name: Wait for MySQL to become available
wait_for:
host: "{{ mysql_bind_address }}"
port: 3306
delay: 5
timeout: 30
delegate_to: localhost
# Configure MySQL root password if not already set
- name: Check if root password is already set
command: mysql -u root -e "SELECT 1;"
register: mysql_check
failed_when: false
changed_when: false
- name: Set MySQL root password (first run)
mysql_user:
name: root
password: "{{ mysql_root_password }}"
login_unix_socket: "/var/run/mysqld/mysqld.sock"
state: present
when: mysql_check.rc != 0
# Remove anonymous users
- name: Remove anonymous MySQL users
mysql_user:
name: ""
host_all: yes
state: absent
login_user: root
login_password: "{{ mysql_root_password }}"
# Disallow root login remotely
- name: Remove remote root access
mysql_user:
name: root
host: "{{ item }}"
state: absent
login_user: root
login_password: "{{ mysql_root_password }}"
loop:
- "%"
- "{{ ansible_default_ipv4.address }}"
- "{{ ansible_hostname }}"
when:
- ansible_default_ipv4.address is defined
- item != "localhost"
# Remove test database
- name: Remove test database
mysql_db:
name: test
state: absent
login_user: root
login_password: "{{ mysql_root_password }}"
# Create custom configuration file
- name: Create MySQL configuration directory
file:
path: /etc/mysql/conf.d
state: directory
owner: root
group: root
mode: '0755'
when: ansible_os_family == "Debian"
- name: Create MySQL configuration directory (RHEL)
file:
path: /etc/my.cnf.d
state: directory
owner: root
group: root
mode: '0755'
when: ansible_os_family == "RedHat"
- name: Configure MySQL with security and performance settings
template:
src: mysql_custom.cnf.j2
dest: "{{ '/etc/mysql/conf.d/custom.cnf' if ansible_os_family == 'Debian' else '/etc/my.cnf.d/custom.cnf' }}"
owner: root
group: root
mode: '0644'
notify: restart mysql
# Validate installation
- name: Validate MySQL installation
command: mysql --version
register: mysql_version
changed_when: false
- name: Show MySQL version
debug:
msg: "MySQL version: {{ mysql_version.stdout }}"
- name: Check MySQL service status
command: mysql -u root -p{{ mysql_root_password }} -e "SELECT VERSION();"
register: mysql_service_check
changed_when: false
- name: Display MySQL version from service
debug:
msg: "Connected to MySQL: {{ mysql_service_check.stdout }}"
handlers:
- name: restart mysql
systemd:
name: "{{ 'mysql' if ansible_os_family == 'Debian' else 'mysqld' }}"
state: restarted
mysql_custom.cnf.j2)Create this template file in your Ansible roles directory:
[mysqld]
# Network
bind-address = {{ mysql_bind_address }}
port = 3306
skip-name-resolve
# Connections
max-connections = {{ mysql_max_connections }}
# Character set / collation
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# InnoDB baseline (adjust based on host RAM and workload)
innodb-buffer-pool-size = 1G
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-flush-method = O_DIRECT
# Binary logging (recommended for recovery/replication)
server-id = {{ inventory_hostname | hash('md5') | truncate(8, True, '') | int + 1 }}
log-bin = mysql-bin
binlog-expire-logs-seconds = 604800
# Slow query logging
slow-query-log = ON
slow-query-log-file = /var/log/mysql/mysql-slow.log
long-query-time = 0.5
# General hardening
local-infile = OFF
Example inventory file (inventory.yml):
all:
children:
mysql:
hosts:
mysql-primary:
ansible_host: 192.168.1.10
mysql-secondary:
ansible_host: 192.168.1.11
vars:
ansible_user: deploy
ansible_ssh_private_key_file: ~/.ssh/id_rsa
# Basic installation
ansible-playbook -i inventory.yml mysql-install.yml
# With extra variables
ansible-playbook -i inventory.yml mysql-install.yml --extra-vars "mysql_root_password=secure_password"
# Using Ansible Vault for passwords
ansible-playbook -i inventory.yml mysql-install.yml --ask-vault-pass