Migrate SQLite installation to a MySQL one

[athamour1]

I would like to migrate my 2fauth installation from SQLite to MySQL.
After searching for information, I couldn't find any references on how to do this.
I know that converting the SQLite file to a MySQL dump is an option, but I'm not sure if this method is suitable for migrating my installation. Is this the best approach, or is there another recommended way to proceed?

Thank you in advance, and I apologize if this question has been asked before.

[Bubka]

Hi,

After searching for information, I couldn't find any references on how to do this.

Indeed, there is no recommended method.

If you have a limited usage of 2FAuth, the best way to proceed is to use the import/export feature of 2FAuth. By limited usage I mean:

• There is only one user registered
• You do not use Groups (are few ones)
• You do not use oauth/personal token/passkeys

In this case, the process is:

1. Backup you current sqlite db. Just to be sure you can rollback everything to a working sqlite setup.
2. In 2FAuth, switch to Manage mode, select all items and export them. You will get a json file.
3. Edit your .env file and configure a MySQL db setup, comment the sqlite one.
4. In a terminal, run php artisan config:clear
5. Then run php artisan migrate.
6. Then run php artisan passport:install
7. Open 2FAuth and register.
8. Click Import and submit the json file.
9. If you were using some Groups, recreate them and move the 2FAs in them.

As you can see, this is relevant for limited usage because some operations need to be redone manually.

---

If you are not in a basic usage, I recommend an hybrid approach:

1. Backup you current sqlite db. Just to be sure you can rollback everything to a working sqlite setup.
2. Create a dump of your sqlite db, but without the structure. Only export the data, from all tables except migrations and sqlite_sequence
3. Edit your .env file and configure a MySQL db setup, comment the sqlite one.
4. In a terminal, run php artisan config:clear
5. Then run php artisan migrate.
6. Search the internet for resources (guide, app, script) to convert a sqlite dump to a mysql one.
7. Once converted, import the dump to MySQL
8. Open 2FAuth, you should be able to sign in.

Note that this is only a suggestion, I have not tested it.

---

If for any reason you need to rollback to your sqlite setup:

1. Edit your .env file, uncomment the sqlite config, comment the MySQL one
2. In a terminal, run php artisan config:clear

This assumes that you have never deleted the .sqlite file in [2FAuth_install_dir]/database.

[Bubka]

Was playing around with mistral AI new Codestral LLM, the hybrid approach is ok. I have used Codestral to convert a sqlite dump into mysql instructions. It imports seamlessly into MySQL.

The sqlite export has been made with sqlitedbbrowser:

• File menu > Export > Database as SQL
• Select all tables except Migrations and sqlite_sequence
• Check the keep column names in INSERT INTO option
• Select Export data only

Obviously, because of the db constraints, the imported data have to be consistent. In my first attempt, the sqlite dump contained some 2FA records bound to a missing user record, so the transaction failed (this was from a test db). But with a consistent dataset, no problem.

Of course, don't do the AI stuff with your own 2FA data. This was just for testing purposes, using fake 2FAs.
The conversion has to be done manually, or with a local script.

Let me know if you have successfully completed your migration, your feedback would be welcome.