Introduction
Ansible manages MySQL/MariaDB through the community.mysql collection and PostgreSQL through community.postgresql. Both collections provide modules for database creation, user management, privileges, configuration, backups, and replication. This guide covers the most common database automation tasks for both engines.
Prerequisites
# Install collections
ansible-galaxy collection install community.mysql
ansible-galaxy collection install community.postgresql
# Python dependencies on target hosts
# MySQL: pip install PyMySQL
# PostgreSQL: pip install psycopg2-binary
MySQL / MariaDB
Install MySQL
---
- name: Install and configure MySQL
hosts: dbservers
become: true
vars:
mysql_root_password: "{{ vault_mysql_root_password }}"
tasks:
- name: Install MySQL server
ansible.builtin.apt:
name:
- mysql-server
- python3-pymysql
state: present
- name: Start MySQL
ansible.builtin.service:
name: mysql
state: started
enabled: true
- name: Set root password
community.mysql.mysql_user:
name: root
password: "{{ mysql_root_password }}"
login_unix_socket: /var/run/mysqld/mysqld.sock
host_all: true
state: present
no_log: true
Create Databases
- name: Create application database
community.mysql.mysql_db:
name: myapp
encoding: utf8mb4
collation: utf8mb4_unicode_ci
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Create multiple databases
community.mysql.mysql_db:
name: "{{ item }}"
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
loop:
- myapp_production
- myapp_staging
- myapp_test
Manage Users and Privileges
- name: Create application user
community.mysql.mysql_user:
name: myapp
password: "{{ vault_mysql_app_password }}"
host: "10.0.%"
priv:
'myapp_production.*': 'SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,INDEX'
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
no_log: true
- name: Create read-only user
community.mysql.mysql_user:
name: readonly
password: "{{ vault_mysql_readonly_password }}"
host: "%"
priv:
'myapp_production.*': 'SELECT'
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
no_log: true
- name: Create admin user with full privileges
community.mysql.mysql_user:
name: dba
password: "{{ vault_mysql_dba_password }}"
host: "10.0.1.%"
priv:
'*.*': 'ALL,GRANT'
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
no_log: true
MySQL Configuration
- name: Configure MySQL
community.mysql.mysql_variables:
variable: "{{ item.name }}"
value: "{{ item.value }}"
login_user: root
login_password: "{{ mysql_root_password }}"
loop:
- { name: max_connections, value: "500" }
- { name: innodb_buffer_pool_size, value: "2147483648" }
- { name: slow_query_log, value: "ON" }
- { name: long_query_time, value: "2" }
Import SQL File
- name: Import schema
community.mysql.mysql_db:
name: myapp_production
state: import
target: /opt/sql/schema.sql
login_user: root
login_password: "{{ mysql_root_password }}"
Backup MySQL
- name: Backup database
community.mysql.mysql_db:
name: myapp_production
state: dump
target: "/backups/myapp-{{ ansible_date_time.date }}.sql.gz"
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Backup all databases
community.mysql.mysql_db:
name: all
state: dump
target: "/backups/all-databases-{{ ansible_date_time.date }}.sql.gz"
login_user: root
login_password: "{{ mysql_root_password }}"
MySQL Replication
- name: Configure replication source
community.mysql.mysql_replication:
mode: changeprimary
primary_host: db01.example.com
primary_user: replicator
primary_password: "{{ vault_replication_password }}"
primary_log_file: mysql-bin.000001
primary_log_pos: 154
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Start replication
community.mysql.mysql_replication:
mode: startreplica
login_user: root
login_password: "{{ mysql_root_password }}"
PostgreSQL
Install PostgreSQL
---
- name: Install and configure PostgreSQL
hosts: dbservers
become: true
tasks:
- name: Install PostgreSQL
ansible.builtin.apt:
name:
- postgresql
- postgresql-contrib
- python3-psycopg2
state: present
- name: Start PostgreSQL
ansible.builtin.service:
name: postgresql
state: started
enabled: true
Create Databases
- name: Create application database
community.postgresql.postgresql_db:
name: myapp
encoding: UTF-8
lc_collate: en_US.UTF-8
lc_ctype: en_US.UTF-8
template: template0
state: present
become: true
become_user: postgres
- name: Create database with owner
community.postgresql.postgresql_db:
name: myapp_production
owner: myapp
state: present
become: true
become_user: postgres
Manage Users (Roles)
- name: Create application user
community.postgresql.postgresql_user:
name: myapp
password: "{{ vault_pg_app_password }}"
role_attr_flags: NOSUPERUSER,NOCREATEDB
state: present
become: true
become_user: postgres
no_log: true
- name: Create read-only user
community.postgresql.postgresql_user:
name: readonly
password: "{{ vault_pg_readonly_password }}"
role_attr_flags: NOSUPERUSER,NOCREATEDB,NOCREATEROLE
state: present
become: true
become_user: postgres
no_log: true
Manage Privileges
- name: Grant privileges to app user
community.postgresql.postgresql_privs:
database: myapp_production
role: myapp
type: schema
objs: public
privs: ALL
state: present
become: true
become_user: postgres
- name: Grant table-level privileges
community.postgresql.postgresql_privs:
database: myapp_production
role: myapp
type: table
objs: ALL_IN_SCHEMA
schema: public
privs: SELECT,INSERT,UPDATE,DELETE
state: present
become: true
become_user: postgres
- name: Grant read-only on all tables
community.postgresql.postgresql_privs:
database: myapp_production
role: readonly
type: table
objs: ALL_IN_SCHEMA
schema: public
privs: SELECT
state: present
become: true
become_user: postgres
- name: Set default privileges for new tables
community.postgresql.postgresql_privs:
database: myapp_production
role: readonly
type: default_privs
objs: TABLES
schema: public
privs: SELECT
target_roles: myapp
state: present
become: true
become_user: postgres
PostgreSQL Extensions
- name: Install extensions
community.postgresql.postgresql_ext:
name: "{{ item }}"
db: myapp_production
state: present
become: true
become_user: postgres
loop:
- pg_stat_statements
- uuid-ossp
- hstore
- pgcrypto
PostgreSQL Configuration
- name: Set PostgreSQL parameters
community.postgresql.postgresql_set:
name: "{{ item.name }}"
value: "{{ item.value }}"
become: true
become_user: postgres
loop:
- { name: shared_buffers, value: "2GB" }
- { name: effective_cache_size, value: "6GB" }
- { name: max_connections, value: "200" }
- { name: work_mem, value: "64MB" }
- { name: log_min_duration_statement, value: "1000" }
notify: restart postgresql
handlers:
- name: restart postgresql
ansible.builtin.service:
name: postgresql
state: restarted
Configure pg_hba.conf
- name: Allow application connections
community.postgresql.postgresql_pg_hba:
dest: /etc/postgresql/16/main/pg_hba.conf
contype: host
databases: myapp_production
users: myapp
source: 10.0.1.0/24
method: scram-sha-256
state: present
become: true
notify: reload postgresql
handlers:
- name: reload postgresql
ansible.builtin.service:
name: postgresql
state: reloaded
Backup PostgreSQL
- name: Backup database with pg_dump
community.postgresql.postgresql_db:
name: myapp_production
state: dump
target: "/backups/myapp-{{ ansible_date_time.date }}.sql.gz"
become: true
become_user: postgres
- name: Full cluster backup with pg_basebackup
ansible.builtin.command: >
pg_basebackup -D /backups/base-{{ ansible_date_time.date }}
-Ft -z -P -X stream
become: true
become_user: postgres
Run SQL Queries
- name: Run SQL query
community.postgresql.postgresql_query:
db: myapp_production
query: |
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
become: true
become_user: postgres
- name: Run parameterized query
community.postgresql.postgresql_query:
db: myapp_production
query: "INSERT INTO settings (key, value) VALUES (%s, %s) ON CONFLICT (key) DO UPDATE SET value = %s"
positional_args:
- app_version
- "2.0.0"
- "2.0.0"
become: true
become_user: postgres
Troubleshooting
MySQL: "Access denied"
# Use unix socket for local root access
- community.mysql.mysql_db:
name: test
state: present
login_unix_socket: /var/run/mysqld/mysqld.sock
PostgreSQL: "Peer authentication failed"
# Use become_user: postgres for local access
- community.postgresql.postgresql_db:
name: test
become: true
become_user: postgres
Related Articles
Conclusion
The community.mysql and community.postgresql collections provide complete database lifecycle management — create databases, manage users and privileges, configure settings, run queries, set up replication, and automate backups. Use no_log: true on all password tasks, become_user: postgres for PostgreSQL local access, and login_unix_socket for MySQL root operations. Both collections follow the same declarative pattern: define the desired state, and Ansible makes it so.