Backup all databases nightly w/ mysqldump

Discussion in 'Command Line' started by Rob, Nov 23, 2011.

  1. Rob

    Rob Administrator Staff Member

    Messages:
    575
    Likes Received:
    217
    Trophy Points:
    43
    So, I want to take a shell script and be able to put it on any machine - and have it backup the databases on that machine using mysqldump.. and put them each separately into a backup directory.. here's what I came up with.


    Can you make it better?

    Code:
    #!/bin/bash
    
    DB_BACKUP="/backups/mysql_backup/`date +%Y-%m-%d`"
    DB_USER="root"
    DB_PASSWD="secretttt"
    HN=`hostname | awk -F. '{print $1}'`
    
    # Create the backup directory
    mkdir -p $DB_BACKUP
    
    # Remove backups older than 10 days
    find /backups/mysql_backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \;
    
    # Option 1: Backup each database on the system using a root username and password
    for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -vi information_schema);
    do mysqldump --user=$DB_USER --password=$DB_PASSWD --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz";
    done
    
    # Option 2: If you aren't using a root password then comment out option 1 and use this
    # for db in $(mysql -e 'show databases' -s --skip-column-names|grep -vi information_schema);
    # do mysqldump --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz";
    # done
    
    If you use this, throw this text into something like /usr/local/bin/mysql_backup.sh and since it has mysql's root password in it, make sure that you chmod 700 to it so no one else can read it. Then just call it from cron like:
    Code:
    30 3 * * * /usr/local/bin/mysql_backup.sh
    
    BTW, a simpler way to grab all of them is to use the --all-databases flag in the mysqldump command.. but it doesn't make nice separate files for you..
  2. jwhite530

    jwhite530 New Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Take a look at the MySQL portion of my backup script currently starting on line 666. It lists every database on a remote server, creates a dump marked with the day and time of the dump via an SSH stream (rather than a local dump and copy), then rotates database dumps daily/weekly/monthly/yearly.

    Link incoming.
  3. jwhite530

    jwhite530 New Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
  4. Rob

    Rob Administrator Staff Member

    Messages:
    575
    Likes Received:
    217
    Trophy Points:
    43
    We actually use a modified version of this.. it will put one copy of each database in the backup directory.. w/o the date in the filename.. and our backup server swoops in and grabs the changes. We keep daily backups for up to a month.

    It looks like you've worked on your script for quite a while - I scanned through it and can't wait to read into it a little closer later today..
  5. lucasbytegenius

    lucasbytegenius New Member

    Messages:
    207
    Likes Received:
    8
    Trophy Points:
    0
    Looks like a great script :) Do you think you could make a version that could be run on shared hosting with FTP features?
  6. ehansen

    ehansen New Member Staff Writer

    Messages:
    115
    Likes Received:
    11
    Trophy Points:
    0
    This isn't FTP, but you can always use SCP instead. :) Essentially the same as cp for remote copying.

    You can generate the backup of the database, and then do something like this:

    Code:
    scp mysql_backup.sql user@host:/remote/directory/user/can/write/to/
    This will copy the backup file to /remote/directory/user/can/write/to/mysql_backup.sql
    Rob likes this.
  7. lucasbytegenius

    lucasbytegenius New Member

    Messages:
    207
    Likes Received:
    8
    Trophy Points:
    0
    Thanks! I'll use that next time I need to make a backup script. Just found out my host automatically backups my site nightly, so I don't think I'll need it now.
  8. Rob

    Rob Administrator Staff Member

    Messages:
    575
    Likes Received:
    217
    Trophy Points:
    43
    I'd never trust my host to have the only copy of my data.. always make your own backups.
  9. ehansen

    ehansen New Member Staff Writer

    Messages:
    115
    Likes Received:
    11
    Trophy Points:
    0
    I was going to say this same exact thing. Take it from someone who has worked for hosting companies in the past. More often than not, they either don't monitor their backup solutions to make sure they actually run properly, or they are just lying to you so you will buy their service. Luckily (?), I've been in the latter of the scenario all the time when it came to restoring backups so I could at least use the next previous backup.

Share This Page