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?