Migrate databases to a different host

mysql_copy.sh

#!/bin/bash -e

# Set permissions: `chmod 755 ./mysql_copy.sh`
# Run: `./mysql_copy.sh <databasename>`

######### CONFIGURE HERE ######################################################

# insert your source database credentials
SRC_DB_HOST=localhost
SRC_DB_USER=root
SRC_DB_PASS=

# insert your destination database credentials
DEST_DB_HOST=localhost
DEST_DB_USER=root
DEST_DB_PASS=

# add the databases you want to ignore here space-separated 
# leave the information_schema, performance_schema and mysql
IGNORE_DBS=( mysql performance_schema information_schema sys YOUR_DB_HERE ANOTHER_DB_HERE )

########## END CONFIGURATION ##################################################

# Helper variables for inclusion on command line mysql commands
SRC_DB_OPTS="-q --host $SRC_DB_HOST --user $SRC_DB_USER -p$SRC_DB_PASS"
DEST_DB_OPTS="-q --host $DEST_DB_HOST --user $DEST_DB_USER -p$DEST_DB_PASS"

# Colors for manipulating the output text
GRAY='\033[1;30m'
GREEN='\033[0;32m'
RED='\033[0;31m'
WHITE='\033[1;37m'
NC='\033[0m'
echo -e "${GRAY}"

if [ "$SRC_DB_HOST" == "$DEST_DB_HOST" ]; then
    echo -e "${RED}Source and destination are the same!${NC}"
    exit 99
fi

importdb() {
    local db=$1

    # Check for database existence in source
    set +e
    mysql $SRC_DB_OPTS $db -e "select 1;" >/dev/null 2>&1
    local exists=$?
    set -e

    # If exists is non-zero, print error and return exit code 1
    if [ $exists -ne 0 ]; then
        echo -e "${RED}Database $db does not exist on source database${NC}"
        return 1
    fi

    # Do the import
    echo -e "${GREEN}Importing database '$db' ..."
    echo -e "${GRAY}"
    mysqldump ${SRC_DB_OPTS} --databases $db | mysql ${DEST_DB_OPTS}
    local result=$?
    echo -e "${NC}"
    return $result
}

# If a database name was passed in, import it, otherwise loop through all of them
if [ ! -z $1 ]; then
    importdb $1
    exit $?
else

    # Used to track the number of databases that failed to import
    ERRORS=0

    # Loop through source databases
    mysql -e "show databases;" $SRC_DB_OPTS | while read db; do
        if [ "$db" != "Database" ] && [[ ! "${IGNORE_DBS[*]}" =~ "$db" ]]; then

            # Check for existence in destination
            set +e
            mysql $DEST_DB_OPTS $db -e "select 1;" >/dev/null 2>&1
            DB_EXISTS=$?
            set -e
            
            # Check to see if the database exists
            if [ $DB_EXISTS -eq 0 ]; then
                echo -e "${GRAY}Skipping database '$db', already exists in destination${NC}"
                continue
            fi
            
            # Ask the user if we want to import
            echo -en "${WHITE}Import database '$db' (y/n)? "
            read confirm </dev/tty
            echo -e "${NC}"
            
            # Verify the user wants to import
            set +e
            echo $confirm | grep "^[Y|y]" >/dev/null 2>&1
            confirmed=$?
            set -e

            if [ $confirmed -eq 0 ]; then

                # Import the database
                importdb $db

                # Insure import succeeded
                if [ $? -ne 0 ]; then
                    ERRORS=$[$ERRORS +1]
                    echo -e "${RED}Error importing database!${NC}"
                fi
            else
                # User elected to skip this one
                echo -e "${GRAY}Skipping database '$db'${NC}"
            fi

        else
            echo -e "${GRAY}Ignoring database '$db', in ignore list${NC}"
        fi
    done

    # Exit with the number of failures
    exit $ERRORS
fi