Cron to back up MySQL

GoWilkes

• November 02, 2022 16:15

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

  • November 03, 2022 07:23

  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 out.sql
  [QUOTE] 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
  

  0
• 

  GoWilkes

  • January 01, 2023 18:42

  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
  

  0

Please sign in to leave a comment.