Results 1 to 6 of 6
  1. #1

    Mysql database backup for all databases in individual files

    Ok, here is what I am trying to accomplish:

    I want to backup each database on my server to a different file. I also want to be able to add databases without having to modify the script.

    I have seen scripts that do a dump of all databases into one file, but I am looking for some way to do it individually so that if I need to restore a small 1M database I don't have to sift through a 1-2G db dump to find that part.


    Thanks in advance.
    *AlphaOmegaHosting.Com* - Hosting since 1998
    Managed Dedicated Servers and VPS
    Hosted Exchange 2010 Email Service

  2. #2
    Join Date
    May 2001
    Location
    Dayton, Ohio
    Posts
    4,962
    Code:
    #!/bin/sh
    
    ##########################
    # mysqldump backup script
    # Written by: Dan
    # www.serve-you.net 
    ##########################
    
    
    BACKUP_DIR=/root/mysql
    BACKUP_MODE=600
    MAX_BACKUPS=3
    
    MYSQLDUMP=/usr/local/psa/mysql/bin/mysqldump
    MYSQL_USER=admin
    MYSQL_PASSWORD=adminpass
    DATE=`date +%d%b%Y-%H%M`
    ARCHIVE_FILE="mysqlbackup-$DATE.sql.tar.gz"
    ARCHIVE_DIR=/usr/local/archive/mysql
    SQL_BACKUP_FILE="$BACKUP_DIR/mysqlbackup-$DATE.sql"
    
    $MYSQLDUMP -lv -u$MYSQL_USER -p$MYSQL_PASSWORD -A > $SQL_BACKUP_FILE
    
    tar cfpsz $ARCHIVE_DIR/$ARCHIVE_FILE $SQL_BACKUP_FILE
    
    chmod $BACKUP_MODE $ARCHIVE_DIR/$ARCHIVE_FILE
    chown root:root $ARCHIVE_DIR/$ARCHIVE_FILE
    
    # remove old backupz
    
    cd $BACKUP_DIR
    rm mysqlbackup*
    
    # remove old archivez
    
    cd $ARCHIVE_DIR
    
    DONE=0
    until [ $DONE -eq 1 ]
    do
            FILES=`ls -1rc`
            TOTAL_FILES=`echo "$FILES" | wc -l`
    
            if [ $TOTAL_FILES -gt $MAX_BACKUPS ]
            then
                    OLD_ARCHIVE=`echo "$FILES" | head -1`
                    rm $OLD_ARCHIVE
            else
                    DONE=1
            fi
    done
    scp -o "Protocol=1" -pC $ARCHIVE_DIR/$ARCHIVE_FILE [email protected]:/path/to/mysql/backup/dir
    
    #end
    This what you looking for??


    [edit]Just read closer.. Hmm I don't think this does indivdual files.. hmmm[/edit]
    -Mat Sumpter
    Director, Product Engagement
    Penton Media

  3. #3
    Thanks for the post. I have something like that...but it puts everything in one file.

    Anyone else?
    *AlphaOmegaHosting.Com* - Hosting since 1998
    Managed Dedicated Servers and VPS
    Hosted Exchange 2010 Email Service

  4. #4
    Join Date
    Feb 2002
    Posts
    1,926
    You could add each mysqldump to cron, but it would mean having to do each database manually, but they would be saved to individual files that way...

  5. #5
    Yes, I have considered that, I even have a script that I can list them individually in, but as users are able to add/delete their own databases, I would like for this to be automatic
    *AlphaOmegaHosting.Com* - Hosting since 1998
    Managed Dedicated Servers and VPS
    Hosted Exchange 2010 Email Service

  6. #6
    Something like this might work assuming you run it as root and have root's .my.cnf file configured appropriately.

    Code:
    #!/usr/bin/perl
    
    my $dir = "/var/lib/mysql";
    my $dumpdir = "/path/to/backup/sql/directory";
    
    opendir(D, $dir) || die "Can't open $dir: $!";
    my @files = readdir(D);
    close D;
    
    foreach my $file (@files) {
      next if ($file =~ /\.+/);
      print "${file}...\n";
      system("mysqldump $file > ${dumpdir}/$file.sql");
    }
    That will dump all of the databases into database_name.sql in the backup directory you specify. Note that running this more than once will overwrite previous backups in your backup directory. No guarantees, no warranties, etc.
    Matt Lightner - http://www.mattlightner.com/
    - First initial to the last name at the mail service provided by the world's largest search engine
    - Founder and CEO (Former) Site5.com, sold in 2008
    - Really honestly wants to be a good WHT citizen but can never remember all the correct etiquette. Mods, sorry in advance

Posting Permissions

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