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" [email protected]
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?
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" [email protected]
so all the output goes to the email.
You should not include passwords in scripts.