Database migrating exportimport scripts » History » Revision 7
Revision 6 (Anchi Cheng, 01/15/2021 05:49 PM) → Revision 7/16 (Anchi Cheng, 01/15/2021 05:52 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 database gets too big to backup or congested with old instrument 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.
These are in myami/dbschema/tools. These are not installed, so you do need a git clone myami to access it.
You 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
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. 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 20dec31a
h3. export_leginon_presets.py
This export presets used by a specific session, typically the same session as above to be used as default in your new system.
*Usage* python export_leginon_presets.py <sessionname> <optional partial application name> <optional node name prefix>
*Example* python 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
h3. import_leginon_instruments.py
*Usage* import_leginon_instruments.py database_hostname
*Example* python 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 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 20dec31a.json
h3. import_leginon_presets.py
*Usage* python import_leginon_presets.py database_hostname tem_camera_presets_json_file
*Example* 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 new_host ref_titan123456+EFKrios+gatank3host+GatanK3.json