Skip to content

Instantly share code, notes, and snippets.

@mattantonelli

mattantonelli/copy_backups.sh

Last active November 30, 2021 13:58
Show Gist options
  • Save mattantonelli/35056b42758fe0627a72 to your computer and use it in GitHub Desktop.
Save mattantonelli/35056b42758fe0627a72 to your computer and use it in GitHub Desktop.
Download ZIP
Copies backups from automysqlbackup to a remote host. Retains 1 weekly and 7 daily backups per database.
Raw
copy_backups.sh
#!/usr/bin/env bash
#
# Purpose:
# Maintains 7 daily and 1 weekly database backups on a remote server.
#
# Configuration:
# Subdirectories must be created on the remote server for each database.
# (This includes status & fullschema directories if applicable.)
#
# Examples:
#
# Daily
# in: /backups/path/daily/mydb/daily_mydb_2016-02-29_03h25m_Monday.sql.gz
# out: daily_mydb_Monday.sql.gz
#
# Weekly
# in: /backups/path/weekly/mydb/weekly_mydb_2016-02-28_03h09m_8.sql.gz
# out: weekly_mydb.sql.gz
scpPath='user@host:path'
find '/backups/path' -type f -mmin -720 | while read file; do
dir=$(basename $(dirname $file))
copyFile=$(basename $file | sed 's/_[0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\}_[0-9]\{2\}h[0-9]\{2\}m\(_[0-9]\+\)\?//')
$(scp $file "$scpPath/$dir/$copyFile")
done
@mattantonelli
Copy link
Author

mattantonelli commented Jan 26, 2017 •
edited
Loading

Setting up automysqlbackup backup redundancy

  1. Move the automysqlbackup script from /etc/cron.daily into the automysqlbackup directory and rename it to backup.sh
  2. Update the line in backup.sh modifying the file permissions to use 600 instead of 400 so that old backups on the remote server can be overridden: find /backups/path -type f -exec chmod 600 {} \;
  3. Copy the script from the gist above into the same directory and name it copy_backups.sh
  4. Create a new script in cron.daily with the following code:
#!/usr/bin/env bash

/automysqlbackup/directory/backup.sh
/automysqlbackup/directory/copy_backups.sh
  1. Ensure that each of these scripts are executable
  2. Create the backup directory on the remote host, including the database_name(s), status, and fullschema subdirectories
  3. Ensure the directories belong to the remote user and are configured with permissions 700
  4. Generate ssh keys for the remote host (see here for a step-by-step)
  5. Manually execute the daily script to test
    • If the backups you wish to test were created more than 12 hours ago, you can temporarily change the find command to:
      find '/backups/path' -type f -mtime -1

@kenguest
Copy link

From what I can see, you now just need to specify a script in the POSTBACKUP line in /etc/default/automysqlbackup for copying/transferring the generated backups off-server.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment