#!/usr/bin/env ruby
 
################
#
# Put this script in your path and make it executable i.e ~/bin then chmod 755 mysqlbackup.rb
# 
# 1. Create your repo (mkdir /path/to/repo && cd /path/to/repo && git init)
# 2. Setup the remote server (ssh user@remote "mkdir mysql_back.git" && cd mysql_back.git && git init")
# 3. Setup the remote (git remote add origin ssh://user@remote/home/user/mysql_back.git)
# 4. run mysqlbackup.rb
#
# comments to matt.allen@gmail.com
# - Matt Allen (mattallen)
################
 
BACKUP_DIR = "/path/to/repo/"
MYSQL_USER = "root"
MYSQL_PASS = ""
MYSQL_COMMAND = "mysql5" # might be mysql
MYSQL_DUMP_COMMAND = "mysqldump5" # might be mysqldump
VERSBOSE = true
PUSH_TO_REMOTE = true
REMOTE_REPO_NAME = "origin"
REMOTE_GC = true
REMOTE_GC_COMMAND = 'ssh user@remote "cd mysql_back.git && git gc"'
 
databases = `#{MYSQL_COMMAND} -u#{MYSQL_USER} -p#{MYSQL_PASS} -Bse 'SHOW DATABASES'`.split("\n")
# databases = ["a_single_db"]
 
 
def log(txt)
  puts txt if VERSBOSE  
end
date = Time.now.to_s
# loop through the databases
databases.each do |database|
  log("Dumping Database - #{database}")
  `mkdir -p "#{BACKUP_DIR}#{database}"`
  tables = `#{MYSQL_COMMAND} #{database} -u#{MYSQL_USER} -p#{MYSQL_PASS} -Bse 'SHOW TABLES'`.split("\n")
  # loop through the tables in the database
  tables.each do |table|
    log("Dumping Table - #{table}")
    `#{MYSQL_DUMP_COMMAND} -u#{MYSQL_USER} -p#{MYSQL_PASS}  --skip-opt --skip-comments --complete-insert --order-by-primary "#{database}" "#{table}" > "#{BACKUP_DIR}#{database}/#{table}.sql"`
  end
end
# version control
log("Adding files")
`cd #{BACKUP_DIR} && git add .`
log("Committing changes")
`cd #{BACKUP_DIR} && git commit -m "cron backup @ #{date}"`
if PUSH_TO_REMOTE
  log("Pushing to origin")
  `cd #{BACKUP_DIR} && git push #{REMOTE_REPO_NAME} master`
end
log("Running GC locally")
`cd #{BACKUP_DIR} && git gc`
if REMOTE_GC
  log("Running GC remotely")
  `#{REMOTE_GC_COMMAND}`
end
log("Done")