We’re in the process of setting up a new virtualmin server. The old one used MySQL and postgres databases and it used the default storage paths for both. We have yet to migrate any of the old accounts but I’m wondering how best to configure database storage.
I’ve been told its worth trying to migrate mysql to mariadb so I’d like to hear from anyone else who has migrated a vmin sever from mysql to mariadb. Did you have any issues and if so were you able to solve them easily?
I’m aware vmin has the option of letting you choose where it stores users databases - in their home directories or centrally on the vmin server under /var. I’m thinking it might make more sense to stores users databases in their home dirs.
Is it possible to configure vmin to store users postgres databases in their home dirs too?
Till a user (or someone on his behalf, like a vendor) deletes some of the database files via the Virtualmin file manager or FTP / SCP client and you get tech support calls to solve the problem with the database.
Going from old MySQL to new Mariadb is generally easy. A dump and restore is sufficient (and Virtualmin uses mysqldump to backup databases).
Going from new versions of MySQL to new Mariadb is not as easy. Oracle has begun introducing incompatible changes. I wouldn’t call it hard to move from current MySQL to current Mariadb, but it may not be as simple as a dump and restore, it may require some work. So, if you want to migrate, doing it before you get to a less compatible version of MySQL is preferable.
It does not make more sense. It’s quite risky, and there is no benefit to it.
The user cannot meaningfully interact with it outside of database clients, so there is no reason to give them direct access to the actual binary files.
If you are worried about quotas, just make sure both /home and /var/lib/mysql are on the same filesystem. A big / filesystem is perfectly reasonable, as the system won’t vary much in size after creation, and then everything owned by the user will apply to quotas. If you can’t make one big directory, you can use a link, or maybe bind mount, to get /var/lib/mysql data stored in /home (but not in a user’s home directory).
Virtualmin has nothing to do with it for PostgreSQL or MySQL or Mariadb, not sure where you got the idea that it’s configurable. Virtualmin uses the database client library to create databases, it does not interact directly with the binary files or decide where they are stored. (SQLite is just a file and it can be in the users home without risk or complications. If that’s what you want, that’s the database you should use, but getting from MySQL to SQLite is non-trivial.)
If you were to change those paths, you would change them in MySQL or Mariadb or PostgreSQL configuration. (Which you could probably do in Webmin, though I’ve never done it, but it’s non-trivial, and again quite risky and complicated.)
What do you define as “old”? Our “old” vmin server was running Ubuntu 22.04 so I’m pretty sure (but not 100% certain as I cannot login to check) it was using mysql 8 which I think is pretty much up to date so in that case it sounds like we’ll have to stick with mysql.
As for moving my db storage to /home, I think I’ll try this guide for moving mysql’s data location:
That’s maybe a version that will cause problems, but I would still recommend you try migrating to Mariadb to see how it goes. It depends on what features the apps are using, as well.
I have my doubts about any of our users requiring any special features that are only available in the latest mysql but I could we be wrong about that.
I know that mariadb is a more community focused and developed fork of mysql but what would you say are the main advantages of us switching from the community / free version of mysql to mariadb?
I don’t want a holy war about it (those are prohibited by our guidelines), but, briefly:
It’s not intentionally breaking compatibility.
It’s what most everyone in the OSS world has decided is the de facto standard (distros, many web applications, etc. treat it like it’s the default implementation).
It’s not owned by Oracle, who have a long history of questionable tactics with OSS projects. There’s a reason, or a few reasons, every OSS project ever acquired by Oracle has been forked soon after, by the community and some original developers of the project (MySQL was not the first or the last).
Performance-wise, it’s gone back and forth between the two, but both are Fast Enough™.
In short: If you have a reason to stick with MySQL, fine. I won’t argue with you. We support it, as best we can. But, if you don’t, taking the path of least resistance seems wise. Mariadb has more users here, including me (I haven’t used MySQL in a decade or more, I mostly switched almost immediately), so you’ll get more help.