FreeBSD Backup All MySQL Databases To a NAS Server

by on January 24, 2009 · 2 comments

  1. #!/bin/sh
  2. # A Shell script to backup all MySQL databases to a NAS server mounted via mount_smbfs
  3. # You need to setup username, password and other stuff
  4. # Tested on FreeBSD 6.x and 7.x - 32 bit and 64 bit systems.
  5. # May work on OpenBSD / NetBSD.
  6. # -------------------------------------------------------------------------
  7. # Copyright (c) 2007 nixCraft project <http://www.cyberciti.biz/fb/>
  8. # This script is licensed under GNU GPL version 2.0 or above
  9. # -------------------------------------------------------------------------
  10. # This script is part of nixCraft shell script collection (NSSC)
  11. # Visit http://bash.cyberciti.biz/ for more information.
  12. # ----------------------------------------------------------------------
  13. ### SETUP BIN PATHS ###
  14. MYSQLADMIN=/usr/local/bin/mysqladmin
  15. MYSQL=/usr/local/bin/mysql
  16. LOGGER=/usr/bin/logger
  17. MYSQLDUMP=/usr/local/bin/mysqldump
  18. MKDIR=/bin/mkdir
  19. CP=/bin/cp
  20. GZIP=/usr/bin/gzip
  21. CUT=/usr/bin/cut
  22. AWK=/usr/bin/awk
  23. MOUNT=/sbin/mount
  24. GREP=/usr/bin/grep
  25. UMOUNT=/sbin/umount
  26. MSMBFS=/usr/sbin/mount_smbfs
  27. HOST=/usr/bin/host
  28. TAIL=/usr/bin/tail
  29. SSH=/usr/bin/ssh
  30. SCP=/usr/bin/scp
  31. HOSTNAME=/bin/hostname
  32.  
  33. ### SETUP MYSQL LOGIN ###
  34. MUSER=root
  35. MPASS='PASSWORD'
  36. MHOST="127.0.0.1"
  37.  
  38. ### SETUP NAS LOGIN ###
  39. NASUSER=vivek
  40. NASPASSWORD=myPassword
  41. NASSERVER=nas05.vip.nixcraft.com
  42. NASMNT=/nas05
  43. NASSHARE=$NASUSER
  44. NASPASSWDFILE=$HOME/.nsmbrc
  45. #GET NAS IP
  46. NASIP=$($HOST $NASSERVER | $TAIL -1 | $AWK '{ print $4}')
  47. # NAS BACKUP PATH
  48. MBAKPATH=${NASMNT}/$(hostname -s)/mysql
  49. NOW=$(date +"%d-%m-%Y")
  50. TIME_FORMAT='%H_%M_%S%P'
  51.  
  52. mount_nas(){
  53. [ ! -d $NASMNT ] && $MKDIR -p $NASMNT
  54. $MOUNT | $GREP $NASMNT >/dev/null
  55. if [ $? -ne 0 ]
  56. then
  57. echo "[$NASIP:$NASUSER]" >$NASPASSWDFILE
  58. echo "password=$NASPASSWORD" >>$NASPASSWDFILE
  59. $MSMBFS -N -I $NASSERVER //$NASUSER@$NASIP/$NASSHARE $NASMNT
  60. fi
  61. }
  62.  
  63. umount_nas(){
  64. $MOUNT | $GREP $NASMNT >/dev/null
  65. [ $? -eq 0 ] && $UMOUNT $NASMNT
  66. }
  67.  
  68. backup_mysql(){
  69. $LOGGER "$(basename $0) mysql: Started at $(date)"
  70. local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
  71. local db="";
  72. [ ! -d $MBAKPATH/$NOW ] && $MKDIR -p $MBAKPATH/$NOW
  73. for db in $DBS
  74. do
  75. local tTime=$(date +"${TIME_FORMAT}")
  76. local FILE="${MBAKPATH}/$NOW/${db}.${tTime}.gz"
  77. $MYSQLDUMP -u $MUSER -h $MHOST -p"$MPASS" $db | $GZIP -9 > $FILE
  78. #mysql_file_hook $FILE
  79. done
  80. $LOGGER "$(basename $0) mysql: Ended at $(date)"
  81. }
  82.  
  83. # process each sql database file and backup to another server via ssh
  84. # must have ssh keys
  85. mysql_file_hook(){
  86. local f="$1"
  87. local d=/nas/mysqlbackup/$(hostname -s)/$NOW
  88. $SSH someuser@remote.nixcraft.com mkdir -p $d
  89. $SCP $f someuser@remote.nixcraft.com:$d
  90. }
  91.  
  92. case "$1" in
  93. mysql)
  94. mount_nas
  95. backup_mysql
  96. umount_nas
  97. ;;
  98. mount)
  99. mount_nas;;
  100. umount)
  101. umount_nas;;
  102. *)
  103. echo "Usage: $0 {mysql|mount|umount}"
  104. esac


4000+ howtos and counting! If you enjoyed this article, join 45000+ others and get free email updates!

Click here to subscribe via email.

  • simplex

    I’ve used it as-is and the mysql part for another backup script, works great.
    Thanks!

  • oussama larhmich

    thinks is very nice article :)

Previous Script:

Next Script: