dumb a given list of tables from a remote mysql database to seperate *.sql files showing progress and skipping existing.

dump_databases.sh

#!/bin/bash

######################################
# please add your settigs here:
DB_HOST=<ip of the databse-server>
DB_USERNAME=<username>
DB_PASSWORD=<password>
######################################

# write a temporary config file with the credentials to prevent terminal warnings
echo "[client]
host=$DB_HOST
user=$DB_USERNAME
password=$DB_PASSWORD
" > "db.cnf"

# add the tables you want to dump here:
for DB_NAME in table_01 table_02 table_03 table_04
do
	OUTPUT_SQL="$DB_NAME".sql
	OUTPUT_TMP="$OUTPUT_SQL".tmp
	OUTPUT_GZ="$OUTPUT_SQL".gz

	# remove old *.tmp file
	rm -rf $OUTPUT_TMP

	if [ ! -f $OUTPUT_GZ ]; then

		# get the size of the selected database (to show progress)
		DB_SIZE=$(mysql --defaults-extra-file=db.cnf \
			--silent --skip-column-names \
			-e "SELECT ROUND(SUM(data_length), 0) \
					FROM INFORMATION_SCHEMA.TABLES \
					WHERE TABLE_SCHEMA = '$DB_NAME';")

		echo "CREATE DATABASE IF NOT EXISTS $DB_NAME;" > $OUTPUT_TMP
		echo "USE $DB_NAME;" >> $OUTPUT_TMP

		echo "dumping $DB_NAME to $OUTPUT_SQL ...";
		mysqldump --defaults-extra-file=db.cnf \
			--single-transaction \
			--order-by-primary \
			--compress \
			--add-drop-database \
			$DB_NAME | pv --size $DB_SIZE >> $OUTPUT_TMP

		mv "$OUTPUT_TMP" "$OUTPUT_SQL"

		echo "compressing $OUTPUT_SQL to $OUTPUT_GZ ...";
		cat $OUTPUT_SQL | pv --line-mode --size $( wc -l $OUTPUT_SQL ) | gzip -c -- > $OUTPUT_GZ

		# removeing unziped sql file
		rm -rf $OUTPUT_SQL

		echo "DONE with file $OUTPUT_GZ !!"
		echo ""
	else
		echo "skipping $OUTPUT_GZ"
	fi
done

# deleting temporary config file
rm -f db.cnf