+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? (/showthread.php?tid=363460)
Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - DrMPS - 2021-07-07
Hi all-
I have an existing MySQL setup hosting my library running on an Rpi4, and shared/synched between my rpi's connected to the TVs as well as my windows computers. That all works well.
Now that I've got fiber at home, I'd like to make the library available via remote protocol (FTP, I guess, probably using vstfpd) for when I travel.
Is there a simple way to do this via pathsubstitution? I could dump the library, setup FTP on the pi, then rescrape all files and repopulate the library, but that sounds silly. There is surely a way to migrate the library to FTP as well, but still, that is silly because then locally, to access wouldn't I still be accessing data remotely, that is, it would go out to my ISP then back to me instead of over my LAN straight to me?
What I'd like to do:
1) set up FTP server on Rpi4 which hosts library, allow remote password protected access to "media/pi/VIDEOS1", "media/pi/VIDEOS2", etc, where the media is hosted
2) edit advancedsettings.xml on windows laptop or fresh pi running osmc to find MySQL library at xxx.xxx.xxx.xxx (the FTP ip address) instead of 192.168.xxx.xxx (the LAN ip address)
3) edit advancedsettings.xml to say that library paths are at ftp://xxx.xxx.xxx.xxx/media/pi/VIDEOS1 instead of /media/pi/VIDEOS1 (or however it would need to read)
4) enjoy my library while out of town
Is this doable this way?
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - Klojum - 2021-07-07
(2021-07-07, 17:25)DrMPS Wrote: enjoy my library while out of town
I think your biggest challenge in all that is taking care of the access security to/in your home setup. Opening ports in a router will be noticed quickly by hackers with port sniffers and other bad goodies.
Changing URLs in your video library *can* be done quickly and easily when you know how to handle SQL queries. There is a page in our Wiki that addresses the path changes in a SQL database.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - DrMPS - 2021-07-07
Yes, I am aware of the security issues, and can handle that responsibly.
Do you suggest editing the SQL database (https://kodi.wiki/view/HOW-TO:Update_SQL_databases_when_files_move I assume)?
Won't this mean that my local, LAN file access will also have to route through the FTP via my outgoing internet connection? I don't want to change anything about how it works in my house, or, have to change it once when I leave, then change it back when I get home again.
Such a thing can't be done programmatically in advancedsettings? I thought I remembered that when pathsubstitution was introduced for purposes of mapping to thumbnail directory over LAN, it also meant the entire library directory could be re-mapped. I could 100% be wrong, I couldn't find a page that says that.
I suppose as an alternate, what I could do is make a separate MySQL user, and create/scrape an entirely new library for remote users over FTP, but then it isn't in sync with the 'at home' library.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - Klojum - 2021-07-07
(2021-07-07, 20:04)DrMPS Wrote: Do you suggest editing the SQL database
One alternative option:
- You could install Raspberry Pi OS as well MariaDB onto 1 RPi4 onto a SDcard/USB3.0-stick/SSD drive via USB, and copy a number of movies / tvseries onto a 2nd storage device. Dump a copy of your SQL video database into MariaDB and edit its paths for local use. You would then also avoid the whole remote access stuff, including possible long-distance connection hiccups.
Personally I've never set up long distance connections to a home Kodi setup. FTP is still better suited for the 'simple' downloads in my eyes, you could also try the other protocols. Everyone of them should work as long as you use the right IP address and open the correct ports on your router/devices. What works best for you is a case of trial and error. Not forgetting to also open the port for the remote database server.
(2021-07-07, 20:04)DrMPS Wrote: Such a thing can't be done programmatically in advancedsettings?Your home IP cannot be used elsewhere, so path substitution would be the only way.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - DrMPS - 2021-07-08
No thanks to carrying a portable drive around, although, technically that would certainly work.
So; how would the pathsubstitution have to work? Do I, in advanced settings, do something like:
<from>library://video/VIDEOS1</from> #where "VIDEOS1" is the path to the local, SMB share, hosted on my rpi4
<to>ftp://[email protected]:21/APPROPRIATE_FTP_PATH</to> #where xxx is login, nnn.nnn is the IP address of my house, etc?
Then in order to see the same library advancedsettings itself has to point to the the FTP server in <videodatabase><host>nnn.nnn.nnn.nnn</host></videodatabase> somehow as well, right?
The relevant wikis (https://kodi.wiki/view/Special_protocol and https://kodi.wiki/view/Path_substitution) don't discuss it clearly enough for me to be sure of what to do.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - jmh2002 - 2021-07-08
I had remote access setup a VERY long time ago. To get around the 'simultaneous' issues you mentioned I had a separate 'Travel' profile in Kodi so that i could have a different advancedsettings.xml pointing to the external access details.
A few points to note:
- You mentioned you have fiber, but will your 'upload' speed on the fiber be enough to stream your content reliably from your home network to your remote location? (this is an issue I had, but it was a long time ago, it wasn't fiber, and internet was a lot slower in general then)
- Again, I'm not sure about your fiber, but often the ISP can change your external IP address (sometimes even daily). So you may need a service like NO-IP that will track that and provide a unique IP address of their own to use instead.
- To be honest, all of this became much easier for me - seamless even (apart from the internet upload speed), by using Emby as the central server instead of mySQL. Since Emby handled the external sharing very nicely (either from your home server or the cloud), as well as added other nice to have options such as managing the access of other users too (eg: family members).
Jellyfin may be an option these days instead of Emby. And I guess Plex can do this as well. The plus is that these central servers can also transcode so that you can watch a reduced quality version of your content to help with internet speed and latency while travelling.
The HUGE minus for me personally when using Emby was that only Kodi Library items were supported, ie: no support for video addons such as YouTube, etc, of which I am a heavy daily user. And coming from a mySQL setup I was very used to having watched and resume status for all these online videos shared across all of my devices too. And it wasn't possible to use both mySQL and Emby at the same time (not sure about the status of that now).
So after a brief spell testing Emby all those years ago it was back to mySQL for me. If you don't use online content addons like YouTube then this might not be a problem for you.
Hope this helps a bit too
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - DrMPS - 2021-07-08
IP address is solid, but of course, as necessary, I could get myself a static. Up speeds are a nonissue. Fiber is FAST. Like, I struggle to think of things to do with how fast this (synchronous up/down) connection is...
Upload speed is so fast, that there's no point transcoding. I will think about Emby or Plex but if I can get Kodi/osmc to work, that would be best.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - jmh2002 - 2021-07-08
Ok, so given you know how to handle the networking, ports, ftp, etc, then on the Kodi side of things (whilst Profiles are not great these days) you should be able to just create a separate Travel profile and use a different advancedsettings.xml there to point to your external details, and it all should work.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - DrMPS - 2021-07-12
(2021-07-08, 06:46)jmh2002 Wrote: Ok, so given you know how to handle the networking, ports, ftp, etc, then on the Kodi side of things (whilst Profiles are not great these days) you should be able to just create a separate Travel profile and use a different advancedsettings.xml there to point to your external details, and it all should work.
I can access the mysql database remotely easily enough (once router configured right). And I can access FTP space on a pi that I set up at home.
How to put two and two together?
When I use kodi remotely, it populates the library just as if I was home. However, it can't access any of the video files.
For test purposes, I've got the database + media all still on its rpi4 on my home network. That rpi4 hosts the mysql database. I don't want to change anything on there yet.
Then, I've set up a separate rpi running an FTP server, which I can access remotely. I've made one folder, /files/, in it. I've copied a particular video file from the server and put it into /files/; calll it DRZHIVAGO.mkv (not actually it).
How do I make advancedsettings with this type of inclusion work:
<pathsubstitution>
<substitute>
<from>smb://LOCATION/OF/MEDIA/</from> ##where LOCATION/OF/MEDIA/ is the folder that DRZHIVAGO.mkv is in, according to the database; this is the smb-share location that the file plays from when running KODI locally
<to>ftp://USER.PASS@IPADDRESS:PORT/files/</to> ##where this points to the FTP directory where I've put a copy of DRZHIVAGO.mkv
</substitute>
</pathsubstitution>
KODI (on remote machine) still boots with this inclusion, but when I browse to Dr Zhivago under Movies in KODI (on remote machine), it says 'this file is no longer available'. So that doesn't work.
When I add the FTP as a video source to the remote KODI, I can play the file smoothly remotely in KODI and could scan it into the library if I wished. But re-scanning the entire library is what I'm trying to avoid doing. What I'd like to do is just substitute SMB location for FTP location programmatically.
Do I just have the <substitute> syntax wrong, or is this not possible / no longer possible in KODI? (Obviously, to get my library to work once I get the <substitute> syntax right, I'd have to run the FTP server on the pi4 with the library, or, I guess, fstab automount the SMB shares onto this 2nd pi)
The idea is, that I'd have a <from> / <to> pair for every shared top level folder on my pi4, ie "smb://SHAREDPI/MOVIES_A-F/" so that I don't have to make a <from> / <to> individualized like "smb://SHAREDPI/MOVIES_A-F/MOVIES_D/DR_ZHIVAGO/" for *every* movie, every episode, etc.
If someone can help me get the test case to work, then I can figure out the rest on my own, most likely.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - DrMPS - 2021-07-12
Hmm; who can spot the typo in my <pathsubstitution>? user:pass, not user.pass....stupid, stupid stupid.
Now it seems to work! At least, the 'test' version does. No thumbnails yet but this is major progress. I will post with an update on overall proper format when I get it.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - jmh2002 - 2021-07-13
Read over what you wrote and noticed the typo, but see that you have spotted it. I tend to use copy and paste to be very sure not to make these types of mistakes.
But great that you got it all working now in general.
Thumbs etc should be pulled again locally on the Travel device I think, right? Unless maybe you have nfo files specifing a path back to your smb drive too?
I didn't check your posts above again regarding that side of your setup.
RE: Simultaneous sharing of existing MySQL database locally on LAN as well as over FTP? - sksbir - 2021-07-14
hello
I made a try not long time ago with my vpn server hosted on my NAS.
Using an android vpn client on my phone + kodi installed with right parameters to use mariadb database just in same manner has when I'm at home... this worked well behind an ADSL connection ( Mariadb and content is connected to internet with fiber).
the only one thing to do is to authorize mariadb conneection while connecting threw my vpn because I land on my lan with a different ip range.
install a vpn server at home and enjoy home services from remote locations just has if you are at home!
this is supposed to be the first historical usage of Virtual PRIVATE Network.