We recently decided to move our HTPC running XBMC Eden 11.0 library to a MySQL backend. Doing so will keep our 6 XBMC installs synced to the one library (we do the with our media files through samba shares). With each install currently running separate libraries we can never tell what has been watched from one room too the next. I’ve made a few notes during the change over which may help others.
XBMC by default uses SQLite when storing the library information, this could not be synced between installs without the use of (sometimes troublesome) scripts. For some time now the XBMC project has implemented experimental support for a centralised library using MySQL. We had experimented with this previously but found it problematic due to the database versions being upgraded between SVN releases on the Team XBMC PPA. This occasionally left stable releases unable to talk to the newer svn upgraded database. Not all our hardware running XBMC was able to upgraded to a SVN release as a result we were forced us to revert back to the localised SQLite library for a time.
Keeping in mind we had tried this previously there was still some old XBMC data left in the MySQL install. Simply connecting XBMC to the existing databases resulted in numerous ACCESS DENIED errors. As it turns out, the XBMC team had changed the way the XBMC Mysql functionality works in the current releases. In current versions the database itself is versioned (i.e. xbmc_video60 for Version 60) which is then copied to a new database on first library update run of a new release. What this means is the xbmc MySQL user now needs the ability to create the actual databases (not just tables).
The XBMC Wiki lists a set of commands which grants the XBMC user complete administrative access to the MySQL install. This is fine for a single use MySQL install but is a unnecessary risk for any multi-database install. Any error with the XBMC SQL statement execution could corrupt any/all databases present in the install. It is true we are forced into setting up the XBMC user as an administrator but a better way is to limit the user access through the table names. Limiting the XBMC users ability to only create databases prefixed with “xbmc_” through MySQL privileges.
A preferable command for XBMC Wiki step 5.2 (Linux Tab)
GRANT ALL PRIVILEGES ON `xbmc_%`.* TO 'xbmc'@'%';
The xbmc_% in the first command tells MySQL your user can create/administer databases with the prefix xbmc_ but nothing else
Ultimately you will want to follow the XBMC teams recommendation and export your existing library, re-importing it once the connection to you new MySQL library is successful.
Simply renaming the existing databases from xbmc_videos to xbmc_video60 resulted in more errors, although it may work for you. Try renaming the database first then check the XBMC log file.
If renaming the existing database fails remove the existing databases, leaving no databases for XBMC pre-made. If you do you will receive additional errors.
Run XBMC, setup your content and begin building a new library. XBMC will create the required database and tables at the beginning of the scan.
NOTE: XBMC does not sync thumbnails, fanarts, etc with the MySQL library; there are other methods for syncing the media files between installs.