Set up MySQL/MariaDB and PhpMyAdmin for your Homelab

When you start your journey with a homelab you get some sort of computer to use as a server and start throwing cool Docker containers on it. You soon find that many of your applications need databases, and you’ll mostly encounter SQL, MySQL/MariaDB, PostgreSQL, Redis and MongoDB.

If you have 20 different applications that require databases, it’s common to see 20 separate database containers running. This is impractical and a waste of resources. Setting up automated backups for so many databases is a chore unless you’re a sysadmin who can easily automate it, but if that’s the case you’re probably not reading this specific article, and you’ll still want to maximize resources.

BUT Titor, all docker composes and tutorials have a separate database container. They MUST know something!

They know that people want a no nonsense setup that has everything included so they and their novice or non technical users won’t have to deal with an amalgam of technical issues. If you only want to run that software and nothing else it does make sense, but if you’re reading this you probably have, or intend to have, tens or hundreds of containers running eventually.

More than just resource benefits

Knowing to work with databases is a crucial skill to master for your homelab, career, and general happiness. Some people have stories about daring adventures of low sobriety, but how many people do you know who can tell you about fighting for hours with a corrupted database to get it functioning with minimal data loss?

Employers love the word SQL as well, even though most people looking over your resume have no idea what it means. It’s an all capital letter buzzword, so it must show technical skill, right?

When you run a MariaDB container you’re actually running a MariaDB server. You can have multiple databases managed by this, and a single server can handle more transactions than you even need, at least for the most part.

Setting up one database to store them all

MariaDB

So how do you get started? You’ll want to set up Docker and Portainer and paste in the following compose:

services:
  mariadb:
    image: lscr.io/linuxserver/mariadb:10.11.6
    container_name: mariadb
    environment:
      - PUID=${PUID}
      - PGID=${PGID}
      - TZ=${TZ}
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    volumes:
      - ${VOLUME_PATH}/mariadb/data:/config
    ports:
      - 3306:3306 
    restart: unless-stopped

Or just grab whatever existing one from one of your containers.

For the environment variables we have:

VOLUME_PATH=/volumes/databases
TZ=Etc/UTC
PUID=1000
PGID=1000
MYSQL_ROOT_PASSWORD=AMz3C@s%9z&L#LcREmB^wMm4kQ&NdSV&MYEyJfgGQybUrn@z7EGcP!jhmGfWP^GweMFSH!z2^*pD6Lftd6n#k4uTsTDGaMM$kSg3Vvzbk7Df#hQ4z56h54pg9QJgVesE

Generate your own password, do not use this one. Set your proper volume path and timezone as well, then start the stack.

PhpMyAdmin

services:
  phpmyadmin:
    image: lscr.io/linuxserver/phpmyadmin:5.2.1
    container_name: phpmyadmin
    environment:
      - PUID=${PUID}
      - PGID=${PGID}
      - TZ=${TZ}
      - PMA_HOSTS= 192.168.89.2:3306,192.168.89.2:3307,192.168.89.2:3308 # This will pre-fill the server IP and port. You can add multiple separated by commas for a dropdown.
      - PMA_ARBITRARY=1 # Allows you to set the IP and port on the login page. Useful for migrating databases to your new shiny centralized one, or one off connections
      - UPLOAD_LIMIT=${UPLOAD_LIMIT}
    volumes:
      - ${VOLUME_PATH}/phpmyadmin/data:/config
    ports:
      - 41322:80 
    restart: unless-stopped

And the Environment Variables:

VOLUME_PATH=/volumes/databases
TZ=Etc/UTC
PUID=1000
PGID=1000
UPLOAD_LIMIT=500M 

For line 15, get a random port here and for the love of God buy Corentin a baguette for his awesome tools! Now you can run your stack.

For the upload limit, you might want an even bigger upload limit for larger databases, and in some scenarios you’ll likely have to use a mysql CLI client.

Migrating your existing databases

I’ll call the new central database we just made the new database, and the databases that your applications are currently using the old databases.

This will differ from application to application, but generally you will:

• Connect to the old database
• Export it
• Create a new user and a new database on your MariaDB server
• Import the old database
• Configure your application to use the new database

Connecting to the old database

I don’t actually have a live database to do it as I already migrated mine, but I spun up an older one.

• Open the PhPMyAdmin page, this is going to be your server ip and the port you set on line 15
• Put in your database credentials and login

Exporting it

• Click on your database on the left
• Click on export on the top menu
• Then Export at the bottom

Creating a new user and database

• Connect to your new database
• Click on User Accounts in the top menu
• Click on Add User Account

• Fill in the User Name, password
• To assure there are no issues, you should use the same username and database name as before
• Check Create database with same name and grant all privileges

Importing the old database

• Just like before when exporting, click on your database on the left
• Click on Import this time

• Click Browse and select the .sql file you exported before
• Click Import at the bottom without changing anything else

Configuring your application to use the new database

This depends on the application, but mostly you need to change the environment variables if it’s something you use through Docker

That’s it! Now you know how to create a new user and associated database, as well as export and import your existing ones in PhpMyAdmin!

Line 13, a bit on ports and security

You’ll notice on line 13 we’re using the default port of the database. Generally it’s a good idea to change the standard ports, but ultimately it’s not someone knowing the port that’s the security flaw.

If your database is local to your Homelab, then someone must have breached your local network. If someone is in your local network you have much bigger problems than your database port, they can find out whatever custom port you have set anyway.

Don’t expose your databases to the internet and the port you’re using will not matter.

What about other databases?

Articles will come, but it’s mostly identical. In PostgreSQL you right click on the login groups on the left side to create a new user, then you create a database and choose that user as its owner.

You might want redis in separate containers as that’s more of a cache rather than a database.