[Deploying Sakai] MySQL dump format for backing up

[Colin Tatham]

Thanks Mike.
I think it’s slightly more complicated in that the ‘tab’ export includes .txt and .sql files, and mysqlimport doesn’t seem to handle both, so AFAIK you need to call mysql to do the .sql files, and mysqlimport to do the .txt files. Maybe I’m over-egging the pudding (and being paranoid :-) ) but I was imagining a script that would read the files from the tar archive without expanding it, and would prompt for the MySQL password rather than have it hard-coded in the script or in the command line history? (I’ve pretty much done that now, but I’m still interested in comparing notes if anyone else has done something similar.)

Any thoughts on how useful it would be to continue doing our backup dumps in this dual file format?

Thanks

Colin


On 11/10/11 3:01 AM, Mike De Simone wrote:

i usually just use mysqldump to produce 1 big file.  but if you have these tab-delimited files, it seems like a small bash script could do what you need:

something like this should work (assuming database is 'dbname', adjust as necessary):

for i in `ls *sql`; do
   mysqlimport dbname $i
done

reference: http://dev.mysql.com/doc/refman/5.0/en/reloading-delimited-text-dumps.html

Thanks,

-------------------------------
Mike DeSimone
Lead Systems Engineer
rSmart | 602-490-0473


On Sun, Oct 9, 2011 at 16:16, 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


_______________________________________________
production mailing list
production at collab.sakaiproject.org
http://collab.sakaiproject.org/mailman/listinfo/production

TO UNSUBSCRIBE: send email to production-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"



--
Colin Tatham | Educational Technologist | Learning Environments
 University Library | The University of Melbourne
W http://www.trs.unimelb.edu.au
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20111010/e1e3ba30/attachment.html