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