How to backup MYSQL databases as separate files using a BASH script in Ubuntu?

[erkrishna9]

Step 1: Create a file with the following contents and save it as 'db_backup.sh'

#!/bin/bash
USER="user_name"
PASSWORD="password"
OUTPUT="/root/db_backups"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Creating Backup of Database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db >  $OUTPUT/`date +%Y%m%d`.$db.sql
        sleep 5
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

Step 2: The uploaded file has to be allowed to execute. To set execute permissions the following command can be used

chmod +x /path/to/db_backup.sh

Step 3: In case of an error "Not able to execute a .sh file: /bin/bash^M: bad interpreter"

1. Install dos2unix application - sudo apt-get install dos2unix
2. Run dos2unix db_backup.sh

Step 4: To execute the file use the following command

./db_backup.sh