How to setup Kodi database on a QNAP NAS.

How to setup Kodi database on a QNAP NAS.

Kodi and a QNAP (or any NAS) is a dream setup.

It’s an even better setup if you use Kodi on multiple devices in your household and have your media collection on a central ‘server’

One annoyance however can be all your libraries are all out of sync, not knowing what you’ve watched or where you’ve got up to on every device can be a little annoying.

However you can solve this by also having all your Kodi clients connect to one central database so your media library is all in sync across all of your Kodi devices, and best of all. If you are watching something on your Raspberry Pi for example connected up to the main TV and decide to want to go to bed to finish off what your watching, then simply stop playback, go upstairs, pick up your Android tablet, load up Kodi, and not only will it know what episode you were up to on your series, it will start playing that episode from the exact spot of where you stopped it, because all the clients are talking to the same database. This is a game changer in the home media centre setup!

Unfortunately setting this up to work, isn’t as simple as it should be, so while guides exist, some have got a little out of date and require updating, so for my own personal reference and anyone else that wants to do it and is having problems with some guides that are years out of date, here’s how to do it in 2018.

    1. Make sure all your Kodi clients are running the same version, and this goes for in the future to, if one client is updated, make sure you update the others to the exact same version number.
    2. Load up the control panel on your QNAP.
    3. Make sure all the media you want to be in your database is in one major folder and that it’s shared. For example on my QNAP I have a folder called Multimedia, and under that, I have folders called Music, TV Shows & Movies. Use the File Manager tool to create your folders and move folders if need be, it will be a lot quicker doing it on the NAS itself than moving files about using Windows or any other desktop OS.
    4. In control panel under network and file services click on Win/Mac/NFS
      Under Microsoft Networking, click to enable if not already set and click apply. (You can use NFS if you prefer, but I find SMB is actually more reliable)
    5. Once all your files are in one place click users.  Create a user account on your QNAP to access your multimedia shares. For simplicity in identifying the user you may simply want to call it Kodi. Giving permissions like the below will be fine, it needs write access as it will also write the thumbnails for the graphics Kodi database to this folder.
    6. Next we need to install MariaDB on the QNAP, In the control panel on the QNAP, click the applications menu (not app centre) and click the SQL server entry.
      Tick the boxes as below and click apply
    7. Before we install phpMyAdmin, click on the web server icon and enable the web server as below and click apply:
    8. Click back onto SQL server, and click the link to install phpmyadmin
      this will take you straight to the phpmyadmin app in the app centre, click the blue button to install it. (Mine says open because I already have it installed)
    9. Once installed click the open link to ensure its working correctly, if you get a https error just click advanced in your browser to bypass.
      The default username and password to login is
      username: root
      password: admin
    10. You should now see something like the below, I’ve cropped my databases on the left out to not confuse you.

      I’d recommend you click on the user accounts button and select the root account which has a YES in the password column, and then select edit privileges.

      Click Change Password

      Enter a new password and then click go.
    11. Next click the SQL tab across the top.
      Enter the following lines of code:
      CREATE USER 'kodi' IDENTIFIED BY 'kodi';
      GRANT ALL ON *.* TO 'kodi';

      Click GO
      And the Kodi user for our database will be created with a password also, of kodi and with enough permissions to create and write to databases.
      If you go back to the user accounts tab, you should now see a kodi user listed

      If you’re concerned about security, change the password as you did for the admin user, just remember what you changed it too when we reference the kodi user again later in this guide.
    12. If you already have a Kodi database you want to use with all your watched and unwatched programs, then we need to export it. If you want to start fresh, or are doing a complete new setup, you can skip this step.
      Click the settings button
      Select Media
      Change the cog at the bottom to Advanced or Expert
      Select Library on the left
      Choose Export Library
      Choose single file
      Choose where to save the file and click OK
      This may take some time depending on how big your library is.
    13. We now need to create 3 xml files for use with Kodi
      13.1 Copy and paste the following into a text file and save it as advancedsettings.xml. Change the 3 references to the IP address of 192.168.0.3 to the IP address of your QNAP NAS.
      <advancedsettings>
      <videodatabase>
      <type>mysql</type>
      <host>192.168.0.3</host>
      <port>3306</port>
      <user>kodi</user>
      <pass>kodi</pass>
      </videodatabase>
      <musicdatabase>
      <type>mysql</type>
      <host>192.168.0.3</host>
      <port>3306</port>
      <user>kodi</user>
      <pass>kodi</pass>
      </musicdatabase>
      <pathsubstitution>
      <substitute>
      <from>special://masterprofile/Thumbnails/</from>
      <to>smb://192.168.0.3/Multimedia/Thumbnails/</to>
      </substitute>
      </pathsubstitution>
      </advancedsettings>
      13.2 Copy and paste the following into a text file and save it as sources.xml. Change the 3 references to the paths of your music, TV shows and Movies to the correct smb paths of the files. (if you choose NFS in step 3 instead of SMB, then ensure you also change the protocol) Feel free to edit the other sources as well, and if you want to add additional sources you can enter them here too:
      <sources>
      <programs>
      <default pathversion="1"></default>
      </programs>
      <video>
      <default pathversion="1"></default>
      <source>
      <name>Videos</name>
      <path pathversion="1">/storage/videos/</path>
      </source>
      <source>
      <name>TV Shows</name>
      <path pathversion="1">smb://192.168.0.3/Multimedia/tv series/</path>
      </source>
      <source>
      <name>Movies</name>
      <path pathversion="1">smb://192.168.0.3/Multimedia/movies/</path>
      </source>
      </video>
      <music>
      <default pathversion="1"></default>
      <source>
      <name>Music</name>
      <path pathversion="1">smb://192.168.0.3/Multimedia/Music/</path>
      </source>
      </music>
      <pictures>
      <default pathversion="1"></default>
      <source>
      <name>Pictures</name>
      <path pathversion="1">/storage/pictures/</path>
      </source>
      </pictures>
      <files>
      <default pathversion="1"></default>
      <source>
      <name>downloads</name>
      <path pathversion="1">/storage/downloads/</path>
      </source>
      <source>
      <name>backup</name>
      <path pathversion="1">/storage/backup/</path>
      </source>
      </files>
      </sources>
      13.3 Copy and paste the following into a text file and save it as passwords.xml, ensure you change the PaSSWordFromStep4 text to the password you choose in step 4 when creating the Kodi user on the QNAP user control panel screen. If you choose a different protocol from SMB remember to change that also.
      <passwords>
      <path>
      <from pathversion="1">smb://192.168.0.3/</from>
      <to pathversion="1">smb://kodi:PaSSWordFromStep4@192.168.0.3/</to>
      </path>
      </passwords>
    14. The 3 above saved xml files now need to be copied into your Kodi file system. Now the location of this will depend on what OS your running Kodi on, but they all need to go in the userdata folder. The userdata folder can be found in the following areas:
      Operating system File path
      Android Android/data/org.xbmc.kodi/files/.kodi/userdata/ (see note)
      iOS /private/var/mobile/Library/Preferences/Kodi/userdata/
      Linux ~/.kodi/userdata/
      Mac /Users/<your_user_name>/Library/Application Support/Kodi/userdata/ (see note)
      LibreELEC/OpenELEC /storage/.kodi/userdata/
      Windows Start – type %APPDATA%\kodi\userdata – press <Enter>
      Windows UWP  %LOCALAPPDATA%\Packages\XBMCFoundation.Kodi_4n2hpmxwrvr6p\LocalCache\Roaming\Kodi\

      Source: https://kodi.wiki/view/Userdata_folder <- Check this page if you can’t find it incase the location has moved.

    15. Restart your Kodi client
    16. Import the database you created from step 11 if you exported a database, following the same instructions as step 11, but choosing to import the library instead.
    17. If you log back into myPHPadmin you should see that your database has been popularised.
    18. Repeat steps 13-15 for any other additional Kodi clients you have on your network.

Troubleshooting
Check the kodi.log file in the first instance which should give you an idea as to what the problem is.
If you want support from me, include it in the comments below with as much additional information as possible.

NOTES: Kodi Upgrades the database on each major new Kodi release, so you may want to log into myphpadmin and delete older numbered versions of the database from time to time, you can find out which number is the current DB from your Kodi version by looking here: https://kodi.wiki/view/databases#Database_Versions

Backing Up MySQL Databases on QNAP – Look at this thread on the QNAP support forums on how to setup a backup of your MySQL databases. Don’t be put off by the text here, follow the instructions and it’s very easy to setup.

Follow:

2 Comments

  1. Lolita Dicaire
    28th June 2018 / 4:00 am

    Hi,
    First thank you for this clear tutorial. I need your help to have this working. What are the settings that are necessary for Kodi to match the above process and have this working?

    • Ade
      Author
      28th June 2018 / 8:12 am

      Hi,

      Sorry, I don’t fully understand your question, but I’ll try and answer what I think you’re asking. In terms of Kodi settings, you need to change nothing. When Kodi loads it will check for an advancedsettings.xml file as well as the others, once it knows these files exist then it will automatically use the settings you’ve entered into them. There’s nothing to change on the KODI settings screen itself.

      I probably should have linked to this page in the post itself, it may help: https://kodi.wiki/view/Advancedsettings.xml

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.