Cron to back up MySQL

[GoWilkes]

I want to back up one site's MySQL database every night, and every other database weekly.

I have this for the nightly backup, which I'm pretty sure will let the database stay active so minimal impact on site users:

0 1 * * * mysqldump --single-transaction --quick example | gzip > "/backup/example.sql.gz"

Separately, I think that this should work to back up all databases separately if I run it via SSH (not tested, though):

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump --single-transaction --quick $DB | gzip > "/backup/$DB.sql.gz";
done

My questions are:

1. How do I modify that second code to exclude "example"; and

2. Do I just put the entire loop as a one-liner in crontab? Eg, 0 2 * * * for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump --single-transaction --quick $DB | gzip > "/backup/$DB.sql.gz"; done

 

[rbairwell]

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump --single-transaction --quick $DB | gzip > "/backup/$DB.sql.gz";
done

...How do I modify that ... code to exclude "example";

Stackoverflow has an answer which might help:

mysql ... -N -e "show databases like '%';" |grep-v -F databaseidontwant |xargs mysqldump ... --databases > out.sql

2. Do I just put the entire loop as a one-liner in crontab? Eg, 0 2 * * * for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump --single-transaction --quick $DB | gzip > "/backup/$DB.sql.gz"; done

You can do, yes - or alternatively save that as in a Bash file such as all_but_databaseidontwant.sh :

#!/bin/bash
for DB in $(mysql -e "show databases;" | grep -v -F databaseidontwant); do
  mysqldump --single-transaction --quick $DB | gzip > "/backup/$DB.sql.gz";
done

chmod it to be executable (chmod +x all_but_databaseidontwant.sh) and then just call that via cron such as 0 2 * * * /root/all_but_databaseidontwant.sh > /backup/cron.log 2>&1

 

[GoWilkes]

Thanks, @rbairwell!

For the sake of future readers, it looks like I can modify the "show databases" in regular MySQL query format to exclude it, too:

for DB in $(mysql -e 'show databases where database != "example"' -s --skip-column-names); do
    mysqldump --single-transaction --quick $DB | gzip > "/backup/$DB.sql.gz";
done