Simple SQL backup script

    • Simple SQL backup script

      This is a down and dirty script that will dump (using mysqldumper) a mySQL DB and back it up to a date name.



      Shell-Script

      1. #!/bin/sh
      2. DIR_BACKUP=/path/to/backup/location
      3. DB_NAME=YOUR_DB_NAME
      4. DB_USER=DB_USERNAME
      5. DB_PASS=DB_USER_PASSWORD
      6. DOW=`date "+%a"`
      7. DB_DUMP=$DIR_BACKUP/dbdump.$DB_NAME.$DOW
      8. mydumper \
      9. --database=$DB_NAME \
      10. --host=$DB_HOST \
      11. --user=$DB_USER \
      12. --password=$DB_PASS \
      13. --outputdir=$DB_DUMP \
      14. --rows=500000 \
      15. --compress \
      16. --build-empty-files \
      17. --threads=2 \
      18. --compress-protocol
      Display All
    • This is the current script I use now to perform my DB backups with.
      It uses the standard mysqldump program.
      The last part of it (the NFS part) is specific to my OVH setup that allows me to have an NFS drive attached to the server.

      Shell-Script

      1. #!/bin/sh
      2. #----------------------------------------------------
      3. # a simple mysql database backup script.
      4. # version 2, updated March 26, 2011.
      5. # copyright 2011 alvin alexander, http://devdaily.com
      6. #----------------------------------------------------
      7. # This work is licensed under a Creative Commons
      8. # Attribution-ShareAlike 3.0 Unported License;
      9. # see http://creativecommons.org/licenses/by-sa/3.0/
      10. # for more information.
      11. #----------------------------------------------------
      12. # (1) set up all the mysqldump variables
      13. FILE=site_name.sql.`date +"%A"`
      14. DBSERVER=127.0.0.1
      15. DATABASE=DB_name
      16. USER=DB_user
      17. PASS=DB_user_password
      18. # This does a CD to a directory where I store the backups
      19. cd /on_server_backups/DB
      20. # (2) in case you run this more than once a day, remove the previous version of the file
      21. unalias rm 2> /dev/null
      22. rm ${FILE} 2> /dev/null
      23. rm ${FILE}.gz 2> /dev/null
      24. # (3) do the mysql database backup (dump)
      25. # use this command for a database server on a separate host:
      26. #mysqldump --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}
      27. # use this command for a database server on localhost. add other options if need be.
      28. mysqldump --opt --single-transaction --user=${USER} --password=${PASS} ${DATABASE} > ${FILE}
      29. # (4) gzip the mysql database dump file
      30. gzip $FILE
      31. # (5) show the user the result
      32. echo "${FILE}.gz was created:"
      33. ls -l ${FILE}.gz
      34. # This is where I copy to an NFS mounted drive
      35. cp ./${FILE}.gz /backups/store/DB
      36. echo "${FILE}.gz was copied to NFS mount"
      Display All