[Opensim-users] Database backups, recommended methods

[Simon Slavin]

On 9 Jun 2011, at 5:16am, missyfitz wrote:

> So is it safe to just backup the database file itself?

Some notes on doing this:

A) A MySQL database is not just one file.  Depending on which version you're using you'll probably find a little nest of files in a directory.
B) Any programs which use the database file must be halted when you do this (even those which just read it and don't write it).
C) That restored file is only guaranteed to work with the identical version of MySQL it was made with.  So if you're trying to restore it in two years time you'll have to find an old version of MySQL, but install it and make it run on newer hardware.

Experienced sysadmins prefer to back up SQL databases by dumping the data in text form (usually as SQL commands).  Then you can .zip it up to make it small.  Then if you ever need to restore the data you have a far wider number of options about what to read it into, and you can even inspect it by eye and understand what you're seeing.  MySQL even comes with a tool which does dumping like this:

<http://dev.mysql.com/doc/refman/5.0/en/mysqldump-sql-format.html>

Simon.