MySQL Database: Multiple XBMC HTPC's with 1 Shared Library

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #1
Since XBMC SVN r28142 MySQL has been supported for hosting the library database so multiple instances of XBMC can connect to and share the same libraray. The original MySQL thread has become a bit large and there are a lot of users having the same issues over and over. With a lot of the info now buried throughout the thread I thought I’d write up a guide on how to set up XBMC to use a MySQL database and guide through some of the more common troubleshooting steps. I’ve meant to do this for a while so apologies for it taking a while to get up and thanks to all those who contributed to the knowledge contained in this, especially Charrua who has helped a lot of people throughout the original thread.

A lot of the guide may seem obvious to a lot of users but I’ve tried to write it from the aspect of needing no knowledge of XBMC or MySQL. That said if using this remember it’s a new feature which is still developing along with the new database ideas and is intended for people who are fairly confident with configuring it hence why it’s only available from advanced settings. The guide will be written primarily from a windows viewpoint, as that is what I’m using, but will try to keep it fairly generic in description so most can follow it. If anyone notices anything missing anyone wants amendments made to the posts just let me know and I’ll try to keep this up to date so most of the info is in one place.

I’m at work so can’t go through all the steps at the moment but will try to make it as complete as possible over the next few days and continue to update as I can to get all the info in there. If anyone is unable to get it working following these steps post details of your problem with a full debug log and someone



*****edit*****
apologies for my lack of input on this been busy elsewhere but Krazypoloc pm'd me re: additional indexes to improve the performance of your mysql db see his post http://forum.xbmc.org/showpost.php?p=746...stcount=90 for details and I'll update post 5 if anyone has anything that should be added let me know.
(This post was last modified: 2011-04-13 22:10 by craigd.)
find quote
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #2
The first thing you need to do is decide if MySQL is the best option for you. Whilst it has a lot of advantages there are also several disadvantages and other ways to achieve similar results so way up the pros and cons and decide if you really need a shared library at the moment.

Advantages:
 New content only needs to be scanned once
 Library always consistent on all systems
 Update all pc’s libraries from one system
 Stop watching a video and resume from same point in any room
 Watched flags sync across all htpc's


Disadvantages:
 Images do not get stored in database
 Profiles not supported so each user will require unique databases if profiles used
 If network down no access to library
 Can be more problematic to set up
 Can be slower than inbuilt local db
 Some addons do not support MySQL databases
 Slow entry into some views
(This post was last modified: 2010-11-18 15:47 by craigd.)
find quote
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #3
Before you can use a shared database you need to set up a MySQL server for XBMC to connect to. Ideally this should be installed on a PC that will be always on.


Download MySQL Community Server from here

Install MySQL (to follow….)

Configure User (to follow…Wink
(This post was last modified: 2010-11-18 14:46 by craigd.)
find quote
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #4
Once you have a MySQL server to connect to you need to set XBMC to use this instead of the default sqllite database. To do this we need to update the advanced settings to direct it to the MySQL server for the chosen content type.

If you have already created a library it should first be backed up so we can import it into the MySQL database when created. In XBMC go to settings then video then select option “Export Video Library”. On the pop choose to export as a “single file” and choose a location to save the backup. It can take quite a while to complete so leave XBMC running and go do something else so you don’t get tempted to tinker. Once completed repeat the steps only this time going to settings then music then select option “Export Music Library” and again export as a single file. Once this has been done close xbmc and we will create the settings to change to MySQL.

Go to you userdata folder which can be found in the following locations depending on your OS:

Win XP = c:\ Documents and Settings\[user]\Application Data\XBMC\
Win7/Vista = c:\ Users\[user]\AppData\Roaming\XBMC\
Linux = $HOME/.xbmc/userdata
Mac = /Users/<your_user_name>/Library/Application Support/XBMC/userdata”


and check if the file advancedsettings.xml exists.

If there is no advancedsettings.xml we will need to create it open a new document in a text editor and paste the following then save the file as advancedsettings.xml

Quote:<advancedsettings>

</advancedsettings>

once you have an advancedsettings.xml open it in your text editor and insert the following between the <advancedsettings> tags , remember to edit in your own systems details…

for video database…
<videodatabase>
<type>mysql</type>
<host>192.168.0.10</host>
<name>xbmc_video</name>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>


for music database…
<musicdatabase>
<type>mysql</type>
<host>192.168.0.10</host>
<name>xbmc_music</name>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>


with the following edits…
<type> = mysql
<host> = IP address of the PC MySQL is installed on
<name> = the name to be given to the database schema, these must be unique for each database)
<user> = the username you have set up in MySQL
<pass> = the password for the user you have set up in MySQL

Save the file and close it, you can now open XBMC, when XBMC opens expect it to take a bit longer as it will log on to the MySQL server and create the required database schemas and tables. You should now be ready to create your library.

If you backed up an existing library you can go to settings then video then select “Import Video Library” and navigate to the file you created at the start and import your old library data. Repeat this for music if required. You can then go to your videos files view and set the content on the folders you want in your video library , then go to the music files view and scan the folders you want to be included in your music library.

Finally copy the advancedsettings.xml file, or update the existing advancedsettings.xml, files on any other XBMC htpc’s so all PC’s now point to your MySQL server.
(This post was last modified: 2010-11-18 15:51 by craigd.)
find quote
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #5
There are a number of common issues when first setting up I’ll track as many of them here as I can but first here’s a list of common items which cause issues, thanks to avus m3 for original collation of these.

1. Ensure you have a build that supports MySQL, Camelot 9.11 is only equivalent to r26018 and a minimum build number of r28142 is required. I’d recommend using the newest Dharma build available.
2. Ensure your sources are valid and accessible and will scan to a local db with the advanced settings removed
3. Ensure user has been created in mysql
4. Ensure user has create, and edit access to server
5. Ensure user has create and edit access to schemas
6. Ensure character and collation of schema is set to latin1
7. Ensure all sources are defined with smb links and not local defined
8. Ensure there are no occurrances of “\” in your sources MySQL requires all path seperators to be “/”
9. Mysql server needs to be contactable on port 3306, Try connecting with all firewalls disabled to see if this is being blocked.



In addition to these issues a number of users have noticed lockups and slow downs accessing certain views here’s the solutions found so far, thanks to albaranov for the music fixes.

1. Delay of 3 minutes when you click on an Artist in Library mode.
Fixed by adding index: CREATE INDEX test_idx ON song(idAlbum);

2. Delay of 20 seconds when you click on "Artists"
Fixed byadding index: alter table song add index idArtist(idArtist);

3. Delay of 10 seconds when you click on "Genres"
Fixed by: alter table song add index idGenre(idGenre);


****update 13/04/2011: indexes how to by Krazypoloc****
Krazypoloc Wrote:Alright guys I got it all figured out (famous last words). Smile
Code:
C:\Documents and Settings\Aaron>[b]mysql -u root -p xbmc_music[/b]
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.41 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> [b]CREATE INDEX test_idx ON song(idAlbum);[/b]
Query OK, 21514 rows affected (1.50 sec)
Records: 21514  Duplicates: 0  Warnings: 0

mysql> [b]alter table song add index idArtist(idArtist);[/b]
Query OK, 21514 rows affected (0.56 sec)
Records: 21514  Duplicates: 0  Warnings: 0

mysql> [b]alter table song add index idGenre(idGenre);[/b]
Query OK, 21514 rows affected (0.63 sec)
Records: 21514  Duplicates: 0  Warnings: 0

Thats for the music bit....I then added...
Code:
C:\Documents and Settings\Aaron>[b]mysql -u root -p xbmc_video[/b]
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.1.41 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> [b]ALTER TABLE movie ADD INDEX ix_idFile(idFile);[/b]
Query OK, 680 rows affected (0.30 sec)
Records: 680  Duplicates: 0  Warnings: 0


mysql> [b]ALTER TABLE movie ADD INDEX idMovie(idMovie);[/b]
Query OK, 690 rows affected (0.16 sec)
Records: 690  Duplicates: 0  Warnings: 0

mysql> [b]ALTER TABLE movie ADD INDEX idFile(idFile);[/b]
Query OK, 690 rows affected (0.11 sec)
Records: 690  Duplicates: 0  Warnings: 0
********



As in the pros and cons at present posters are not able to be tracked or stored by the database. This means only the htpc which originally updates the library will shows the correct images for items in the library. At present to get the images you will need to refresh each item or enter and exit the info screen for each item. There are two main ways to automate a workaround to this issue.

1. Use symlinks to share thumbnails folder. Using symlinks all thumbnails are held on one pc (usually the same as the mysql db sits on) and rather than a folder a shortcut is essentially created which points each other pc to the shared folder.
Pros:
Only one copy of each file is created.
All systems always show correct images

Cons:
Network performance will control how quickly images can be loaded so can impact user experience.

2. Synchronise folders so copies of files are held centrally and copies are cascaded to all other htpc's. Using tools such as synctoy, rsynch, dropbox folders can be set to replicate manually or automatically depending on selected tool.

pros:
guaranteed best performance as files are cached locally
simple to set up

cons:
images not available until sync completes
not all tools automate easily and some cost money
(This post was last modified: 2011-04-13 22:13 by craigd.)
find quote
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #6
Below are the issues I'm aware of that are affecting users of MySQL databases

1. Thumbnails are not replicated automatically across all systems.
2. Recently Added Scripts don;t always show movies
3. 20 second freeze entering and exiting movie library
4. Slow downs and freezing navigating music library
(This post was last modified: 2010-11-18 16:09 by craigd.)
find quote
avus m3 Offline
Senior Member
Posts: 245
Joined: May 2010
Reputation: 0
Post: #7
Thanks for taking the initiative to group this info together. I have been contemplating doing this for awhile but I have too much on my plate right now. Fortunately, I graduate college in 3 weeks so with just having to worry about work hopefully I will have more time for hobby stuff. I recommend symlinking both advancesettings and sources.xml. I haven't seen anyone try this up to this point but I have been using it for awhile with no problems whatsoever. This way if you change IP or db names, all pc's are synced. Plus it saves from errors in creating the xml. For sources, it keeps all pc's on the same page if you add a new folder to one of your libraries automatically. It's also kind of a pain but if you went from say Beta 1 or 2 to say 4, I would recommend deleting the db and recreating to solve a lot of little issues. As new builds are rolled out, new features/changes are implemented that might cause quirks.

As I mentioned creating a new thread that should be stickied, as info changes and new info is presented the first couple post should be updated. That way problems/solutions and tips are not scattered across 400 post. Looking forward to contributing more in the future!
(This post was last modified: 2010-11-18 19:18 by avus m3.)
find quote
darkscout Offline
Posting Freak
Posts: 2,145
Joined: Jul 2008
Reputation: 12
Post: #8
After seeing the disadvantage of "Images not stored in DB" I *knew* that some guy was tripping acid one evening and implemented it in FUSE.

I was right.
http://www.linux.com/archive/articles/127055
http://sourceforge.net/projects/mysqlfs/files/

Anyone want to test that out?
find quote
mwkurt Offline
Posting Freak
Posts: 1,064
Joined: Mar 2010
Reputation: 10
Post: #9
Craigd,

There is another issue that you may want to include. Make sure the port that mysql db use (3306?) is open on your mysql pc. Of course, you may be including that in the "connect with all firewalls off". Would that be the same thing?

Mark
find quote
craigd Offline
Senior Member
Posts: 285
Joined: May 2007
Reputation: 0
Post: #10
mwkurt Wrote:Craigd,

There is another issue that you may want to include. Make sure the port that mysql db use (3306?) is open on your mysql pc. Of course, you may be including that in the "connect with all firewalls off". Would that be the same thing?

Mark

Yeah had tried to simplify it but will update to reflect actual port number.

Will try to do a complete install of sql again and write the step by step for that section this weekend
find quote
twotone8 Offline
Senior Member
Posts: 116
Joined: Apr 2010
Reputation: 0
Post: #11
would it make more sense to run xbmc in portable mode from a dropbox folder while keeping your media in the same mapped network drive on all computers?
(This post was last modified: 2010-11-19 23:50 by twotone8.)
find quote
castortray Offline
Fan
Posts: 496
Joined: May 2009
Reputation: 0
Post: #12
Hi,
I wrote a short instruction How to configure XBMC with MySQL step by step.
xbmc_mysql
I use xbmc on 4 pc, and I love this synchronization.

Now I need to think how to automatically create backup of database once a week and save last three backups.

Any help is appreciated Smile
find quote
webmosher Offline
Member
Posts: 70
Joined: Jan 2009
Reputation: 0
Location: Toronto, ON
Post: #13
castortray Wrote:Hi,
I wrote a short instruction How to configure XBMC with MySQL step by step.
xbmc_mysql
I use xbmc on 4 pc, and I love this synchronization.

Now I need to think how to automatically create backup of database once a week and save last three backups.

Any help is appreciated Smile

I've used this reliably for quite some time. Generally easy to setup if you are familiar with modifying scripts:

http://sourceforge.net/projects/automysqlbackup/

Throw a in a cron entry for it and you are all set.

Enjoy.
find quote
steve1977 Offline
Posting Freak
Posts: 1,970
Joined: Dec 2006
Reputation: 0
Post: #14
craigd Wrote:Disadvantages:
 Images do not get stored in database
 Some addons do not support MySQL databases

quick question. Given all my posters are stored in the movie folder, does this impact me? Also, which are the major non-supported add ons. I recall the lack of add-on support prevented me before, but not sure whether still reel ant.

Thanks!

Server: Asus Sabertooth Z77 | Intel Core i5 3.4 GHz | 12 GB DDR3 | 256 GB SSD, 40 TB (1 x 4 TB, 6 x 3TB, 9 x 2 TB)
HTPC 1: Apple Mac Mini (mid-2011) | Intel Core i5 2.5 GHz | 4GB DDR3 | 500 GB
HTPC 2: Apple Mac Mini (mid-2007) | Intel Core 2 Duo 2.0 GHz | 4 GB DDR2 | 16 GB SSD
find quote
czfj5r Offline
Member
Posts: 58
Joined: Jul 2010
Reputation: 0
Location: Sweden
Post: #15
castortray Wrote:Hi,
I wrote a short instruction How to configure XBMC with MySQL step by step.
xbmc_mysql
I use xbmc on 4 pc, and I love this synchronization.

Now I need to think how to automatically create backup of database once a week and save last three backups.

Any help is appreciated Smile

Hi,

I have used this little trick to do backup of the database. Perhaps it can help. Windows related though...

1. Open Notepad
2. Add the following to the file:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=xbmc --password=xbmc --add-drop-table --all-databases --result-file=C:\Temp\backup-%DATE%.sql --dump-date

3. Save the file with a .cmd at the end, as example "MySQLBackup.cmd"

4. Go to control panel / schedule task / add new task

5. When asked for the program to run, browse to your newly created file and choose it. Then continue the wizard and choose the options you want (daily, weekly, ...)

Done.

In the commandtext above you need to be aware of the following:
- adjust the path to the mysqldump.exe if needed
- Insert your user and password (I used xbmc as example above)
- I have a %DATE% in the filename above, please be aware that if you use "/" as date seperator it will not work as that is not allowed in teh filename so change your regional settings to use "-" as date seperator
- after the --result-file= you can add the place you want the backup to be put (as example onto another share, usb-stick or...)

I also saw you wanted to remove things automatically after some time. I found the following commandline item you can try to add to the file. I have NOT tested it but feel free Smile

forfiles /p C:\Temp\ /m backup-*.* /d -7 /c "cmd /c del @path"

This command should remove files older then 7 days so just change the 7 to whatever you need. As said, not tested yet...

Hope it helps. You also do not have to add any other program as mysqldump comes with the MySQL installation
find quote
Post Reply