Re: database backups

[Peter Carter]

Here is a snipit from my daily crons......

/pumbaa is a nfs that gets backed up info
/pumbaa/router/mysql is the dir I dump the mysql databases in

This script compresses the data and rotates daily backups for 1 week. In
this case, root can get at everything.

-------------------
# mysql mysql archive
for db in "mysql www db1 db2" do
if [ -f /pumbaa/router/mysql/$db.gz.6 ] ; then
	 /bin/mv  /pumbaa/router/mysql/$db.gz.6 /pumbaa/router/mysql/$db.gz.7
	 fi
if [ -f /pumbaa/router/mysql/$db.gz.5 ] ; then 
	/bin/mv  /pumbaa/router/mysql/$db.gz.5 /pumbaa/router/mysql/$db.gz.6
	 fi
if [ -f /pumbaa/router/mysql/$db.gz.4 ] ; then 
	/bin/mv  /pumbaa/router/mysql/$db.gz.4 /pumbaa/router/mysql/$db.gz.5 
	 fi
if [ -f /pumbaa/router/mysql/$db.gz.3 ] ; then 
	/bin/mv  /pumbaa/router/mysql/$db.gz.3 /pumbaa/router/mysql/$db.gz.4
	 fi
if [ -f /pumbaa/router/mysql/$db.gz.2 ] ; then 
	/bin/mv  /pumbaa/router/mysql/$db.gz.2 /pumbaa/router/mysql/$db.gz.3
	 fi
if [ -f /pumbaa/router/mysql/$db.gz.1 ] ; then 
	/bin/mv  /pumbaa/router/mysql/$db.gz.1 /pumbaa/router/mysql/$db.gz.2
	 fi
if [ -f /pumbaa/router/mysql/$db.gz ] ; then 
	/bin/mv  /pumbaa/router/mysql/$db.gz /pumbaa/router/mysql/$db.gz.1
	 fi
/usr/local/bin/mysqldump --user=root --password=whatever $db | gzip >
/pumbaa/router/mysql/$db.gz
done
-----------------------------


At 09:34 AM 3/23/99 -0800, you wrote:
>Hello!
>
>Has anyone out there figured out a good way to do database backups without
>shutting down the database?  We are starting a 24 X 7 (international)
>service and are trying to figure out the best way to backups without
>stopping (killing) the database for an hour.    As I understand it, our
>options are:
>
>1. Scheduled replication using the log files.  This is the best solution for
>us (no stopping the service at all) but the problems is that it is very hard
>to determine if something goes wrong in the middle of the replication
>process so we still need to do a full backup weekly (at least).
>2. Setting a flag in the database to signal that we are backing up the
>database and coding all our write operations to check that flag before
>writing to the db so we don't write during backup (file copying).  Besides
>all that extra bug prone code, we still have to halt the service while we
>copy GBs of stuff across disks.
>3. Using mysqldump to pump the database to a backup file.  The problem again
>is that the -lock-tables option will cause our UIs to hang for an hour or so
>until the tables are unlocked.
>
>Are these just the price I must pay?
>
>DJ
>
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread843@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.
>
>
--
Peter Carter  
Media Wave
web / solutions
55 Metcalfe Street
Suite 280
Ottawa, Ontario
Canada. K1P 6L5

Phone: [phone]
Pager: 613.751.4660
Fax: [phone]
Toll Free: 1.888.296.6682
http://www.mediawav.com