export-all-databases-into-separate-gz-files.md

Export all databases into separate .gz files

dbs_export() {

	MYSQL_HOST='localhost'
	MYSQL_USER='root'
	MYSQL_PASS=''
	MYSQL_DB=''

	while getopts ":h:u:p:d:" opt; do
	  case $opt in
	    h) MYSQL_HOST="$OPTARG"
	    ;;
	    u) MYSQL_USER="$OPTARG"
	    ;;
	    p) MYSQL_PASS="$OPTARG"
	    ;;
	    d) MYSQL_DB="$OPTARG"
	    ;;
	    \?) echo "Invalid option -$OPTARG" >&2
	    ;;
	  esac
	done
	
	echo "-- START --"

	echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" > tmp_sqlhead.sql
	echo "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;" > tmp_sqlend.sql

	if [ -z "$MYSQL_DB" ]
	  then
	    echo "-- Dumping all DB ..."
	    for I in $(mysql -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS -e 'show databases' -s --skip-column-names); 
	    do
	      if [ "$I" = information_schema ] || [ "$I" =  mysql ] || [ "$I" =  phpmyadmin ] || [ "$I" =  performance_schema ]  # exclude this DB
	      then
	         echo "-- Skip $I ..."
	       continue
	      fi
	      echo "-- Dumping $I ..."
	      # Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)
	      mysqldump -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz" 
	    done

	else
	      I=$MYSQL_DB;
	      echo "-- Dumping $I ..."
	      # Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)
	      mysqldump -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz" 
	fi

	# remove tmp files
	rm tmp_sqlhead.sql
	rm tmp_sqlend.sql

	echo "-- FINISH --"

}