>>> We regularly make drive images of our entire server and store it at a
backup site. To be honest I have never fully understood how the imaging
programs deal with open files. My question is will the drive image make
a good copy of MySQL DB or should I be running a mysqldump before doing
the drive image?
If the MySQL server is actively running (data modifying) queries
while the drive image is being made, you can be pretty sure the
resulting image will potentially have lots of problems. You *might*
get 99% of your data back, but considering that you don't know which
1% is bad, is that acceptable? It's probably better than nothing
after a large fire, but it could still kill your business. You
might see partial changes where the change you see was made after
the change you don't.
In general, the way imaging programs deal with open files is either
"You are instructed not to have any while making the image" (rather
difficult for a root drive), or "BADLY!". Files open read-only
probably aren't a problem.
Assuming you can individually extract files from an image, rather
than restoring the whole thing, you can probably retrieve table
schema (.frm files) unless someone was running ALTER TABLE while
the image was being made, and you can probably retrieve the `mysql`
privileges database as it likely doesn't change that often. If you
try retrieving any table data, I'd sure run CHECK TABLE before
depending on the data, and there's still no guarantee of consistency
of changes even if the indexes look OK.
Check out the options --lock-all-tables and --lock-tables of mysqldump.
A long lock shuts down operations for a long time but you get a
more consistent result.
Gordon L. Burditt
Isn't mysqlhotcopy a viable solution for this, to backup your database
while its still running? Thanks for clarification if I'm wrong.
mysqlhotcopy has some disadvantages:
(1) You have to run it on the mysql server, meaning you need shell
access on that system.
(2) It only does MyISAM and ISAM tables.
(3) It requires UNIX file permissions to read the table files (meaning if
the database is shared, you can steal everyone's data, bypassing any
MySQL permissions). This pretty much means you have to be the admin
of the MySQL server or trusted by the admin or sleeping with the admin.
(4) It still requires locks, so you still shut down (write) operations for
a while if the database is large. (both mysqlhotcopy and mysqldump
let you leave the server running, but it can get really, really
slow for a while due to the locking to anyone trying to make changes
(think about a 1 hour time to refresh a web page that submitted an order).)
(5) The resulting files cannot be easily accessed except by stuffing them
into a database directory on another server (within an acceptable
range of server versions).
(3) is a killer if you are using a hosted web server and database server.
You can also consider using replication, and doing your backup on
the replicated server. Run STOP SLAVE; changes quit getting made
on the slave, run your backup (nobody gets locked out since nobody
is using the slave for anything else), then run START SLAVE; and
the slave catches up with the changes.
Replication by itself is not that good as a backup. It's good
against hardware failures. However, it will replicate accidentally-typed
queries like "DELETE FROM table;" quickly and accidentally erase
your data on all the copies, just like you asked it to.
Gordon L. Burditt
