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" ###
# 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 
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by [url]http://bash.cyberciti.biz/backup/wizard-ftp-script.php[/url]
# ---------------------------------------------------------------------

### 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="[email protected]"

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