Date separated MySQL backups

Posted by Dan Sosedoff on September 18, 2009

Here is the bash shell script that makes archived dumps of your database server. All databases are separated from each other and stored into date based folders. 

#!/bin/bash

MyUSER="root"
MyPASS=""
MyHOST="localhost"
NOW="$(date +"%d-%m-%Y")"
STOREDIR="/home/storage/backup/database/by_dates/$NOW"
DBLIST="$(mysql -u $MyUSER -h $MyHOST -Bse 'show databases')"

[ ! -d $STOREDIR ] && mkdir -p $STOREDIR || :

for db in $DBLIST
do
	FILE="$STOREDIR/$db.gz"
	mysqldump -u $MyUSER -h $MyHOST $db | gzip -9 > $FILE
done

Simple MySQL backup script

Posted by Dan Sosedoff on January 15, 2009

There is a small useful ruby script to backup your MySQL databases in small projects, where speed of backup not so important.
Source:

#!/usr/bin/ruby
# MySQL Backup Utility
# Usage: ./mysql_backup.rb or ruby mysql_backup.rb
 
$backup_archive = true # gzip files after processing
$backup_dir = "/home/storage/backup/" # output directory
$backup_template = "project-%s-%s.sql" # text-%dbname-%timestamp.sql
$backup_cmd = "mysqldump -u local_backup --add-drop-table --databases %s > %s"
$backup_dblist = [ # list of databases to backup
  'main',
  'users',
  'admin',
  'cards'
] 
 
def backup_database(database)
  time = Time.now()
  time_str = sprintf("%02i-%02i-%04i-%02i%02i%02i",time.day, time.month, time.year, time.hour, time.min, time.sec)
  filename = sprintf($backup_template,database,time_str)
  filename = "#{$backup_dir}#{filename}"
 
  cmd = sprintf($backup_cmd,database,filename)
  if system(cmd) then
    system("gzip --best #{filename}") if $backup_archive
  end
end
 
$backup_dblist.each do |db|
  puts "Processing database... #{db}"
  backup_database(db)
end

Paste: http://pastie.org/341839