[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Google
  Web www.spinics.net

Summary: Options for Backing Up MySQL Database



Much thanks to the many who responded:
Kevin Korb
bishop
Katie Bechtold
Joe Rice
Sean McAvoy
Neil Dombrowski
Tim Evans
Ryan Williams
Bill Bacher
Charles Iliya Krempeaux
Luke Rosenthal
Joe Radinger, and,
Rob Rankin

"mysqldump" used in custom scripts, scheduled via cron was the general
response.  This creates flatfiles which are then backed up using any of
serveral methods from commercial b/u (TSM, etc.) to local tape/CD. 
"mysqlhotcopy" was also mentioned.  Volume/size was mentioned as the
determining factor in which utility to use.  Some folks provided scripts
and/or command line/crontab entry examples(thank you very much) which
follow.

Some specific comments:

(bishop) 
I've read, here or there, of people who are replicating mysql data to a

remote db server.  What I read would suggest that it's either something

mysql does, or that it's an easy wall to scale with only a little help

from perl or shell.

Considering that mysql backs onto a bunch of files as it is, backup 
solutions that will back up those files and restore snapshots seems to

be a valid and usable solution.  Timestamped tarballs from the 
replication target seem to present themselves as the lowest-tech 
solution, if you're looking for something that should be bulletproof by

virtue of its simplicity.

(Katie Bechtold)
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Backup

(Joe Rice)
I manage very, very simple mysql DBs (mostly PHP/Perl web
applications).
All I have setup is a daily dump of each individual DB to separate
files. These are dated, gziped, and then stored for 7 days. Again this
works great for simple DBs, I'm not sure about larger more complex DB
requirements.

(Tim Evans)
Provided the following Solaris script that c/b easily modified for
Linux.

#!/bin/sh
# mysqlback.sh -- do mysql database dumps -- t|<evans
# Sun Jun 22 11:00:35 EDT 2003
##############################
PATH=/usr/local/bin:$PATH
BACKDIR=/data/backups/mysql
DAYSBACK=7
export PATH BACKDIR DAYSBACK
#
########################
# clean up backups older
# than $DAYSBACK
########################
cd $BACKDIR
find . -mtime +$DAYSBACK -exec rm -f {} \;
#########################
# get a list of databases
#########################
cat <<EOF > tmpsql$$
show databases;
EOF
mysql -u root --password=XXXXX < tmpsql$$ | sed '/Database/d' >
dblist$$
############################
# back each one up in turn;
# --opt locks tables during
# backup, so no need to shut
# down mysql; but access will
# be denied during backup
#
# we also pipe through gzip
############################
for DB in `cat dblist$$`
do	mysqldump -u root --password=XXXXX --opt --flush-logs $DB | gzip
\
> $DB.`date '+%m.%d.%y'`.gz
done
###################
# empty the logfile
###################
rm /var/mysql/mysql.log.*
cat /dev/null > /var/mysql/mysql.log

#######
# email
#######
ls -l $BACKDIR | /usr/bin/mailx -s "MySQL Backup Completed" admin
##########
# clean up
##########
rm dblist$$ tmpsql$$

(Ryan Williams)
I have a cron job that executes the following:
mysqldump -u root --password=**** -A --all -l -F -q | gzip  > 
/var/backup/mysql/`date -I`.gz

This fills up my /var/backup/mysql directory with nice little
date-named 
backups (I run it daily).  If your database isn't as trivial as mine, 
you may also want to set up some cron jobs to prune the backups 
directory.  I've never had to restore them, but they are formatted as 
SQL instructions so in principle it should be as simple as running:
mysql -u root < 2003-07-07
for example.

(Bill Bacher)
We've got a crude system, but it seems to work.

We have a cron process that does a find to get every directory in the
MySQL data directory, then passes that list to a script that determines
if the directory is in fact a darabase then does a 'mysqldump' of each
database. We then let the usual tape backup come along and backup the
dump files.

Specifically, the cron command looks like this:

45 0 * * 2-6 find /var/lib/mysql -type d -maxdepth 1 -exec
/usr/local/scripts/mysqlbackup.sh {} \;

We only run it days 2-6 since those are the only days we perform a tape
backup, and it's not a highly dynamic database.

The script it calls looks like this:

cat /usr/local/scripts/mysqlbackup.sh
#!/bin/sh
#
# mysqlbackup.sh
#
# Script to perform a mysqldump on all databases on a server.
# The intent is that the 'dump' will be backed up to tape
# allowing future restore.
#
# Called from 'find' command or similar method to locate all
# directories in the database.
#
# Typical cron entry:
# 45 0 * * 2-6 find /var/lib/mysql -type d -maxdepth 1 -exec
/usr/local/scripts/mysqlbackup.sh {} \;
#
# Does not require shutting the database down during the dump
#
# This script needs a user with read access to all databases.
# This is set up in the 'mysqldump' line.
#
#
DATADIR=/var/lib/mysql                  # Location of databases. If
this changes
                                        # the 'sed' line must be
updated also.
                                        # Also, the 'find' command run
by cron
                                        # will need to be changed.
BACKUPDIR=/usr/local/MySQLbackup        # Location of backup files
MYSQLBIN=/usr/bin                       # Location of mysql binaries
DATECODE=`date +%Y%m%d`                 # Date Code for Unique
Filenames

FULLNAME=$1                     # Full path to dir passed from calling
program

if ls -1 $FULLNAME | grep -s MYD > /dev/null

then                            # This is a MySQL Directory

#       echo $FULLNAME                  # For Debugging
        DBNAME=`echo $FULLNAME | sed 's/\/var\/lib\/mysql\///'` # Strip
path of DB Name
        FILENAME=$DBNAME'-'$DATECODE
        $MYSQLBIN/mysqldump -u backup -p"password" --opt  $DBNAME >
$BACKUPDIR/$FILENAME
        gzip $BACKUPDIR/$FILENAME

fi


I'm sure there are much more elegant solutions, but this one does
work.

(Luke Rosenthal)
Under RedHat Linux....the default location of all
MySQL data is /var/lib/mysql.  I back this up nightly to CD, along with
the
contents of /etc which catches the small config file "my.cnf".  There's
only
a couple of hundred megs in my databases, and tarring/gzipping
compresses it
nicely.  I have found through multiple crashes and full system restores
that
this is all thats req'd to get a MySQL database fully back up and
running,
at least with my setup.

Another option is to nightly dump all data to a flat text file and
compress
that to tape or CD, although doing so this way will lose a lot of
settings
specific to your install (eg. admin passwords and other settings). 
The
advantage of this methough though is the portability between versions -
if
you had a crash and decided to rebuild using a newer distro and switch
to
MySQL v4.x, the flat text file would be easier to import.  Or, if you
had
many large databases and they didn't fit on CD, I guess you could also
use
this method to dump/compress/burn them one at a time.

(Joe Radinger)
mysqldump
and i backup the files under /var/lib/mysql (the actual db-files).
mysqldump is better and human readable.

(Rob Rankin)
Backing up MySQL:  depends.  anything from a normal file level
backup system (Legato, TSM, Arkeia, etc) down to a simple rsync / scp
script will work, if you can shut down mysql for the duration of the
backup.  Since MySQL uses file-level objects this may be more than
enuf
to ensure backups of your databases.

If you can't shut down MySQL you might have some extra trouble.  I'm
not
sure about online database backup tools available for MySQL, but
wouldn't be surprised to find out they did exist.

>>> "Dave Warchol" <Warchol@harthosp.org> 07/07/03 01:03PM >>>
Hello (again):
        I would like to know what folks are doing/using to backup
MySQL
databases.  I will summarize.

Thanks (again):
Dave
submissions: LinuxManagers@linuxmanagers.org 
subscribe/unsubscribe:
http://www.linuxmanagers.org/mailman/listinfo/linuxmanagers
_______________________________________________
LinuxManagers mailing list - http://www.linuxmanagers.org
submissions: LinuxManagers@linuxmanagers.org
subscribe/unsubscribe: http://www.linuxmanagers.org/mailman/listinfo/linuxmanagers

[Home]     [Kernel List]     [Linux SCSI]     [Video 4 Linux]     [Linux Admin]     [Yosemite News]     [Motherboards]

Powered by Linux