Check MySQL connection from shell (bash) and backup database

et voilà. In case you should ever be in a situation to check your mysql connection from a shell script you can easily do so by:

#!/bin/bash
echo -n -e "Please enter the DB user: \t\t"
read DB_USER

echo -n -e "Please enter the DB password: \t\t"
read DB_PWD

DB_con_ok=$(mysql -u $DB_USER --password=$DB_PWD -e "show databases;"|grep "mysql")
if [[ $DB_con_ok != "mysql" ]]
then
echo
echo "The DB connection could not be established. Check you username and password and try again."
echo
exit;
fi
#whatever code is executed here, only is done so, IF the mysql credentials are valid

And above, if you need to back up your DB (e.g. in a cron), you can easily do so by

#!/bin/bash
#DEFINITIONS FIRST
DB_USER="mysqlUser"
DB_PWD="mysqlPwd"
VERSION=$(date +"%d-%m-%Y") #store the day, month and year the backup was performed

#CHANGE TO BACKUP DIRECTORY
cd /backup
#PERFORM THE MYSQL BACKUP COMMAND
mysqldump --routines --all-databases -u $DB_USER --password=$DB_PWD > db_$VERSION.mysql

The --routines command makes sure, you're backing up stored procedures as well.

OR if you only want to backup specific databases, just use

#!/bin/bash
#DEFINITIONS FIRST
DB_USER="mysqlUser"
DB_PWD="mysqlPwd"
DATABASES="database1 database2 database3" #define the databases that you want to backup (delimited by the ' ' whitespace character)
VERSION=$(date +"%d-%m-%Y") #store the day, month and year the backup was performed

#CHANGE TO BACKUP DIRECTORY
cd /backup
#PERFORM THE MYSQL BACKUP COMMAND
mysqldump --routines -u $DB_USER --password=$DB_PWD --databases $DATABASES > db_$VERSION.mysql

check out the MySQL manual for mysqldump as well. Hope this was useful to some of you :D

Cheers,
Charly