program to import|export a database faster by using csv files

db_importer_exporter.sh

#!/bin/bash
#title          :program to import|export a database faster by using tsv files
#description    :This script will generate a .tsv file for all data and a .sql file for the structure of each table from MySQL database.
#author         :Gebhard Nigsch
#date           :02/11/2020
#version        :1.0
#requires:      :mysql, mysqlimport --secure-file-priv=""
#based on:      :https://www.percona.com/blog/2018/02/22/restore-mysql-logical-backup-maximum-speed/

USAGE="$(basename "$0") [-h] [-u -p -l -f -v -z] [import|export] databaseName -- program to import|export a database faster by using csv files

needed in my.ini for export:
secure-file-priv=\"\"

needin in my.ini for import:
innodb_autoinc_lock_mode=2

where:
    -c the number of cpu for import
    -h  show this help text
    -d  set the import|export dir
    -l  set set the mysql host
    -u  set the mysql user name
    -p  set the mysql password
    -P set the mysql port
    -v enable debug
    -z zip export
    --ssh-user ssh user
    --ssh-host ssh host
    --ssh-port ssh port
    --docker-container execute in docker container
    --read-from-docker-container read db settings from docker container
    --export-lines how many lines per file used for spit in export mode -> default 100000
    --ignore-tables ignore-tables ignore tables on import|export separated by ','
    @todo --tables-only only import|export this tables separated by ','
"

#config
DATABASE=""         #param -t
USER="root"         #param -u
PW="root"           #param -p
PORT="3306"         #param -p
HOST="127.0.0.1"    #param -h
DIR="$(pwd)/backup" #param -d
VERBOSE=""          #param -v
CPU=""              #param -c
ZIP_EXPORT=""       #param -z
EXPORT_LINES=100000
IGNORE_TABLES=""
DOCKER_CONTAINER=""
DOCKER_DIR="/var/lib/mysql/backup"
SSH_USER="deployment"
SSH_PORT="22"
SSH_HOST=""
SSH_DIR=""

while :; do
  case "$1" in
  -c)
    CPU="$2"
    shift 2
    ;;
  --docker-container)
    DOCKER_CONTAINER="$2"
    shift 2
    ;;
  --docker-dir)
    DOCKER_DIR="$2"
    shift 2
    ;;
  --export-lines)
    EXPORT_LINES="$2"
    shift 2
    ;;
  --ssh-user)
    SSH_USER="$2"
    shift 2
    ;;
  --ssh-port)
    SSH_PORT="$2"
    shift 2
    ;;
  --ssh-host)
    SSH_HOST="$2"
    shift 2
    ;;
  --ssh-dir)
    SSH_DIR="$2"
    shift 2
    ;;
  --ignore-tables)
    IGNORE_TABLES="$2"
    shift 2
    ;;
  -h | --help)
    echo "$USAGE"
    exit
    ;;
  -u)
    USER="$2"
    shift 2
    ;;
  -p)
    PW="$2"
    shift 2
    ;;
  -P)
    PORT="$2"
    shift 2
    ;;
  -l)
    HOST="$2"
    shift 2
    ;;
  -d)
    DIR="$2"
    shift 2
    ;;
  -v | --verbose)
    VERBOSE="1"
    shift 1
    ;;
  -z)
    ZIP_EXPORT="1"
    shift 1
    ;;
  --) # End of all options
    shift
    break
    ;;
  -*)
    echo "Error: Unknown option: $1" >&2
    ## or call function display_help
    exit 1
    ;;
  *) # No more options
    break
    ;;
  esac
done

i=1
for param in $@; do
  if [ "$i" == 1 ]; then
    ACTION="$param"
  elif [ "$i" == 2 ]; then
    DATABASE="$param"
  fi
  i=$(($i + 1))
done

if [ "$ACTION" == "" ]; then
  echo "missing param 1 for action import|export" >&2
  echo "$usage" >&2
  exit 1
  el
  case "$ACTION" in
  export)
    echo "export_Db"
    ;;
  import)
    echo "import_Db"
    ;;
  *)
    echo "wrong param 1 for action import|export" >&2
    echo "$usage" >&2
    exit 1
    ;;
  esac
fi

if [ "$DATABASE" == "" ]; then
  echo "missing param 2 for mysql DATABASE to import|export" >&2
  echo "$usage" >&2
  exit
fi

if [ ! -d "$DIR" ]; then
  echo "directory $DIR does not exist" >&2
  echo "$usage" >&2
  exit 1
fi

if [ "$CPU" == "" ]; then
  CPU=$(cat /proc/cpuinfo | grep processor | wc -l)
fi

USE_SSH=""
if [[ "$SSH_HOST" != "" && "$SSH_USER" != "" && "$SSH_PORT" != "" ]]; then
    USE_SSH=1
fi

IGNORE_TABLES_STRING=""
if [ "$IGNORE_TABLES" != "" ]; then
  IGNORE_TABLES_STRING="$(IFS=","; echo "${IGNORE_TABLES[*]/#/--ignore-table=}")"
fi

VERBOSE_STRING="";
if [ "$VERBOSE" != "" ]; then
    VERBOSE_STRING="-v";
fi

#Export db
function export_db() {
  clear_folder $DIR
  echo "start export of $DATABASE"
  time (
    if [ "$DOCKER_CONTAINER" != "" ]; then
      docker exec $DOCKER_CONTAINER mysqldump -h $HOST -P $PORT -u $USER -p$PW --compress --tab $DOCKER_DIR --fields-enclosed-by='"' --lines-terminated-by="||[NL]||" ${IGNORE_TABLES_STRING} ${VERBOSE_STRING} $DATABASE
    else
      if [ "$USE_SSH" != "" ]; then
        # already ssh tunnel open kill it
        checkSSH=$(ssh -S db_import_export -O check $SSH_USER@$SSH_HOST 2>&1);
        if [[ $checkSSH == *"running"* ]]; then
          close_ssh
        fi

        #for windows disable path translation
        export MSYS_NO_PATHCONV=1

        # open ssh tunnel in background
        ssh -M -S db_import_export -fnNT -L 3336:$HOST:$PORT -p $SSH_PORT $SSH_USER@$SSH_HOST

        mysqldump -h 127.0.0.1 -P 3336 -u $USER -p$PW --compress --tab /tmp/$DATABASE --fields-enclosed-by='"' --lines-terminated-by="||[NL]||" ${IGNORE_TABLES_STRING} ${VERBOSE_STRING} $DATABASE

        close_ssh
      else
        mysqldump -h $HOST -P $PORT -u $USER -p$PW --compress --tab $DIR --fields-enclosed-by='"' --lines-terminated-by="||[NL]||" ${IGNORE_TABLES_STRING} ${VERBOSE_STRING} $DATABASE
      fi
    fi
  )

  export_splitted_files
  zip_export
}

function close_ssh() {
  echo "close ssh";
  echo "ssh -S db_import_export -O exit $SSH_USER@$SSH_HOST"
  ssh -S db_import_export -O exit $SSH_USER@$SSH_HOST
}

function export_splitted_files() {

  cd $DIR

  #has files
  txtFiles=$(find $DIR -type f \( -iname "*.txt" \))
  if [ "$txtFiles" == "" ]; then
    echo "no files"
    return
  fi

  #replace new line placeholder ||[NL]||
  echo "start replacing new line placeholders"
  time (
    for i in ./*.txt; do
      #replace all new lines
      sed -E -i ':a;N;$!ba;s/\r{0,1}\n/\\\\\\n;/g' $i
      #replace ||[NL]|| to \n
      sed -i 's/||\[NL\]||/\n/g' $i;
    done
  )

  split_installed=1
  type split >/dev/null 2>&1 || split_installed=0
  if [ "$split_installed" == 1 ]; then
    echo "start splitting of data files"
    time (
      for i in ./*.txt; do
        split -d -a 6 -l $EXPORT_LINES -- $i $(basename $i .tsv).
        rm "$i"
      done
    )
  else
    echo "split not found"
  fi
}

#clearing folder with $1 argument 1
function clear_folder() {
  clearFolder=$1;
  echo "clear folder $clearFolder"
  rm -f $clearFolder/*.txt*
  rm -f $clearFolder/*.sql
}

function zip_export() {
  if [ "$ZIP_EXPORT" != "" ]; then
    echo "start zip export"
    name="$DATABASE.tar.gz"
    tar --transform 's/.*\///g' -zcf $name $DIR/*
    clear_folder $DIR
    mv $name $DIR/$name
  fi
}

# Import db
function import_db() {
  echo "start import of $DATABASE with $CPU core(s)"

  name="$DATABASE.tar.gz"
  if [ -f "$DIR/$name" ]; then
    tar -zxf $DIR/$name --directory $DIR
  fi

  time (
    #delete existing data and create database
    mysql -h $HOST -P $PORT -u $USER -p$PW ${VERBOSE_STRING} -e "drop database if exists \`$DATABASE\`;create database \`$DATABASE\`;set global FOREIGN_KEY_CHECKS=0;"
    # create tables
    sqlFiles=$(find $DIR -type f \( -iname "*.sql" \))
    if [ "$sqlFiles" != "" ]; then
      (
        echo "SET FOREIGN_KEY_CHECKS=0;"
        cat $sqlFiles
      ) | mysql -h $HOST -P $PORT -u $USER -p$PW ${VERBOSE_STRING} $DATABASE
    fi
    # import data
    txtFiles=$(find $DIR -type f \( -iname "*.txt*" \))
    if [ "$txtFiles" != "" ]; then
      # - force not to stop on error
      mysqlimport -h $HOST -P $PORT -u $USER -p$PW --use-threads=$CPU --fields-enclosed-by='"' --lines-terminated-by="\n" ${VERBOSE_STRING} $DATABASE $txtFiles
    fi
  )
}

if [ "$ACTION" != "" ] && [[ "$ACTION" == "import" || "$ACTION" == "export" ]]; then
  if [ "$ACTION" == "import" ]; then
    time import_db
  elif [ "$ACTION" == "export" ]; then
    time export_db
  fi
else
  echo "please provide an action: import|export"
fi