[Gimli[2BV!2B]]

Versione di sviluppo di uno script per restore parallelo di una serie di database MySQL compressi in bz2.
Potrebbero esserci imprecisioni, l'ho poi affinato sul sistema di destinazione dove è entrato in produzione.

#!/bin/bash

# Restore MySql database backups
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# Copyleft (C) 2011-2018 Gimli
# -------------------------------------------------------------------------
# 20171109 - v1.0.0
# 20180131 - v1.0.1
# 20180405 - v1.0.2 test again if database mysql is being processed and ask
# 20180911 - v1.0.3 DECOMPRESSOR variable, and MySQL SESSION variables
# 20181001 - v2.0.0 parallel
# -------------------------------------------------------------------------

usage()
{
cat << EOF
utilizzo: $0 "pattern"

Questo script esegue il restore di database mysql sul server locale.
Consuma file compressi con bz2 (teoricamente creati con backup.sh)
Specificare un solo parametro:
  "\backup\mysql\*.2017-11-09_04.00.sql.bz2" pattern match dei file da ripristinare

EOF
}

# this function is called when Ctrl-C is sent
function trap_ctrlc ()
{
	# perform cleanup here
	printf "\n\nCtrl-C caught... "
	stty echo
	echo "exit."

	# exit shell script with error code 3
	exit 3
}

# initialise trap to call trap_ctrlc function
# when signal 2 (SIGINT) is received
trap "trap_ctrlc" INT

if [ -z "$1" ]; then
  echo "Nessun file specificato!"
  usage
  exit 1
fi

FILES="$1"

echo "Decompressor: "
DECOMPRESSOR="$(which lbunzip2 2>/dev/null)";
if [ -z "$DECOMPRESSOR" ]; then
  DECOMPRESSOR="$(which pbunzip2 2>/dev/null)";
fi
if [ -z "$DECOMPRESSOR" ]; then
  DECOMPRESSOR="$(which bunzip2)";
fi
printf "  $DECOMPRESSOR\n\n"

echo "Parallel: "
ENV_PARALLEL="$(which env_parallel.bash 2>/dev/null)"
if [ -z "$ENV_PARALLEL" ]; then
	printf "  NOT available\n\n"
else
	printf "  $ENV_PARALLEL\n\n"
	. $ENV_PARALLEL
fi

echo "Restoring files matching: "
printf "  $FILES\n\n"
list=($FILES)
echo "Matched ${#list[@]} files:"
for file in "${list[@]}"; do
	if [ -f "$file" ]; then
		echo "  $file";
	else
		echo "  NO VALID MATCHES: check the argument passed to the script"
		exit 2
	fi
	if [[ $file == */mysql\.* ]] || [[ $file == ^mysql\.* ]]; then
		echo "################################################################################"
		echo "WARNING! Is this the mysql database? This will overwrite users, grants, etc..."
		echo -e "################################################################################\n"
	fi
done
printf "\n\n"
echo "INFO: press CTRL C to abort, at any time (be aware that this will *kill* the restore at the point reached)"

stty -echo
printf "MySQL root password: "
read PASSWORD
stty echo
printf "\n"

printf "Start: "
date
SECONDS=0

FILES_TOT=0
FILES_CONFIRMED=()
for file in $FILES ; do
	if [[ $file == */mysql\.* ]] || [[ $file == ^mysql\.* ]]; then
		echo "################################################################################"
		echo "WARNING! Is this the mysql database? This will overwrite users, grants, etc..."
		echo -e "################################################################################\n"
		printf "Press [Y]es to continue, [N]o or Ctrl C to skip\n"
		while true; do
			read -p "" yn
			case $yn in
				[Yy]* ) FILES_CONFIRMED+=("$file"); break;;
				[Nn]* ) printf "\nSkipped file:\n$file\n"; break;;
				* ) echo "Please answer Yes or No";;
			esac
		done

		printf "\n"
	else
		FILES_CONFIRMED+=("$file")
		((FILES_TOT++))
	fi
done

filter_sql()
{
  sed -e '1s|^|SET SESSION myisam_sort_buffer_size = 5*1024*1024*1024;|' \
      -e '1s|^|SET SESSION read_buffer_size = 256*1024*1024;|' \
      -e 's|/\*\!40000 ALTER TABLE \(`.*`\) DISABLE KEYS \*/;|ALTER TABLE \1 DISABLE KEYS;|' \
      -e 's|/\*\!40000 ALTER TABLE \(`.*`\) ENABLE KEYS \*/;|ALTER TABLE \1 ENABLE KEYS;|'
}

restore()
{
	CMD_TIME="$(which time)"
	if [ -z "$ENV_PARALLEL" ]; then
		printf "\nFile [%2s/$FILES_TOT] -> $1\n" "$2"
		CMD_PV="pv -fperb"
    pv "$1" | $DECOMPRESSOR | filter_sql | MYSQL_PWD="$PASSWORD" mysql -uroot
	else
		printf '\n\n%*s' "${COLUMNS:-$(tput cols)}" '' | tr ' ' -
		printf "\nFile terminato [%2s/$FILES_TOT] -> %-55s " "$2" "$1"
		CMD_PV="pv -fa"
		( pv -fa "$1" | $DECOMPRESSOR | filter_sql | MYSQL_PWD="$PASSWORD" TIME="Time %E" $CMD_TIME mysql -uroot 2>&1 ) 2>&1
	fi
}

if [ -z "$ENV_PARALLEL" ]; then
	count=1
	for file in $FILES ; do
		restore $file $count
		((count++))
	done
else
	env_parallel -j-1 --bar --env PASSWORD --env DECOMPRESSOR --env FILES_TOT --env ENV_PARALLEL --env filter_sql --env restore restore {} {#} ::: "${FILES_CONFIRMED[@]}"
fi

printf "Exit: "
date
echo "Elapsed seconds: $SECONDS"

Esempio di utilizzo:

sertan ~/restore # ls -la sql/
totale 5020
drwxr-xr-x 1 root root     212 29 set 01.27 .
drwxr-xr-x 1 root root     102  5 ott 22.55 ..
-rw-r--r-- 1 root root 5073345 29 set 00.38 amarokdb_20180929.sql.bz2
-rw-r--r-- 1 root root    3659 29 set 00.39 elips_20180929.sql.bz2
-rw-r--r-- 1 root root   53964 29 set 01.27 tc_20180929.sql.bz2
-rw-r--r-- 1 root root    2138 29 set 00.39 ww_20180929.sql.bz2
sertan ~/restore # restore.sh "sql/*"
Decompressor: 
  /usr/bin/lbunzip2

Parallel: 
  /usr/bin/env_parallel.bash

Restoring files matching: 
  sql/*

Matched 4 files:
  sql/amarokdb_20180929.sql.bz2
  sql/elips_20180929.sql.bz2
  sql/tc_20180929.sql.bz2
  sql/ww_20180929.sql.bz2


INFO: press CTRL C to abort, at any time (be aware that this will *kill* the restore at the point reached)
MySQL root password: 
Start: ven  5 ott 2018, 22.57.30, CEST
[...]
Exit: ven  5 ott 2018, 22.57.36, CEST
Elapsed seconds: 6