Friday, August 26, 2011

Configure a Synology NAS as MySQL server for XBMC



NOTE! This site has MOVED!


Please visit www.quixventure.com instead! the info here is getting old and may no longer be accurate!!


WE HAVE MOVED! Ozymandyaz Tek is now QuixVenture.com


If you use XBMC and you have a Synology NAS device, then you ought to think about using the NAS to host not only your music and videos, but also your MySQL database! The Synology has built in support for MySQL and it only take a few minutes to set it up for XBMC. This will allow you to use multiple XBMC installations and share the same database and thumbnails. In my case I use an Apple TV 2nd gen in the living room with a Crystalbuntu AppleTV 1st gen in the media room. I also have  a couple of windows installs that I can use to both watch movies anywhere in the house, or just to manage the library from my desktop.


The main benefit of a MySQL setup are that there is only ever one place where you will need to update TV Show and movie information. But, the reason I like it is because I can watch something upstairs and stop in the middle and then resume from any other XBMC player in the house. And, if I ever need to re-install one of my devices, there is no need to re-scrape all of my media. I just put a few config files in the userdata folder and everything is just the way it was before.



Enough with the why...How do you do it??


Since I like to keep things easy we will install MyPHP Admin on the Synology and enable MySQL for Web Station. MyPHP is graphical interface to MySQL and makes things very easy to manage later. I followed this guide on the Synology web site and here is how I did it:


Step one is to enable MySQL and the Web Station interface on the Synology. Just open the control panel choose Web Services. In the Web Services window, check "Enable Web Station" and "Enable MySQL."





Next, we will install the MyPHP plugin for Synology.


Download the MyPHPAdmin package for Synology for the Synology web site.
http://usdl.synology.com/download/packages/phpMyAdmin-3.2.5-001.zip


To Install the Package (copied from Synology Web Site):

  1. Upgrade your Synology DiskStation to firmware versions: DSM 2.1-0803 or above.
  2. Unzip the file to have "phpMyAdmin-3.2.5-001.spk".
  3. Go to [System > Package Management] page of Synology DiskStation Manager 2.1 or above, and click Install button.
  4. Follow the Package Installation Wizard to install the package.

To Run the phpMyAdmin Service:

  1. Go to [System > Package Management] page, select the package and click Run button.
  2. Click the phpMyAdmin URL link, and enter the username/password on the pop-up window.
  3. The default username is "root", while the default password is empty (none).
  4. Point a web-browser to http://synologyserver/phpMyAdmin

Congratulations! You now have a centralized MySQL database!


Next, we need to create a pair of databases for XBMC to use:

In the default window for MyPHP Admin, locate the "Create New database" dialogue and type in "xbmc_video" then click "Create."
You should see a dialogue saying that it completed successfully. use the back button on your browser (or click "Server:localhost" at the top) and add another database named "xbmc_music". Once created click back again and on the left of the screen you should see both of your new databases:


Now we have the databases, but we need to add a user so that XBMC can log in and use the database. To start this process, click on "Databases" at the top of the screen. The result will be a list of the databases in MySQL. Locate the xbmc_video database and click the padlock icon to the right of the listing:

 This will give you a new screen where you can add a user to the database. Click "Add a new user" at the bottom:

Give the user a username of "xbmc" and the same password, or one of your choice. I just use xbmc to keep it easy. Do not add anything for host. In the middle section, make sure "Grant all privileges on database "xbmc_video" is checked. In the last section, select "Check All and then click GO!


You should see a confirmation saying that all went well.. Now click on databases again and do the same thing for the "xbmc_music" database.

That's it! MySQL is setup. Now we need to do a couple of things on the AppleTV, Linix box or Windows box to make Thumbnals work right. Currently all of the XBMC thumbnails are housed locally but we want all of your XBMC boxes to share the same thumbnails folder. There are a couple of ways to do this and some of them depend on the version of your software, but right now all currently released versions of XBMC that work on the ATV1 and ATV2 support path substitution. If you need to use a symbolic link for an older build of XBMC I can explain that if there is any interest.

The easy way (works with) :
  • An Apple TV 1 using Crystalbuntu with the July update or better (not using Dharma)
  • An Apple TV 2 using 10.0-7 or above
  • Just about anything using a nightly build
You can do this the easy way by adding a single line to the advancesettigns.xml file for XBMC. The Advanced settings will probably not yet exist on your ATV2 or a windows/Linux install but Crystalbuntu (ATV1 via Sam's image) will include the file already. The advancedsettigns.xml file is used by XBMC to enable features that are otherwise hidden, such as the MySQL shared database. There is no big secret to the file, it is plain text and if it exists in the userdata folder of the XBMC install, it will be read and used.


For the sake of simplicity I will list out generic instructions for the ATV1 below and add a note at the end for windows and the ATV2. The text and settings are all the same but  there are minor differences in where the file is located and what may or may not be in the file already.


For an ATV1 with Crystalbubntu (Sam's Image):


You will need to add a few lines to the already existing file. You must have already booted the ATV and actually gotten all the way to XBMC so that Sam's scripts will have updated the file system and included the most recent advancedsettigns.xml update. Basically, boot all the way once, reboot a couple times and then follow the instructions below. If you have used your AppleTV for a while, just re-boot once to be safe.


Use Putty to SSH into the ATV2 with a userID of ATV and a password of ATV.


Type:

sudo nano /root/.xbmc/userdata/advancedsettings.xml

password is atv again
Once nano opens you may see an XML file with this and possibly more in it:


<advancedsettings>
<useddsfanart>true</useddsfanart>
</advancedsettings>

Or the file may be brand new and empty... In either case you will need to add a new section between  a pair of <advancedsettings> and </advancedsettings> tags. If there is noting in the file, you will need to put <advancedsettigns> above and </advancedsettigns> below what is added next.

Add the following:

    <videodatabase>

        <type>mysql</type>
        <host>192.168.1.XXX</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_video</name>
    </videodatabase> 
    <musicdatabase>
        <type>mysql</type>
        <host>192.168.1.XXX</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_music</name>
    </musicdatabase>
    <pathsubstitution>
     <substitute>
      <from>special://masterprofile/Thumbnails/</from>
      <to>smb://192.168.1.XXX/media/Thumbnails/</to>
     </substitute>
    </pathsubstitution>

The trick here is to replace all of the IP addresses with the address of the NAS and to also check the path for the thumbnails. You will need an SMB share for now though I believe it can be done with NFS as well. In my case this assumes I have a root shared folder called "media" and a folder inside named "Thumbnails" and that it has public read/write permissions. The thumbnail folder must be writeable!


FOR AN ATV2:
The file is not created by default and is located in:

/private/var/mobile/Library/Preferences/XBMC/userdata


So the command to edit the file will be:


sudo nano /private/var/mobile/Library/Preferences/XBMC/userdataadvancedsettings.xml



Shares Setup


Finally, you will likely want to set-up your shares on the ATV or some other XBMC device and then copy the sources.xml file from that device and copy it to all of your other devices. The sources.xml file points XBMC to all of your shares and if you use a shared database then you need to have exactly the same shares on each XBMC box as well. I use all SMB shares since I know those will work on all of my players. And keep in mind, the protocol for each share must be the same on each box sharing a database, so you cannot do NFS one one box and SMB on another. 


That's it, enjoy!