Any knowledgeable Kodi MySQL users please?

ArcticWolf

Trusted Helper
#1
First up, I've NEVER used MySQL or anything like it before, so please be gentle ;). Don't go too mad with the whole tech aspect of it up front ;).

Right now, I have 2 x Synology DS418 NAS boxes on my home network but that will hopefully be increased to 3 x DS418's over the coming days as well. (It's my limited understanding that I'd need to use MariaDB as the MySQL server on a Syno NAS?) Additionally, I use the DSM Web UI to manage all aspects of the NAS's themselves, (no SSH/terminal based access etc.). Presumably, PHPMyAdmin is going to be my best option there too rather than messing around (or messing up) with command lines. (I used to use SSH (PuTTY etc.) to remotely compile/admin IRC servers 15-20 years ago but I've not had anything to do with Linux systems for so long all of that knowledge is just completely gone now :(.) If it affects MySQL config/access too, the firewalls on the NAS's are also enabled/configured to only allow LAN access.

Obviously, I have several individual devices that are all running the same base version of Kodi (18). They've all been individually configured to have the same libraries from the shares on my NAS boxes. However, it just isn't an option to keep each individual library updated to include the "my ratings" of my movie/TV show collection, or marking media as watched, tracking progress, etc., etc.

As such.... I'd like to attempt setting up a centralised MySQL system
.

I've been reading/viewing several different online guides/tutorials for setting up MySQL, but they're all essentially guides for setting up MySQL with a single NAS!!! What I can't find is any info for setting up MySQL if multiple NAS's are in use.

How would that work please? How do I configure MySQL if I have media stored in several different directories across all three NAS boxes? Would I need to install MariaDB on all of the NAS's & each NAS would then become a server & create its own DB? Or, do I still just install MariaDB on one of the NAS's & a single NAS runs as the MySQL server?

I might have stood a chance of successfully setting up MySQL by myself if I was simply following a step by step tutorial & just using a single NAS lol ;). However, having multiple NAS's in use obviously complicates things.

Any help or info much appreciated :).

Cheers,
Wolfy.
 

Skynet

Administrator
#2
Hi Wolfy,

I'm not a database specialist but you can try this :

- Configure a fixed IP address to all Nas (same Lan, nothing else to do)
- Configure only One database on one NAS
- All Kodi database must point to the Nas's one.
- You can even synchronize Msql database to others nas if you want to ;)

Also you can read this before : https://kodi.wiki/view/MySQL/Setting_up_MySQL
 

ArcticWolf

Trusted Helper
#3
Hi Wolfy,

I'm not a database specialist but you can try this :

- Configure a fixed IP address to all Nas (same Lan, nothing else to do)
- Configure only One database on one NAS
- All Kodi database must point to the Nas's one.
- You can even synchronize Msql database to others nas if you want to ;)

Also you can read this before : https://kodi.wiki/view/MySQL/Setting_up_MySQL
Ello mate,

Thanks for the info :).

1. ALL of my devices already have static IP's on the LAN where possible. (Otherwise, if a given device doesn't have the facility to configure a static IP itself, it has a reserved IP via my router.). With the number of devices I now have it makes life a lot easier, but I've used static IP's for my network devices for years anyway :).

2. OK, understood. You're saying to proceed with just one NAS acting as the MySQL (MariaDB) server. What I don't get is how I configure everything so the media on my other NAS boxes is also added to the central DB?????

3. As I understand it. I set up the MySQL server. Then, essentially configure the library using Kodi on one device & just copy the sources & advancedsettings.xml files from that original copy of Kodi into any other Kodi device I want as a client?

4. Not worried about synchronisation of the DB to the other NAS boxes, I just want their shares to be included in the main DB if that makes sense lol :)???

5. Thanks. I've already read through the Kodi MySQL wiki/setup pages several times (for multiple OS's in case they had any extra clues), but it isn't very clear on having multiple devices as shares for the DB/library. Some of it also seems to be out of date for Kodi 18 too. I've also read through 15-20 step by step guides & watched a load of video tutorials, but unfortunately they ALL go through the setup with a single NAS/share, not multiple. NAS's/shares.

When I get a chance I'll give it a go. I'll just have to wipe it all & start over if it gets messed up. Given that the new NAS 3 will be empty & completely fresh, I think I'll use that as the MySQL server once it's initially set up, (it'll save messing everything up on the 2 existing NAS boxes). I'll also use a U9-H to act as the initial client, same applies, if that gets messed up I can just factory reset lol.

Oh well, hope I maybe work something out as I go. Cheers lol ;).
 

ArcticWolf

Trusted Helper
#5
A sql server could be reached by a fixed ip address, a port, and IDs.
If all your devices share the same LAN then all of them can access this database.

I never configured a kodi database this way but it makes sense.

I found a good tutorial but in french. Maybe you can translate the webpage ...
https://technotips.fr/132-kodi-xbmc-creer-base-donnees-centralisee/
Yes, the server & all client devices will be on the same LAN, same IP range, etc. No devices outside of my LAN IP range will be used!!

That guide is pretty much identical to all of the other guides I've read so far (thanks :)). It basically covers setting up MySQL with one NAS!! Unfortunately, like several other guides I've read, it's for older versions of Kodi and/or DSM too.

I'm hoping that once I basically go through the initial setup with the first Kodi client, add my sources from all three NAS boxes & alter my advancedsettings file, it will simply add them all to the sources.xml file & build the DB. Then I can just copy the sources.xml & advancedsettings.xml files to all of the other Kodi clients & job done.

Whether it's that simple remains to be seen lol ;)!!

YIKES!!
 

notrui

Trusted Helper
#6

I am also not a database specialist, you can try this with my fellow Portuguese Roberto Jorge if you want a kodi server on a NAS.
My knowledge is in management informatics.
I like computer network a lot.
See if that link helps you.
I agree with static ip to all NAS also.
 
Last edited:

ArcticWolf

Trusted Helper
#7

I am also not a database specialist, you can try this with my fellow Portuguese Roberto Jorge if you want a kodi server on a NAS.
My knowledge is in management informatics.
I like computer network a lot.
See if that link helps you.
I agree with static ip to all NAS also.
Thanks :). However, I've watched several MySQL tutorials for Syno NAS's that are exactly the same. I know I need MariaDB & PHPMyAdmin, the Syno NAS's are the same. I understand how to get that far :)!!

But, my point is (as he discusses in his video) is that he's setting up a SINGLE NAS only, especially the configuration of the advancedsettings.xml file!!

What if I have movies sourced upon multiple NAS boxes (which each has multiple directories)? How do I enter the IP for each source NAS or do I even need to enter the IP for each NAS in the advancedsettings.xml file?

How does the DB/library compile itself from multiple source NAS boxes if I just choose one NAS box as the MySQL server?

That's the info I need & what I'm stuck on :)!!!
 

notrui

Trusted Helper
#8
Thanks :). However, I've watched several MySQL tutorials for Syno NAS's that are exactly the same. I know I need MariaDB & PHPMyAdmin, the Syno NAS's are the same. I understand how to get that far :)!!

But, my point is (as he discusses in his video) is that he's setting up a SINGLE NAS only, especially the configuration of the advancedsettings.xml file!!

What if I have movies sourced upon multiple NAS boxes (which each has multiple directories)? How do I enter the IP for each source NAS or do I even need to enter the IP for each NAS in the advancedsettings.xml file?

How does the DB/library compile itself from multiple source NAS boxes if I just choose one NAS box as the MySQL server?

That's the info I need & what I'm stuck on :)!!!
Yes i know, in a single NAS is easy. For multiples NAS it is much harder and complicated . Ideal was if you can join all the info (multiple hard drives) into one volume (one hard drive), and then is just acess that one hard drive (one volume).
I have only one NAS, and my hard drives are formartted in standard mode (in my case two hard drives).
I dont know if you use raid mode, in raid 5 for example you can have 3 hard drives working as a single one (single volume)
If you have for example 3 NAS you can put that hard drives in raid mode, then is easier to configure everything. I dont know if you understand my idea, sorry for my english ☺
 
Last edited:

ArcticWolf

Trusted Helper
#9
Yes i know, in a single NAS is easy. For multiples NAS it is much harder and complicated . Ideal was if you can join all the info (multiple hard drives) into one volume (one hard drive), and then is just acess that one hard drive (one volume).
I have only one NAS, and my hard drives are formartted in standard mode (in my case two hard drives).
I dont know if you use raid mode, in raid 5 for example you can have 3 hard drives working as a single one (single volume)
If you have for example 3 NAS you can put that hard drives in raid mode, then is easier to configure everything. I dont know if you understand my idea, sorry for my english ☺
Thanks :).

Yes, I fully understand what you mean. You're just using JBOD with your 2 bay NAS, no RAID array :).

Each of my 4 bay NAS boxes are configured to use SHR (Synology Hybrid Raid) which isn't dissimilar to RAID 5, just more options & features, including different sized HDD's in the RAID array rather than them all having to be equal with RAID 5. All 4 drives in each NAS are already one large volume as well :).

Obviously, each NAS has its own IP etc., they are essentially separate devices as far as Kodi is concerned.

I'm hoping that once I've setup the MySQL server & configure the first Kodi client with all of the sources from all 3 NAS boxes (as I normally would if I just had one Kodi device), that it will simply add all of the individual sources to the sources.xml file for Kodi. Then I just copy that file into every other Kodi client & the sources are instantly added with no further fuss!!???????

Was hoping to do it all soon but I have things going on with family right now which obviously takes priority!! Will attempt it when I get a chance ;).
 

notrui

Trusted Helper
#10
Thanks :).

Yes, I fully understand what you mean. You're just using JBOD with your 2 bay NAS, no RAID array :).

Each of my 4 bay NAS boxes are configured to use SHR (Synology Hybrid Raid) which isn't dissimilar to RAID 5, just more options & features, including different sized HDD's in the RAID array rather than them all having to be equal with RAID 5. All 4 drives in each NAS are already one large volume as well :).

Obviously, each NAS has its own IP etc., they are essentially separate devices as far as Kodi is concerned.

I'm hoping that once I've setup the MySQL server & configure the first Kodi client with all of the sources from all 3 NAS boxes (as I normally would if I just had one Kodi device), that it will simply add all of the individual sources to the sources.xml file for Kodi. Then I just copy that file into every other Kodi client & the sources are instantly added with no further fuss!!???????

Was hoping to do it all soon but I have things going on with family right now which obviously takes priority!! Will attempt it when I get a chance ;).
No. I dont use JBOD , I use standard configuration. My hard drives had different sizes, was the option i choosed when i setup it up a time ago. All your NAS are very good. For me and for now my two bay dns320l (dlink) is enough.
 
Last edited:

ArcticWolf

Trusted Helper
#11
My MySQL setup is now all up & running.

Asked around on some other forums & it turns out that I was completely overthinking how the initial setup worked with multiple sources lol ;).

Once I had the server installed & configured it was just a case of copying the advancedsettings.xml & sources.xml files into any additional Kodi clients, then Kodi just does the rest by itself :).

Bit of faffing around initially but not as complicated to achieve as I thought. Using PHPMyAdmin & fully understanding databases is another matter entirely though lol. That is definitely more complicated!!! Will definitely need to learn how to back it all up before I inevitability mess it up ;).

Thanks for all the original responses, however, much appreciated :).
 

notrui

Trusted Helper
#12
My MySQL setup is now all up & running.

Asked around on some other forums & it turns out that I was completely overthinking how the initial setup worked with multiple sources lol ;).

Once I had the server installed & configured it was just a case of copying the advancedsettings.xml & sources.xml files into any additional Kodi clients, then Kodi just does the rest by itself :).

Bit of faffing around initially but not as complicated to achieve as I thought. Using PHPMyAdmin & fully understanding databases is another matter entirely though lol. That is definitely more complicated!!! Will definitely need to learn how to back it all up before I inevitability mess it up ;).

Thanks for all the original responses, however, much appreciated :).
Hi.
I am thinking in doing what you did (but for now in one NAS)
My question is :
Did you try with different kodi versions?
One machine with kodi 17.2 and other with kodi 18.2.
If you can please try and let me know.
I already done with a old NAS dns320l (did not work, i dont know why) but i now have a new ds418 synology as you recommend me and i want to try again.
I also kept the old NAS.

I understand the process but my fear is kodi versions....
I use trakt and it is working but it is a addon , a local php server will work better.

Too bad with just sql server we dont know if we had a particular video in our database (NAS).
With trakt we know if we had a particular video in our database and we also had the possibility to iniciate/resume a video in other machine.
 
Last edited:

ArcticWolf

Trusted Helper
#13
Hi.
I am thinking in doing what you did (but for now in one NAS)
My question is :
Did you try with different kodi versions?
One machine with kodi 17.2 and other with kodi 18.2.
If you can please try and let me know.
I already done with a old NAS dns320l (did not work, i dont know why) but i now have a new ds418 synology as you recommend me and i want to try again.
I also kept the old NAS.

I understand the process but my fear is kodi versions....
I use trakt and it is working but it is a addon , a local php server will work better.

Too bad with just sql server we dont know if we had a particular video in our database (NAS).
With trakt we know if we had a particular video in our database and we also had the possibility to iniciate/resume a video in other machine.
Hey notrui2,

Hope you're happy with your new DS418 NAS & it's meeting your expectations :)??? What did you populate the NAS with in the end? (Let me know if you need any help configuring the 418 ;).)

I've never really been a fan of Trakt or actively used it with my Kodi library after reading about the potential concerns of exactly who is able to access that information. Another reason why I really wanted my own local MySQL server ;).

With the Synology DS418 acting as the MySQL server you'll need MariaDB 10, both use the same protocol & MariaDB essentially works the same as MySQL. MariaDB 10 is directly available from the package center on the NAS.

All of my "client" devices are now running Kodi 18.x. (I haven't attempted to use both Kodi 17.6 & 18.2 as the MySQL clients as it just wouldn't work with two completely different versions of Kodi!!! Each base version of Kodi creates a different database version which isn't compatible with others.

I.e. you can't have Kodi 17.2 on one machine & Kodi 18.2 on another. However, you can have variants of the same version in use. I.e. you could have Kodi 18.0 on one machine & 18.2 on another. (I'm assuming you can't install Kodi 18 on your X8-H Plus????)

Some additional information on that subject here for you.....
https://kodi.wiki/view/MySQL . (Read the contents of the red box!)
https://forum.kodi.tv/showthread.php?tid=327488

You could essentially run two separate libraries from the same MySQL/MariaDB server if you wish to do so!!!!!???? Obviously it wouldn't help if you're using two different versions of Kodi but it would centralise your libraries which can be useful for a variety of reasons.

If you still want to proceed with the same version of Kodi 18.x on your U9-H & the N42C-4???.......

1) Install the "Web Station" package from the package center on your DS418. (Once you've completed step 3 you can verify the server status is normal & that you have PHP installed.)
2) Install the "MariaDB 10" package (which for me automatically installed the "PHP 5.6" package at the same time). Setup a strong password for the MariaDB server.
3) Install the "PHPMyAdmin" package. Running it opens a WebUI based control panel, setup a strong password for the root user as well. (Once you're in PHPMyAdmin it's very straightforward to create a new user for Kodi.)

FYI, I'm now using NFS for my Kodi sources. I've trialled both SMB & NFS for significant time periods. NFS has always been slightly quicker & more responsive than SMB when jumping, fast forwarding or rewinding through movies etc. SMB would often buffer for a second or two whilst NFS is more or less instant.

Using multiple NAS boxes doesn't matter at all. Basically, once you've added any media as a source from any NAS, you simply copy the sources.xml file from the first Kodi client & drop it into the userdata folder of any additional Kodi clients along with the advancedsettings.xml file. Kodi then does the rest by itself & automatically pulls the library information off the server/database & it's ready to go.

Another FYI!! Kodi 18 doesn't seem to include the TVDB scraper addon by default for some seriously annoying reason. If you're setting up a new Kodi client that will use your MySQL server, first run Kodi before you drop the .XML files in, grab the TVDB scraper addon from the repository & configure it ;)!!! Exit Kodi & drop the .XML files in, then run Kodi again. (This step is necessary or the new Kodi client will wipe your TV Shows library from the DB & you have to sort them all out again. (Another lesson learned the hard way lol, thankfully you get that one for free ;) )).

Let me know if you need any further assistance with MySQL. (If I'm able to help I will but I'm not an expert lol, obviously I've only been using it a short time myself ;).)

I read well over 20 different tutorials & watched multiple YouTube tutorials as well. Eventually I had to ask questions on the Kodi forum too. However, this video below is all you really need to tell you how to get the job done. (It is slightly outdated for the latest Synology DSM version, however, it's still the most recent guide I could find & it's why I've included the 3 steps above for you as well ;) ).....

Hope that lot is of some use :).

 

notrui

Trusted Helper
#14
Thank You ArcticWolf.
I have the same problem that i had before.
I Watched again and now your youtube video
and the problem i faced is :
I set and configure the file advancedsettings.xml and copy to my nas.
I copy also from the client sources.xml and copy to my nas as a backup.

I run kodi with just advancedsettings.xml and sources.xml modified and after i set content in one directory (for example tv shows) the libray update dont run (dont scan the database and dont kept my settings (example directory contains tv shows with tvdb provider) , last step of the video to populate the database, if this was working i had acess to more two databases (video database and music database) in the phpMyAdmin and that two databases do not appear.
I dont know what is the problem.

I already configure the advancedsettings.xml like this:

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.105</host>
<port>3306</port>
<user>kodi</user>
<pass>kodi</pass>
<name>kodi_video</name>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>192.168.1.105</host>
<port>3306</port>
<user>kodi</user>
<pass>kodi</pass>
<name>kodi_music</name>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
</advancedsettings>

My NAS has a ip of 192.168.1.105
I dont think the problem is in sources.xml because this file is for save time in setup

If the library update run i can had all the Database information and then watch a show.

If you had any ideas .

if i delete advancedsettings.xml in a client i can set content in one directory and scan the database.

I am very happy with ds418 , i also buy two 8TB HDD WD Red , i am using SHR mode.
 
Last edited:

notrui

Trusted Helper
#15
I did it !!!! :)
Thank you ArticWolf !!! It was the MariaDB port number, i had to change to 3307 in the advancedsettings.xml.
It was a small detail but very important.
 

ArcticWolf

Trusted Helper
#16
I did it !!!! :)
Thank you ArticWolf !!! It was the MariaDB port number, i had to change to 3307 in the advancedsettings.xml.
It was a small detail but very important.
WOOP WOOP, glad you're all sorted :).

I nearly mentioned make sure you enter the right port number, but the latest version of MariaDB 10 now has the ability for adjusting the port number yourself ;). If you run the MariaDB package on the NAS you'll immediately see the option to adjust the port number lol ;)!!! Obviously you missed that, & it is obvious hehe ;).

I also mentioned in my reply that the default The TVDB scraper is missing from Kodi 18.2 ;)!! If you don't grab that from the repository & configure it in your Kodi clients before you add your .XML files, it chokes on properly scraping any local info the device may not get from the MySQL DB that it might need to grab from online, or worse, it'll wipe info from the existing DB ;).

Anyway, now you're up & running if you want to it's really easy to wipe the MySQL DB from the MariaDB app on the NAS & start over with fresh Kodi's too. It doesn't take long & that way you can be happy & 100% certain your library DB is built properly with nothing missing. Your Kodi clients will update almost instantly on first run too, doing everything they should & with a complete library too :).

It's certainly very satisfying to see it all working properly once you nail it lol :).

Enjoy :).
 

notrui

Trusted Helper
#18
ArticWolf now my DB is working good.
Do you use trakt ?
I need your advice to use trakt with my phpmyadmin setup.
It is safe to use trakt now ?
Trakt is good to know my watched movies/tv shows and have the possibility of resume also and other things.
Of course if trakt one day stop working we have our DB configuration kept in our NAS

Another question,
If i want to update my library i can do in any kodi client now?
 

ArcticWolf

Trusted Helper
#19
ArticWolf now my DB is working good.
Do you use trakt ?
I need your advice to use trakt with my phpmyadmin setup.
It is safe to use trakt now ?
Trakt is good to know my watched movies/tv shows and have the possibility of resume also and other things.
Of course if trakt one day stop working we have our DB configuration kept in our NAS

Another question,
If i want to update my library i can do in any kodi client now?
Yes, you can update & clean your library from any properly configured Kodi client.

No, I don't use Trakt at all, don't need it ;).

Why would you need Trakt ;)????? In addition to other information your MySQL DB is also keeping track of the watched status of all your media, all resume points of partially watched media, & all of your personal ratings :).

Forget Trakt mate, it's no longer necessary. It's essentially redundant & obsolete in the face of MySQL. MySQL is much more comprehensive too ;).

Backup or export your MySQL DB/library every so often just in case ;)!! (Don't ask me how to do that, I haven't got that far yet myself either lol ;).)
 

notrui

Trusted Helper
#20
Yes, you can update & clean your library from any properly configured Kodi client.

No, I don't use Trakt at all, don't need it ;).

Why would you need Trakt ;)????? In addition to other information your MySQL DB is also keeping track of the watched status of all your media, all resume points of partially watched media, & all of your personal ratings :).

Forget Trakt mate, it's no longer necessary. It's essentially redundant & obsolete in the face of MySQL. MySQL is much more comprehensive too ;).

Backup or export your MySQL DB/library every so often just in case ;)!! (Don't ask me how to do that, I haven't got that far yet myself either lol ;).)
Ok. Thanks. I already saw the option to export in sql , i am going to export and backup to my NAS the file just in case.
MySql keeps all the database, tables, querues , stored procedures etc.
I saw the video that you recommend me and i already test also this:
If you have a problem (wrong library) is just drop the my videos and my music new database tables, of course that will erase your entire library, it is important a backup.
With for example kodi 19 is just creating a new user kodi19, pass kodi19 and edit advancedsettings.xml .
With the users it is the same thing as the tables you can drop or create new users.
It have many options MySql but i dont want test many things because can put my db configuration and my library all wrong.

I tested in android and in windows and it works very well. I am watching a video in android for example and then i stop the video. I change to windows and is just resume that video.
 
Last edited:
Top