Migrate from SQLite to MySQL


#1

I have a running gitea instance with SQLite. Now I want to change the database from SQLite to MySQL.

a) Is this possible?
b) Is there any existing documentation how to do that?
c) Can someone point me in the right direction?

What I currently want to try is to export the SQLite database with an appropriate tool, import that into a MySQL database, change the gitea config to use the MySQL database instead of the SQLite one and in a perfect world this would just work…


#2

We just did this last week. Basically, here’s what I did:

do a gitea dump - this dumps the db (among other things)
extracted the dump - it’s in the form of SQL statements. However, the syntax is different between sqlite and mysql. So… delete all of the “create table” and “create index” statements. So all you’re left with is insert statements.

get a copy of the mysql ddl. I did this by firing up a copy of gitea in a docker container, and doing a barebones install to a mysql db. This got me a blank database, with no data. I backed that up with the mysql workbench app. (so I could repeat all this for testing)

So, I now have a blank database with the proper schema, and a bunch of insert statements. Add one to the other, and almost done.

re-configure gitea to point to the mysql db, and restart. Log in with a local ID, and confirm everything. I also had to go into my authentication sources, and fix the LDAP (Active Directory) settings. One of the search things changed from & to u0026 (unicode), and I had to re-enter the bind password for the service acoount.

Now… I did a crapload of testing, because I also did all this with docker containers. And we were already using gitea for prod stuff, and I would have been job hunting if I screwed up the migration (well, probably). So I strongly suggest testing a bunch of times. Get it all repeatable. Rollback, the whole shebang.

But yes - it is possible. Using this, it’s probably even possible to switch from sqlite to something other than MySQL too.

Bonus points - apparently mysql has memcache built in. So I set that up too. Now Gitea points to mysql for the db stuff, and the memcache daemon in mysql for memcache stuff. No idea if this is better or worse, but it’s kinda cool :slight_smile: