How to restore DB and encryption key

jlewis57

Hey all, I know there are lots of google articles about the specifics of restoring a DB, but I am not finding them super helpful, maybe it is just me.

I have a backup (.sql) file that I took and have always backed up my master encryption key. My original ITFlow VM crashed really hard, and it seems that the VM was never properly configured in Veeam, so it was never backed up at the VM level. I have rebuilt from scratch and have stood up the instance all the way to a blank web interface now. I was half expecting to see an import option under the Backups window, but that only allows you to backup the DB and key. I now need to actually restore, but cannot figure out a way to do this.

Are there any detailed guides, or can someone give me a simply walkthrough of this process?

aftechro

hey jlewis57 if CLI on linux to advanced for you or you are not familiar with the restoring DB, you can try to download adminer.org which basically it`s a single php file that allow you to manage your database. download file, upload it on your itflow.domain.tld/sql-manage.php (or rename the file as you wish), connect to your DB using the root credentials, and then, import your sql file.

I played around with this option to test it, and works fine. Sure, important here is that root login is allowed and upload limit should be over your backup.sql file.

As for VM backup, along with veeam, try to take snapshoots each time before doing something on the VM.

wrongecho

Hey! @jlewis57

I'm assuming you've read the backup docs, but I'll link them again anyway: https://docs.itflow.org/backups. It sounds as though you're missing your files, but have the (arguably more important) database and encryption key. Note that only logins are encrypted - everything else is clear text.

I'd say you have two real options here:

Setup a fresh install, restore your database over the top
Decrypt the logins using the master key

I assume you want to continue using ITFlow, so option 1:-

Spin up a fresh VM with all the requirements for ITFlow (https://docs.itflow.org/installation).
Setup phpMyAdmin as well to make your life easier.
Download and run through the setup of ITFlow as if you were installing from scratch (https://docs.itflow.org/installation).
Once setup is complete, restore your database SQL on top of the current install using phpMyAdmin. The latest install may have tables/fields your backup didn't - that's okay, they'll just be blank/default. You may have to even do this table by table, but hopefully not.
Your ITFlow database should now contain your old data. You can only access your encrypted data by logging in using the same email + password you did at the time of the backup
Note that any files you uploaded will still be referenced, but inaccessible (as they don't actually exist on disk)

Let me know how you get on 🙂

pr0tex

Hello all, i too am suffering total loss of my ESXi VM. i was able to grab my master key and a .sql backup the day before the the ssd burned out. I've now spun up a ProxMox VM. im new to vm's and linux and have no clue how to do the above. i tried installing myphpadmin on my synology nas to try to see if i can recover my itflow DB. i cant wrap my mind around how to do it. are there step by steps guides that can help? or can someone reach out and teach me how to.

felix

pr0tex No problem. First get the latest Ubuntu LTS on that VM. Next you can simplify the process by installing Hestia https://hestiacp.com/install.html. Then follow Hestia's documentation for enabling phpmyadmin. Follow their docs for adding an app and upload the ITFlow files to its web folder. Create a new database for ITFlow. Then use phpmyadmin to restore your old database.

I can give more details if you get stuck.

pr0tex

Thank you for the quick response. I forgot to mention I used the installer script to install my it-flow instance. I'm ashamed to say this publicly, please don't judge. I seriously have no clue on doing any of this stuff. I know I shouldn't be playing with fire, but this side of IT isn't my thing.

pr0tex

cant i just take the backed-up database and drop it in a new instance of itflow? i can use file zilla to copy and paste the backed up db and replace the new one.

felix

pr0tex Sadly no, most databases cannot be treated like files. The only exception I know of is sqlite. It's just a file, but it has size limitations.

Now lets get you on phpmyadmin. It's just PHP files and like HTML files you just put them in your web folder and the server handles the rest. So just drop the files in a phpmyadmin subfolder and you can reach it by going to http://domain.com/phpmyadmin. Then just follow any guide on restoring a mysql/mariadb database using phpmyadmin.

pr0tex

felix tried your drop method and it didnt work.

tried this sudo apt-get install phpmyadmin and i got to the point to choose a db

┌────────────────────────────────────────────┤ Configuring phpmyadmin ├─────────────────────────────────────────────┐

│ │

│ The phpmyadmin package must have a database installed and configured before it can be used. This can be │

│ optionally handled with dbconfig-common. │

│ │

│ If you are an advanced database administrator and know that you want to perform this configuration manually, or │

│ if your database has already been installed and configured, you should refuse this option. Details on what needs │

│ to be done should most likely be provided in /usr/share/doc/phpmyadmin. │

│ │

│ Otherwise, you should probably choose this option. │

│ │

│ Configure database for phpmyadmin with dbconfig-common? │

│ │

│ <Yes> <No> │

│

what do i do next?

i cant attach a screenshot for some reason

pr0tex

felix i chose no

pr0tex

Thanks for a bit of clarity. I’ll try your suggestions and let you know

wrongecho

You need to host images somewhere like imgur for now.

Restart the apache2 service.

Load up phpmyadmin in your browser: 127.0.0.1/phpmyadmin

pr0tex

wrongecho ok done, whats the username & password? root i think. but the password for the clean install? i have no idea.

BTW thank you for taking the time to help out a noob 🙂

pr0tex

felix wrongecho @aftechro

https://www.reddit.com/user/pr0texx/comments/13bmfmk/itflow_db_restore_failure/?utm_source=share&utm_medium=web2x&context=3

ca someone look this over please. not sure what im doing wrong. ive stepped through the restore process in the screen shots on reddit. if this is a lost cause just let me know.

felix

pr0tex You can find the username and password in config.php in the web folder.

"ITFlow DB restoration isn't as easy as its supposed to be"

In the nicest way possible, there is nothing specific here about ITFlow. This is very basic database administration. You should be comfortable with the basics of MariaDB/MySQL, Apache and troubleshooting if you want to self-host ITFlow.

The initial error is that you're trying to re-create the tables. You can either remove this or add the "IF NOT EXISTS". MariaDB docs: https://mariadb.com/kb/en/create-table/#create-table-if-not-exists

The datetime error is because SQL is being too strict about the dates. You should be able to run SET SQL_MODE='ALLOW_INVALID_DATES'; Here's a similar question on Stackoverflow: https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000

You may also be able to workaround both of these by turning off SQL strict mode, but I haven't tested this.

pr0tex

wrongecho thank you for you response. I'm not a dev guy, nor a programmer. like i stated I'm a noob to this side of the world. so the links look like Latin to me. they also don't help me as I'm not versed in Linux as i should be. I'm willing to do the work but am not able to get a full grasp of it all. for me months of data is either lost or locked up in db which i don't have the proper skillset to fix. following other peoples post and recommendations have got this far where i can try simple things and take screenshots. its the minor intricate details that elude me, and that's where the issues are.

Thanks for being patient and trying to help.

wrongecho

Hey @pr0tex

I understand. I've got some spare time this afternoon if you'd like to go through this on a screen share instead?

pr0tex

wrongecho sorry now seeing this, yes id like all the help i can get. dm me so we can arrange better time.