Hello! Hopefully someone can answer this question. Recently there was hardware migration on version 9.6CU23+HF+MySQL. The restoration took about 6 hours. When it finished we realized that the backup package had the dataminer.lic file from the source DMA so the new DMA was no longer licensed. However, the backup seemed to have finished successfully. On the database side, the table structure in the SLDMADB folder was missing the .IBD files but all the .FRM files were there. When looking at the table structure through the workbench, all the data seems to be there: element tables were there, alarm tables had alarms from months ago, etc. The concern is that the database structure isn't what we're used to in regards to the table files in the SLDMADB folder. We don't know where exactly the workbench is pulling the data if there are no .IBD files in the system. The only thing I can think of would be the ibdata1 file that's about 123GB but I have no idea what it contains. The questions are:
- Can this DMA migration be considered safe to run with the .IBD files missing?
- Why wasn't the complete table structure migrated to the SLDMADB folder?
- From where is the workbench pulling the data?
- The size of the .SQL file in the backup package is about 60GB uncompressed, about 10GB compressed. Would this cause the restoration to take about 6 hours? The new servers are running SSDs.
Thank you!
Hey Luis,
The main issue here is probably caused due to a difference in the MySQL settings between the source server and the destination server. Let us go over each question
- Why wasn’t the complete table structure migrated to the SLDMADB folder?
This is probably caused due to the destination server being configured differently, namely the "One file per table" option being disabled. This causes MySQL to ingest the data differently from backup. (see the DataMiner help).
- From where is the workbench pulling the data?
The IBData1 file present in the MySQL data directory
- Can this DMA migration be considered safe to run with the .IBD files missing?
Temporarily, yes. However having all the data pulled into one big file can lead to issues when the file is locked due to operations for one table, and you try to do queries on another table. Skyline advises the "One file per table option". For more info on how to enable this see the dataminer help
- The size of the .SQL file in the backup package is about 60GB uncompressed, about 10GB compressed. Would this cause the restoration to take about 6 hours? The new servers are running SSDs.
Backup restore is more than just the sql file. we need to restore the entire dataminer as well. It is hard to tell where the bottleneck is without further investigations. Common culprits are software scanning files, concurrency for disk access,...
Hmm, odd the links were indeed empty. reconfigured them.
To answer the question. The option always apply to the database, unrelated if you import a DB or not. So even if you just started using the DataMiner without loading any backup you would have one file for all the data.
I am the customer related to this question and have a follow up question. We employed a different migration procedure, please see below. Is this an acceptable procedure?
1) Take a custom backup of the original DMA that does NOT include the database
2) Stop the DMA
3) Export the entire sldmadb database using MySql workbench export to a single file option
4) On the replacement server with fresh install of matching versions of DM run the BU recovery package from step 1
5) After the recovery is complete the DMA will NOT be started due to the fact that the BU package contains the License file from the old server so DM with not start up
6) Import the exported database from step 3 into the new server using MySQL Workbench Import option
This appeared to work fine and cut down the time of the entire process by a couple of hours, mostly because taking a DM backup of the old DM that included the database took 2hr 45min but taking a backup without the database only took a few minutes and the database export only took ~30 min. However, please note that prior to exporting the database via Workbench I stopped the DMA so it was not interacting with the database during the export.
Thanks in advance for any feedback
Hi Brent. Thank you very much for the information. That is very helpful. Follow up question: does the option of “One file per table” only apply to an imported DB or would this also have applied if we had started using the DMA without the backup restore? Meaning, after creating elements and alarms, would all the new data still be stored in one table? Also, I think the links you provided are empty 🙂