3 files changed, 107 insertions, 0 deletions
diff --git a/roles/mysql_backup/README.md b/roles/mysql_backup/README.md
new file mode 100644
index 0000000..9ee05cf
--- /dev/null
+++ b/roles/mysql_backup/README.md
@@ -0,0 +1,18 @@
+# mysql_backup
+
+Archives SQL Database dumps in an encrypted BORG repo.
+
+compatible with kompetenzbolzen.stuff.mariadb configuration for databases
+
+Example config
+
+```
+---
+sql_backup:
+  borg_repo_dir: /var/backup/borg_mysql/
+  borg_repo_key: 'changeme'
+  keep_last: 3
+  keep_daily: 7
+  keep_weekly: 4
+  keep_monthly: 6
+```
diff --git a/roles/mysql_backup/defaults/main.yml b/roles/mysql_backup/defaults/main.yml
new file mode 100644
index 0000000..ade30ea
--- /dev/null
+++ b/roles/mysql_backup/defaults/main.yml
@@ -0,0 +1,8 @@
+---
+sql_backup:
+  borg_repo_dir: /var/backup/borg_mysql/
+  borg_repo_key: 'changeme'
+  keep_last: 3
+  keep_daily: 7
+  keep_weekly: 4
+  keep_monthly: 6
diff --git a/roles/mysql_backup/tasks/main.yml b/roles/mysql_backup/tasks/main.yml
new file mode 100644
index 0000000..7ce7748
--- /dev/null
+++ b/roles/mysql_backup/tasks/main.yml
@@ -0,0 +1,81 @@
+---
+- name: Check for BORG
+  command: which borg
+  register: borg_check
+  ignore_errors: yes
+
+# Kinda hacky but saves time
+- name: Install BORG
+  apt:
+    name:
+      - borgbackup
+  become: yes
+  when: not borg_check.rc == 0
+
+- name: Create BORG repo
+  file:
+    path: '{{ sql_backup.borg_repo_dir }}'
+    state: directory
+    mode: 'u=rwx,g=,o='
+  become: yes
+
+- name: Initialize BORG repo
+  command:
+    cmd: borg init --encryption=repokey
+    creates: '{{ sql_backup.borg_repo_dir }}/config'
+  environment:
+    BORG_REPO: '{{ sql_backup.borg_repo_dir }}'
+    BORG_PASSPHRASE: '{{ sql_backup.borg_repo_key }}'
+  become: yes
+
+- name: Create tempdir
+  file:
+    path: /tmp/sql
+    state: directory
+    owner: server
+    group: server
+    mode: 'u=rwx,g=,o='
+  become: yes
+
+- name: Dump databases
+  community.mysql.mysql_db:
+    state: dump
+    name: '{{ item }}'
+    target: '/tmp/sql/{{ item }}.sql'
+    login_unix_socket: /var/run/mysqld/mysqld.sock
+  loop: '{{ dbs }}'
+  become: yes
+
+- name: Create BORG backup
+  command: 'borg create --compression lz4 --verbose ::{hostname}-{now} /tmp/sql'
+  environment:
+    BORG_REPO: '{{ sql_backup.borg_repo_dir }}'
+    BORG_PASSPHRASE: '{{ sql_backup.borg_repo_key }}'
+  register: borg_output
+  become: yes
+
+- name: Borg Output
+  debug:
+    var: borg_output.stderr
+
+- name: Delete TEMP files
+  file:
+    path: /tmp/sql
+    state: absent
+  become: yes
+
+- name: Prune BORG backup
+  command: 'borg prune --list
+    --keep-last {{ sql_backup.keep_last }}
+    --keep-daily {{ sql_backup.keep_daily }}
+    --keep-weekly {{ sql_backup.keep_weekly }}
+    --keep-monthly {{ sql_backup.keep_monthly }}'
+  environment:
+    BORG_REPO: '{{ sql_backup.borg_repo_dir }}'
+    BORG_PASSPHRASE: '{{ sql_backup.borg_repo_key }}'
+  register: borg_prune
+  become: yes
+
+- name: Prune Output
+  debug:
+    var: borg_prune.stderr