Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    Jan 2006
    Location
    Pottstown, PA
    Posts
    15

    Question Using cron to run mysqldump?

    I want to run cron once a day to make a backup of my databases using 'mysqldump'. My command seems to execute fine via the command line. How ever when I try to run it via CRON it won't.

    Also running this command via 'logrotate' would also be an option. As I only really want or need to keep 14-30 days of running backup. Thoughts?

    Code:
    /usr/bin/mysqldump --opt wordpress-testing | /bin/gzip > /usr/home/favingerphotography/sql_backups/wordpress-testing-`/bin/date+\%m\%d\%Y\%H\%M`.sql.gz
    Using `/bin/date+\%m\%d\%Y\%H\%M` to add a date stamp at the end of the file.

  2. #2
    Moderator wildjokerdesign's Avatar
    Join Date
    Jun 2003
    Location
    Kansas City Mo
    Posts
    5,721

    Default

    I think it is the pipe that is messing things up for you. Give this a try. Put the following in a plain text file and name it something like mysqlbackup.sh
    Code:
    #!/bin/sh
    /usr/bin/mysqldump --opt wordpress-testing | /bin/gzip > /usr/home/favingerphotography/sql_backups/wordpress-testing-`/bin/date +\%m\%d\%Y\%H\%M`.sql.gz
    exit
    Note: I added a space after /bin/date in your code. That was throwing and error even in shell.

    Upload that to the server and chmod it to 755 so it is executable. Now all you have to do is point your cron job to that script. Don't forget to make note of the path where you uploaded it.

    This older thread may be of interest to you, it expands on the process above that I have suggested. http://forums.westhost.com/showthrea...Your-Databases
    Shawn
    Please remember your charity of choice: http://www.redcross.org

    Handy Links: wildjokerdesign.net | Plain Text Editors: EditPlus | Crimson

  3. #3

    Default

    Good idea from wildjoker. It's generally safer to run a cron job by having it run a shell script, rather then have the script in the cron file itself. Let us know if that still is having issues. Also something to keep in mind is that cron can be pretty picky if there's any extra characters or returns in the wrong places. If it's still having issues, feel free to PM me your domain name and I can take a look on the server to see if I can find anything.
    Nathan Collings
    Level 2 Technical Support Rep

    http://www.westhost.com/

  4. #4
    Junior Member
    Join Date
    Jan 2006
    Location
    Pottstown, PA
    Posts
    15

    Default

    Seems to be working fine. Putting the command in a shell script was my next idea. It was just nice to keep thing self contained in cron. But the script seems to be working with out issues.

    My next question is... What's the safe way to add a delete function to the above script to delete any file in my 'sql_backups' folder (noted above) older then say, 14 days? - I don't need months and months of backups clogging the server.

  5. #5

    Default

    Hey Rich,

    You could add something like this to the shell script:

    find /path/to/backups -type f -mtime +14 -exec rm -f {} \;

    That will find any files in that directory that are over 14 days old and delete them. Needless to say, you would need to be very careful when using something like that, because if it was put in there somewhat incorrectly, or if the path was wrong, it could end up deleting a lot of stuff you don't want it to.

    A safer option might be to add it to the logrotate. I'm not too familiar with logrotate, but the conf file is in /etc/logrotate.conf. The man page has some good info on logrotate:

    http://linuxcommand.org/man_pages/logrotate8.html

    You could likely add your backup directory files to the logrotate in the logrotate.conf file, and setup a biweekly rotation. But maybe someone with more logrotate experience could chime in and let us know how well that would work.
    Nathan Collings
    Level 2 Technical Support Rep

    http://www.westhost.com/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •