Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: backup MySQL to a date named file

  1. #1
    Join Date
    Aug 2008
    Location
    Victoria, BC Canada
    Beans
    1,612

    backup MySQL to a date named file

    I am manually backing up my server now with mysqldump and that works but I was wondering about mechanizing the process.

    Nirvana would be to dump to a file named with the date

    This way I have backups going back over time
    SERVER: Azure datacenters, Hyper-V

  2. #2
    Join Date
    Jan 2010
    Location
    Australia
    Beans
    544
    Distro
    Ubuntu 11.04 Natty Narwhal

    Re: backup MySQL to a date named file

    Code:
    ryan@ryan-pc:~$ date
    Fri Sep 24 07:27:03 EST 2010
    ryan@ryan-pc:~$ mysqldump -u root -p dbname > "`date`.sql"
    Enter password: 
    ryan@ryan-pc:~$ ls *.sql
    Fri Sep 24 07:27:13 EST 2010.sql
    Play with the date parameters to format it as you want.

  3. #3
    Join Date
    Aug 2008
    Location
    Victoria, BC Canada
    Beans
    1,612

    Re: backup MySQL to a date named file

    I was hoping to get a simple format such as

    20100923.sql so that I can gzip it up to save on storage
    SERVER: Azure datacenters, Hyper-V

  4. #4
    Join Date
    Oct 2006
    Beans
    4,624
    Distro
    Kubuntu 15.04 Vivid Vervet

    Re: backup MySQL to a date named file

    Quote Originally Posted by Vegan View Post
    I was hoping to get a simple format such as

    20100923.sql so that I can gzip it up to save on storage
    Code:
    date +%Y%m%d
    Blog | Ubuntu User #15350 | Zsh FTW | Ubuntu Security | Nothing to hide?
    AMD Phenom II X6 1075T @ 3GHz, Nvidia GTX 650, 8GB DDR3 RAM, 2 X 1TB, 1 X 3TB HDD
    Please don't request support via PM


  5. #5
    Join Date
    Nov 2008
    Location
    Metro Boston
    Beans
    9,979
    Distro
    Kubuntu 14.04 Trusty Tahr

    Re: backup MySQL to a date named file

    Code:
    #!/bin/sh
    
    # where to put the backups and log
    BACKUPDIR=/backup/mysql
    LOG="$BACKUPDIR/mybackup.log"
    
    # how many days should we keep 
    ROTATE=15
    
    
    # leave empty to backup local server,
    # else specify remote server's hostname
    DBHOST=
    
    # enter custom MySQL port if other than default (3306)
    DBPORT=3306
    
    
    # MySQL database owner and password; use root to back up everything
    DBUSER="root"
    DBPASS="root.password"
    
    
    # list of MySQL databases to back up; a separate file will 
    # be created for each database in the list
    DBLIST="msqldb1 mysqldb2"
    
    
    ######################################################################
    
    # Here be monsters, cap'n!
    
    # if a port is specified on the command line, use it
    [ "$1" != "" ] && DBPORT=$1
    
    echo -n `date -R` >> $LOG
    echo " MySQL backup procedure starting" >> $LOG
    
    TODAY=`date +%y%m%d`
    STALE=`date +%y%m%d --date="$ROTATE days ago"`
    
    for D in `echo $DBLIST`
    do
            NEWFILE="$BACKUPDIR/dump-$D.$TODAY"
            OLDFILE="$BACKUPDIR/dump-$D.$STALE"
    
            echo "Creating backup file $NEWFILE" >> $LOG
            mysqldump --user=$DBUSER --password=$DBPASS $D > $NEWFILE 2>>$LOG 3>>$LOG
    
            echo "Deleting stale backup file $OLDFILE" >> $LOG
            rm -f $OLDFILE 2>>$LOG 3>>$LOG
    
    done
    
    echo -n `date -R` >> $LOG
    echo " MySQL backup procedure completed" >> $LOG
    This code generates backup files of the form "dump-dbname.YYMMDD" and stores them, and a log, in BACKUPDIR. It keeps up to ROTATE days of backups. You're welcome to it!

    I usually put administrative scripts like this one in /usr/local/sbin. If you create a symlink to the file in /etc/cron.daily, it will be run automatically every night.

    Be careful if copying that the "backtick" character (`) is handled correctly. It can be accidentally deleted when moving from one character encoding to another.
    Last edited by SeijiSensei; September 24th, 2010 at 02:43 AM.

  6. #6
    Join Date
    Jan 2007
    Beans
    323

    Re: backup MySQL to a date named file

    I've used this for a few years now.
    http://sourceforge.net/projects/automysqlbackup/

    A how-to:
    http://www.howtoforge.com/creating-m...utomysqlbackup

    Basically you just edit the file and set it to run nightly in your crontab and done.

  7. #7
    Join Date
    Aug 2008
    Location
    Victoria, BC Canada
    Beans
    1,612

    Re: backup MySQL to a date named file

    I downloaded that script from sourceforge and I edited with my specs and I have chmod'ed it to 777 and symlinked it to /etc/cron.daily

    The script lives in my home directory, for want of a place to put it.

    now to see how well it works.

    I am sure this thread will be of benefit to legions of Linux users.
    SERVER: Azure datacenters, Hyper-V

  8. #8
    Join Date
    Jan 2007
    Beans
    323

    Re: backup MySQL to a date named file

    If it runs as root you only need the permissions to be 700. I put the script in /bin, but like SeijiSensei said /usr/local/sbin is fine too. I guess the location doesn't matter as much, but I wouldn't want it world readable with my mysql passwords!

  9. #9
    Join Date
    Aug 2008
    Location
    Victoria, BC Canada
    Beans
    1,612

    Re: backup MySQL to a date named file

    So far the script is not doing anything. So I am still backing up manually.

    I have a symlink to the /etc/cron.daily and the script is marked executable.
    SERVER: Azure datacenters, Hyper-V

  10. #10
    Join Date
    Jan 2007
    Beans
    323

    Re: backup MySQL to a date named file

    Have you tried running the backup script manually as root?

Page 1 of 3 123 LastLast

Bookmarks

Posting Permissions

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