Can I backup mysql while mysql is running?

Question

I just installed Jungle Disk server edition on my linux server and have it set to backup the full /var directory once a night. Is there any issue with backing up this directory while my server and mysql are running? I've read that you should shut down mysql before backing up mysql files.. but I haven't seen any such warning in the Jungle Disk documentation.

Answer 1

If you want a good backup of the Mysql Database you should exclude the Mysql Database directory from Jungledisk and then use mysqldump periodically to create a file which you will be able to backup.

If you don't do this, the Jungledisk backup of your Mysql data will have a good likelyhood of being corrupt, depending on how active your database is.

Answer 2

In my opignon your best bet is to use a script that runs mysqldump and dumps the database into a folder that jungledisk then backups, this will give you a good solid backup of the database. Mysqldump is easy to use, its just a few flags and voila good hot copy backup.

Answer 3

The other answers have the right idea, you can't, shouldn't, and won't (right?) back up live database files. You MUST use some kind of export utility, but instead of the built in mysqldump utility, I highly suggest mysqlhotcopy to ensure data consistency. Backing up data as it changes will leave you in a broken state when restored.

Answer 4

Save this and run it on a cron job - Then simply have the backup script you have backup the db dump directory

Great script from the folks over @ NixCraft btw:

    #!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------

MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can not 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="/backup"

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

# Get hostname
HOST="$(hostname)"

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

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

# DO NOT BACKUP these databases
IGGY="test"

[ ! -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

Answer 5

Regarding the provided script and doing mysqldumps -- since this discussion is focused on jungledisk, you do NOT want to gzip the dump.

Jungledisk uses data deduplication and differential backups to help improve the efficiency of its storage. If you do raw dumps; then it all works. If you gzip first, then it has to store the entire (different) gzip; and no optimizations in backup speed or required backup storage space are gained.

Answer 6

This is a really old question but there's no an accepted answer for it yet, and only one answer references a tool other than mysqldump to perform your backup. As VxJasonxV mentions, mysqlhotcopy can be used to take an online copy of your databases without shutting down. However, mysqlhotcopy only works with MyISAM tables.

If you run a lot of InnoDB tables, check out Percona's xtrabackup tool. It will make an online backup of your MySQL database without shutting down MySQL. Percona has built an all-in-one script that will backup your InnoDB tables and your MyISAM tables, without taking your database offline. The script is called innobackupex and it incororates the the above-mentioned xtrabackup tool to perform your online backups.

Check it out, it definitely makes backing up MySQL databases very easy.