mysql | Tech Blog by New Leaf Digital

Nov 16 '10 12:27am

Incremental database backups with Git

I'm trying a new method for database (MySql) backups: incremental tracking every 10 minutes with Git. Basically I have a Git repository cloned to a directory on the server, every 10 minutes on cron the database is dumped to the directory, and all changes are committed. I added a changed-line counter to make the logs more interesting.

I'm not sure how this will work in production, or what strain mysqldump puts on the database (I should run this off a slave when it goes live, I'm thinking), but the ability to revert the database to any 10-minute increment in the past and view changes to each table line by line seems pretty cool. This is the script:

#! /bin/bash
 
cd /var/backups/db-git
DRUSH=/usr/bin/drush
$DRUSH --root=/path/to/webroot sql-dump > /var/backups/db-git/site-db.sql
syncDT=`date "+%Y-%m-%d_%H-%M"`
 
## count # of lines changed
NUMLINES=$(git diff | grep -e "^[+-]" | wc -l)
## don't count 2 header lines
NUMLINES=$(( $NUMLINES - 2 ))
 
git ci -a -m"DB backup DEV: $syncDT ($NUMLINES lines changed)"
git push origin

Any thoughts?

Jul 24 '10 3:17pm

Database Sync Script with SSH and Drush

This is a script I wrote in 2009 and updated a few months ago, to automate the creation of Bash scripts for synchronizing a database from a remote server to a local server via SSH. It's a single PHP file which presents a form, you fill in the form with the connection and DB info on both sides, and it creates a .sh file that you run to synchronize. (This script does not synchronize anything itself.) The original script used mysqldump and asked for database credentials; the new version uses Drush, so it's only for Drupal but doesn't need DB credentials anymore. (And if you use an SSH key it doesn't need SSH credentials either.) You will need Drush on both sides for it to work.

I'm not hosting this script because I don't want to be responsible for the security of your server information. Instead download the .tar file below and host it on your own server. Check out the code of the .sh script that it creates before you run it so you understand exactly what's going on.

(I'll be mentioning this in my Developer's Arsenal of Productivity Hacks presentation tomorrow at DrupalCampNYC, so stay tuned for that!)

I welcome any and all bug reports, fixes, or other feedback.

Update: Someone pointed out correctly in the comments that Drush already has sql-sync, so I realized I need to explain what this does better. sql-sync makes a remote connection to a database and transfers directly between them. Most of the servers I've worked with, however, do not allow remote login to their database server (except maybe from a separate web server). All the MySql security recommendations I've read say the same thing. So this script will dump the database on the remote server via SSH (run locally on that server), zip the file, transfer it down, and import it. It also backs up both databases which Drush doesn't.
(If Drush does in fact do this, please let me know!)

Update 2: I stand corrected! Thanks to the commenters for enlightening me, Drush does in fact do this. I'll take the script off of here, since it doesn't add anything (except the form maybe). The method is:
drush sql-sync SRC DEST --source-remote-host=XX --source-dump=YY --target-dump=ZZ (and some other options available at drush help sql-sync).

{tech}.Ben Buckman.net

Incremental database backups with Git

Database Sync Script with SSH and Drush

Networking

Recent Tech Posts

Tech Topics

Recently Bookmarked

RSS

Travels