Shared database for my 2 vero 4k+ boxes

Hi I’m new on here, i currently have 2 x vero4k+ boxes which i use with a unraid server, what’s the best way of going about a shared database?
I’m not bad with computers but never used mysql before and configured this!
I know there’s a wiki on how to do it but am i correct in setting up mysql on unraid and having the database on there, i currently have all my media linked to my unraid server… What’s the best way, can anyone help.?.. Thanks

Generally it makes sense to have the shared MySQL server on the same server where your Media is stored. So yes best to install it on the unraid server.
Alternatively you also can install the MySQL server on one of the two Vero’s but then you depend on that machine if you want to watch from the other. So better to do on the Unraid Server

Has anyone got a idiot proof video or instructions on how to do this.? Lol I’ve tried to follow HOW To guides but to be honest it’s clear as mud…be great if anyone has… I’ve found the MYSQL docker on unraid and installed it and opened up the console but after that I’m sunk… Any help would be much appreciated :blush:

1 Like

Are you still working on this? I have several boxes sharing the one database (Mariadb) and in a container.

I have some notes on what I did so if you still need help let me know and I will see what I can do.

Hi mate yes it’d be good if you could send me any notes on a how to guide… I’d kinda given up lol thanks for the offer of help… I can use the mariadb docker in unraid so thsts no problem

Part 1

I’m using OpenMediaVault (OMV) as my server. I have four Kodi boxes (3 OSMC and 1 Libreelec) accessing the database. I use mariadb as the central SQL server. I am accessing my devices from a mac, using terminal commands.

Configuring KODI:

You have to tell/configure each Kodi box to access the central database instead of its local db. This is done by editing /home/osmc/.kodi/userdata/advancedsettings.xml.

  1. Start a ‘terminal’ session and connect via ‘ssh’ to your Kodi box:

ssh osmc@‘your_kodi_IP_address’

  1. Change into the userdata directory

cd .kodi/userdata

  1. Check you are in the correct place

pwd

  1. You should get /home/osmc/.kodi/userdata in return so we are in the correct directory.
  2. Backup up your current advancedsettings.xml file - Enter:

cp advancedsettings.xml advancedsettings-‘today’s date’

  1. Edit the advancedsettings.xmlfile with your favourite text editor. You create two entries - <videodatabase> and <musicdatabase>:

     <videodatabase>
     	<name>MyVideos</name>
     	<host>your_server_ip_addr</host>
     	<user>kodi</user>
     	<pass>kodi</pass>
     	<type>mysql</type>
     	<port>3306</port>
     </videodatabase>
     <musicdatabase>
     	<name>MyMusic</name>
     	<host>your_server_ip_addr</host>
     	<user>kodi</user>
     	<pass>kodi</pass>
     	<type>mysql</type>
     	<port>3306</port>
     </musicdatabase>
    
  2. Save the file. Once we restart Kodi, this file will be active. If you need to go back to local databases, rename the advancedsettings.xml file to something else, and rename your ‘date’ file back to advancedsettings.xml.

Part 2:

Installing Docker:

In OMV, it is a plugin that you enable - part of OMV-Extras. For other systems, you will have to install Docker.

Installing Mariadb:

I had a problem setting the root password. You should be able to pass the environment variables in the yaml file below but I couldn’t get it to work. Nothing I did would allow me to access the mariadb until I did the creation as follows (From this link: Change root password in MariaDB Docker container running with docker-compose · GitHub):

1> Create a YAML file named ‘docker-compose.yml’ using your favourite text editor. Format is important. Mine looks like this:

    version: '3.3'

    services:

    mariadb:

    image: mariadb:latest

    container_name: mariadb

    ports:

    - 3306:3306

    restart: always

    entrypoint: mysqld_safe --skip-grant-tables --user=mysql

    volumes:

    - /srv/dev-disk-by-uuid-c75776fd-72f7-470a-8d4f-71bcb020bed3/Configs/mariadb:/var/lib/mysql

This file will pull down the latest version of mariadb, create a container named mariadb using ports 3306 and will always restart on startup. The volumes statement is for my particular config, and is a pointer or redirecting outside of the container to where I want my /var/lib/mysql to exist (on my raid drive as opposed to the default system drive).

Start it:

$> docker-compose up -d

Then login to the Docker container:

$> sudo docker exec -ti mariadb bash

And login as root without password:

$> mysql -u root -p

Change the root password in mysql cli:

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY 'your_secret_pwd’;

mysql> FLUSH PRIVILEGES;

Logout of mysql and the Docker container (2x exit), remove the entrypoint line from the docker-compose.yml and reload the Docker Composer stack:

$> docker-compose up -d

You can now login to the MariaDB container and connect to the database with the new root password:

$> sudo docker exec -ti mariadb bash

$> mysql -u root -p

At this point, my install was done and good to go because my database and kodi credentials all existed in the database, it was just the operating system and database software I was reinstalling.

I think you will have to create user kodi, password kodi (to match what you have in the advancedsetings.xml file) in order for kodi to access/create the db.

Part 3:

The Nebulous:

You need to create the kodi user to access the db as well as the IP address for the db to listen to. Follow the steps in the wiki on Kodi: MySQL/Setting up MySQL - Official Kodi Wiki

Some commands to check your container (don’t remember where I got this to give credit):

remove a container:

docker rm CONTAINER_ID

List containers:

docker container ls

A list of running containers:

docker ps

Docker allows us to restart a container with a single command:

docker restart mariadb

The container can also be stopped like this:

docker stop mariadb

In case we want to destroy a container, perhaps because the image does not suit our needs, we can stop it and then run:

docker rm mariadb

Note that the command above does not destroy the data volume that Docker has created for /var/lib/mysql. If you want to destroy the volume as well, use:

docker rm -v mariadb

If the container doesn’t start, or is not working properly, we can investigate with the following command:

docker logs mariadb

To access the container via Bash, we can run this command (it = interactive terminal):

docker exec -it mariadb bash

Find the IP address that has been assigned to the container:

docker inspect -f ‘{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ mariadb

I hope this helps and I’m a noob at this.

Right… I’ve ssh in OK to one vero box OK useing putty, i put in…
cd .kodi/userdata…
Which seemed to work i put in the… cp advancedsettings.xml advancedsettings-‘today’s date’… Nothing happened i had no confirmation that it backed up any file?.. I saw nothing pop up of any description… Any ideas.?

You can try, in your putty window:
ls -al

That should show two advance settings files.

OK thanks I’ll try that… :wink:

Right ok, I’ve tried the ls-al command and it brought up files OK including a couple of. Xml files but there were no advanced settings files there!
In the command above…
cp advancedsettings.xml advancedsettings-‘today’s date’
, i take it i am doing it correctly by putting today’s actual date in.?
Which i am doing.!
I can not see any advancedsettings.xml files there at all.?
Help lol :joy:

if you have not previously created an advancedsettings.xml file, then you would not have one yet. So, just # vi advancedsettings.xml and create it.
Heres the documentation for the advancedsettings.

https://kodi.wiki/view/Advancedsettings.xml
or this wiki for the mysql
https://kodi.wiki/view/MySQL/Advanced_notes

You may find that useful. Once you are using a common, remote database, it’s also not unusual to also need to do things like path mapping if some clients have the media mounted locally, and others don’t (or mounted on different local paths)

You didn’t say, but if you are using profiles (different people have their own logins, so that they can track their watched files independently, or even have different collections of media). then you’d create an advancedsettings.xml file for each user under /home/osmc/.kodi/userdata/profiles/[profilename]/advancedsettings.xml (and you might set the databasename differently for each user in that case

<name>Kevsvideos</name>
vs
<name>wifesvideos</name>

Many thanks guys… I’ll try again!