Coding with Titans

so breaking things happens constantly, but never on purpose

HowTo: MariaDB in Docker on Raspberry Pi

in  , , , ,

Originally I wanted to replace my very old Synology NAS (from 2011 with 4 HDD drives) with something newer. Then after listing, why I would need it in the first place - I realized I could immediatelly unplug the power out of it, if only I have had transferred a single service out of it - folder synchronization. This is the way I easily move my docs between macOS, PC and mobile and somehow I never trusted free services like OneDrive, Google Drive nor DropBox to do this job. I like to be the only viewer of my private data and I like to do backups. So after a small research I wanted to give NextCloud a try and host it myself.

What I need here to continue is:

And as usual setting it all up haven’t been so straigforward as I initially expected. But first things first - let’s start from setup the database up at least today.

Info

Let’s print some current info.

Since I already had a Raspberry Pi 4 running from an SSD (connected via USB 3.0, detains here), I will only display a reference of the system. You can use Raspberry Pi Imager to prepare everything and then move to OS from SD-card fully to your disc for faster booting and reliability with no problem following any online tutorial (one here).

My current system (patched to latest stuff) has following details:

$ uname -a

Linux YellowStar 5.10.60-v7l+ #1449 SMP Wed Aug 25 15:00:44 BST 2021 armv7l GNU/Linux

It’s YellowStar since I’ve built the yellow case for it with my kids.

Docker on Raspberry Pi

There are also planty of tutorials on how to install Docker on Raspberry Pi (like here, here or here).

Simplest is to use a dedicated script from docker.com website:

$ curl -fsSL https://get.docker.com -o get-docker.sh
$ sudo sh get-docker.sh

and add pi user to proper group not to have always type sudo in front of every docker command:

$ sudo usermod -aG docker pi

Now is the moment, where the easy stuff ends. Turned out that running MySql or MariaDB inside this docker above is really painful.

Why? - would you ask. Because Oracle (owner of MySql) or MariaDB (fork of original MySql project) don’t release official ARM32v7 docker images!

MariaDB

I potentially have options now:

  • build the docker image myself
  • download an image built by someone else.

Image could be installed from docker.com/linuxserver/mariadb.

docker run -d --restart unless-stopped --name mariadb -v /mnt/mariadb/data:/var/lib/mysql -v /mnt/mariadb/config:/config -e "MYSQL_ROOT_PASSWORD=SECURE_PASSWORD" -p 3306:3306 linuxserver/mariadb:alpine

docker exec -it mariadb bash

root@3117bc7beac6:/# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)
root@3117bc7beac6:/# mysql -h 127.0.0.1 -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)
root@3117bc7beac6:/# mysql -h 127.0.0.1 -P 3306 --protocol=TCP -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)
root@3117bc7beac6:/# ./client/mysql
bash: ./client/mysql: No such file or directory
root@3117bc7beac6:/# mysql --host=localhost --protocol=tcp --port=3306 test
ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (99)
root@3117bc7beac6:/# mysql --host=localhost --protocol=tcp --port=3306 root
ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (99)
root@3117bc7beac6:/# mysql --host=localhost --protocol=tcp --port=3306 root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (99)

$ docker logs -f mariadb

Brought to you by linuxserver.io
-------------------------------------

To support LSIO projects visit:
https://www.linuxserver.io/donate/
-------------------------------------
GID/UID
-------------------------------------

User uid:    911
User gid:    911
-------------------------------------


@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Your DockerHost is most likely running an outdated version of libseccomp

To fix this, please visit https://docs.linuxserver.io/faq#libseccomp

Some apps might not behave correctly without this

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

[cont-init.d] 10-adduser: exited 0.
[cont-init.d] 30-config: executing... 
[cont-init.d] 30-config: exited 0.
[cont-init.d] 40-initialise-db: executing... 
Setting Up Initial Databases
Installing MariaDB/MySQL system tables in '/config/databases' ...
578018-03-21 22:44:31 0 [ERROR] This MySQL server doesn't support dates later than 2038

Installation of system tables failed!  Examine the logs in
/config/databases for more information.

Update recipe could be found on LinuxServer.io.

For latest versions visit pkg repo.

$ wget http://ftp.us.debian.org/debian/pool/main/libs/libseccomp/libseccomp2_2.5.1-1_armhf.deb
$ wget http://ftp.us.debian.org/debian/pool/main/libs/libseccomp/libseccomp-dev_2.5.1-1_armhf.deb

$ sudo dpkg -i libseccomp2_2.5.1-1_armhf.deb
$ sudo dpkg -i libseccomp-dev_2.5.1-1_armhf.deb

pi@YellowStar:~ $ sudo dpkg -i libseccomp2_2.5.1-1_armhf.deb 
(Reading database ... 52997 files and directories currently installed.)
Preparing to unpack libseccomp2_2.5.1-1_armhf.deb ...
Unpacking libseccomp2:armhf (2.5.1-1) over (2.3.3-4) ...
Setting up libseccomp2:armhf (2.5.1-1) ...
Processing triggers for libc-bin (2.28-10+rpt2+rpi1) ...
pi@YellowStar:~ $ sudo dpkg -i libseccomp-dev_2.5.1-1_armhf.deb 
(Reading database ... 52997 files and directories currently installed.)
Preparing to unpack libseccomp-dev_2.5.1-1_armhf.deb ...
Unpacking libseccomp-dev:armhf (2.5.1-1) over (2.5.1-1) ...
Setting up libseccomp-dev:armhf (2.5.1-1) ...
Processing triggers for man-db (2.8.5-2) ...

Bonus content

Once you established a connection with your database engine running in a docker, you can use following set of commands to create a new database and dedicated user:

-- create new db
CREATE DATABASE `nextcloud`;

-- create new user for new db
CREATE USER 'nextcloud_user' IDENTIFIED BY 'SECRET_PASSWORD';
GRANT ALL privileges ON `nextcloud`.* TO 'nextcloud_user'@'%';
FLUSH PRIVILEGES;

-- review permissions
SHOW GRANTS FOR 'nextcloud_user'@'%';

That should be fine.

NextCloud

Start a docker image with following parameters:

$ docker run -d --restart unless-stopped --name "nextcloud" -v /mnt/nextcloud/data:/var/www/html -p 5050:80 nextcloud

Content of the proxy redirect: /etc/nginx/sites-enabled/nextcloud

server {
    listen       80;
    server_name  <nextcloud_public_host>;
    return       301 https://<nextcloud_public_host>$request_uri;
}

server {
    listen       443;
    server_name  <nextcloud_public_host>;

    location / {
        proxy_pass http://<docker_machine_hosting_nextcloud_ip>:<nextcloud_port>;
        client_max_body_size 100M;
    }
}