Ansible for PostgreSQL and MySQL: Database Automation Complete Guide

# PostgreSQL
ansible-galaxy collection install community.postgresql

# MySQL / MariaDB
ansible-galaxy collection install community.mysql

# Dependencies on control node
pip install psycopg2-binary  # PostgreSQL
pip install PyMySQL           # MySQL/MariaDB

---
- name: Install and configure PostgreSQL
  hosts: db_servers
  become: true

  vars:
    pg_version: 16
    pg_data_dir: "/var/lib/postgresql/{{ pg_version }}/main"
    pg_conf_dir: "/etc/postgresql/{{ pg_version }}/main"

  tasks:
    - name: Install PostgreSQL
      ansible.builtin.apt:
        name:
          - "postgresql-{{ pg_version }}"
          - "postgresql-client-{{ pg_version }}"
          - "postgresql-contrib-{{ pg_version }}"
          - python3-psycopg2
        state: present
        update_cache: true

    - name: Start PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: started
        enabled: true

    - name: Configure PostgreSQL
      ansible.builtin.lineinfile:
        path: "{{ pg_conf_dir }}/postgresql.conf"
        regexp: "^#?{{ item.key }} ="
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: listen_addresses, value: "'*'" }
        - { key: max_connections, value: "200" }
        - { key: shared_buffers, value: "'4GB'" }
        - { key: effective_cache_size, value: "'12GB'" }
        - { key: work_mem, value: "'64MB'" }
        - { key: maintenance_work_mem, value: "'1GB'" }
        - { key: wal_level, value: "'replica'" }
        - { key: max_wal_senders, value: "5" }
        - { key: max_replication_slots, value: "5" }
        - { key: log_min_duration_statement, value: "1000" }
        - { key: log_checkpoints, value: "on" }
        - { key: log_connections, value: "on" }
        - { key: log_disconnections, value: "on" }
      notify: restart postgresql

    - name: Configure pg_hba.conf
      community.postgresql.postgresql_pg_hba:
        dest: "{{ pg_conf_dir }}/pg_hba.conf"
        contype: host
        users: "{{ item.user }}"
        source: "{{ item.source }}"
        databases: "{{ item.db }}"
        method: scram-sha-256
      loop:
        - { user: app_user, source: "10.0.0.0/24", db: app_db }
        - { user: readonly, source: "10.0.0.0/24", db: app_db }
        - { user: replication, source: "10.0.1.0/24", db: replication }
      notify: reload postgresql

  handlers:
    - name: restart postgresql
      ansible.builtin.systemd:
        name: postgresql
        state: restarted

    - name: reload postgresql
      ansible.builtin.systemd:
        name: postgresql
        state: reloaded

    - name: Create application database
      community.postgresql.postgresql_db:
        name: app_db
        encoding: UTF-8
        lc_collate: en_US.UTF-8
        lc_ctype: en_US.UTF-8
        template: template0
        state: present
      become_user: postgres

    - name: Create application user
      community.postgresql.postgresql_user:
        name: app_user
        password: "{{ vault_pg_app_password }}"
        encrypted: true
        state: present
      become_user: postgres
      no_log: true

    - name: Grant privileges
      community.postgresql.postgresql_privs:
        database: app_db
        privs: ALL
        type: database
        role: app_user
        state: present
      become_user: postgres

    - name: Grant schema privileges
      community.postgresql.postgresql_privs:
        database: app_db
        privs: ALL
        type: schema
        objs: public
        role: app_user
        state: present
      become_user: postgres

    - name: Create read-only user
      community.postgresql.postgresql_user:
        name: readonly
        password: "{{ vault_pg_readonly_password }}"
        state: present
      become_user: postgres
      no_log: true

    - name: Grant read-only access
      community.postgresql.postgresql_privs:
        database: app_db
        privs: SELECT
        type: table
        objs: ALL_IN_SCHEMA
        schema: public
        role: readonly
        state: present
      become_user: postgres

    - name: Install extensions
      community.postgresql.postgresql_ext:
        name: "{{ item }}"
        db: app_db
        state: present
      loop:
        - pg_stat_statements
        - pgcrypto
        - uuid-ossp
        - pg_trgm
      become_user: postgres

---
- name: PostgreSQL backup
  hosts: db_servers
  become: true
  become_user: postgres

  vars:
    backup_dir: /backups/postgresql
    retention_days: 30

  tasks:
    - name: Create backup directory
      ansible.builtin.file:
        path: "{{ backup_dir }}"
        state: directory
        mode: '0700'
        owner: postgres

    - name: Full database dump
      community.postgresql.postgresql_db:
        name: app_db
        state: dump
        target: "{{ backup_dir }}/app_db_{{ ansible_date_time.date }}.sql.gz"

    - name: Clean old backups
      ansible.builtin.find:
        paths: "{{ backup_dir }}"
        patterns: "*.sql.gz"
        age: "{{ retention_days }}d"
      register: old_backups

    - name: Remove old backups
      ansible.builtin.file:
        path: "{{ item.path }}"
        state: absent
      loop: "{{ old_backups.files }}"

    - name: Deploy backup cron job
      ansible.builtin.cron:
        name: "PostgreSQL daily backup"
        minute: "0"
        hour: "2"
        job: "pg_dump -Fc app_db | gzip > {{ backup_dir }}/app_db_$(date +\\%Y-\\%m-\\%d).sql.gz"
        user: postgres

---
- name: Configure PostgreSQL primary
  hosts: pg_primary
  become: true
  tasks:
    - name: Create replication user
      community.postgresql.postgresql_user:
        name: replication
        password: "{{ vault_replication_password }}"
        role_attr_flags: REPLICATION,LOGIN
      become_user: postgres

    - name: Create replication slot
      community.postgresql.postgresql_slot:
        name: replica_slot_1
        slot_type: physical
        state: present
      become_user: postgres

---
- name: Configure PostgreSQL replica
  hosts: pg_replica
  become: true
  tasks:
    - name: Stop PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: stopped

    - name: Remove existing data
      ansible.builtin.file:
        path: "{{ pg_data_dir }}"
        state: absent

    - name: Base backup from primary
      ansible.builtin.command:
        cmd: >
          pg_basebackup
          -h {{ hostvars['pg_primary']['ansible_host'] }}
          -U replication
          -D {{ pg_data_dir }}
          -Fp -Xs -P -R
          --slot=replica_slot_1
      become_user: postgres
      environment:
        PGPASSWORD: "{{ vault_replication_password }}"

    - name: Start PostgreSQL replica
      ansible.builtin.systemd:
        name: postgresql
        state: started

---
- name: Install MySQL 8
  hosts: mysql_servers
  become: true

  tasks:
    - name: Install MySQL
      ansible.builtin.apt:
        name:
          - mysql-server-8.0
          - mysql-client-8.0
          - python3-pymysql
        state: present
        update_cache: true

    - name: Start MySQL
      ansible.builtin.systemd:
        name: mysql
        state: started
        enabled: true

    - name: Set root password
      community.mysql.mysql_user:
        name: root
        password: "{{ vault_mysql_root_password }}"
        host: localhost
        login_unix_socket: /var/run/mysqld/mysqld.sock
        state: present
      no_log: true

    - name: Create .my.cnf for root
      ansible.builtin.template:
        src: my.cnf.j2
        dest: /root/.my.cnf
        mode: '0600'

    - name: Create application database
      community.mysql.mysql_db:
        name: app_db
        encoding: utf8mb4
        collation: utf8mb4_unicode_ci
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock

    - name: Create application user
      community.mysql.mysql_user:
        name: app_user
        password: "{{ vault_mysql_app_password }}"
        host: "10.0.0.%"
        priv: "app_db.*:SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true

    - name: Create read-only user
      community.mysql.mysql_user:
        name: readonly
        password: "{{ vault_mysql_readonly_password }}"
        host: "10.0.0.%"
        priv: "app_db.*:SELECT"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true

    - name: Create backup user
      community.mysql.mysql_user:
        name: backup
        password: "{{ vault_mysql_backup_password }}"
        host: localhost
        priv: "*.*:SELECT,SHOW VIEW,TRIGGER,LOCK TABLES,PROCESS,RELOAD"
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
      no_log: true

    - name: Deploy MySQL configuration
      ansible.builtin.template:
        src: mysqld.cnf.j2
        dest: /etc/mysql/mysql.conf.d/mysqld.cnf
        mode: '0644'
      notify: restart mysql

    # templates/mysqld.cnf.j2
    # [mysqld]
    # bind-address = 0.0.0.0
    # max_connections = 200
    # innodb_buffer_pool_size = 4G
    # innodb_log_file_size = 1G
    # innodb_flush_log_at_trx_commit = 1
    # slow_query_log = 1
    # slow_query_log_file = /var/log/mysql/slow.log
    # long_query_time = 2
    # log_error = /var/log/mysql/error.log
    # server-id = {{ mysql_server_id }}
    # log_bin = /var/log/mysql/mysql-bin
    # binlog_format = ROW
    # expire_logs_days = 7

  handlers:
    - name: restart mysql
      ansible.builtin.systemd:
        name: mysql
        state: restarted

    - name: Run schema migration
      community.mysql.mysql_query:
        login_db: app_db
        query:
          - CREATE TABLE IF NOT EXISTS users (
              id BIGINT AUTO_INCREMENT PRIMARY KEY,
              email VARCHAR(255) NOT NULL UNIQUE,
              name VARCHAR(255),
              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB;
          - CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
        login_unix_socket: /var/run/mysqld/mysqld.sock

    - name: Import SQL file
      community.mysql.mysql_db:
        name: app_db
        state: import
        target: /tmp/schema.sql
        login_unix_socket: /var/run/mysqld/mysqld.sock

    - name: Backup MySQL database
      community.mysql.mysql_db:
        name: app_db
        state: dump
        target: "/backups/mysql/app_db_{{ ansible_date_time.date }}.sql.gz"
        login_unix_socket: /var/run/mysqld/mysqld.sock

    - name: Deploy backup script
      ansible.builtin.copy:
        content: |
          #!/bin/bash
          BACKUP_DIR=/backups/mysql
          DATE=$(date +%Y-%m-%d)
          mysqldump --single-transaction --routines --triggers app_db | gzip > ${BACKUP_DIR}/app_db_${DATE}.sql.gz
          find ${BACKUP_DIR} -name "*.sql.gz" -mtime +30 -delete
        dest: /usr/local/bin/mysql-backup.sh
        mode: '0755'

    - name: Schedule backup
      ansible.builtin.cron:
        name: "MySQL daily backup"
        minute: "0"
        hour: "3"
        job: /usr/local/bin/mysql-backup.sh

---
- name: Harden database security
  hosts: db_servers
  become: true
  tasks:
    # PostgreSQL
    - name: Remove default postgres trust auth
      community.postgresql.postgresql_pg_hba:
        dest: /etc/postgresql/16/main/pg_hba.conf
        contype: local
        users: all
        databases: all
        method: scram-sha-256
      when: "'postgresql' in group_names"

    # MySQL — remove anonymous users and test database
    - name: Remove anonymous MySQL users
      community.mysql.mysql_user:
        name: ""
        host_all: true
        state: absent
        login_unix_socket: /var/run/mysqld/mysqld.sock
      when: "'mysql' in group_names"

    - name: Remove test database
      community.mysql.mysql_db:
        name: test
        state: absent
        login_unix_socket: /var/run/mysqld/mysqld.sock
      when: "'mysql' in group_names"

    - name: Disallow root remote login
      community.mysql.mysql_user:
        name: root
        host: "%"
        state: absent
        login_unix_socket: /var/run/mysqld/mysqld.sock
      when: "'mysql' in group_names"