MariaDB/MySQL batabase backup

I’ve switched from SQLite to MariaDB on my local DigiKam machine (Windows 10) - it’s much faster now, BTW. Now I’m wondering what is the best way to backup the database. With SQLite it was easy - just copy all the database files to another (safe) location.

With MariaDB I’m doing the following and would like to know if this is good enough.

I use HeidiSQL admin tool for this procedure to copy the DK database to my local Linux server with MySQL installed:

  1. Tools -> Export database as SQL
  2. Choose the Output as my MySQL server and same database as on source
  3. Select to create tables and “Delete + Insert (truncate existing data)”
  4. Select the digikam DB on my local machine
  5. Click Export and wait for process to finish.

So my questions are:

  • is this procedure OK and will I be able to restore the database the same way so digiKam will use it without problems (hope I won’t need this )?
  • what bothers me is the fact that on the destination server the tables’ properties are different after export than on the source (local machine), for example there are different numbers of rows reported. Any ideas why that might happen?

Or does somebody have any other suggestions on how to easily backup the database?

Hi @avalx
You could backup to an SQL file rather than an actual database copy (it sounds like that’s what you’re doing for now?). That way it’s simple to keep historic versions if you like. It’s probably a good idea to test restoring from that dump file to your linux server at least occasionally though. There are other tools to create backups with as well, for example mysqldump from command line/terminal… that way you can script it if you want. Perhaps digikam has something built in (I don’t use it myself).

No idea about the row counts question without seeing more detail, there could be a few reasons - any error messages during the process?

1 Like

Thanks for your tips. Yes, I also considered backing up to an SQL file. I will try this and see how restore to my server works (to check the thing about different row counts).