I found the ideal basic XBMC setup to be the one where you have:
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);
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).
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)
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.
Older XBMC versions will work the same way for SVN version R28117 or above, but might require you to run two additional SQL statements!
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc'; GRANT ALL ON *.* TO 'xbmc';
Create the user “xbmc” with the password “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
This will give the user “xbmc” sufficient access to create and modify databases.
OK, so from a MySQL perspective we’re set …
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).
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.
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.
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”
<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>
The name tag (<name>…</name> 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“.
Shutdown and restart your XBMC HTPC.
If you find that the startup of your XBMC takes forever then please verify a few things:
Repeat step 2 and 3 (use the same advancedsettings.xml file) for your other XBMC boxes – even if they run on different operating systems!
After doing this for two XBMC setups;
Now I can see on both machines where I paused, to then resume on the other machine.
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:
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):
ALTER TABLE movie ADD INDEX ix_idFile(idFile); ALTER TABLE movie ADD INDEX idMovie(idMovie); ALTER TABLE movie ADD INDEX idFile(idFile);
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);
Even though implementing this is relatively easy; this topic can come with additional questions, find below some links to related webpages:
Enjoy watching TV with your newly added XBMC feature …