[SOLVED] MySQL backup script with notification

MySQL backup script with notification

i have been scouring the internet this entire weekend in hopes to find a variety of scripts which i can edit to accomplish my goal.

i am trying to create a script that will automatically backup&compress all mysql databases to individual file on the localhost, then output the list of dbs backed up with the size of each db in an email

using Centos7 and latest mysql(mariaDB) (installed may 2018) and mailx for mail agent

this is the script i have so far...

#!/bin/bash
# Custom script to backup all MySQL dbs on localhost
# and output db list and sizes to email

# Some Variables
myuser="root"
mypass="******"

# Get current date
# date +"%d-%m-%y %T"

# Get list of DBs to backup
mysql -u $myuser -p$mypass -e 'show databases' > dblist

# Cleanup dblist
sed -i '/information_schema/d' dblist
sed -i '/mysql/d' dblist
sed -i '/performance_schema/d' dblist
sed -i '/Database/d' dblist

for db in $(mysql -u root -p****** -e 'show databases');
do
if [[ "$db" != *Database* && "$db" != *schema* && "$db" != "mysql" && "$db" != *performance* && "$db" != "mysql" ]];
then
mysqldump -u root -p****** $db | gzip -c > /backups/WEB02DBs/$db.sql.gz
fi
done

echo $dblist | mail -s "WEB02 DBs Backed UP" myemail@email.com

if i use just $db in the echo at end, it only includes the performance db in the email body, basically anything else i try to get list of dbs in email turns up emtpy

the db size part i can just not get right, either there is some syntax error, or it puts nothing into the email, even if i output the results to a file the email's body is empty

the first few variables is me trying to use $myuser and $mypass in script instead of putting root/password for each function

then i can also get a list of dbs, and "manually" remove the system DBs

can anyone help me get this working?

Solution

I guess that you want something like this

for db in $(mysql -u root -p****** -e 'show databases');
do
if [[ "$db" != *Database* && "$db" != *schema* && "$db" != "mysql" && "$db" != *performance* && "$db" != "mysql" ]];
then
    mysqldump -u root -p****** $db | gzip -c > /backups/WEB02DBs/$db.sql.gz && \
    printf '%s backed up\n' "$db"
fi
done | mail -s "WEB02 DBs Backed UP" myemail@email.com

so all the output goes to the email.

You should not include passwords in scripts.