+ Post New Thread
Results 1 to 6 of 6
How do you do....it? Thread, MySQL Backup with phpMyAdmin in Technical; Hi All Really i would like to know how to backup a MySQL database that is hosted on a remote ...
  1. #1
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    212
    Thank Post
    23
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    MySQL Backup with phpMyAdmin

    Hi All

    Really i would like to know how to backup a MySQL database that is hosted on a remote linux box.

    i currently use phpMyAdmin to export an gzipped copy to my local machine and upload when i wanto to restore but what i would like to do is backup to the linux box and restore directly from the linux box

    Mark

  2. #2
    SteveBentley's Avatar
    Join Date
    Jun 2007
    Location
    Yorkshire
    Posts
    1,429
    Thank Post
    119
    Thanked 262 Times in 188 Posts
    Rep Power
    72

    Re: MySQL Backup with phpMyAdmin

    mysqldump is the command you want here:

    mysqldump -u username -p [password] dbname > filename

    password can either be given in the command directly after -p or interactively, dbname can be the name of the database or --all-databases to do the the lot.

    To bring the dumped file back in, either use phpmyadmin or

    mysql -u username -p [password] dbname < filename

  3. #3


    Join Date
    Jan 2006
    Posts
    8,202
    Thank Post
    442
    Thanked 1,032 Times in 812 Posts
    Rep Power
    339

    Re: MySQL Backup with phpMyAdmin

    Code:
    #!/bin/sh
    export PATH=/bin:/usr/bin:/sbin:/usr/sbin
    # List of databases to be backed up separated by space
    dblist="database1 database2" ## db's to backup
    
    # Directory for backups
    backupdir=/path/to/backup/dir
    
    # Number of versions to keep
    numversions=4
    
    # Full path for MySQL hotcopy command
    hotcopycmd=/usr/bin/mysqlhotcopy
    
    # MySQL Username and password
    userpassword=" --user=root --password=xxx" ###<db account
    
    # Create directory if needed
    /bin/mkdir -p ${backupdir}
    if [ ! -d ${backupdir} ]
    then
            /bin/echo "Invalid directory: ${backupdir}"
    exit 1
    fi
    
    # Hotcopy begins here
    echo "Hotcopying MySQL Databases..."
    RC=0
    for database in $dblist
    do
            /bin/echo "Hotcopying $database ..."
            $hotcopycmd $userpassword $database ${backupdir}
            RC=$?
            if [ $RC -gt 0 ]
            then
                    break;
            fi
    
            # Rollover the backup directories
            i=$numversions
            /bin/mv ${backupdir}/${database} ${backupdir}/${database}.0 2> /dev/null
            /bin/rm -fr ${backupdir}/${database}.$i 2> /dev/null
            while [ $i -gt 0 ]
            do
                    /bin/mv ${backupdir}/${database}.`expr $i - 1` ${backupdir}/${database}.$i 2> /dev/null
                     i=`expr $i - 1`
            done
    done
    
    if [ $RC -gt 0 ]
    then
            /bin/echo "MySQL Hotcopy failed!"
            exit $RC
    else
            # Hotcopy is complete. List the backup versions!
            /bin/ls -l ${backupdir}
            /bin/echo "MySQL Hotcopy is complete!"
    fi
    exit 0
    I use the script above to mysqlhotcopy to a location on the local server, this is then rsync'ed to the backup server.
    Script is placed run from /etc/cron.daily

  4. #4

    Geoff's Avatar
    Join Date
    Jun 2005
    Location
    Fylde, Lancs, UK.
    Posts
    11,803
    Thank Post
    110
    Thanked 583 Times in 504 Posts
    Blog Entries
    1
    Rep Power
    224

    Re: MySQL Backup with phpMyAdmin

    Here's my backup script. It backs up over FTP to a terastation:

    Code:
    #!/bin/sh
    # System + MySQL backup script
    # Full backup day - Sat (rest of the day do incremental backup)
    # Copyright (c) 2005-2006 nixCraft <http://www.cyberciti.biz/fb/>
    # This script is licensed under GNU GPL version 2.0 or above
    # Automatically generated by http://bash.cyberciti.biz/backup/wizard-ftp-script.php
    # ---------------------------------------------------------------------
    
    ### System Setup ###
    DIRS="/etc/ /usr/local/nagios/etc/ /var/www/"
    BACKUP=/tmp/backup.$$
    NOW=$(date +"%d-%m-%Y")
    INCFILE="/root/tar-inc-backup.dat"
    DAY=$(date +"%a")
    FULLBACKUP="Sat"
    
    ### MySQL Setup ###
    MUSER="mysql.username"
    MPASS="mysql.password"
    MHOST="localhost"
    MYSQL="$(which mysql)"
    MYSQLDUMP="$(which mysqldump)"
    GZIP="$(which gzip)"
    
    ### FTP server Setup ###
    FTPD="/array1/backup/goliath/incremental"
    FTPU="username"
    FTPP="password"
    FTPS="terastation"
    NCFTP="$(which ncftp3)"
    
    ### Other stuff ###
    EMAILID="whatever@school.lea.sch.uk"
    
    ### Start Backup for file system ###
    [ ! -d $BACKUP ] && mkdir -p $BACKUP || :
    
    ### See if we want to make a full backup ###
    if [ "$DAY" == "$FULLBACKUP" ]; then
      FTPD="/array1/backup/goliath/full"
      FILE="fs-full-$NOW.tar.gz"
      tar -zcvf $BACKUP/$FILE $DIRS
    else
      i=$(date +"%Hh%Mm%Ss")
      FILE="fs-i-$NOW-$i.tar.gz"
      tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS
    fi
    
    ### Start MySQL Backup ###
    # Get all databases name
    DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
    for db in $DBS
    do
     FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
     $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
    done
    
    ### Dump backup using FTP ###
    #Start FTP backup using ncftp
    ncftp3 -u"$FTPU" -p"$FTPP" $FTPS<<EOF
    mkdir $FTPD
    mkdir $FTPD/$NOW
    cd $FTPD/$NOW
    lcd $BACKUP
    mput *
    quit
    EOF
    
    ### Find out if ftp backup failed or not ###
    if [ "$?" == "0" ]; then
     rm -f $BACKUP/*
    else
     T=/tmp/backup.fail
     echo "Date: $(date)">$T
     echo "Hostname: $(hostname)" >>$T
     echo "Backup failed" >>$T
     mail  -s "BACKUP FAILED" "$EMAILID" <$T
     rm -f $T
    fi

  5. #5
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    212
    Thank Post
    23
    Thanked 11 Times in 9 Posts
    Rep Power
    16

    Re: MySQL Backup with phpMyAdmin

    Cheers for the replies guys

  6. #6

    plexer's Avatar
    Join Date
    Dec 2005
    Location
    Norfolk
    Posts
    13,329
    Thank Post
    622
    Thanked 1,578 Times in 1,415 Posts
    Rep Power
    413

    Re: MySQL Backup with phpMyAdmin

    I use same script as Geoff.

    Generate you own here:

    http://bash.cyberciti.biz/backup/wizard-ftp-script.php

    Ben

SHARE:
+ Post New Thread

Similar Threads

  1. MySQL automated backup
    By contink in forum *nix
    Replies: 6
    Last Post: 4th January 2008, 09:35 PM
  2. Veritas Backup Exec Remote Backup
    By apeo in forum Windows
    Replies: 7
    Last Post: 20th September 2007, 10:16 PM
  3. Backup Tapes Replacement & Backup Schedules
    By robknowles in forum Hardware
    Replies: 2
    Last Post: 18th April 2007, 10:55 PM
  4. Replies: 9
    Last Post: 1st August 2006, 10:25 AM
  5. Backup Script for NT backup and Robocopy
    By ChrisH in forum Scripts
    Replies: 12
    Last Post: 20th October 2005, 01:01 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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