GoWilkes

Well-Known Member
Sep 26, 2006
692
33
178
cPanel Access Level
Root Administrator
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:

Code:
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):

Code:
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

Well-Known Member
May 28, 2022
98
42
18
Mansfield, Nottingham, UK
cPanel Access Level
Root Administrator
Code:
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:
Code:
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 :
Code:
#!/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
 
  • Like
Reactions: cPRex

GoWilkes

Well-Known Member
Sep 26, 2006
692
33
178
cPanel Access Level
Root Administrator
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:

Code:
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