Setting Up Multi-Device Shared Library (mySQL) **newb perspective**


#1

In order to increase the WAF (wife acceptance factor :wink:) of my system, I wanted to have my three Vero4K+ boxes share the same library to achieve:

  • “watched state” synced on all devices
  • pause (on one device) & resume (on another device)

There is the side benefit of slightly reduced storage space overall, and perhaps saving that space on each box helps it run a little better over the long term. {It’s jumping to the end, but at the end, there are two large files on each device (MyMusic###.db and MyVideos###.db) that can be safely deleted. However the biggest file called Textures13.db …it sores images/thumbnails, I believe… must remain local, and so it’s not much of a benefit, if any.}

Anyways, I got it working and the drama as it unfolded is here.
This post is intended to provide feedback on the process, and perhaps help others.

About me: I should be considered a newb. I haven’t much experience with Kodi, and I don’t know linux code. However, I suppose I felt computer savvy enough that I could probably follow instructions and get through it. I fully expect any experts that read this will laugh at my ignorance and I ask them to please reply & correct any egregious errors.

About my system:

  • 3 Vero 4K+ boxes (upgraded from only one Vero4K; sold it; goodbye LED light)
  • Network Attached Storage (NAS), in my case, an older QNAP TS-121
  • Windows PC
  • Ethernet runs to/between all devices

About my content:

  • movies, TV shows
  • large music library
  • that’s all I use the boxes for

After asking for help, I was referred to: https://kodi.wiki/view/MySQL …it helps you install mySQL.

Step 2 of those instructions states:
You’ll need to (1) choose which of your computers, Kodi devices, or NAS will be the MySQL server. (2a) The server needs to be on 24/7, or (2b) have wake-on-lan (preferably the former), and (3) needs to have a local static IP.

(1) Choose “server”: in my case, it’s my NAS
(2a) On 24/7: yep
(3) A long time ago I assigned it a static IP address. This was done in my router settings. I forget the details to be honest, but I had put a sticker on the device to always remind myself what it is.

I got stuck at that point because I didn’t know how to install mySQL on my QNAP/NAS and I didn’t know which of the eight sub-instructions to use. After internet searches about my NAS, I learned it runs linux, but still was not sure which version of it – Ubuntu? Arch? RedHat? Well, I got lucky because the firmware of the NAS itself, came with an option inside its Control Panel, to install & enable mySQL. It was an older version of mySQL but I went with it.

More specifically:
On my QNAP NAS, in its Control Panel, the option to enable mySQL showed that it would be v5.1.73. It has an ‘Enable’ box to check… done. Under this I can optionally also Enable TCP/IP networking… done (as instructed on this forum). I was then instructed on this forum: The port should default to 3306. If not, set it to 3306. …done

Just FYI, my understanding of the linux instructions …remember, I didn’t have to follow them… is that you need to use something like PuTTY, open an SSH session into your server/device, log in, and then issue the commands as instructed. Sorry if this isn’t quite right. There is a place in the Control Panel of my QNAP where I can enable SSH. I tried it briefly, and tried to issue the first command in the instructions, but it didn’t work. More specifically, read this helpful tip. Also read this.

Next, the searches about putting mySQL on my NAS indicated I could install another program to help me administer it, called phpmyAdmin. Again, it was an older version – required because the version of mySQL was older – but I went with it. There is probably a way to “force” an installation of the newer mySQL and the newer phpmyadmin onto my QNAP NAS, but I was not sure how to do that.

So, to recap, at this point I had installed mySQL and phpmyadmin onto my NAS = QNAP = server.

The next step was to set up a “user” within mySQL so that each of the Vero4K boxes could access mySQL running on the server and do what they needed to do. The instructions for this were within the linux instructions, at step 6:

  1. Enter the following commands:
    1. Type in: CREATE USER 'kodi' IDENTIFIED BY 'kodi'; and press return
    2. Type in: GRANT ALL ON *.* TO 'kodi'; and press return
    3. Type in: flush privileges; and press return

{You do not need to use 'kodi', this is not a magical username; instead you could use 'osmc' or anything else you want. Just remember what you used and be consistent.}

I used phpmyadmin because it has a place (a tab) specifically for issuing these commands. It also has another place (another tab) where one can create users, I think, but I just blindly followed the instructions and then when I looked back on the users tab, the user 'kodi' was there.

The next step in the instructions is a doozy. You have to make your media files accessible to all your Kodi devices by sharing them on the network through file sharing. Well, mine were already accessible. I think through Windows/Microsoft Networking, SMB or SAMBA. I’m not really sure. That is a whole other topic – you can read the saga to see a little bit of how much of a bumbling fool I am in that area. Actually, within the Vero4K+ I used the option called ZeroConf . Read more here.

Next: export your library (optional). Those instructions are here. I recommend that you do this for your videos but NOT for your music, unless you have only a modest size music library. Mine is large and it took a very long time to export it. If you content is stored centrally, like mine is, you only have to export the library once, from one device.

Now you will create an XML file – it is just a text file – called advancedsettings.xml . You need to copy & paste the text into a text editor – and be sure to make the changes edits as instructed. I believe something as simple as NOTEPAD would work, but I used jEdit because of some past experience with it – and it displays ‘code’ like this better.

Once you’ve saved the XML file, you are instructed to: Copy this advancedsettings.xml file you just created to the userdata folder of every Kodi install you want to sync with.

Well, you can directly copy & paste it into your Vero4K+. In Windows File Explorer, go into options and make sure you can see ‘hidden’ files & folders. Then type in the network address: \\_____\osmc\.kodi\userdata where you replace the underscores with the network name of your Vero4K / OSMC device.

At this point, you should choose a primary OSMC device (mine are all Vero4K+), and shut down the others. Reboot the primary device. The advancedsettings.xml file kicks in, and tells it to look for the library on mySQL on your NAS=QNAP=server. But since it’s the first time it booted with those settings, you’ll have nothing there.

Then, follow the next step (import) if you previously exported. Personally, I tried to import and could not figure out how to do it, so I punted and just told it to rescan all my sources – rebuild the library from scratch. If you didn’t export, then you’ll have to force the primary OSMC device to update its library. Again, focus on your videos first. If you invoke a music library update, you have to wait a long time.

At this point, you are basically done. You can speed things up by following the final instructions (here). However, I have the impression that if you didn’t, when you power on the other devices they would get everything done on their own.

If you want to use the GUI on your OSMC device to confirm that it’s working, under MyOSMC, there is a Network icon, and under that, you can see mySQL. Selecting that item brings up a screen to view details about the shared library. It’s pretty much what is typed into advancedsettings.xml . It appears you can arrow over and click or select items to enable/disable stuff, and/or edit things.

Also, it’s now that you can finally go back to each userdata folder, and safely delete the two files MyMusic###.db and MyVideos###.db but NOT the one called Textures13.db .

Good luck all.


Shared storage of media info, watched (etc) for multiple Vero4k+