Ubuntu – Tips to backup ubuntu server

backupMySQLphp5Ubuntuweb-applications

I have a Ubuntu server hosting a PHP5 / MySQL webapp. I also have another server (offsite) with a directory just for backups of my webapp server. Currently, I have a simple cron job which performs the following:

Copies /var/www to local backup directory. Command: cp -ua /var/www /backup/www
Performs mysqldump of information_schema and my app's database and copies those files to the local backup directory.
Mounts the backup server as an sshfs volume.
Copies all files from the local backup directory to the sshfs volume.

Questions:

Is this an efficient solution or would a dedicated backup program work better/faster?
Am I covering everything I need? For example: are there configuration files that I should backup too (PHP, Apache2, MySQL)?
If there is a dedicated backup program that would serve my needs better, which would you recommend?

Thanks for any advice

Best Answer

I'd recommend something that can do incremental backups, because that way you can go back to files that were changed several days ago. We use dirvish for this (it's available from the ubuntu repos) - basically it's a wrapper for rsync written in perl that makes it easier to setup a system to run regular incremental backups, and then expire them based on given rules.

On our system we keep backups for 7 days, but keep the first backup of the week for 1 month, and the first backup of the month for one year, so usually you can find files that were modified and deleted some time ago. For things that are small, but important, we have much longer expiry times.

As for what you should backup, imo if you have the space back up everything unless you're sure you don't need to back up. With incremental rsync backups you're only copying over the files that have changed since the last backup - I'd rather have an extra 3gb of static data belonging to the operating system on my backup drive than find it was missing one important file when I needed to recover it.

At a minimum I'd backup the entire contents of /etc/, and most of /var depending on what stuff was running on the server.

If your mysql database is fairly small then your current approach is probably acceptable, but since you need to lock the database to generate the dump this isn't practical on large databases. Because of this lots of people run a slave mysql server, which is just a read only copy of the main one on the backup server, and use this to generate backups.

The mysql documentation on how to do this is fairly good, I worked through it the first time in an evening, without much previous mysql experience.

We have our slave mysql server tied into dirvish, so each day it generates a dump of each individual database, as well as an entire copy of /var/lib/mysql (it's stops the mysql server before doing this)

Related Solutions

Ubuntu – backup thesql directory

doing a backup of /var/lib/mysql is not a good idea, because the mysql daemon needs to be stopped before.

use tools like mysqldump to dump the databases into a file in e.g. /var/backups/mysql/ or whereever and backup these files

the dump of the databases can be started by the user creating the backup, so there will be no problems. but think of removing rights on the folder for group and world if you do not need them

Linux – Help on writing Linux script to backup and ftp files to another server

I would not recommend using FTP for any files you care about (particularly database backups if that database contains customer information), but that is ultimately up to you.

To start with, script the creation of your backups on the Linux server with something like the following:

#!/bin/bash
# 
# Call script with:
# /cron-mysql_bkup.sh mysql_hostname mysql_username mysql_password /backup/directory
# 
if [ -n "$1" ]; then
 MYSQL_HOST="$1"
else
 echo "MYSQL_HOST not specified."
 exit 1
fi
if [ -n "$2" ]; then
 MYSQL_USER="$2"
else
 echo "MYSQL_USER not specified."
 exit 1
fi
if [ -n "$3" ]; then
 MYSQL_PASS="$3"
else
 echo "MYSQL_PASS not specified."
 exit 1
fi
if [ -n "$4" ]; then
 DIR_BKUP="$4"
 mkdir -p ${DIR_BKUP}
else
 echo "DIR_BKUP not specified."
 exit 1
fi
# 
# Generate filename
# 
BACKUP_FILENAME=`date "+%Y-%m-%d"`".sql"
# 
# Dump all databases
# 
mysqldump -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} --all-databases > ${DIR_BKUP}/${MYSQL_HOST}/${BACKUP_FILENAME}
# 
# Write status of the operation to the mysql.log file in the backup directory
# 
if [ $? -ne 0 ]; then
 echo `date`" - ERROR creating backups of host "${MYSQL_HOST} >> ${DIR_BKUP}/mysql.log
else
 echo `date`" - MySQL Backup complete for "${MYSQL_HOST} >> ${DIR_BKUP}/mysql.log
 BACKUP_TAR_NAME=${BACKUP_FILENAME}".tar.gz"
 tar czf ${DIR_BKUP}/${MYSQL_HOST}/${BACKUP_TAR_NAME} ${DIR_BKUP}/${MYSQL_HOST}/${BACKUP_FILENAME} && rm ${DIR_BKUP}/${MYSQL_HOST}/${BACKUP_FILENAME}
fi

This script will:

Check to see whether all the required parameters were set
Dump all MySQL databases to a file named YYYY-mm-dd.sql under the backup directory specified (this directory will be created if it does not exist)
GZIP the file and, if the GZIP operation succeeds, delete the original SQL file

This script should be fairly straightforward for you to customize if you wish to - see the Advanced Bash Scripting Guide to learn more about doing so.

... once your databases are backed up and can be found in a specific location, why not automate backup retrieval with WinSCP instead of transferring your files over FTP?

Best Answer

Related Solutions

Ubuntu – backup thesql directory

Linux – Help on writing Linux script to backup and ftp files to another server

Related Topic