MariaDB/MySQL batabase backup

Software digiKam
1

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?

2

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
3

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).

4

My collection is 37K pictures. I am wondering whether a migration from SQLite to MariaDB will give me any performance benefits, but I find contradicting information (SQLite can easily support up to 100K pictures vs above 20K a migration is already beneficial).

@avalx, you noticed a major performance improvement. How large is your photo collection and in what way did your dK performance improve?

5

My library is about 100.000 photos (no videos) and the database is 1,6GB big. I installed MariaDB on my Win 10 machine without any additional tuning. I didn’t make any specific testing but it seems (!!) to me that the performance has improved somehow after I switched to MariaDB. I have a local database on an SSD drive, so this might help as well.
Again this is just my personal observationnot result of any comparison testing.