I’ve been asked many times to put together mysql database backup with weekly/monthly rotation.
I’m pretty sure there are plenty of such scripts out there on the web.
I am going to add mine so that somebody can compare and choose.
For such tasks shell is the best choice – fast, lightweight in terms of resources and pretty straightforward.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
#!/bin/sh # simple database backups # where to create backups WD="/backup" RC=0 #weekly creates Monday ...Sunday DAY=`/bin/date +%A` #monthly creates 1..31 DAY=`/bin/date +%d` # shortcuts M="/usr/bin/mysql" MD="/usr/bin/mysqldump" MD_OPT="--skip-opt -CcQqR" COMPLETE_OPT="--add-drop-database" MSOPT="-u root" #here goes root password - sorry no miracles, #at least it's not visible in process list export MYSQL_PWD="XXXXXX" # do db full cd $WD DBLST=`$M $MSOPT -BNe 'show databases;' | \ egrep -vE 'information_schema|mysql' | xargs` if [ "X$DBLST" != "X" ] ; then if [ ! -d $DAY ] ; then mkdir $DAY else touch $DAY fi cd $DAY # careful with this rm -f ./* for d in $DBLST do $MD $MSOPT $MD_OPT $COMPLETE_OPT $d > $d.sql # primitive error check RC=$? touch $d.sql done $MD $MSOPT $MD_OPT mysql user >mysql-user.sql RC=$? touch mysql-user.sql $MD $MSOPT $MD_OPT mysql db >mysql-db.sql RC=$? touch mysql-db.sql fi exit $RC ########################################### |
If you are short in disk space you can easily add gzip with piping mysqldump output via gzip. In this case it would be more efficient to drop C from mysqldump options (compression) otherwise whole process will take too much of CPU.
Keep in mind that mysqldump locks the tables so you may want to run it at some off-peak time on your server.
If you want to modify it for your own needs but feeling not very familiar with the language I can recommend [amazon_link id=”0596005954″ target=”_blank” ]Classic Shell Scripting[/amazon_link] or [amazon_link id=”0596009658″ target=”_blank” ]Learning the bash Shell[/amazon_link] books.
0 Comments.