Sunday, 11 March 2012

Multiroom XBMC Library using MySQL and why its weird

*Note: This page gets a lot of views, More then anything else I've posted. If you have questions about multi-room XBMC hit me up in the comments and I'll try to answer/help.

Running XBMC off MySQL is an excellent setup. It allows for XBMC to be made into a (kind of) multi-room entertainment system. OK so it can't do synchronized media playback but it does synchronize libraries and resume points. If your like me, however, the best part is you can do library updates on your computer and then the Apple TV that your using to run XMBC does not choke up for 5 minutes as it painstakingly scans 3TB's of downloads and DVD rips.

The XBMC wiki contains, most of, the information you need to configure this but it does not do a very good job of explaining things especally how it interacts with mysql databases, so I wanted to publish some of these details as they are glossed over/ignored with most setup guides.

My Setup

  • Main XBMC running on Apple TV2

  • Secondary XBMC running on a Windows 7 Desktop, this is used to manage and update library.

  • Media served from a usb Drobo via a Atom powered netbook (they may be useless as a PC but they make a nice little server)

If you already have a library in use then now is a the time to export it to a single file from the XBMC settings menu so you can import it once the library is setup. Then you need to create a file in your xmbc user data folder, stored whereever your OS stores its profile data.

C:\user\[username]\AppData\Roaming\XBMC\userdata\advancedsettings.xml
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.0.254</host>
<port>3306</port>
<user>xbmc</user>
<pass>****c</pass>
<name>xbmc_video</name>
</videodatabase>

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



Now xbmc will take ages to start up as it tries to connect to MySQL and the library database. I have put in the name field to specify the name of the database because its important to note that this is not the full name of the database it creates. The name field is not actually required if your happy to let xbmc use the default database name.

The XBMC wiki page on setting up MySQL for this purpose just gives the xbmc user freedom to do whatever it needs to inside MySQL, it then creates a database with the version number appended onto the end of it. I have no upgraded xbmc a couple of times so my MySQL database list looks like so:
+--------------------+
| Database
+--------------------+
| information_schema
| mysql
| test
| xbmc_music_craig18
| xbmc_video_craig58
| xbmc_video_craig60
+--------------------+

As you can see I have a music library configured in schema version 18 and currently my video library is running on schema 60 with a deprecated version 58 still stored in MySQL.



The obsessive inside me wants to tweak the grant privileges for the xbmc user so it can not access the rest of my databases and when (if) I get the time to play with the privileges and get that setup then I'll update this post in the mean time I'd recommend when you first run xbmc use a privileged account then once the database is created revert to one with access to only the latest database.