A bash script to dump and migrate data from a MySQL database to one on your own machine.

dumpgrate.sh

#!/bin/bash

host=abc-prod-db
dbs=(a_metrics b_metrics c_metrics)
ignored_table=irrelevant_table

time=$(date +'%H%M%S')
day=$(date +'%F')

migrate_cache=/tmp/migrate_cache
dump_cache=$migrate_cache/dump


function create_migrate_cache {
    if [ ! -d  $migrate_cache ]; then
        mkdir $migrate_cache
    else
        echo "Migrate cache folder already exists."
    fi
}

function set_timestamp_cache {
    local timestamp=$time.$day    

    if [ ! -d $migrate_cache ]; then
        echo "Error!! No migrate_cache folder for timestamp cache!"
        exit 1
    fi

    echo $timestamp > $migrate_cache/timestamp
}

function get_timestamp_cache {
    if [ ! -d $migrate_cache ]; then
        echo "Error!! No migrate_cache folder for timestamp cache!"
        exit 1
    fi

    cat "$migrate_cache/timestamp"
}

function destroy_timestamp_cache {
    echo "[START] Clearing timestamp cache."

    if [ ! -d $migrate_cache ]; then
        echo "Error!! No migrate_cache folder for timestamp cache!"
        exit 1
    fi

    if [ ! -f $migrate_cache/timestamp ]; then
        echo "Error!! No timestamp cache! Nothing to delete!"
        exit 1
    fi

    rm $migrate_cache/timestamp

    echo "[FINISH] Clearing timestamp cache."
}

function create_dump_cache {
    if [ ! -d $dump_cache ]; then
        mkdir $dump_cache
    else
        echo "Dump cache already exists."
    fi
}

function destroy_dump_cache {
    echo '[START] Clearing dump cache.'
    
    if [ ! -d $dump_cache ]; then
        echo "Error!! No dump_cache folder for dump cache!"
        exit 1
    fi
    
    rm -rf $dump_cache

    echo '[FINISH] Clearing dump cache.'
}

function destroy_migrate_cache {
    echo '[START] Clearing migrate cache.'

    if [ !-d $migrate_cache ]; then
        echo "Error!! No migrate_cache folder for the migrate script!"
        exit 1
    fi

    rm -rf $migrate_cache

    echo '[FINISH] Clearing migrate cache.'
}

function dump {
    local mode=$1

    echo '[START] Data dump.'

    for db in ${dbs[@]}
    do
        echo "Creating dump for $host.$db."
        
        local start=`date +%s` 
        local timestamp=$(get_timestamp_cache)
        local filename="$host.$db.$timestamp.sql"
        echo "Filename: $filename" 

        if [ -z $host ]; then
            echo 'Error!! $host must not be empty!!'
            exit 1
        fi

        local args="-h $host"

        if [ ! -z $ignored_table ]; then
            args="$args --ignore-table=$db.$ignored_table"
        fi

        local cmd="mysqldump $args $db > $dump_cache/$filename"

        case $mode in
            "dryrun" )
                echo "DUMP Command: [ $cmd ]" ;;
            * )
                eval $cmd ;;
        esac

        echo 'Done!'

        local end=`date +%s`

        echo "Completed in $((start - end)) seconds".
    done

    echo '[FINISH] Data dump.'
}

function migrate {
    local mode=$1

    echo '[START] Data migration.'

    for db in ${dbs[@]}
    do
        echo "Migrating dump for $host.$db." 
        local start=`date +%s` 
        local timestamp=$(get_timestamp_cache)
        local filename="$host.$db.$timestamp.sql"
        echo "Filename: $filename"

        if [ -z $host ]; then
            echo 'Error!! $host must not be empty!'
            exit 1
        fi

        local cmd="mysql -h $host $db < $dump_cache/$filename"

        case $mode in
            "dryrun" )
                echo "MIGRATE Command: [ $cmd ]" ;;
            * )
                eval $cmd ;;
        esac

        echo 'Done!'

        local end=`date +%s`

        echo "Completed in $((start - end)) seconds".
    done

    echo '[FINISH] Data migration.'
}

function print_help {
    local msg="[START] Help.\n"
    msg="$msg Creates a data dump of each of the specified databases in the specified host.\n"
    msg="$msg Configure this script by editting the variables at the top of the file.\n"
    msg="$msg Make sure you have your ~/.my.cnf file configured.\n"
    msg="$msg Currently only supports MySQL.\n"
    msg="$msg OPTIONS:\n"
    msg="$msg --help (print this help message)\n"
    msg="$msg --dump (create a data dump of each database)\n"
    msg="$msg --migrate (migrate the data dump into your local MySQL service)\n" 
    msg="$msg --full (run both the dump and migrate functions)\n" 
    msg="$msg --clear-timestamp (clear the timestamp cache)"
    msg="$msg --clear-dump (clear the dump cache)"
    msg="$msg --clear-all (clear the entire cache folder for this script)"
    msg="$msg --dryrun (show the commands to be run in both dump and migrate)\n"
    msg="$msg[END] Help.\n" 
    echo -e $msg
}

function main {
    local mode=$1

    if [ ! -d $migrate_cache ]; then
        create_migrate_cache
    fi

    if [ ! -d $dump_cache]; then
        create_dump_cache
    fi

    if [ ! -f $migrate_cache/timestamp ]; then
        set_timestamp_cache
    fi

    case $mode in
        "--dump" )
            dump ;;
        "--migrate" )
            migrate ;;
        "--full" )
            dump && migrate ;;
        "--dryrun" )
            dump "dryrun" && migrate "dryrun" ;;
        "--clear-timestamp" )
            destroy_timestamp_cache ;;
        "--clear-dump" )
            destroy_dump_cache ;;
        "--clear-all" )
            destroy_migrate_cache ;;
        "--help" )
            print_help ;;
        "" )
            print_help ;;
        * )
            echo "Error! Invalid argument."
            exit 1 ;;
    esac
}

main $1