Database Management
What is Database Management with Ansible? Ansible provides comprehensive modules for managing MySQL, PostgreSQL, MongoDB, and other databases. Automate database installation, configuration, user management, backups, replication, and migrations with idempotent, repeatable playbooks.
Understanding Database Automation
Why Automate Databases?
Database automation provides significant benefits:
- Consistency: Identical configurations across environments
- Version Control: Track database schema and config changes
- Disaster Recovery: Automated backup and restore procedures
- Scalability: Quickly provision new database servers
- Compliance: Enforce security and access policies
Common Use Cases
- Install and configure database servers
- Manage database users and permissions
- Create and delete databases
- Run schema migrations
- Configure replication and clustering
- Automate backups and restores
MySQL / MariaDB Management
Install MySQL Collection
ansible-galaxy collection install community.mysql
Install and Configure MySQL
---
- name: Setup MySQL server
hosts: db_servers
become: true
tasks:
- name: Install MySQL server
ansible.builtin.yum:
name:
- mysql-server
- python3-PyMySQL
state: present
when: ansible_os_family == "RedHat"
- name: Install MySQL (Debian)
ansible.builtin.apt:
name:
- mysql-server
- python3-pymysql
state: present
when: ansible_os_family == "Debian"
- name: Start MySQL service
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
state: present
- name: Remove anonymous users
community.mysql.mysql_user:
name: ''
host_all: true
state: absent
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Remove test database
community.mysql.mysql_db:
name: test
state: absent
login_user: root
login_password: "{{ mysql_root_password }}"
Create Databases and Users
---
- name: Manage MySQL databases and users
hosts: db_servers
tasks:
- name: Create application database
community.mysql.mysql_db:
name: "{{ db_name }}"
encoding: utf8mb4
collation: utf8mb4_unicode_ci
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Create application user
community.mysql.mysql_user:
name: "{{ app_db_user }}"
password: "{{ app_db_password }}"
priv: "{{ db_name }}.*:ALL"
host: "%"
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Create read-only user
community.mysql.mysql_user:
name: readonly_user
password: "{{ readonly_password }}"
priv: "{{ db_name }}.*:SELECT"
host: "10.0.0.%"
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Grant specific privileges
community.mysql.mysql_user:
name: limited_user
password: "{{ limited_password }}"
priv:
"mydb.*": "SELECT,INSERT,UPDATE"
"testdb.*": "ALL"
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
MySQL Backup and Restore
---
- name: MySQL backup operations
hosts: db_servers
tasks:
- name: Dump database to file
community.mysql.mysql_db:
name: "{{ db_name }}"
state: dump
target: "/backup/{{ db_name }}_{{ ansible_date_time.date }}.sql"
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Dump all databases
community.mysql.mysql_db:
name: all
state: dump
target: /backup/all_databases.sql
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Restore database from file
community.mysql.mysql_db:
name: "{{ db_name }}"
state: import
target: /backup/database_backup.sql
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Copy backup to remote storage
ansible.builtin.copy:
src: "/backup/{{ db_name }}_{{ ansible_date_time.date }}.sql"
dest: /mnt/backup_share/
remote_src: yes
MySQL Replication Setup
---
- name: Configure MySQL primary server
hosts: mysql_primary
tasks:
- name: Configure replication settings
community.mysql.mysql_variables:
variable: "{{ item.name }}"
value: "{{ item.value }}"
login_user: root
login_password: "{{ mysql_root_password }}"
loop:
- { name: 'server_id', value: '1' }
- { name: 'log_bin', value: '/var/log/mysql/mysql-bin.log' }
- { name: 'binlog_do_db', value: "{{ db_name }}" }
- name: Create replication user
community.mysql.mysql_user:
name: repl_user
password: "{{ replication_password }}"
priv: "*.*:REPLICATION SLAVE"
host: "%"
state: present
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Get primary status
community.mysql.mysql_replication:
mode: getprimary
login_user: root
login_password: "{{ mysql_root_password }}"
register: primary_status
- name: Configure MySQL replica server
hosts: mysql_replicas
tasks:
- name: Set server ID
community.mysql.mysql_variables:
variable: server_id
value: "{{ server_id }}"
login_user: root
login_password: "{{ mysql_root_password }}"
- name: Configure replication
community.mysql.mysql_replication:
mode: changeprimary
primary_host: "{{ hostvars[groups['mysql_primary'][0]]['ansible_host'] }}"
primary_user: repl_user
primary_password: "{{ replication_password }}"
primary_log_file: "{{ hostvars[groups['mysql_primary'][0]]['primary_status']['File'] }}"
primary_log_pos: "{{ hostvars[groups['mysql_primary'][0]]['primary_status']['Position'] }}"
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 Management
Install PostgreSQL Collection
ansible-galaxy collection install community.postgresql
Install and Configure PostgreSQL
---
- name: Setup PostgreSQL server
hosts: db_servers
become: true
tasks:
- name: Install PostgreSQL
ansible.builtin.yum:
name:
- postgresql-server
- postgresql-contrib
- python3-psycopg2
state: present
when: ansible_os_family == "RedHat"
- name: Initialize database
ansible.builtin.command: postgresql-setup initdb
args:
creates: /var/lib/pgsql/data/PG_VERSION
- name: Start PostgreSQL service
ansible.builtin.service:
name: postgresql
state: started
enabled: true
- name: Configure pg_hba.conf for remote access
ansible.builtin.lineinfile:
path: /var/lib/pgsql/data/pg_hba.conf
line: "host all all 10.0.0.0/8 md5"
insertafter: EOF
notify: restart postgresql
- name: Configure postgresql.conf
ansible.builtin.lineinfile:
path: /var/lib/pgsql/data/postgresql.conf
regexp: "^#?listen_addresses"
line: "listen_addresses = '*'"
notify: restart postgresql
handlers:
- name: restart postgresql
ansible.builtin.service:
name: postgresql
state: restarted
Create PostgreSQL Databases and Users
---
- name: Manage PostgreSQL databases and users
hosts: db_servers
become: true
become_user: postgres
tasks:
- name: Create database
community.postgresql.postgresql_db:
name: "{{ db_name }}"
encoding: UTF8
lc_collate: en_US.UTF-8
lc_ctype: en_US.UTF-8
template: template0
state: present
- name: Create user
community.postgresql.postgresql_user:
name: "{{ app_db_user }}"
password: "{{ app_db_password }}"
state: present
- name: Grant database privileges
community.postgresql.postgresql_privs:
database: "{{ db_name }}"
role: "{{ app_db_user }}"
privs: ALL
type: database
state: present
- name: Grant table privileges
community.postgresql.postgresql_privs:
database: "{{ db_name }}"
role: "{{ app_db_user }}"
objs: ALL_IN_SCHEMA
privs: SELECT,INSERT,UPDATE,DELETE
type: table
state: present
- name: Create read-only role
community.postgresql.postgresql_user:
name: readonly_user
password: "{{ readonly_password }}"
state: present
- name: Grant SELECT on all tables
community.postgresql.postgresql_privs:
database: "{{ db_name }}"
role: readonly_user
objs: ALL_IN_SCHEMA
privs: SELECT
type: table
state: present
PostgreSQL Backup and Restore
---
- name: PostgreSQL backup operations
hosts: db_servers
become: true
become_user: postgres
tasks:
- name: Dump database
community.postgresql.postgresql_db:
name: "{{ db_name }}"
state: dump
target: "/backup/{{ db_name }}_{{ ansible_date_time.date }}.sql"
- name: Dump database in custom format
ansible.builtin.command: >
pg_dump -Fc {{ db_name }} -f /backup/{{ db_name }}.dump
- name: Dump all databases
ansible.builtin.command: >
pg_dumpall -f /backup/all_databases.sql
- name: Restore database
community.postgresql.postgresql_db:
name: "{{ db_name }}"
state: restore
target: /backup/database_backup.sql
MongoDB Management
Install MongoDB Collection
ansible-galaxy collection install community.mongodb
Install and Configure MongoDB
---
- name: Setup MongoDB server
hosts: db_servers
become: true
tasks:
- name: Add MongoDB repository
ansible.builtin.yum_repository:
name: mongodb-org
description: MongoDB Repository
baseurl: https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/6.0/x86_64/
gpgcheck: true
enabled: true
gpgkey: https://www.mongodb.org/static/pgp/server-6.0.asc
- name: Install MongoDB
ansible.builtin.yum:
name:
- mongodb-org
- python3-pymongo
state: present
- name: Start MongoDB service
ansible.builtin.service:
name: mongod
state: started
enabled: true
- name: Configure MongoDB for remote access
ansible.builtin.lineinfile:
path: /etc/mongod.conf
regexp: "^ bindIp:"
line: " bindIp: 0.0.0.0"
notify: restart mongod
handlers:
- name: restart mongod
ansible.builtin.service:
name: mongod
state: restarted
Create MongoDB Users
---
- name: Manage MongoDB users
hosts: db_servers
tasks:
- name: Create admin user
community.mongodb.mongodb_user:
database: admin
name: admin
password: "{{ mongodb_admin_password }}"
roles:
- db: admin
role: userAdminAnyDatabase
- db: admin
role: readWriteAnyDatabase
state: present
- name: Create application database user
community.mongodb.mongodb_user:
database: "{{ db_name }}"
name: "{{ app_db_user }}"
password: "{{ app_db_password }}"
roles:
- db: "{{ db_name }}"
role: readWrite
state: present
login_user: admin
login_password: "{{ mongodb_admin_password }}"
- name: Create read-only user
community.mongodb.mongodb_user:
database: "{{ db_name }}"
name: readonly_user
password: "{{ readonly_password }}"
roles:
- db: "{{ db_name }}"
role: read
state: present
login_user: admin
login_password: "{{ mongodb_admin_password }}"
MongoDB Backup
---
- name: Backup MongoDB
hosts: db_servers
tasks:
- name: Create backup directory
ansible.builtin.file:
path: /backup/mongodb
state: directory
- name: Dump MongoDB database
ansible.builtin.command: >
mongodump
--db {{ db_name }}
--out /backup/mongodb/{{ ansible_date_time.date }}
--username admin
--password {{ mongodb_admin_password }}
--authenticationDatabase admin
- name: Restore MongoDB database
ansible.builtin.command: >
mongorestore
--db {{ db_name }}
/backup/mongodb/{{ backup_date }}/{{ db_name }}
--username admin
--password {{ mongodb_admin_password }}
--authenticationDatabase admin
Database Migrations
Run SQL Migrations
---
- name: Run database migrations
hosts: db_servers
tasks:
- name: Copy migration files
ansible.builtin.copy:
src: "{{ item }}"
dest: /tmp/migrations/
with_fileglob:
- migrations/*.sql
- name: Run MySQL migrations
community.mysql.mysql_db:
name: "{{ db_name }}"
state: import
target: "{{ item }}"
login_user: root
login_password: "{{ mysql_root_password }}"
with_fileglob:
- /tmp/migrations/*.sql
- name: Run PostgreSQL migrations
community.postgresql.postgresql_query:
db: "{{ db_name }}"
path_to_script: "{{ item }}"
become: true
become_user: postgres
with_fileglob:
- /tmp/migrations/*.sql
Using Flyway for Migrations
---
- name: Run Flyway migrations
hosts: db_servers
tasks:
- name: Install Flyway
ansible.builtin.unarchive:
src: https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/9.16.0/flyway-commandline-9.16.0-linux-x64.tar.gz
dest: /opt/
remote_src: yes
- name: Copy migration files
ansible.builtin.copy:
src: migrations/
dest: /opt/flyway/sql/
- name: Run Flyway migrate
ansible.builtin.command: >
/opt/flyway/flyway
-url=jdbc:postgresql://localhost:5432/{{ db_name }}
-user={{ db_user }}
-password={{ db_password }}
migrate
Best Practices
Database Management Best Practices:
- Use Vault: Encrypt database passwords with Ansible Vault
- Backup Regularly: Automate backups before changes
- Test Restores: Regularly verify backup integrity
- Least Privilege: Grant minimum required permissions
- Monitor Performance: Set up database monitoring
- Version Migrations: Track schema changes in version control
- Use Transactions: Wrap migrations in transactions when possible
- Document Changes: Maintain migration documentation
Common Issues
Authentication Failures
- Verify user credentials are correct
- Check pg_hba.conf or MySQL user host permissions
- Ensure Python database libraries are installed
- Use become_user for PostgreSQL operations
Connection Refused
- Verify database service is running
- Check firewall rules allow connections
- Ensure database is configured to listen on correct interface
- Test connectivity with database client tools
Module Not Found
- Install required collections
- Install Python database libraries (psycopg2, PyMySQL, pymongo)
- Verify Ansible version compatibility
Next Steps
- Ansible Vault - Secure database credentials
- Docker - Containerized databases
- Cloud Databases - RDS, Azure Database, Cloud SQL
- Advanced Topics - Complex database setups
- Try the Playground - Practice Ansible concepts