MYSQLDUMP all databases separately

[csdude55]

I'm currently using this to manually back up databases:

mysqldump --single-transaction --quick user_db | gzip > /backup/user_db.sql.gz

I have several databases on the server, so I currently run it once for each database, changing "user_db" to the name of each database. The whole process takes about 1 1/2 hours, so I only do it once a week.

I'd like to set up a CRON to back them all up automatically and keep them in their own .sql.gz files, so I can have it done nightly without me babysitting it.

Does this look right?

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

I have VERY little experience with cron, so I'm only guessing that (assuming the code is good) I can add this using "crontab -e".

It feels like my MySQL is held together with bubble gum and prayers, so I'm always very hesitant to run something unless I'm 100% positive that it'll work flawlessly :-O I'm not in a position where I can just "run it and see" if a potential typo could crash everything, ya know?

[Brett_Tabke]

you know you can just run shell commands in a cron driven shell script?
So take all your manual commands and put them in one file.
This is mine that runs nightly here for top posters:

grep "memberposts" -R /home/webmasterworld/public_html/newmembers/* > /home/webmasterworld/public_html/top-posts.txt
sed -e 's/\//:/g' -e 's/=/:/' -e 's/\./:/' -e 's/\r/:/g'< /home/webmasterworld/public_html/top-posts.txt > /home/webmasterworld/public_html/fixed-top-posts.txt
awk -F":" '{ print $10","$7}' /home/webmasterworld/public_html/fixed-top-posts.txt > /home/webmasterworld/public_html/fixed-top-posts2.txt
sort -gr /home/webmasterworld/public_html/fixed-top-posts2.txt > /home/webmasterworld/public_html/sorted-top-posts.txt
rm /home/webmasterworld/public_html/top-posts.txt
rm /home/webmasterworld/public_html/fixed-top-posts.txt

Just name the file with a cron extension for readability.

To test it out, make some test scripts just doing directories or something and dump the out put to a file.

[csdude55]

This is a tad above my expertise... is this a .SH file that you then run via crontab?

Are you suggesting that I add the "for DB...done" loop to the .SH file, or define each database manually?

[Brett_Tabke]

Well, if you are running your backup, by running command line commands, just take those commands and put in a text file, and then call that text file from cron. (just like an old batch file - one command per line).

So the above is my text file for calc'ing top posts here (which each line is just a shell command). Then the whole thing is called from a cron job. I use bash as my default cli:

My cron line:
/bin/bash /home/webmasterworld/make-top-100-posts.cron

[csdude55]

I gotcha. The downside to this, though, is that if a hosting client creates a new database then it won't be backed up unless I manually add it to the list.

[Brett_Tabke]

do a cron job as root and back up 'all' of mysql at one shot.


mysqldump -u root -p --all-databases > all_databases.sql

guide:
[linuxize.com...]