Mysql – Best practices for backing up databases

backupdatabaseMySQLsvn

I asked this on stackoverflow, but someone pointed out it would be better to ask here.

Let's assume Subversion and MySQL on a RAID NAS. What are the best practices for backing up data?

I was thinking putting mysqldumps under subversionn control, and then maybe backing up the svn repository periodically by 7zipping the whole thing.

Unless you're storing svn backups on a different physical hard drive, it doesn't seem like making backups of the repository would help. Is this true? If not, why?

Finally, how often should backups be made, and how many should be saved?

Best Answer

First, don't version control your database backups.
A backup is a backup - a point in time. Using version control sounds like a nice idea but realize that it means you will need to restore the whole SVN repository (ZOMG Freaking HUGE) if you have a catastrophic failure and need to get your database back. That may be additional downtime time you can't afford.

Second, make sure your backups are getting off site somehow. A backup on the local machine is great if you need to restore data because you messed up and dropped a table. It does you absolutely no good if your server's disks die.
Options include an external hard drive or shipping the backups to a remote machine using rsync. There are even storage service providers like rsync.net that specialize in that.

Third, regarding frequency of backups: Only you know how often you need to do this.
My current company has a slave database with near-real-time replication of our production data. That slave is backed up every night to a local machine, which then syncs to an off-site storage facility.
In the event of a production hardware failure we activate the slave. Data loss should be minimal, as should downtime. In the event of an accidental table deletion we can restore from the local backup (losing up to 1 day of data). In the event of a catastrophic incident we can restore from the off-site backup (which takes a while, but again will only lose up to 1 day of data).
Whether that kind of backup scheme works for you depends on your data: If it changes frequently you may need to investigate a backup strategy that gets you point-in-time recovery (log-shipping solutions can often do this). If it's mostly static you may only need to back up once a month. The key is making sure that you capture changes to your data within a reasonable time from when they are made, ensuring you don't lose those changes in the event of a major incident.

Related Solutions

Postgresql – What’s the best way to automate backing-up of PostgreSQL databases

the same as you do for any other repetitive task that can be automated - you write a script to do the backup, and then set up a cron job to run it.

a script like the following, for instance:

(Note: it has to be run as the postgres user, or any other user with the same privs)

#! /bin/bash

# backup-postgresql.sh
# by Craig Sanders &lt;cas@taz.net.au&gt;
# This script is public domain.  feel free to use or modify
# as you like.

DUMPALL='/usr/bin/pg_dumpall'
PGDUMP='/usr/bin/pg_dump'
PSQL='/usr/bin/psql'

# directory to save backups in, must be rwx by postgres user
BASE_DIR='/var/backups/postgres'
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p "$DIR"
cd "$DIR"

# get list of databases in system , exclude the tempate dbs
DBS=( $($PSQL --list --tuples-only |
          awk '!/template[01]/ && $1 != "|" {print $1}') )

# first dump entire postgres database, including pg_shadow etc.
$DUMPALL --column-inserts | gzip -9 > "$DIR/db.out.gz"

# next dump globals (roles and tablespaces) only
$DUMPALL --globals-only | gzip -9 > "$DIR/globals.gz"

# now loop through each individual database and backup the
# schema and data separately
for database in "${DBS[@]}" ; do
    SCHEMA="$DIR/$database.schema.gz"
    DATA="$DIR/$database.data.gz"
    INSERTS="$DIR/$database.inserts.gz"

    # export data from postgres databases to plain text:

    # dump schema
    $PGDUMP --create --clean --schema-only "$database" |
        gzip -9 > "$SCHEMA"

    # dump data
    $PGDUMP --disable-triggers --data-only "$database" |
        gzip -9 > "$DATA"

    # dump data as column inserts for a last resort backup
    $PGDUMP --disable-triggers --data-only --column-inserts \
        "$database" | gzip -9 > "$INSERTS"

done

# delete backup files older than 30 days
echo deleting old backup files:
find "$BASE_DIR/" -mindepth 1 -type d -mtime +30 -print0 |
    xargs -0r rm -rfv

EDIT :
pg_dumpall -D switch (line 27) is deprecated, now replaced with --column-inserts
https://wiki.postgresql.org/wiki/Deprecated_Features

Windows – How to switch an existing set of Subversion repositories to use ActiveDirectory? (svnserve/windows)

As already mentioned, VisualSVN Server is the tool you want. Its an all-in-one package for Windows that just happens to use Apache inside it- you'd never know unless you went poking around in the dlls it installs though, to everyone its a Windows service that has a mmc snap-in to administer it. You can change the port it runs on if you already have port 80 taken.

I understand sasl support through svnserve is being worked on at the moment.

Best Answer

Related Solutions

Postgresql – What’s the best way to automate backing-up of PostgreSQL databases

Windows – How to switch an existing set of Subversion repositories to use ActiveDirectory? (svnserve/windows)

Related Topic