Get list of excluded databases from command line by asking user

Question

Based on this I'm working on this bash script for export all databases from MySQL but exlude some ones and also system ones. This is how it looks like:

read -e -p "Enter MySQL root user: " -i "root" root_user
read -e -p "Enter MySQL root user password: " root_password

FILENAME="mysqlbck"-$(date +%d-%m-%Y-%T).sql
read -e -p "Enter backup filename: " -i "$FILENAME" file_name
read -e -p "Enter directory for save backup: " -i "/home" directory

DATABASES_TO_EXCLUDE=""
EXCLUSION_LIST="'information_schema','performance_schema','mysql'"

for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done

SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"

for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done

MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump -u"$root_user" -p ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > "$directory/$filename"

I have two main issues around it:

• DATABASES_TO_EXCLUDE should holds a comma separated list for each DB that I want to exclude, how do I ask, in command line, for those names and build something like db1,db2,db3 and so on and put the values on the var? I mean something like the flow below:

  Enter DB name: db1
  // DATABASES_TO_EXCLUDE="db1"
  Enter DB name: db2
  // DATABASES_TO_EXCLUDE="db1,db2"
  Enter DB name: db3
  // DATABASES_TO_EXCLUDE="db1,db2,db3"
  Enter DB name: 
  // got empty value stop asking for DB name 
  // and continue script execution on EXCLUSION_LIST line and below
  

• I'm having two issues executing the script as it's right now. See the console output below:

  Enter MySQL root user: root
  Enter MySQL root user password: ********
  Enter backup filename: mysqlbck-12-03-2015-11:49:22.sql
  Enter directory for save backup: /home
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  /usr/local/bin/script-task: line 132: /home/: Is a directory
  

  Why do I get access denied while trying to use mysqldump? I have tried the way you are seeing on the script above and also tried this one:

      mysqldump -u"$root_user" -p"$root_password" ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > "$directory/$filename"
  

  Both have failed with same error, any advice?

  As per second issue shouldn't $directory/$filename read as /home/mysqlbck-12-03-2015-11:49:22.sql or the problem is the %T parameter and the : on the filename? Where is the error?

Answer 1

Please look back at my post again.

• DATABASES_TO_EXCLUDE
  • not supposed to be comma separated
  • supposed to be space separated
• EXCLUSION_LIST
  • supposed to be a comma-separated list of single-quote enclosed database names
  • this is for the sake of forming the SQL to extract names from information_schema.schemata

If you made DATABASES_TO_EXCLUDE="db1,db2,db3", add the following lines to remove the commas

DBX=${DATABASES_TO_EXCLUDE}
DATABASES_TO_EXCLUDE=`echo "${DBX}" | sed 's/,/ /g'`

Your filename should look at little different

FILENAME="mysqlbck"-`date +"%Y%m%d-%H%M%S"`.sql

As for "Access Denied", you need to make sure you have the proper grants to the database.