What are the recommended solutions for backing up your mySQL database?

Suppose I'm only using half my Linode disk space. Then, the obvious solution is:

- stop database
- make copy
- restart database
- download backup

Is there anyway to make a backup of my database without taking down my site?

Sure. I just do a dump of my data while the database is running. You can do it from the command line by just running mysqldump and redirecting it to a file:

mysqldump -u USERNAME -p DATABASE_NAME > backup.sql

That's what I do and it works fine. I'm able to take the result and import it into mysql to restore the database.

Is that what you mean?

.

Since I have various databases that I need backing up, and new ones are always being created I made a small script to backup the databases. This script keeps 4 day's worth of backups.

#!/bin/bash

MyUSER="backupuser"
MyPASS="password"
MyHOST="localhost"

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/backups/db"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# Move backups up one level
rm -rf $DEST/04
mv $DEST/03 $DEST/04
mv $DEST/02 $DEST/03
mv $DEST/01 $DEST/02

# Main directory where backup will be stored
MBD="$DEST/01"

# Get hostname
HOST="$(hostname)"

# File to store current backup file
FILE=""
# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY=""

[ ! -d $MBD ] && mkdir -p $MBD || :
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
        for i in $IGGY
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
        FILE="$MBD/$db.$HOST.$NOW.gz"
        # do all inone job in pipe,
        # connect to mysql using mysqldump for select mysql database
        # and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

mysqldump is the command I was looking for. I'm a newbie mySQL admin also.

I read elsewhere that you had to stop the database to make backups.

That's good to know.

Suppose I'm nearly using my full disk allocation. Is there any way to temporarily get an extra couple of GB, so I can do the dump and then download it and then free the disk space?

(This is just idle fantasies. I'd be lucky to have that many users for it to matter.)

A simple mysqldump will probably be sufficient for most purposes. But if you really need to ensure integrity, it won't work without some more thought. Because Table A may refer to data in Table B. mysqldump backs up Table A, then something changes both tables, then mysqldump backs up Table B. Now you're out of sync, and have a bad backup.

If you're using InnoDB, you can execute mysqldump in its own transaction, which maintains integrity.

You can also do things like lock all the tables, copy the raw database files elsewhere, and unlock the tables. Either keep those raw files as your backup, or restore them in another MySQL instance and run mysqldump on that.

Or use replication: keep a live slave mirroring your database, and do your backups from that machine, which you can take down as you like.

There are some other tricks, too, like using LVM snapshots to grab the database at one point in time. There's a whole world out there!

But as I said, for most purposes, just running mysqldump on your running server is probably fine.

You can download the backup directly from your home PC:

ssh -t username@server "mysqldump -u USERNAME -p DATABASE_NAME" >> databse-backup 

AFAIK, disk upgrades are handled just like any other upgrade. That means you can go to the extra's tab, add a few more GB, and then when you are done remove the extra storage for a prorated refund. It should come out to only costing a few cents if you just use it for a day or two.