Ansible Role: automysqlbackup

This role installs the automysqlbackup tool on Debian or Ubuntu Linux servers.

Requirements

mail must be installed

You can install it using apt install mailutils, or create a fake mail command with touch /usr/local/bin/mail.

Role Variables

Here are the variables you can set, along with their default values (found in defaults/main.yml):

• Backup User and Password:

  • automysqlbackup_username: The username to access the database for backups.
  • automysqlbackup_password: The password for the database user.

• Database Host:

  • automysqlbackup_host: The hostname or IP address of the database server. Defaults to localhost.

• Databases to Exclude:

  • automysqlbackup_db_exclude: List of databases to ignore during backups (like information_schema, mysql, performance_schema, test).

• Backup Directory:

  • automysqlbackup_backup_dir: The location where backups will be stored (default: /var/backup/db).

• Multi-core Processing:

  • automysqlbackup_multicore: Determines if multi-core processing is enabled based on the number of processor cores.
  • automysqlbackup_multicore_threads: Sets the number of threads for processing backups, based on core count.

• Backup Schedule:

  • automysqlbackup_do_monthly: Month day for monthly backups (01 for the first day).
  • automysqlbackup_do_weekly: Day of the week for weekly backups (5 for Friday).
  • automysqlbackup_rotation_daily, weekly, monthly: Keep the last 6 daily, 35 weekly, and 150 monthly backups.

• MySQL Dump Options:

  • This includes settings for dump behavior, such as compression (gzip), logging, and notification preferences.

• Cron Job Settings:

  • automysqlbackup_cron_*: Define when the automatic backup runs (default is at 20:06 daily).

• Export Privileges:

  • automysqlbackup_export_priviledges: Set this to true if you want to export user privileges.

Dependencies

There are no external dependencies.

Example Playbook

Here's how to use the role in an Ansible playbook:

- hosts: server
  roles:
    - { role: tschifftner.automysqlbackup }

Exporting Privileges

If you want to export user credentials via cron, set automysqlbackup_export_priviledges to true. Ensure the root user does not need a login prompt in the command line.

Restoring Database

Using gzip

gzip -dc my_database.sql.gz | mysql -h localhost -u root -p my_database

Using bzip2

bunzip2 < my_database.sql.bz2 | mysql -h localhost -u root -p my_database

or

bzip2 -d my_database.sql.bz2 | mysql -h localhost -u root -p my_database

Supported Operating Systems

• Debian 9 (Stretch)
• Debian 8 (Jessie)
• Ubuntu 18.04 (Bionic Beaver)
• Ubuntu 16.04 (Xenial Xerus)

Required Ansible Version

This role requires Ansible version 2.5 or higher.

License

This project is licensed under the MIT License.

Author Information

• Tobias Schifftner, ambimax® GmbH