MySQL Backup with phpMyAdmin

[mark80]

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

[SteveBentley]

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

[CyberNerd]

#!/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

[Geoff]

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

#!/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

[mark80]

Cheers for the replies guys

[plexer]

I use same script as Geoff.

Generate you own here:

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

Ben