Get list of excluded databases from command line by asking user 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} &gt; "$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} &gt; "$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?
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.