Alister West

home is where your code is ...

Dump a MySQL live-db and reload it into dev-db

Innodb Tables

Use http://www.innodb.com/wp/products/hot-backup/.

backup-live-db.sh

This is a pretty simple script. Make sure to echo the commands first to make sure its doing what you expect (I'll probably re-write it in perl sometime with a --dry-run option).

#!/bin/sh
#
# Backup live db for loading into dev.
#

BACKUPS=~/backups
DB=my_db
DB_USER=my_username
DB_PASS=my_password

# --skip-lock-table - if we don't have lock permissions.
# --single-transaction - sends 'BEGIN SQL' to server before dump. should avoid any locking issues with innodb-tables.
DUMP_OPTS="--skip-lock-table --single-transaction"

# --
mkdir -pv $BACKUPS
DATE=`/bin/date +%s`
SCHEMA=$BACKUPS/${DB}_$DATE.schema.sql.gz
DATA=$BACKUPS/${DB}_$DATE.data.sql.gz

echo Running mysqldump to ... $SCHEMA
mysqldump -d $DUMP_OPTS -u$DB_USER -p$DB_PASS $DB | gzip > $SCHEMA

echo Running mysqldump to ... $DATA
mysqldump -t $DUMP_OPTS -u$DB_USER -p$DB_PASS $DB | gzip > $DATA

ls -lth --time-style=+%Y-%m-%d $SCHEMA
ls -lth --time-style=+%Y-%m-%d $DATA

echo "Done!"

load-last-backup-into-dev.pl

#!perl
# 
# load-last-backup-into-dev.pl -  Load last live backup into dev
#
use strict;
use warnings;

my $LIVE_DB="my_db";
my $DEV_DB="my_dev_db";
my $DEV_USER="my_dev_user";
my $DEV_PASS="my_dev_password";

# Don't do anything if has any arguments
my $DRY_RUN = scalar @ARGV;

# --force so will continue if errors.
my $DEV_MYSQL="mysql  -u$DEV_USER  -p$DEV_PASS  --force  $DEV_DB";

my $DATE=`/bin/date +%s`;

chomp( my $LAST_SCHEMA= `ls -t ~/backups/${LIVE_DB}_*.schema.sql.gz | head -n1`);

my $LAST_DATA  = $LAST_SCHEMA;
$LAST_DATA =~ s/\.schema\./.data./;

# Exit if it can't find matching backups.
unless (-e $LAST_SCHEMA && -e $LAST_DATA ) {
    print "Warning: No backups found ($LAST_SCHEMA, $LAST_DATA); Exiting!\n";
    exit;
}

# TODO: as IPC::Run3
sub cmd {
    my @cmds = @_;
    print "CMD: @cmds\n";
    print `@cmds` if !$DRY_RUN;
}

cmd("ls -sh $LAST_SCHEMA");
cmd("zcat $LAST_SCHEMA | $DEV_MYSQL");

cmd("ls -sh $LAST_DATA");
cmd("zcat $LAST_DATA | $DEV_MYSQL");

print "Debug: DB's Loaded.\n\n";

print "DEBUG: Stripping all email addresses ...\n";

cmd(qq{ $DEV_MYSQL -e '
    UPDATE Users 
        SET Name = substr( Name FROM 1 FOR instr(Name, "@") -1 ) 
      WHERE Name LIKE "%@%" 
        AND EmailAddress NOT LIKE "alister%alisterwest.com" '
});

print "Done!\n"; 

dump-all-databases

# On Ubuntu/Debian we can use the debian.cnf for credentials 
# We also want to ignore the mysql, test and other schemas

DATABASE_LIST=$(mysql --defaults-file=/etc/mysql/debian.cnf -NBe 'show schemas' | grep -Ev '^(mysql|test|performance_schema|information_schema)$' )

MYSQLDUMP_OPTS="--defaults-file=/etc/mysql/debian.cnf --single-transaction --max_allowed_packet=512M"

mysqldump $MYSQLDUMP_OPTS --databases $DATABASE_LIST | gzip -c > /backups/all-dbs.sql.gz
By Alister West