Migrating MariaDB MySQL Server Plugin to MariaDB Docker Container

    • OMV 4.x

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Migrating MariaDB MySQL Server Plugin to MariaDB Docker Container

      Migrating MariaDB MySQL Server Plugin to MariaDB Docker Container

      In the new version of OMV 5.x, the MariaDB Plugin will most likely not exist and if you are currently using this Plugin,
      you will have to take a different approach to installing MySQL server and migrating your databases.

      One option is to manually install it like any other Debian package directly onto the OS.
      Other option is use a new (relatively to OMV) technology of Docker Container to run MySQL service.

      To learn more about what is Docker, there are many resources on the net.

      Docker needs to be installed on the Host operating system (Linux, Windows, Mac OS, etc).
      For OMV, there is Docker Plugin that provides a GUI to install and manage Docker Images & Containers for App/Services .

      The objective of this tutorial is to show you how to migrate a working MySQL OMV Plugin to a MariaDB Docker Container - a MySQL Server.

      As a follow up in Part 2, I will add Nextcloud Docker service that uses the same MariaDB Container.

      In this example, I will show how I migrated my Kodi databases running under MySQL Plugin to a Docker Container.

      PART 1

      What you will need (pre-requisites):
      - Access to OMV SSH Shell (using your favourite method - Terminal, Putty, Shellinabox, etc).
      - Docker Plugin already installed
      - A folder called /sharedfolders/docker
      - An OMV User ID already set up
      - Working Kodi system with MySQL based Library hosted on your OMV Server

      Summary of steps
      Step 1. Backup the exiting Kodi databases
      Step 2. Uninstall MySQL Plugin
      Step 3. Prepare Docker environment setup for MariaDB
      Step 4. Install MariaDB Docker
      Step 5. Import the Kodi databases into the new MySQL server
      Step 6. Test the configuration

      STEP 1
      Kodi has many versions of databases that it creates for your Media depending on the version of Kodi you are running.
      If you are like me, and have grown with Kodi (from Version 14 to the current version 18) and kept your media metadata on
      the MySQL server, then you will have several databases on your server for each different versions of Kodi.

      Additionally, you may have used the same MySQL server for other Applications too.

      So the first step is to back up all your databases so that we can import them back into the new MySQL MariaDB Docker Container.

      If you have not setup Kodi databases or any other databases, then there's nothing for you backup and can skip this step.
      Otherwise, start a shell on OMV (via Terminal on your desktop, Putty, or Shellinabox, etc).
      Enter the following command to backup your MySQL databases:

      Source Code

      1. mysqldump -u root -p --single-transaction --quick --all-databases | gzip > alldb.sql.gz
      If you had setup a root password, you will be prompted enter it.
      All your Databases, their User ID's & passwords will be saved to the compressed file alldb.sql.gz.
      Copy that file somewhere (e.g. in one of your /sharedfolders)
      BTW, it's a good practise to do this regularly.

      STEP 2
      Uninstall MySQL Plugin. Since this service will be listening on TCP Port 3306, it needs to be either stopped or uninstalled.
      Follow these steps to uninstall the MySQL Plugin.

      Screenshot 2019-03-20 at 20.06.57.png

      On the OMV GUI, navigate to the Plugins screen and find the MySQL Plugin for OpenMediaVault.
      Click the Checkbox and then click Uninstall button:

      Just to be sure that MySQL was removed, go to the terminal and run the following commands

      Shell-Script

      1. sudo apt-get remove --purge mysql-server mysql-client mysql-common -y
      2. sudo apt-get autoremove -y
      3. sudo apt-get autoclean
      4. rm -rf /etc/mysql
      5. sudo find / -iname 'mysql*' -exec rm -rf {} \;
      This may take a while as it searches your system for anything with 'mysql*' so you may want to skip this command 5 above.
      Just for good measure, reboot the OMV server though it is strictly not necessary.

      STEP 3
      Prepare Docker environment setup.

      Before we install MariaDB Docker, first we need to setup a couple of environmental elements for
      MariaDB Docker (and for future Containers that would connect to it):

      1. Host (OMV OS) folders that the MariaDB Container will access
      2. A User-defined Docker Network Bridge for connecting Containers together.

      STEP 3.1
      Docker Containers have their own file system and they are not accessible to the Host OS.
      However, Containers allows parts of their file systems to be mapped to the Host OS folders.

      First create folders which the MariaDB Container will access on your OMV file system.
      MariaDB Container requires one folders on the Host OS (e.g. in /sharedfolders) for the MariaDB config files and for the actual databases.

      The latest MariaDB Docker Image creates database folder within the Config files for you so there's no need for us to create it.

      Go to terminal and enter the following command:

      Shell-Script

      1. mkdir /sharedfolders/docker/mariadb
      I use the folder /sharedfolders/docker as a base for storing all Docker Container related data.
      If this does not exist on your system, create it. I called it docker but you can call it whatever you wish (e.g. AppData).
      This Docker folder helps in managing all the various current and future Container data in one place.

      Make a note of the path to the MariaDB folder since this will be needed in STEP 4 where we map this Host OS path to the Container folder paths like so:

      /sharedfolders/docker/mariadb -> /config

      This mapping is a portal into the Container's filesystem.

      STEP 3.2
      Next create a user-defined Docker Network bridge to which the MariaDB Container will be attached.
      The reason for setting up this network will be become apparent in Part 2.

      But basically, attaching MariaDB Container to this user-defined network will allow other Apps/Services (connected to
      this network) to use the same MariaDB Container.
      You can then refer this Container by its name instead of the IP address as Docker's DNS will resolve the name-to-address.

      Any Container you create on this network can immediately connect to other Containers on the same network.
      This network isolates Containers from other networks (including external) .
      However, you can expose and publish certain Container TCP ports on the network, allowing portions of your bridge access to an
      outside network (as we will see in the next step).

      Go to the terminal and enter the following command:

      Source Code

      1. docker network create my-docker-net
      Docker will create a user-defined called 'my-docker-net' and we will need this name in the next step.
      I named the network 'my-docker-net' but you can name it whatever you wish.

      STEP 4 in the next post.
      HP Microserver N40L; OMV 4.1.18-2;

      The post was edited 2 times, last by ryecoaaron ().

    • Migrating MariaDB MySQL Server Plugin to MariaDB Docker Container

      STEP 4


      This step assumes you have already installed the OMV Docker Plugin.
      Now we are ready to install MariaDB Docker.

      On the OMV GUI, click the Docker icon in the Services menu.

      In the search box enter linuxserver/mariadb Docker Image and once found, select it.

      In the pop-up box, click Start to download the Docker Image and once completed, click Close.
      Screenshot 2019-03-23 at 14.51.14.png

      At this stage we have downloaded a Docker Image for MariaDB.

      Now to Instantiate a Container (to start the Service), select this docker image and click on Run Image button.
      The Container configuration screen will pop-up.

      Enter a name in the Container name box (I called mine mariadb).
      Make a note of this name as you will need it in the future for other Containers to connect to MariaDB.

      Select 'Always' for the Restart Policy (restarts the Container on reboot).
      In the Networking section set the Networking mode to Bridge (should be default)


      Screenshot 2019-03-23 at 16.02.18.png


      In the Port Forwarding section, set:
      Host IP = 0.0.0.0 (should be default already)
      Host Port = 3306 (this will route any access on OMV's IP address on port 3306 to MariaDB's port 3306)
      Exposed Port = 3306/tcp (MariaDB's port 3306)
      Click "+" for each entry (do not forget to do this)

      In the Environment variables section, add the following variables (remember to you click "+" button for each)

      MYSQL_ROOT_PASSWORD = mypassword (or whatever you wish)
      PUID = 1000 (this is the User uid)
      PGID = 100 (this is the Group gid)
      TZ = Europe/London (or whatever your continent/city)

      Under Volumes and Bind mounts section, enter the path of the Host Path of the folders created in Step 3.1:

      Host Path = /sharedfolders/docker/mariadb
      Container Path = /config
      remember to click "+" button to add the entry.

      In the Extra arguments section, enter the name of the Docker Network that you created in Step 3.2:

      --network my-docker-net

      Screenshot 2019-03-23 at 15.22.32.png


      Finally, click Save to actually start the MariaDB Container which will configure itself using the configuration
      data & environment variables you provided above.

      Note: you need to have created your User ID previously and can retrieve it from terminal e.g.:

      Source Code

      1. root@OMV:~# id macnb
      2. uid=1000(macnb) gid=100(users) groups=100(users)
      3. root@OMV:~#

      STEP 5


      The MariaDB Container should have configured itself and running as a Daemon within the Container.

      Let's check it via the terminal by shelling into the MariaDB Container itself.

      This is done via a Docker command:

      Source Code

      1. docker exec -it mariadb bash
      where mariadb is the name of the MariaDB Container configured in Step 4.

      You will be presented with a new shell prompt from the MariaDB Container itself.

      Now enter the following command (and mypassword for the password when prompted):

      Source Code

      1. mysql -u root -p
      You will see the following:

      Source Code

      1. Welcome to the MariaDB monitor. Commands end with ; or \g.
      2. Your MariaDB connection id is 156
      3. Server version: 10.3.13-MariaDB-1:10.3.13+maria~bionic-log mariadb.org binary distribution
      4. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
      5. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      6. MariaDB [(none)]>
      Enter quit to exit mysql interactive session.

      You will now be back at the MariaDB Container's Shell prompt.

      If you had backed up your exiting Kodi (and other databases) in Step 1 then, import those
      databases back into MariaDB using following command (enter your root password when prompted):

      Source Code

      1. gunzip < alldb.sql.gz | mysql -u root -p
      This may take a while depending on the size of your databases.
      All your databases (including Users' ID's and their passwords) will have been imported.

      Note the following step is ONLY necessary If you do not have a Kodi database and this is the first time you are setting up Kodi database on MySQL
      then run the following commands (to create a Kodi User):

      Source Code

      1. mysql -u root -p
      At the MariaDB prompt enter the following commands:

      Source Code

      1. CREATE USER 'kodi' IDENTIFIED BY 'kodi';
      2. GRANT ALL ON *.* TO 'kodi';
      3. flush privileges;
      4. quit

      Then see this link to configure your Kodi media player to use MariaDB MySQL server.

      Enter exit to exit MariaDB Container bash shell.


      STEP 6

      There are several ways to test your new MariaDB Container.

      1. The obvious is to start your Kodi media player that had it's library on MySQL.
      It should connect to the OMV server as before and continue to access the Kodi Library metadata.

      2. Use MySQL database manager Applications such as Sequel Pro (for Mac OS)
      or MySQLWorkbench (by Oracle) for Windows, Various Linux distros and Mac OS.

      MySQLWorkbench is very powerful and thus a little more complex to use.

      That concludes Part 1.

      In Part 2, we'll add Nextcloud Container to use the same instance of MariaDB Container.
      HP Microserver N40L; OMV 4.1.18-2;
    • PART 2: Adding Nextcloud docker to existing MariaDB Docker Container

      PART 2

      As follow up to the previous guide on how to migrate MySQL server to MariaDB Docker, in Part 2 we extend the
      use of the MariaDB Container created in Part 1 for use with another service - namely Nextcloud - your personal Cloud server.

      What you will need (pre-requisites):

      - Access to OMV SSH Shell (using your favourite method - Terminal, Putty, Shellinabox, etc).
      - MariaDB Container already setup and running

      Summary of steps

      Step 1. Prepare Docker environment setup for Nextcloud Container
      Step 2. Create Nextcloud MySQL User and Database in the existing MariaDB Container
      Step 3. Install Nextcloud Docker
      Step 4. Setup Nextcloud service


      STEP 1

      First create folders which the Nextcloud Container will access on your OMV file system.
      Nextcloud Container requires TWO folders on the Host OS (e.g. in /sharedfolders) :
      one for the config files and second one to store your actual Cloud data.

      Go to terminal and enter the following command:

      Source Code

      1. mkdir /sharedfolders/docker/nextcloud
      2. mkdir /sharedfolders/docker/nextcloud/my-data

      The /sharedfolders/docker/nextcloud will map to /config within the Nextcloud container.
      The /sharedfolders/docker/nextcloud/my-data will map to /data within the Nextcloud container.

      Effectively, my-data folder will be your Cloud folder that will be presented in the Nextcloud web-gui.


      STEP 2

      Create Nextcloud MySQL User and Database within the existing MariaDB Container.

      Nextcloud Container expects that the MySQL database to be pre-prepared before it is run so we have to manually set it up.
      This is easy since we already have a running MariaDB Container (from Part 1).

      First Shell into the MariaDB Container

      Source Code

      1. docker exec -it mariadb bash
      Now enter the MariaDB Server (and enter the password when prompted):

      Source Code

      1. mysql -u root -p
      Next, create a User ID ('nextcloud') & password ('nextc-pwd') for the Nextcloud database ('nextcloud') and the database itself:

      Source Code

      1. create user 'nextcloud' identified by 'nextc-pwd';
      2. create database if not exists nextcloud;
      3. grant all privileges on nextcloud.* to 'nextcloud' identified by 'nextc-pwd';
      4. flush privileges;
      5. quit

      Make a note of the database info since these will be needed later when setting up Nextcloud GUI later:

      Nextcloud database User ID = nextcloud
      Nextcloud database User ID Password = nextc-pwd
      Nextcloud database name = nextcloud

      Exit the MariaDB Container shell by entering exit.


      STEP 3

      With the Host folders setup and the Nextcloud database & User setup, we can now configure the Nextcloud Container and run it.

      On the OMV GUI, click the Docker icon in the Services menu.
      In the search box enter linuxserver/nextcloud and once found, select it.
      In the pop-up box, click Start and once completed, click Close.

      Screenshot 2019-03-23 at 10.21.23.png

      At this stage we have downloaded a Docker Image for Nextcloud.

      Next, Instantiate the Nextcloud Container (to start the Service), select this docker image and click on Run Image button.

      The Container configuration screen will pop-up.

      Enter a name in the Container name box (I called mine nextcloud).
      Select 'Always' for the Restart Policy (restarts the Container on reboot).

      Screenshot 2019-03-23 at 12.18.14.png

      In the Networking section set the Networking mode to Bridge (should be default)

      In the Port Forwarding section, need to setup TWO Host IP addresses:

      Host IP = 0.0.0.0 (should be default already)
      Host Port = 8080 (this will route any access on OMV's IP address on port 8080 to Nextcloud's web server on port 80)
      Exposed Port = 80/tcp (Nextcloud's port 80)
      Click "+" to save the entry (do not forget to do this)

      Host IP = 0.0.0.0 (should be default already)
      Host Port = 444 (this will route any access on OMV's IP address on port 444 to Nextcloud's web server on port 443)
      Exposed Port = 443/tcp (Nextcloud's port 443)
      Click "+" to save the entry (do not forget to do this)

      In the Environment variables section, there's nothing to add but adding PUID & PGID is recommended as it will allow your OMV User ID to access the Nextcloud Host folder remotely (via SMB, AFP, etc). Also add the TimeZone

      PUID = 1000 (this is the User uid)
      PGID = 100 (this is the Group gid)
      TZ = Europe/London (or whatever your continent/city)

      Under Volumes and Bind mounts section, enter the path of the Host Path of the folders created in Step 3.1:

      Host Path = /sharedfolders/docker/nextcloud
      Container Path = /config
      Click "+" button to add the path

      Host Path = /sharedfolders/docker/nextcloud/my-data
      Container Path = /data
      Click "+" button to add the path

      In the Extra arguments section, enter the name of the Docker Network that was created in Part 1:

      --network my-docker-net

      This is the docker network name on which the MariaDB Container is also running.

      Screenshot 2019-03-24 at 17.13.59.png

      Finally, click Save to actually start the Nextcloud Container which will configure itself using the configuration data &
      environment variables you provided above. This will take a while so wait for several minutes so be patient.


      STEP 4

      In your browser enter https: //your-OMV-IP-ADDRESS:444

      You will receive a warning since SSL certificates are not setup but it's OK to proceed to the Nextcloud site.
      (On Chrome browser, click Advance and allow it to proceed to the site). E.g.:
      Screenshot 2019-03-23 at 12.48.39.png
      Once the Nextcloud Home page appears, it will ask you create an Admin account (name & password), enter the
      Nextcloud database name, database User ID, database password and the hostname of database server.

      Screenshot 2019-03-23 at 12.53.17.png

      For the Admin account name & password, enter whatever you wish but REMEMBER the Admin password !
      For database configuration, enter the following information (from STEP 2):

      Database User = nextcloud
      Database password = nextc-pwd
      Database name = nextcloud
      localhost = mariadb

      BTW, for the localhost we can use mariadb because the Nextcloud Container is on the SAME network (my-docker-net) as the MariaDB Container.
      There’s no need worry about or hardcode IP Addresses since Docker’s network DNS takes care Name-to-IP translation.

      Next, click the Finish Setup button.

      It will take a while to complete this setup and you will be presented with the login screen where you enter the Admin user name and password.

      Note: You may see an NGIX error page instead of the login screen.
      In this case, wait a few minutes and refresh the browser screen and should then see the Login Screen.

      Login and manage your documents.

      To put your Nextcloud server onto the public internet, you will need a static IP address from your Service Provider and then Install an Encryption service such as Letsencrypt. Of course there's a Docker Image for that too.

      See excellent video's by @TechnoDadLife which show you how to do that.

      Extras:

      Useful MySQL commands:

      Source Code

      1. show databases;
      2. SELECT User FROM mysql.user;
      3. select User, Host from mysql.user;
      4. desc mysql.user;
      5. SHOW GRANTS FOR 'root'@'%';use mysql;
      6. SELECT host, user, password FROM user WHERE user = 'root';
      7. SELECT host, user, password FROM user WHERE host = '%';
      8. Show variables where variable_name like "bind%";
      9. drop user a_user_id;
      10. drop database my_database;

      Docker commands:

      Source Code

      1. docker exec -it container_name bash
      2. docker restart container_name
      3. docker network ls
      4. docker inspect network your_network_name
      Files
      HP Microserver N40L; OMV 4.1.18-2;

      The post was edited 1 time, last by macnb: Added optional PUID & PGID environmental variables ().