Database migrating exportimport scripts » History » Revision 15
Revision 14 (Anchi Cheng, 01/15/2021 06:09 PM) → Revision 15/16 (Anchi Cheng, 01/15/2021 06:13 PM)
h1. Database migrating export/import scripts When you need to move all your data in the database to a new computer, you can Either copy the all the database files over. See where in [[standard mysql database file locations]] (This only works well if your new computer is identical in setup. Or # do a full [[Mysqldump_of_your_database|mysqldump]] which includes instruction on how to recreate it through mysql command and then # [[Load_mysqldumped_databases|restore it]] on the new host h2. Start fresh but keep the instruments/calibrations/settings even most recent gain/dark references and other tables that you really care. As years goes by, the databases get too big to backup or congested with old instrument and images you no longer own or care. A few scripts were written to make it possible to take a clean installation and migrate from the old system the stuff you need to care over. |what|export|import| |tem,mag table, camera|export_leginon_instruments.py|import_leginon_instruments.py| |calibrations|export_leginon_cal.py|import_leginon_cal.py| |leginon settings|export_leginon_settings.py|import_leginon_settings.py| |presets|export_leginon_presets.py|import_leginon_presets.py| |gain/dark reference images|export_leginon_ref.py|import_leginonref.py| The old databases can be kept as archive that you can still view with its own myamiweb but no longer write to. These are in myami/dbschema/tools. They are not installed by the auto-installer, so you do need a git clone myami to access it. You also need to know your sinedon.cfg of the system you migrate from and to. h2. Step 1: Use mysqldump to migrate single tables (see above and mysql documentation on the internet) and restore them on the new host. Here are the ones you might want: 1. all projectdb except the two tables projectexperiments and processingdb (You can dump/restore the whole database and then empty the data in these two tables). 2. leginondb tables GroupData and UserData If you want all old applications, you can also dump/import ApplicationData. Otherwise, individual application import/export is easiest using myamiweb administration tab. h2. Step 2: On the old system, run these in a clean directory For example, a subdirectory from myami/dbschema/tools source_database_hostname is what is in your sinedon.cfg All output files except reference images are json files. The name format is used for parsing in the import. h3. export_leginon_instruments.py *Usage* export_leginon_instruments.py source_database_hostname <hostname1,hostname2> (optional hostname1, hostname2 etc are specific instrument hostname to export. default will export all) *Example* python ../export_leginon_instruments.py localhost Titan123456,GatanK3host h3. export_leginon_cal.py *Usage* export_leginon_cal.py source_database_hostname source_camera_hosthame camera_name <source_tem_name> *Example* python ../export_leginon_cal.py localhost GatanK3host k3host EFKrios h3. export_leginon_settings.py This export settings used by a specific session, typically a session you know containing good settings to be used as default in your new system. *Usage* python export_leginon_settings.py <sessionname> <optional partial application name> <optional node name prefix> *Example* python ../export_leginon_settings.py 20dec31a h3. export_leginon_presets.py This export presets used of by a specific session, typically the same session as above to be used as default in your new system. It general files separated by tem/camera combo. *Usage* python export_leginon_presets.py <sessionname> <optional partial application name> <optional node name prefix> *Example* python ../export_leginon_presets 20dec31a h3. export_leginon_ref.py *Usage* python export_leginon_ref.py source_database_hostname source_camera_hosthame camera_name *Example* python ../export_leginon_ref.py localhost k3host GatanK3 This script and its import counter part are also used as a regular backup to quickly recover from file server issue that require us to use a different file disk and avoid accessing the problem disk. h2. Step 3: copy the resulting json and mrc files to the new leginon host (called new_host in the examples) h2. Step 4: On the clean installation that has already administrator user and other default in the installation, run these imports on the json files. h3. import_leginon_instruments.py *Usage* import_leginon_instruments.py database_hostname *Example* python ../import_leginon_instruments.py import_leginon_instruments.py new_host h3. import_leginon_cal.py *Usage* import_leginon_cal.py database_hostname camera_cal_json_file *Example* python ../import_leginon_cal.py import_leginon_cal.py new_host cal_titan123456+EFKrios+gatank3host+GatanK3.json h3. import_leginon_settings.py *Usage* python import_leginon_settings.py <applicationname or json filepath> *Example* python ../import_leginon_settings.py import_leginon_settings.py 20dec31a.json h3. import_leginon_presets.py *Usage* python import_leginon_presets.py database_hostname tem_camera_presets_json_file *Example* ../python python import_leginon_presets.py new_host preset_titan123456+EFKrios+gatank3host+GatanK3.json h3. import_leginon_ref.py *Usage* import_leginon_ref.py database_hostname camera_ref_json_file *Example* python ../import_leginon_ref.py import_leginon_ref.py new_host ref_titan123456+EFKrios+gatank3host+GatanK3.json