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:

    Code
    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

    Bash
    sudo apt-get remove --purge mysql-server mysql-client mysql-common -y
    sudo apt-get autoremove -y
    sudo apt-get autoclean
    rm -rf /etc/mysql
    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:

    Bash
    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:

    Code
    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.

  • 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.:


    Code
    root@OMV:~# id macnb
    uid=1000(macnb) gid=100(users) groups=100(users)
    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:

    Code
    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):

    Code
    mysql -u root -p

    You will see the following:


    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):

    Code
    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):

    Code
    mysql -u root -p

    At the MariaDB prompt enter the following commands:


    Code
    CREATE USER 'kodi' IDENTIFIED BY 'kodi';
    GRANT ALL ON *.* TO 'kodi';
    flush privileges;
    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.

  • 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:


    Code
    mkdir /sharedfolders/docker/nextcloud
    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

    Code
    docker exec -it mariadb bash

    Now enter the MariaDB Server (and enter the password when prompted):


    Code
    mysql -u root -p

    Next, create a User ID ('nextcloud') & password ('nextc-pwd') for the Nextcloud database ('nextcloud') and the database itself:


    Code
    create user 'nextcloud' identified by 'nextc-pwd';
    create database if not exists nextcloud;
    grant all privileges on nextcloud.* to 'nextcloud' identified by 'nextc-pwd';
    flush privileges;
    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:

    Code
    show databases;
    SELECT User FROM mysql.user;
    select User, Host from mysql.user;
    desc mysql.user;
    SHOW GRANTS FOR 'root'@'%';use mysql;
    SELECT host, user, password FROM user WHERE user = 'root';
    SELECT host, user, password FROM user WHERE host = '%';
    Show variables where variable_name like "bind%";
    drop user a_user_id;
    drop database my_database;


    Docker commands:

    Code
    docker exec -it container_name bash
    docker restart container_name
    docker network ls
    docker inspect network your_network_name

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!