Dave Greco wrote:
> Looking over the documentation for MySQL, there doesn't seem to be a
> consensus on the best way to perform backups of MySQL databases. So far, I
> have come up with the following ideas:
>
> 1. Just copy the files in the data directory to wherever I want them backed
> up. This doesn't work too well because the files are in use as I copy them.
>
> 2. Shutdown the database server, copy the actual files in the data
> directory. I can't use this method because I can't have the database down
> for the amount of time it would take to copy this data. At least, I can't
> have the database server down like this on a daily basis.
>
> 3. Perform a database dump (using mysqldump) of the data to a file. The
> problem with this is that the data is very big in that format, and the
> backup takes a long time.
>
> 4. Perform a hot copy of the databases (using mysqlhotcopy). This also
> seems to take a while, but at least the database server does not have to
> shutdown. The trouble with this is that it locks the table from writes when
> it does the copy. Some of my tables take a while to copy (they are hundreds
> of MBs in size), and are written to quite often, so this option (while the
> best so far) also has its shortcomings.
>
> 5. Use MySQL replication to create another database server with the
> identical data. Then shutdown the database server on that machine and run a
> backup (like in #2). This way will not require shutting down the real
> backup server at all, so therefore the backup can take as long as I want.
> The downside to this approach is that the data being backed up is not
> necessarily the most up-to-date. If the backup takes 2 hours, the databases
> near the end of the backup can have some significant changes by then.
> However, this disadvantage is diminished because when the backup is done, I
> restart the slave server and the data replicates back down again. So, we
> have online database backups on the actual slave server and then we do
> daily (probably could be every couple of hours) tape backups of the data on
> the slave.
>
> Does anyone see a problem with idea #5? Does anyone have any better ideas
> for performing backups?
>
> ____________________________________
> Dave Greco
> [EMAIL PROTECTED]
> Network Administrator
> Gleim Publications, Inc.
> (800) 87-GLEIM x312
> http://www.gleim.com
> ____________________________________
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
David,
You to distinguished if you want hotbackups or cold backups. What the difference?
Hotbackups means that the database server is never shutdown to perform the
backups.
Cold Backups means the database server is shutdown to perform backups.
The reason for the difference is to protect the data integrity. So just doing a
copy of the data can be
a no no unless you stop the database server. If you going to do hot backup,
usually export the database
to a file. Place that export to a filesystem other then current database
filesystem. Then you can completely
backup that filesystem without ever having to stop the database server at all.
Then when it comes time to restore you can restore database much easier this
way. This also eliminates the
need of shutting critical database that are backend to application and web
servers that maybe running.
The biggest reason is that coping data while the database server is up and
running can cause corruption of
the data as open files are an issue. But I know there are other application
that help in getting around this.
I'm not near familiar with mysql as I am with Oracle, Sybase, or Informix
databases. Haven't played with
DB2 but might in the future.
--
************************************************************
*** Phillip B. Bruce ***
*** http://pbbruce.home.mindspring.com ***
*** [EMAIL PROTECTED] ***
*** ***
*** "Have you ever noticed? Anybody going slower than ***
*** you is an idiot, and anyone going faster than you ***
*** is a maniac." - George Carlin ***
************************************************************
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
