[Archivesspace_Users_Group] Migrating data from Derby to MySQL

[Steven Majewski]

> On Sep 22, 2015, at 10:35 AM, Levy, Michael <mlevy at ushmm.org> wrote:
> 
> Let's say, hypothetically, that one has entered some test resources into ArchivesSpace and in the fullness of time those test data grew, and then one takes a look at what one has and thinks, hmm, it would be nice to migrate from Derby to MySQL. In this hypothetical scenario, what advice would you give? Find a way to export and import the Derby table data to the empty MySQL database created at install? Are there better export/import functions provided by ArchivesSpace? 
> 
> We know now to avoid production data in Derby.


For resource trees you can export and import as EAD. 
Barcodes do not export/import with the standard EAD converters, but I have a plugin to handle those if needed. I haven’t noticed anything else missing from the export, but I would suggest that you compare old and new versions before decommissioning the old demo server. 

I’ve moved other models like user and repository info using the backend API. 
Just save the JSON output from a GET and use it as data on a POST to the new server backend.

I haven’t tried moving subjects or agents. You could probably use the backend API. 
I would probably just reimport using LSNAF/LCSH. 

How much and what material do you have to move ? 

In theory, you should be able to do it with SQL, but I suspect bridging the differences between Derby and MySQL would be more work that the above.  If you want to give that route a try, you can download the Apache derby distribution, which has some command line programs you can use to dump the archivesspace derby db, but it doesn’t appear as simple as doing a mysqldump and reloading it.  You might have better luck dumping to CSV than SQL. By creating a properties file that points to the ArchivesSpace derby db in build/ directory, I was able to connect with derby’s ‘ij’ and do a ‘select * from RESOURCE;’ 


Googleing for ‘derby mysqldump equivalent’ comes up with this suggestion: 

http://stackoverflow.com/questions/3751425/mysql-dump-sql-script-and-import-to-an-apache-derby <http://stackoverflow.com/questions/3751425/mysql-dump-sql-script-and-import-to-an-apache-derby>

http://dbcopyplugin.sourceforge.net <http://dbcopyplugin.sourceforge.net/>



— Steve. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20150922/ce80d008/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4943 bytes
Desc: not available
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20150922/ce80d008/attachment.bin>