How to migrate from MySQL back to SQLite?


#1

Is it possible? how to manipulate with the databases and config files?


#2

I do not know if Seafile has their own utility to pull this off. However, I do know, floating around in the web, that there are online tools to convert it and scripts to do it.

@shoeper, @DerDanilo - You guys have any idea whether Seafile includes a utility?


#3

Don’t think so.
Please explain to me why one would migrate back to SQLite? Who would change superior technology for a limited one?


#4

When I first deployed it long ago, I was simply a newbie to Linux, so I challenged myself to do things as complex as possible, for learning (and maybe for satisfying the curiosity as well), which was why I choose MySQL rather than the simple default SQLite deployment.

After that, everything has been working just fine. However, my service is just for home use, most time I’m the only single user that use it, then I figured that MySQL is not needed at all. Although I could just leave the service running with MySQL, I can’t help considering to get rid of it. If I can roll back to the file based SQLite, I could do backup much more convenient, by making a ZFS snapshot and then zip it.


#5

The official bash script which is used to migrate from sqlite to mysql is on the site, maybe we can dig it and reverse the things it does?


#6

I’ll look into it. I don’t know what they use to convert it in the first place. It would depend on whether or not what the script uses can work in reverse.


#7

If you could relocate (move) the MySQL data directory to, say, your Seafile server directory would that make backups easier for you? Would that solve your problem?

As an aside, as I understand it ZFS snapshots can include a whole volume. Why is grabbing a snapshot of your server a problem? Are you running ZFS on Linux?


#9

The official manual is here, if you don’t know.
https://manual.seafile.com/deploy/migrate_from_sqlite_to_mysql.html


#10

Thank you for the suggestions.
My data and database are on different disks. Even if they are on the same pool, I prefer sqlite now.
Anyway it’s not a problem about backup, which is mentioned just for an example. I simply want to migrate back to sqlite, wish you guys could give some help.
And yes I’m using zfs on linux.


#11

Finally weird configured.


#12

I looked at the conversion script to convert from SQLite to MySQL, and also the manual’s instructions. What the script does is use SQLite to export the databases to new files with delimiters included. After that’s done, the manual details how to import those databases into MySql using custom mysql scripts which have to be downloaded.

IE, in short, reversing the process would require someone to write some code. First, the databases would have to be exported by MySQL and then imported into SQLite.

I think that an online tool or one of the scripts I mentioned early on would be your best bet. Even then, it may not work. The most effective and sure fire way to do it would be to change Seafile back to the stock settings for SQLite, and then recreating/resyncing the libraries.

My knowledge of programming and scripting for databases is very limited. There may be someone else out there that has pulled this off in Seafile, but I don’t remember ever seeing a thread on how to do it.


#13

Exporting/importing to/from MySQL Is easy(many MySQL programs have simple two click progress for that), it is sqlite3 that is the problem. BUT the hard part was writing export from sqlite3, importing should be much easier, maybe even a single command line similar to “sqlite3 .import mysqlExport.txt” (google for actual syntax I do not know it exactly), but it would need a little experimenting, if lucky it will work on first try :slight_smile:

As for the process you would simple create a new fresh server, shut it down, copy data from old database to new database, reconfig so it looks in the right folder for files, start

But again I REALLY recommend against moving back to sqlite3:

  • it is less robust
  • simple backup with copying is working just fine with MySQL, I even configured MySQL to save database in the subdirectory of seafile, so I do whole-server-backup with a simple copy of one folder.
  • if you really want a simple config for home server there is also docker version of seafile with in-built MySQL, so to you it will look as only one “seafile” service. Haven’t tried this yet, but this is how I think it works from reading posts about it…

#14

@wthess I totally agree with you a simple exporting/importing of database is not enough for the reverse migiration, and all those configuration needed is full of traps. That surefire method you mentioned is a good suggestion.

@Lonsarg Now I don’t want sqlite that much, as you said I could simply do file-based mysql backups, and yes I have been doing similar things all the way (backup mongodb by borg-backup its raw files on my VPS).

Still, I may do some experiments on migrating back to sqlite, using all the info you guys provided. However there’s no urge anymore.

Let’s call it an end here.


#15

sqlite is not inferior to mysql, it’s less scalable. If you want a database for a simple one/three user server, sqlite3 can be much more efficient than mysql (you don’t need to touch the networking stack, authentication protocols, …). I made the mistake myself of setting up Seafile with mysql on my home server, 4 years ago.


#16

Sure, fortunately the pain is bearable :wink: