Back up MySQL databases according to a schedule, and prune the backups according to a retention policy
Root should be able to run mysql without being prompted for a password. Modern mariadb does this by default. The traditional method if you don't have that option is to place credentials at /root/.my.cnf with 0600 mode.
# requirements.yml
---
- name: acromedia.simple-mysql-backups
src: https://github.com/AcroMedia/ansible-role-simple-mysql-backups
version: origin/master
# group_vars/all.yml
---
simple_mysql_backups_keep_last_hourly: 1
simple_mysql_backups_keep_last_daily: 6
simple_mysql_backups_keep_last_weekly: 3
simple_mysql_backups_keep_last_monthly: 11
# playbook.yml
---
- hosts: mysql-servers
gather_facts: true
become: true
roles:
- name: Configure simple mysql backups
role: acromedia.simple-mysql-backups
-
- In percent. Default:
90
- To avoid filling up the volume, the script will stop and throw an error if this disk usage level is reached before or between backup operations.
- Adjust to what makes sense for your own environment
- In percent. Default:
Before a database is backed up, old backups are examined to see if they can be pruned (removed), according to these retention policy variables:
-
- Default: 7
- If there are only this many (or fewer) backups for a given database, pruning will not happen at all. This is a safety mechanism to keep backups from aging out of existence. The assumption is that a very old backup is more useful than none at all.
-
- Default: 1
- Designed for those that need to pull multiple backups throughout the day. If there are more than this many backups that exist within the last 24 hour period (of the script running), the surplus backups will be removed.
-
- Default: 6
- A backup will be kept for each of the past
simple_mysql_backups_keep_last_daily
days.
-
- Default: 3
- A backup that is older than
simple_mysql_backups_keep_last_daily
will be kept from each of the previoussimple_mysql_backups_keep_last_weekly
weeks.
-
- Default: 11
- A backup that is older than
simple_mysql_backups_keep_last_weekly
will be kept from each of the previoussimple_mysql_backups_keep_last_monthly
months.
-
You MUST set all the
...keep_last_xxx
variables to at least1
in order for a backup to age through that retention window.If you set any of the above variables to zero, you will be instructing the prune script not to keep any backups for that window.
If no backups exist in a window, then none will ever age beyond that window.
Example
If you only wanted to keep the last 5 days worth of backups, and delete everything else, you would set:
simple_mysql_backups_keep_last_hourly: 1
simple_mysql_backups_keep_last_daily: 5
simple_mysql_backups_keep_last_weekly: 0 # All backups older than 5 days will be deleted.
simple_mysql_backups_keep_last_monthly: 9999 # Has no effect, since weekly is 0.
However, if you mistakenly set "hourly" to zero, thinking you only care about daily, then you will find yourself with no backups:
simple_mysql_backups_keep_last_hourly: 0 # Oops! All backups will be pruned.
simple_mysql_backups_keep_last_daily: 6 # Has no effect, since hourly is 0, and there are no hourly backups to age into daily.
simple_mysql_backups_keep_last_weekly: 3 # Has no effect, since there are no daily backups to age into weekly.
simple_mysql_backups_keep_last_monthly: 11 # Has no effect, since there are no weekly backups to age into monthly.
-
- default:
45
- default:
-
- default:
4
- default:
-
- default:
'*'
- default:
-
- default:
root
- default:
-
- default:
present
- default:
-
- Default:
'^information_schema$|^performance_schema$|^sys$'
- Specify the list of databases you want to be excluded from being backed up
- This expression is fed directly to
grep -E
. Treat it accordingly.
- Default:
-
- If a database is empty, it technically counts as an error, since the script wasn't able to back up the DB.
- The default (
'.*'
) is to ignore warnings about all empty databases, since in most cases, these are not a concern. - The expression is fed to
grep -E
, so treat it accordingly - Examples:
- Ignore all empty databases:
'.*'
- Warn about any empty database:
''
- Ignore warnings about a specific database:
'^my_db_name$'
- Ignore warnings about two specific databases:
'^my_db_name$|^my_other_db$'
- Ignore all empty databases: