Get list of excluded databases from command line by asking user

by ReynierPM   Last Updated September 11, 2019 21:06 PM

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?



Answers 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.

RolandoMySQLDBA
RolandoMySQLDBA
March 13, 2015 17:36 PM

Related Questions




How do you mysqldump specific table(s)?

Updated June 19, 2015 23:02 PM

Backup and restore "mysql" database

Updated September 14, 2017 22:06 PM