I’m pretty new to the self hosting thing but I’m enjoying it a lot and want to go a bit further down the proverbial rabbit hole.
I’m looking at a bunch of services and they all require a DB, usually a MySQL DB. It seems counterintutitive to have 20 MySQL databases each in its own Docker container. So is there a way to have one DB across most of my services? (I realize that Nextcloud and other bigger items should have a dedicted DB.)
How would I set up a shared DB in a docker-compose file?
You’ll want to learn some database administration before you attempt this. Simpler to just give them all their own instance.
I have 1 postgres container with 7 DBs created in it and 1 mysql container with 1 DB in it, (bookstack…) so it is definitely possible and would make sure persistence and storage is properly configured as another user has gone through.
You could set up a docker with an exposed port for connections to the MySQL database server and run 20 databases inside it, that will come with its own risks fyi. You may have MySQL version mismatches to start with, you may have concurrent connections trying to use the same internal port, you may have a number of different situations where reads or writes take a much longer time due to other services wanting data.
Not only it can be done but I think it is the way to go. You then have to manage permissions and backup only on one database, and the performance improves given that you let postgresql manage it’s own IO. It goes without saying that you should use postgresql instead of mysql
Im using one mariadb (lsio) for multiple services, but I wouldnt do it like that if I started over. I just didnt see recommendations on time 😄, but no issues so far.
You can install phpmyadmin (also docker) for db management with nice gui. You can open existing db or create new one quite easy
In many cases it should be fine to point them all at the same server. You’ll just need to make sure there aren’t any collisions between schema/table names.
Yes and no. Theoretically, yes you may use one database for all services. Historically it was even an old assumption and some services still ask you for a table prefix to allow various services to use the same db without clashes. However it’s no more recommended and you should absolutely avoid going this way.
But nothing blocks you to have several separate databases on the same server. Here your mysql docker container play the role of a database server. You can use the same container for different services using different databases. Create one specific user/database per service inside your container (you may wrap the official mysql image inside your own image to script its creation), then add it in your docker-compose file and make all your services depend on the same service.
Not recommended as one service could screw up all the tables. Each service should have its own DB and then use persistence to a SSD or a NFS
Virtually all database solutions support limiting users to specific databases/schemas/tables/whatever you need.
Persistence to NFS is also generally bad advice for most databases for performance reasons.
