Export MySQL database with optimized options - #mysql #database #export #bash

export-mysql.sh

#!/usr/bin/env bash

: ' A simple MySQL dump script with optimized options

'

# check if debug flag is set
if [ "${DEBUG}" = true ]; then

  set -x # enable print commands and their arguments as they are executed.
  export # show all declared variables (includes system variables)
  whoami # print current user

else

  # unset if flag is not set
  unset DEBUG

fi

# bash default parameters
set -o errexit  # make your script exit when a command fails
set -o pipefail # exit status of the last command that threw a non-zero exit code is returned
set -o nounset  # exit when your script tries to use undeclared variables

# parameters
__mysql_user="${1:-"root"}"
__mysql_password="${2:-"password"}"
__mysql_port="${3:-"3306"}"
__mysql_host="${4:-"127.0.0.1"}"
__mysql_database="${5:-"database"}"
__output_file="${6:-"/tmp/${__mysql_database}.gz"}"

# binaries
__MYSQLDUMP=$(which mysqldump)
__GZIP=$(which gzip)

# create mysqldump string
readonly __mysql_dump_string="${__MYSQLDUMP} --user="${__mysql_user}" \
                                     --password="${__mysql_password}" \
                                     --host="${__mysql_host}" \
                                     --port="${__mysql_port}" \
                                     --skip-opt \
                                     --skip-add-locks \
                                     --create-options \
                                     --no-create-db \
                                     --single-transaction \
                                     --quick \
                                     --extended-insert \
                                     --hex-blob \
                                     --routines \
                                     --set-charset \
                                     --add-drop-table \
                                     "${__mysql_database}" \
                                     | ${__GZIP} --stdout > "${__output_file}""

# try to dump the file
echo "Dumping database "${__mysql_database}" to file "${__output_file}", please wait..."
if ! eval $__mysql_dump_string; then

  echo "ERROR! MySQL could not perform dump for database "${__mysql_database}" to file "${__output_file}""
  exit 1

fi