mysql question

[Ben Pfaff]

Adam <bsdx@looksharp.net> writes:

> When mysqld is running does it make sure it keeps the database files in an
> uncorrupted state, or are there times when if you make a copy of it with
> mysqld running you would get a damaged copy because you didnt stop
> it?  Basically I'd like to know if I can just tar the db files whenever I
> want without affecting the database operation, or if I need to "pause" it
> somehow to be 100% safe? Thanks.

There's a couple of issues here AFAIK:

	1. MySQL doesn't implement transactions, so the database
           might be in an inconsistent state when you copy out
           the files, or it might change while you're copying.

	2. Even if MySQL did implement transactions, they
           probably wouldn't be visible to tar and thus such an
           operation would still break.

	3. Copying internal database files is not a good way to
           do backups.  What if the database format changes in
           later versions, for instance?

Solutions:

	1. (Partial) Kill the database server and copy the
           files.  Still problem #3.[1]

	2. Dump the database contents using a utility that talks
           to the database server, while no other clients are
           talking to the server.[2]

[1] I don't know much about MySQL, but this will definitely work
with PostgreSQL.

[2] PostgreSQL provides a utility pgdump for this purpose,
probably MySQL has something similar.