MySQL backup using Ruby script

README.md

MySQL backup using Ruby script

This is a simple script that I wrote to backup my local databases. It uses terminal-notifier to create OS notifications and time_difference to report how much time it takes to execute a backup.

Installation

• Install the required gems: gem install time_difference terminal-notifier
• Adjust the relevant variables: databases, path
• The connection variables are set in the .my.cnf file. I don't want your MySQL password in the script.
  • Create the following .my.cnf file in your home directory. The correct permission for this file is 600:

[client]
host=127.0.0.1
user=yourmysqluser
password=yourpassword

• Run the script: ruby backup.rb

Extra tip: you should create an user with only select privilegies to execute this backup script

backup.rb

require 'terminal-notifier'
require 'time_difference'

started_at = Time.now
databases = %w(database1 database2 database3)
path = '/Users/youruser/backups'

start_message = 'Backup started'

puts start_message
TerminalNotifier.notify(start_message, title: 'MySQL Backup')

databases.each do |database|
	filename = "#{path}/#{database}_#{Time.now.strftime("%Y%m%d%H%M")}.sql"
	cmd = "mysqldump --defaults-extra-file=.my.cnf #{database} > #{filename}"
	system(cmd)
	puts "Database: #{database}"
end

finished_at = Time.now

puts "Deleting old backups..."
system("find #{path} -type f -mtime +3 -delete")

end_message = "Backup ended in #{TimeDifference.between(started_at, finished_at).in_seconds} seconds"

puts end_message
TerminalNotifier.notify(end_message, title: "MySQL Backup")