Backup MySQL databases and sync to S3

backupsql.rb

#!/usr/bin/env ruby

# Backup specified databases - place in a cron job
# Previously we used sftp to save files to a remote server.
# Now sync directly with a bucket on AWS S3
#
# Use a MySQL configuration file (.cnf) to store the password, so that it doesn't
# appear in the process list
#
# example.cnf
# ------
# [client]
# password=some_password
#------------------------------------------------------------------------------
require 'fileutils'
require 'date'

#------------------------------------------------------------------------------
def backup_sql_with_cnf(user, cnf, database, domain = user)
  date_prefix   =  Time.now.strftime("%Y.%m.%d")
  cnf_path      = "#{ENV['HOME']}/backups/#{cnf}"
  sql_file      = "#{ENV['HOME']}/backups/sql/#{date_prefix}_#{database}.sql"

  `mysqldump --defaults-file="#{cnf_path}" --skip-extended-insert --max_allowed_packet=128M --add-drop-table -u #{user} #{database} > #{sql_file}`
  `gzip #{sql_file}`

  # one option is to sftp each file to a server
  # `scp -2 #{sql_file}.gz #{@scp_path}/#{domain}/`
end

# age based on the date the file was created
#------------------------------------------------------------------------------
def file_age(name)
  (Time.now - File.ctime(name))/(24*3600)
end

# parses out a valid date from the filename. so 2016.08.20_db.sql.gz
# will return Date.new(2016, 08, 20), or today's date if date is invalid
#------------------------------------------------------------------------------
def date_from_name(name)
  Date.parse(name) rescue Time.now.to_date
end

#------------------------------------------------------------------------------
def days_since_today(date)
  (Time.now.to_date - date).to_i
end

# remove any files older than `days` or unless it matches a specific day of the month
#------------------------------------------------------------------------------
def remove_older_than(days, keep_day = 1)
  Dir.glob("#{ENV['HOME']}/backups/sql/*.sql.gz").each do |filename|
    filedate = date_from_name(filename)
    File.delete(filename) if (days_since_today(filedate) > days) && filedate.mday != keep_day
  end
end

#------------------------------------------------------------------------------

backup_sql_with_cnf('dbuser', 'mysql_dbuser.cnf', 'mydb_production')

remove_older_than(30)

s3_path = 's3://mybucket/backups/sql'
`aws s3 sync #{ENV['HOME']}/backups/sql/ #{s3_path} --delete`