BTMash

Nearly every place, I work at, a common problem comes up that is not implemented: backing up the database(s). Working with Drupal, I commonly see the usage of Backup and Migrate for backing up databases. Which isn't a bad first approach. But a number of questions come to mind - what if you have a database server that doesn't just serve Drupal websites (I have a db server that hosts sites running wikimedia, wordpress, and in the past Ruby on Rails applications)? And what do you do when dealing with very large databases (backup and migrate will go down unless you can allocate memory and/or increase the time limit for the php scripts)? There are other things to think about (mainly revolving around security and ensuring that aspect is set up well) but I find the first 2 larger things to think about. A few years back, I had written a script that would backup all your databases (or atleast the ones your db credentials had access to), save to a folder, and keep it in a rolling backup (it used to be more complex, but keeping it for 30 days fits in pretty well). In the spirit of sharing and hopefully learning from others around me, below is the script that I use for backing up my MySQL database server. It goes through a few steps:

1. Creates a directory with the date and time of the database snapshot.
2. Create a separate directory for each database.
3. Create a separate dump of each table into a separate file.
4. Zip each database up individually
5. Zip up the date/time directory

Its a bit more work to set up a new server with your dump, but the advantage would be that if you are dealing with a particularly large database dump, you could exclude certain tables (or files in this scenario) if they don't need to be imported into a database (for drupal, examples can include cache tables, search index tables, sessions). So since the source is posted below, the script is (naturally) open to be modified to exclude certain tables (could easily put in checks of tables to not import) or to obfuscate data from other tables (have the result for certain tables get piped into some other function that you create). But it is reasonably quick to backup, graceful, and can be connected with other tasks fairly easily. Other disadvantages include not directly tying in with your Drupal installation(s) but the point is for a slightly more generic tool for a MySQL database and to use a tool that does what it does really well.

#!/bin/bash
# Backup entire database to a folder (place in a cron job to be completed daily)
# Requirements for script to fully function:
# 1) Proper write permissions
# 2) Create a .my.cnf file to connect to the database - leave credentials out of the file!
 
Year=`eval date +%Y`
Month=`eval date +%m`
Date=`eval date +%d`
Hour=`eval date +%H`
Minute=`eval date +%M`
#change all below code for appropriate area - the path to mysql/mysqldump may be different or you want to save the backup to another directory, for example
sqlDir="/usr/bin"
dailyBackupFolder=~/dailyBackup
dateToday="$Year.$Month.$Date.$Hour.$Minute"
 
mkdir -p $dailyBackupFolder/$dateToday
 
# Look at all the tables that exist
for i in `$sqlDir/mysql -NB -e "SHOW DATABASES"`
do
  # Ignore core mysql databases
  if [ "$i" != "mysql" ] && [ "$i" != "information_schema" ]; then
    echo "Backing up $i"
    mkdir -p $dailyBackupFolder/$dateToday/$i
    # Retrieve all the tables for the database and back up one by one.
    for j in `$sqlDir/mysql -NB -e "SHOW TABLES" $i`
    do
      echo "Backing up database $i: table $j"
      $sqlDir/mysqldump --opt $i $j > $dailyBackupFolder/$dateToday/$i/$j.sql
    done
    tar -czvf $dailyBackupFolder/$dateToday/$i.tar.gz -C $dailyBackupFolder/$dateToday $i
    rm -rf $dailyBackupFolder/$dateToday/$i
  fi
done
tar -cvf $dailyBackupFolder/$dateToday.tar -C $dailyBackupFolder $dateToday
rm -rf $dailyBackupFolder/$dateToday
 
find $dailyBackupFolder -mtime +30 -exec rm -rf {} \;

So please, leave a comment if it helps or if you have suggestions on ways to make it better.