Craig White wrote:
> On Tue, 2007-03-06 at 10:09 -0700, Nathan Aubrey wrote:
>   
>> Is is safe to compress an ldap database or a mysql database for archival 
>> purposes?
>>     
> ----
> I'm gathering that the reason that you are asking is because you want a
> backup of the binary blob data files which in both cases is probably the
> least desirable method of backing up.
>
> A decent backup for either mysql or ldap would be a mysqldump or slapcat
> which would be a text file which could be piped to tar to simultaneously
> dump the data and compress it and I wouldn't be surprised if mysqldump
> had a tar/zip function built into it's client software to do this on the
> fly
I can't comment about LDAP.

There are 2 main downsides to SQL-based backups for MySQL.  Size and
speed.  SQL is much fatter than the binary equivalent.  Creating SQL is
slower than copying the data files directly.  Restoring from a SQL
backup file is much slower as well.  For small databases this doesn't
matter very much, but once you get a few gigs of data the differences
are pretty noticeable.

The downside to binary backups is mainly flexibility.  You can't move
the data to other DBMSs, and you may even have problems moving between
MySQL versions, depending on which versions, what your data looks like, etc.

How will the backup be used?  If you want to use it on other systems,
use SQL.  If it's a 'just in case' backup that will only be used in case
of a disaster, the binary may work fine.  You can dump the binary now,
and if you ever need to move it elsewere you can dump it to SQL then.

Check out the --tab option for mysqldump as an intermediate option.  It
does a database dump in a tab-delimited format which you can use with
the 'LOAD DATA INFILE' SQL command.  It's MySQL-specific, but it saves
the SQL parsing and makes both the export and a later import much much
faster than straight SQL.  I believe it's also safer to use across MySQL
versions, but you'd have to check the docs on that to be sure.  Search
dev.mysql.com for 'SELECT INTO OUTFILE' and 'LOAD DATA INFILE'.

In any case you need to make sure the data is not being accessed while
the dump is taking place.  We use a MySQL replication slave for
backups.  We can stop the slave server and take a backup whenever we
want.  Once it's done, we start the slave back up and it catches up to
the master.

alex