The “Ideal” XBMC setup …

I found the ideal basic XBMC setup to be the one where you have:

• One (or more) network location containing all the media.
  For example a NAS box (QNAP for example), a Windows setup with a shared media folder, a Linux box running FreeNAS, etc.
• One or more XBMC PC’s throughout the house.
  They should all be running exact the same version of XBMC, but can be running on different operating systems.

Installing MySQL

MySQL is a freely available, yet powerful database engine intended for server use – you wouldn’t know, but the majority of webservers actually utilize MySQL to run their dynamic content. Recent changes in XBMC make it possible to use MySQL for storing information that can be shared amongst several other XBMC setups in your house. Setting up MySQL is straight forward and you might already have it … for example my QNAP NAS already comes with MySQL pre-installed (it just needs enabling).

Here is my article on How to install MySQL, Web-Server, and phpMyAdmin on your QNAP.

MySQL is available other platforms as well: Windows, Linux, MacOS X.
You could of course decide to manually install MySQL – go to this link and select the OS (not needed for a QNAP NAS). I do however strongly recommended (not needed for a QNAP NAS setups) to use one of the many AMP packages (most are free);

• WAMP Server for Windows, or
• XAMPP for multiple platforms including Windows, MacOS X and Linux.
• MAMP for MacOS X (OS X Server already comes with MySQL)

The advantage being that it’s easy to install and they all come with phpMyAdmin preinstalled – which makes maintaining MySQL and running queries so much easier straight from any web-browser. The disadvantage (?) might be that Apache (webserver) will be installed as well (disk-space / minimal CPU load).

I don’t want phpMyAdmin …

If you decide to use a setup without phpMyAdmin then make yourself familiar with how to access MySQL from the command line on your system (or get a program like EMS MySQL Manager or DevArt SQL Studio), see the MySQL documentation for details on your system since you will have to manually (in a shell) go to the mysql command line tool for execute a few SQL statements.

The typical command for all systems is: mysql -u username -p
(replace username with your actual username, the -p will make MySQL ask for your password)

Tip:

On some systems (Windows migth be one of those) you will have to make sure that the port (default: 3306) you’re using for database access is not blocked by your firewall.

Preparing a Database

These instruction assume you use XBMC Eden (v11) or above!

Older XBMC versions will work the same way for SVN version R28117 or above, but might require you to run two additional SQL statements!

The Short Version

Go to phpMyAdmin and click the “SQL” tab. In the upcoming text-box type (or paste) the following SQL statements and click the “Go” button in the lower right corner to execute the SQL statements:

CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
GRANT ALL ON *.* TO 'xbmc';

What does this do?

Create the user “xbmc” with the password “xbmc“

CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';

PHPMyAdmin/MySQL – Create a user for XBMC

After execution, PHPMyAdmin will jump back to the PHPMyAdmin start page – unless you somehow made a typo. You can click the tab “Privileges” and under the “User Overview”  you should now see (at the bottom) the user “xbmc“.

Note: I used the password “xbmc” in this example (without the quotes, right after “IDENTIFIED BY“), feel free to use a different password if you’re concerned with security. Make sure to use that new password consistently throughout the next steps.

Grant the user “xbmc” full access

GRANT ALL ON *.* TO 'xbmc';

This will give the user “xbmc” sufficient access to create and modify databases.

OK, so from a MySQL perspective we’re set …

These instruction assume you use XBMC Eden (v11)!

Older versions might require you to run two additional SQL statements to create the actual databases (Eden will do this automatically):

1. CREATE database xbmc_video;
2. CREATE database xbmc_music;

CREATE database xbmc_video;
CREATE database xbmc_music;

Migrating from an older XBMC version

When you have used this setup with an older XBMC version, then the newer version (Eden and newer) will copy the data of the old database to a new database. The new database, to be used by the newer XBMC version, will have the same name followed by a 2 digit number.
For example: xbmc_music (old – will remain untouched) will be copied into xbmc_music18 (new – will now be used).

Configure your XBMC setup(s)

All XBMC’s – Same paths to Movies, Music, and TV-Series!

Before we proceed with the MySQL connection for our XBMC boxes we should make sure that the path to our Movies, Music, and TV-Series are consistent for all XBMC’s you’re using. So make sure that all “sources” for all your XBMC HTPC’s are setup equally!

This is a must, otherwise the different paths will be seen as different files!

I recommend starting with 1 single XBMC setup. Make clear notes on what shares (path) have been setup for the different sources and media types – we want to replicate those on the other XBMC’s.

To keep paths persistent;

Avoid storing Movies, Music, and TV Series on the XBMC itself – it can be done, it can however make things more complicated.
I recommend storing all your media on a central share that is not on a XBMC box. For example; your QNAP NAS.

First we need to make sure that we use a version of XBMC that can actually handle all this fun.
It is said that SVN version R28117 and above should be good to go (source), but I by now I hope that either ‘Eden” is available (v11) or that nightly builds are by definition way beyond that number. Get your XBMC version now: XBMC Download Page.

Note: I’m not sure if this is still relevant, but at the time issues were reported with AppleTV and iOS setups not supporting MySQL syncing using Eden Beta 1.

Step 1: Export your current Library (optional)

This is optional, but it might save a lot of time if we simply re-import the hard work once the MySQL Connection is up and running. This way we don’t have to re-scan your entire library and instead use what we already have.

In XBMC go to the “System” menu, select “Settings” → ”Video” where you select “Export Library” option. XBMC will now ask if you want to save this as a single file (yes) and where to save it – pick your destination. After the export you’ll find a folder name (something like) “xbmc_videodb_2012-01-23”

XBMC (Eden/ v11) – System menu – Select Settings

XBMC (Eden/ v11) – Video menu – Export Video Library

Step 2: Create a file “advancedsettings.xml”

Open a plain text editor (Windows: Notepad or Notepad++, MacOS X: TextWrangler, Linux: VI or Nano) and paste the following text.

Note:

• Replace 192.168.1.100 with the IP address of your MySQL Server.
• If you changed the default port number of your MySQL server, then change 3306 to the port number you picked (3306 is the default port, so if you didn’t change it during the MySQL setup, don’t change it here either).

<advancedsettings>
    <videodatabase>
        <type>mysql</type>
        <host>192.168.1.100</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_video</name>
    </videodatabase> 

    <musicdatabase>
        <type>mysql</type>
        <host>192.168.1.100</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_music</name>
    </musicdatabase>
</advancedsettings>

<name>…</name> no longer needed?

The name tag (<name>…</name&gt is no longer needed if you’re using one of the more recent versions of XBMC if you use only one shared database. XBMC will choose it’s own name and add the version number of the database structure after it for example “xbmc_video60″ or “xbmc_video67″. When a new database version is being created the old data will be copied into the new database. This is (probably) a result of an experimental stage in which the database structure changes over time. Unfortunate, specially when you use a few different versions of XBMC as it makes quite a mess in MySQL.

It can however be useful if you want to utilize two different databases, for example one for XBMC stations for Adults and one for the Kids.
The use of multiple databases however have been reported to cause issues. 

Now save this file as “advancedsettings.xml“.

• Windows XP:
  Move this file to “C:\Documents and Settings\<your_user_name>\Application Data\XBMC\userdata\“.

• Windows Vista,7 and newer:
  Move this file to “%APPDATA%\XBMC\userdata“.

• MacOS X:
  In the Finder, click the “Go” menu and select the “Go to Folder …” option. A window opens saying “Go to the folder“, paste “~/Library/Application Support/XBMC/userdata” (without the quotes of course) in the text box and click “Go“.
  Now move the advancedsettings.xml file to the folder that just opened.

• Linux:
  The file needs to go into this folder: ~/.xbmc/userdata
  Accessing this under a live version is a little harder, use either SSH access, or press CTRL+ALT+F2 to go to the shell (use CTRL-ALT-F7 to go back to XBMC) and do this all manually with either VI or nano.
  Some great version can be found at XBMCFreak, or you can use the XBMC Live version.

• OpenElec:
  A shared folder called “userdata” can be found in your network for the machine running OpenElec, drop the file in there and reboot the machine.
  OpenElec is for example available for Linux, AppleTV (gen.1), and Raspberry Pi – they all use the same method.

• Apple iOS device (iPad, AppleTV2, etc):
  The instructions claim to save the file here: “/private/var/mobile/Library/Preferences/XBMC/userdata/”.

• CrystalBuntu / CrystalHD:
  Use SSH to access the file (ssh username@ip-address). The file goes here: “/root/.xbmc/userdata” .

• Others
  There are a few alternatives besides these out there, for example the AppleTV (1st Generation – silver) based on a small Linux distro like CrystalHD. Unfortunately, I’m not familiar enough with all these variations to confirm or to tell you how to do it — sorry. 

Step 3: Restart XBMC and optionally import the exported library

Shutdown and restart your XBMC HTPC.

If you find that the startup of your XBMC takes forever then please verify a few things:

• Did you use the right IP address, username, password, and database name in the advancedsettings.xml?
• Does your server or XBMC box have a firewall that blocks port 3306?
• Did you execute the SQL statements properly? (most common issue: lack of access rights)
• Is MySQL actually running?
• Does your XBMC version support MySQL syncing?
• Do you have an older XBMC version (Dharma) and did you execute the 2 additional SQL statements (CREATE DATABASE)?

phpMyAdmin – XBMC made some tables!

Repeat step 2 and 3 (use the same advancedsettings.xml file) for your other XBMC boxes – even if they run on different operating systems!

The Result …

After doing this for two XBMC setups;

1. Running Eden Beta2 on MacOS X Lion
2. Running a nightly build of OpenElec (Linux) on a dinky AMD E350 computer (Foxconn nt-a3500 - Dutch review)
3. Running MySQL on my QNAP NAS

Now I can see on both machines where I paused, to then resume on the other machine.

XBMC – Machine 1 – MacOS X Eden Beta 2 paused here …

XBMC – Machine 2 – OpenElec (nightly build) Wants to resume here …

Tips to prevent issues and make configuration easier

We now have an XBMC setup that syncs with your MySQL database. But little problems might still sneak in and quite a few of them are easy to prevent.

A few items I ran into are for example;

On one box the server was referred to by it’s name and the other one used an IP address – resulting in two different entries for every file in the database!
This obviously prevented it from marking the proper entry as “wachted”.

For unknown reasons one XBMC box saved a path in the regular format (ie. sbm://<servername>/path), while the other one added username and password (ie. smb://<username>:<password>@<server>/<path>) – obviously again the database stored this as two different files, and again not tracking “watched” properly.

Having to enter sources manually does not only cause these kind of issue, but it is also an cumbersome exercise. Do it once and copy the proper files saves on work and problems later on due to inconsistency.

A problem I have not found a good solution for:
Probably the biggest issue I’ve been running into is the different database structures for different versions of XBMC – this unfortunately has not been made backwards compatible and creates additional databases on your MySQL server. Not only does it seem unnecessary, but it also comes with problem, since the newer XBMC and the older XBMC versions will NOT keep those databases in sync.

To prevent most of these issues:

• Use the username:password@server/path format when referring to a server for your sources.
• Refer to a server by it’s IP address for your sources, it appears faster.
• Setup one XBMC box with all the sources and settings you’d like and then copy the configuration files to the other XBMC boxes (reboot after copying).
  The following files appear to be crucial in this process:
  - advancedsettings.xml
  - sources.xml
  - passwords.xml

For certain versions of XBMC the databases have not been fully optimized for speed. Adding the following indices will give it a little boost (source: OpenElect configure MySQL):

For Video:

ALTER TABLE movie ADD INDEX ix_idFile(idFile);
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile);

For Music:

CREATE INDEX idAlbum_idx ON song(idAlbum);
CREATE INDEX idArtist_idx ON song(idArtist);
CREATE INDEX idArtist_idx ON album(idArtist);
CREATE INDEX idArtist_idx ON exartistsong(idArtist);
CREATE INDEX idArtist_idx ON exartistalbum(idArtist);
ALTER TABLE song ADD INDEX idArtist(idArtist);
ALTER TABLE song ADD INDEX idGenre(idGenre);

Additional Resources

Even though implementing this is relatively easy; this topic can come with additional questions, find below some links to related webpages:

• XBMC MySQL Wiki page
• XBMC Forum
• Lime Techonology Forum (older XBMC version)
• Ozymandyaz Tek (for Synology NAS, AppleTV 1st and 2nd Generation)
• Lifehacker.com Article (older XBMC version)
• XBMCFreak (awesome resource for a nicely tuned XBMC)
• OpenElec (another very fast XBMC distribution)
• XBMC Database structure draft

Enjoy watching TV with your newly added XBMC feature …