Helper for importing SQL dumps into mysql server running in Docker containers

docker-mysql-import

#!/bin/bash

# NAME
#
#	docker-mysql-import
#
# DESCRIPTION
#
#	Helper for importing SQL dumps into mysql server running in Docker containers.
#
# INSTALLATION
#	
#	Make script executable and add to your $PATH:
#
#		chmod +x /path/to/docker-mysql-import
#		mv /path/to/docker-mysql-import /directory/in/your/$PATH
#	
#	For a progress bar display, install pv:
#
#		homebrew install pv
#
# EXAMPLE
#
#	docker-mysql-import -p pass1234 -d new_database --recreate ~/Downloads/dump.sql
#
# EXTRAS
#	
#	Copy these functions into your shell profile (~/.bashrc, ~/.zshrc)
#
#	- Open a mysql shell in running container
#	
#       function docker-mysql-shell() {
#           { docker exec -it $(docker ps -qf "name=$1" | head -n1)\
#               /usr/bin/mysql "${@:2}"
#           } 2>/dev/null;
#       }
#
#       > docker-mysql-shell CONTAINER [MYSQL ARGS]
#       > docker-mysql-shell database -pmygoodpass -d new_database
#
#
#	- Monitor queries being excecuted in container
#       
#       function docker-mysql-processlist() {
#         while :; do clear;
#           { echo 'show processlist;'|\
#               docker exec -i $(docker ps -qf "name=$1" | head -n1)\
#               /usr/bin/mysql "${@:2}"
#           } 2>/dev/null;
#           sleep 2;
#         done
#       }
#
#       > docker-mysql-processlist CONTAINER [MYSQL ARGS]
#       > docker-mysql-processlist database -pmygoodpass
#

usage() {
    echo "Usage:"
    echo "  docker-mysql-import [/path/to/data.sql]"
    echo "      [-u user] [-p password] [-d database]"
    echo "      [-c container_id]"
    echo
    echo "Required:"
    echo "    -d , --database"
    echo "        The MySQL database to import the supplied"
    echo "        sql file into."
    echo
    echo "Options:"
    echo "    -u , --user"
    echo "        The MySQL user name to use when connecting"
    echo "        to the container. Defaults to 'root'"
    echo
    echo "    -p , --password"
    echo "        The MySQL password to use when connecting"
    echo "        to the container. Defaults to 'root'"
    echo
    echo "    -n , --container-name"
    echo "        Name of the docker container running mysql"
    echo "        Defaults to the output of"
    echo '        docker ps -qf "name=mysql"'
    echo
    echo "    -c , --container-id"
    echo "        ID of the docker container running mysql"
    echo "        Defaults to the output of"
    echo '        docker ps -qf "name=mysql"'
    echo
    echo "    --recreate"
    echo "        Drops database and creates it before import"
    echo
    exit
}

err() {
    echo "$1"
    exit 1
}

DATABASE=''
PASSWORD='root'
USER='root'
SQLFILE=''
RECREATE=false
CONTAINERNAME='mysql'

# Parse arguments
PARAMS=""
while (( "$#" )); do
    case "$1" in
        -u|--user)
            USER=$2
            shift 2
            ;;
        -p|--password)
            PASSWORD=$2
            shift 2
            ;;
        -d|--database)
            DATABASE=$2
            shift 2
            ;;
        -c|--container-id)
            CONTAINER=$2
            shift 2
            ;;
        -n|--container-name)
            CONTAINERNAME=$2
            shift 2
            ;;
        --recreate)
            RECREATE=true;
            shift 1
            ;;
        --) # end argument parsing
            shift
            break
            ;;
        -*|--*=) # unsupported flags
            echo "Error: Unsupported flag $1" >&2
            exit 1
            ;;
        *) # preserve positional arguments
            PARAMS="$PARAMS $1"
            shift
            ;;
    esac
done

# Set positional arguments in their proper place
eval set -- "$PARAMS"

# Ensure sql file path is set
if [[ $# -ne 1 ]] ; then
    usage
    exit
fi
SQL_PATH="$1"

# Support pv if installed for progress monitoring
PIPE="cat"
if hash pv 2>/dev/null; then
    PIPE="pv"
fi

# Grab container from id or name
CONTAINER=$(docker ps -qf "name=$CONTAINERNAME" | head -n1)

# Do the dang thing
MYSQL_COMMAND="/usr/bin/mysql -u $USER -p$PASSWORD"
if $RECREATE; then
    echo "Dropping database $DATABASE..."
    { echo "DROP DATABASE IF EXISTS $DATABASE;"\
        | docker exec -i $CONTAINER $MYSQL_COMMAND; } 2>/dev/null;
    echo "Creating database $DATABASE..."
    { echo "CREATE DATABASE IF NOT EXISTS $DATABASE;"\
        | docker exec -i $CONTAINER $MYSQL_COMMAND; } 2>/dev/null;
fi

echo "Importing $SQL_PATH into database $DATABASE..."
$PIPE $SQL_PATH | docker exec -i $CONTAINER $MYSQL_COMMAND $DATABASE;