Just a quick question if anyone can help me.
Can I backup my MySQL data by just copying the whole of the data folder to
another drive? Is this sufficient? Can I restore them by just copying them
back to the original place?
Better still, does anyone know of any software that would aide the backing
up of MySQL data? I really need to do it on a day by day basis, or
automatically if possible.
Thanks
Cheryl
On Thu, 21 Apr 2005 15:12:04 +0100, "Cheryl Holmes"
>Can I backup my MySQL data by just copying the whole of the data folder to
>another drive? Is this sufficient? Can I restore them by just copying them
>back to the original place?
I wouldn't recommend it :o)
>Better still, does anyone know of any software that would aide the backing
>up of MySQL data? I really need to do it on a day by day basis, or
>automatically if possible.
If you need to do this automatically and you have access to the server
and can execute programs on it then investigate the "mysqldump"
routine. You could set up a crontab scheduled task to run this and
send the output to a (zipped?) text file - and then to a backup tape
or FTP it to another machine if you wanted.
Best wishes
Chris R.
Cheryl Holmes wrote:
>
> Can I backup my MySQL data by just copying the whole of the data
> folder to another drive? Is this sufficient? Can I restore them
> by just copying them back to the original place?
Yes to all questions. With a BIG qualifying statement: MySQL has
to be down during backup and restore processes. If MySQL continues
to operate during backup or restore, data corruption is possible.
In fact, this is almost exactly the approach used by mysqlhotcopy
program. The twist is that mysqlhotcopy obtains read lock on all
tables in the database being backed up and releases the lock after
the files have been copied. This allows mysqlhotcopy to back up
databases without shutting down the MySQL server.
> does anyone know of any software that would aide the backing
> up of MySQL data? I really need to do it on a day by day basis,
> or automatically if possible.
There are a couple programs that come with MySQL that are written
to assist in backups. Check MySQL documentation for information
on mysqldump and mysqlhotcopy. You may also consider replication
(setting up a slave server, which will continuously update itself
with data from your current server, which will act as a master).
I highly recommend "High Performance MySQL" by Jeremy Zawodny and
Derek Balling; Chapter 9 deals in quite some detail with backup
and restore issues.
Cheers,
NC
On Thu, 21 Apr 2005 15:12:04 +0100, Cheryl Holmes wrote:
> Just a quick question if anyone can help me.
>
> Can I backup my MySQL data by just copying the whole of the data folder to
> another drive? Is this sufficient? Can I restore them by just copying them
> back to the original place?
>
> Better still, does anyone know of any software that would aide the backing
> up of MySQL data? I really need to do it on a day by day basis, or
> automatically if possible.
>
> Thanks
> Cheryl
I use the mysqldump routine for backups. I created a user that has only
select access to all databases, in my case called backerupper, and use the
following command:
mysqldump -u backerupper --opt --all-databases > $BACKUPFILE
I set up backerupper so that a password was not needed, hence the absence
of a -p in this command. I use this line within a shell script, where the
target file $BACKUPFILE is set to something including the current date and
in some cases also the time.
This is my entire shell script:
#!/bin/sh
SAVEDIR=/home/sti/backups/
NOW=`date`
DATEID=` echo $NOW | cut -d " " -f 2,3,6 | tr ' ' '_' `
BACKUPFILE=${SAVEDIR}'db_backup_'${DATEID}'.sql'
mysqldump -u backerupper --opt --all-databases > $BACKUPFILE
In reality, my SAVEDIR is located on another machine, our company server,
which is backed up daily to tape by another backup routine. My backup
script will just then make a dump of my databases, put it into a file
called, for example, db_backup_Apr_20_2005.sql located on a drive which
gets backed nightly to tape.
I just call my shell script once each night with a cron job. And this
works out fine.
Hi
Try Navicat from www.navicat.com
Steinar
Cheryl Holmes wrote:
> Just a quick question if anyone can help me.
>
> Can I backup my MySQL data by just copying the whole of the data folder to
> another drive? Is this sufficient? Can I restore them by just copying them
> back to the original place?
>
> Better still, does anyone know of any software that would aide the backing
> up of MySQL data? I really need to do it on a day by day basis, or
> automatically if possible.
>
> Thanks
> Cheryl
>
>
"NC" wrote:
>
>
>Yes to all questions. With a BIG qualifying statement: MySQL has
>to be down during backup and restore processes. If MySQL continues
>to operate during backup or restore, data corruption is possible.
>
>
Good point, but you can also issue a database lock before backup and
issue an unlock after, and safely backup things up in between (the
data dir). That would obviously stop live users /processes from
updating your db, but a bit less drastic than brining the server down.
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/PHP-SQL-Backing-data-ftopict216439.h tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=761741
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com