[Deploying Sakai] MySQL dump format for backing up

Colin Tatham colin.tatham at unimelb.edu.au
Tue Oct 11 17:58:33 PDT 2011

Thanks for that (and your Oracle reply). Now I have even more options to
choose from for our MySQL backup! :-)

Does anyone currently use the standard all-in-one dump for Sakai backups, or
should we avoid that?



On 11/10/11 10:27 AM, Matthew Jones wrote:

> While not Sakai, what I would do in the past is create two dumps:
> One with the --no-create-info and another with the --no-create-db. The
> --no-create-db would dump all of the data and other would just dump the schema
> (which wouldn't change often). To the data, I'd add the option
> "--order-by-primary" because I checked both files each night into a a local
> svn repo and committed so I had backup "forever" of the database. Since it was
> ordered, only the diff's each day were stored in svn, so it didn't take up
> much space in the total repo.
> Then I could roll back the database to any point in the past (or as long as
> the file system resources and actual deployed version of the software was
> available, which it was) We only usually kept file snapshots around 30 days,
> using something like http://www.mikerubel.org/computers/rsync_snapshots/. . .
> Was awhile ago but that process seems familiar.
> We had to homebrew everything back then, popular off the shelf packages are
> probably just as good.
> You should be able to restore the schema from auto.ddl as well, the data is
> the most important piece. You'd probably want to test this out though. ;)
> -Matthew 
> On Sun, Oct 9, 2011 at 7:16 PM, Colin Tatham <colin.tatham at unimelb.edu.au>
> wrote:
>> Hi
>> Just wondering whether anyone has any experience or opinions on this.
>> mysqldump has an option which produces separate files for the database
>> structure (in SQL files) and content (in tab-delimited plain text files):
>> http://dev.mysql.com/doc/refman/5.0/en/mysqldump-delimited-text.html
>> We have a Sakai DB dump in this format which I need to restore, but as there
>> doesn't seem to be any MySQL facility to restore from the 677 files in the
>> tar archive automatically, I'll need to script it. Anyone done that already?
>> I'm also interested to know what the usual practice is for backing up; do
>> most of you do the standard mysqldump back-up to a single very large SQL
>> file, and then have that backed up at the file-level by your normal back-up
>> software (TSM, in our case)? Anyone have any opinions of the possible
>> advantages of using the separated format?
>> Thanks!
>> Colin

Colin Tatham | Educational Technologist | Learning Environments
 University Library | The University of Melbourne
W http://www.trs.unimelb.edu.au

More information about the production mailing list