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

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
castortray Offline
Fan
Posts: 496
Joined: May 2009
Reputation: 0
Post: #16
czfj5r Wrote: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

This is exactly what i was looking for, Thank You Smile
find quote
bionic1234 Offline
Senior Member
Posts: 206
Joined: Oct 2006
Reputation: 0
Post: #17
craigd Wrote:Disadvantages:
 Profiles not supported so each user will require unique databases if profiles used

Any tips for how to setup profiles with their own unique database?
find quote
Bikeman Offline
Junior Member
Posts: 4
Joined: Jan 2011
Reputation: 0
Post: #18
Dear all,

I'm quite new to XBMC and actually stumbled upon it looking for a decent media library, not really a media center. Looking at the demo videos I was quite impressed, and decided to give it a chance, also as a media center.

The main target remained to have a centralized media library, however. Centralized storage of all data is important to me. I have a server running in the house, including a MySQL-server. I also use this server as a file storage. So this thread was really very helpful to get things up and running. Thanks for this! Smile

There were still some quircks, however. The centralized thumbnail and fanart storage appears not to be really straightforward yet, as they are stored in the userdata-folder by default, and this appears not to be customizable. Hard symlinks might be a beautiful solution for this, but in Windows Vista/7 they don't work with network shares ... I may have found a workaround for this issue, however! Big Grin

When I was looking at the wiki-page about profiles, I noticed this:
Code:
...
<profile>
        <name>spiff</name>
        <directory>q:\userdata</directory>
        <!--allows to move the userdata dir-->
...
Interesting, as the thumbnails and other files reside under the userdata folder. The question remained: does this work with network shares?

Well, it does! Below I'll describe what I've realized.

1) Video and Music Databases
As described in this and other threads, I added a user to my existing MySQL, created 2 databases and granted the new user full rights to these databases. I added the advancedsettings.xml file to the userdata-folder on my local PC.

2) File shares
To increase security (passwords further on are stored in clear text...) I created a new user group on my Windows XP Pro SP3 server. I created a new user also, that is only member of the new user group. I have a user "XBMC" being member of "FileShare Only" group. Creating the group makes sure that user XBMC can only be used for SMB file access, not for logging on to the server.
Then I created a few new shares. The first set was for my media files. I gave the user "XBMC" read-only access to these shares. The last share I created was an "XBMCData" share. I gave "XBMC" and the Administrators-group full access to this share.
As a last step I copied all files from the local userdata-folder to this new share on my server.

3) Profiles.xml
In the third step I modified the profiles.xml-file that XBMC had created in the userdata-folder on my local PC (not the system folder as is indicated on the wiki!). I modified the default profile to this:
Code:
<profiles>
    <lastloaded>0</lastloaded>
    <useloginscreen>false</useloginscreen>
    <profile>
        <name>Master user</name>
        <directory pathversion="1">\\server\XBMCData</directory>
        ...
I also tried the "smb://"-style syntax, so I could select the user used to connect to the share, but this does not work.

4) Sources
As described in this and other threads, I created the sources in XBMC. They all use the "smb://"-style syntax (as they are located on the server).

Using this configuration I am able to use XBMC with a minimum of data stored locally. Some finetuning and testing might be needed, especially testing. Preliminary conclusion seems that it's slightly slower, but usable.

If anything comes up, I'll post it here. But I felt that I should share this with you all.

Any comments or suggestions more than welcome!
find quote
Bikeman Offline
Junior Member
Posts: 4
Joined: Jan 2011
Reputation: 0
Post: #19
Ok, meanwhile for one reason or another things are not working anymore. I restarted XBMC. Then, when I went to the video library XBMC told me that "This file is not present anymore. Do you want to delete it from the library?". I said 'yes'. I could do this a few times.

Next time I went to "Videos", it didn't ask me anything anymore. XBMC just hangs, after showing "Working..." for a few seconds ...

This is what the logfile says when I enable debug logging:
Code:
...
23:31:04 T:6968 M:1537687552   DEBUG: ------ Window Init (MyVideo.xml) ------
23:31:04 T:6968 M:1537687552    INFO: Loading skin file: MyVideo.xml
23:31:04 T:6968 M:1536937984   DEBUG: CGUIMediaWindow::GetDirectory ()
23:31:04 T:6968 M:1536929792   DEBUG:   ParentPath = []
23:31:04 T:2808 M:1536757760   DEBUG: thread start, auto delete: 0
23:31:04 T:2808 M:1533620224   DEBUG: Thread 2808 terminating
23:31:09 T:6968 M:1555927040   DEBUG: CGUIMediaWindow::GetDirectory (smb://XBMC:***@server/Movies/)
23:31:09 T:6968 M:1555927040   DEBUG:   ParentPath = []
23:31:09 T:6680 M:1555750912   DEBUG: Trying to connect to \\server\Movies with username(XBMC) and password(XXXX)
23:31:09 T:6680 M:1555701760   ERROR: CThread::staticThread - Unhandled exception caught in thread process, attemping cleanup in OnExit
23:31:09 T:6680 M:1555701760   DEBUG: Thread 6680 terminating (autodelete)
23:31:09 T:6968 M:1555828736   DEBUG: ------ Window Init (DialogBusy.xml) ------
23:31:09 T:6968 M:1555828736    INFO: Loading skin file: DialogBusy.xml
After adding this to the log file, XBMC hangs ("Not Responding"). I kill the process in the Vista Task Manager, after which one last message is added to the log:
Code:
23:31:34 T:2016 M:1562222592   DEBUG: Thread 2016 terminating (autodelete)

I'll try to fix this tomorrow or the day after, but for now: Sorry for posting the above (slightly euphoric) post without more thorough testing! Blush
find quote
czfj5r Offline
Member
Posts: 58
Joined: Jul 2010
Reputation: 0
Location: Sweden
Post: #20
Bikeman,

Nice description above and I hope you get it working. Would be nice to keep it in the advanced settings file if possible.

Just a note though - I use Windows 7 and Symlinks work great there. In my understanding it should also work in Vista. I saw you mentioned that it did now work in Vista/7? Or did I get it wrong.

Anyway.. symlinks work but they have to be created from the command line (cmd.exe)

Again, I hope you get it all fixed through the alternative route you work on Smile

Here is the syntax I used:
"mklink /d Thumbnails \\server\share"
(This post was last modified: 2011-01-02 01:20 by czfj5r.)
find quote
steve1977 Offline
Posting Freak
Posts: 1,980
Joined: Dec 2006
Reputation: 0
Post: #21
steve1977 Wrote:quick question. Given all my posters are stored in the movie folders, 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!

Any thoughts on above?

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: #22
As far as I have experienced I have not seen any add-ons that are impacted by me running central MySQL. I have tried what I think is the major ones but not sure what is considered "major". Anyway, from my view i had no issues.

In regards to posters with movies i am not sure what you are after. If you have teh NFO file with the movie and poster then XBMC will take that information into the database and store the poster under Thumbnails as far as I understand. if there is no nfo then it will most likely grab a suitable one from Internet and store it away.

If you go to the movie and hit "i" you get the info window. from there you can hit the update and then XBMC will ask if you want to update from local info or go to internet in case you want to update OR you can browse to the poster yourself. Once chosen it is stored as the selected one into Thumbnails so your other XBMC instances will use the same one as well.

Hope it helps, otherwise feel free to elaborate Smile
find quote
Bikeman Offline
Junior Member
Posts: 4
Joined: Jan 2011
Reputation: 0
Post: #23
czfj5r Wrote:Here is the syntax I used:
"mklink /d Thumbnails \\server\share"
Again, I apparently was too much in a hurry ... Symlinks do work, it's hard links (junctions) that don't work. (Wikipedia on links in MS Windows) But symlinks can do the job, I guess?

I'm still continuing my experiment, though. Windows XP users don't have symlinks available (I think), so for them my way of working can be a solution.

I might have found the cause for the issue in my second post. I reinstalled XBMC and adding the network path didn't work, unless I used the server's IP address. I recall using the server name before, so maybe the IP address is more stable.

Also, notice that because the smb://-syntax doesn't work in the directory-property, the user that runs XBMC locally needs to have read/write access to the XBMC data directory on the server!
find quote
steve1977 Offline
Posting Freak
Posts: 1,980
Joined: Dec 2006
Reputation: 0
Post: #24
czfj5r Wrote:As far as I have experienced I have not seen any add-ons that are impacted by me running central MySQL. I have tried what I think is the major ones but not sure what is considered "major". Anyway, from my view i had no issues.

Thanks, I believe that I remember what has not been working with mysql. It was not an addon, but the remote. Is there any ipad/iphone remote out that works despite using mysql server. I believe that this is what has stopped me from using it in the past.

czfj5r Wrote:In regards to posters with movies i am not sure what you are after. If you have teh NFO file with the movie and poster then XBMC will take that information into the database and store the poster under Thumbnails as far as I understand. if there is no nfo then it will most likely grab a suitable one from Internet and store it away.

My question was refering to the opening post stating that "Images do not get stored in database". I was wondering whether this shortcoming is effecting me given I store all posters locally in the movie folder anyways?

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
cmonpellier Offline
Member
Posts: 83
Joined: Oct 2009
Reputation: 0
Post: #25
steve1977 Wrote:My question was refering to the opening post stating that "Images do not get stored in database". I was wondering whether this shortcoming is effecting me given I store all posters locally in the movie folder anyways?

Yes it will because there is a 'cached' version of the poster in the Thumbnail folder. What this mean is that the first client that updates the library will get the poster. The second client will realize that no update is necessary (because the database is updated which is intended) and will look for the poster in the Thumbnail folder. If you do not have a symlink to a central location then it will not display anything.

A symlink is working perfectly. On Windows you will need to store credentials to have access to it, so the easiest way to do this is to map the share to a drive with auto-connect and store credentials. Then you can use mklink /D X:\ Thumbnails. Note that on Win7 you will need to run the cmd as Admin to be able to create that symlink in the user tree.
find quote
cmonpellier Offline
Member
Posts: 83
Joined: Oct 2009
Reputation: 0
Post: #26
bionic1234 Wrote:Any tips for how to setup profiles with their own unique database?

You need a seperate advancedsettings.xml for each profile (one at the root for the master user and one under each profile\'name' folder).

In the 'root' advancedsetting use the standard

Code:
<videodatabase>
<type>mysql</type>
<host>localhost</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>xbmc_video</name>
</videodatabase>

and for each profile change the name of the database to

Code:
<videodatabase>
<type>mysql</type>
<host>localhost</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>[b]xbmc_profile1_video[/b]</name>
</videodatabase>
find quote
cmonpellier Offline
Member
Posts: 83
Joined: Oct 2009
Reputation: 0
Post: #27
twotone8 Wrote: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?

You are going to have concurrency issues (and possible database corruption) if 2 clients are open at the same time. This is where mySQL helps.
find quote
travisbell Offline
themoviedb.org (TMDb) admin
Posts: 137
Joined: Aug 2008
Reputation: 1
Location: Calgary, Canada
Post: #28
cmonpellier Wrote:You are going to have concurrency issues (and possible database corruption) if 2 clients are open at the same time. This is where mySQL helps.

I've got the MySQL backend setup but I'm curious, I'd like to have my 2 clients configured as easily and similarly as possible (both running the exact same hardware, remotes, network and media server) so could you just throw your userdata folder on a network share? Or is that not recommended when instead, only throwing the Database and Thumbnail folders somewhere?

Bottom line, what can one sync back to a network/remote share to have little duplication across his network but not run into any concurrency issues?
find quote
SpeedFreak73 Offline
Junior Member
Posts: 4
Joined: Jan 2011
Reputation: 0
Post: #29
Hi,

I was wondering if somebody could help. I have a QNAP 410 which stores my movies, TV series. I also have mySQL installed on it so I can use one database. When I setup XBMC to use the remote database I get very low response when going to the Movie section:

20:50:45 T:3079346048 M:2807828480 DEBUG: Time for actual SQL query = 1123
20:50:49 T:3079346048 M:2806939648 DEBUG: Time to retrieve movies from dataset = 3855

I had also mySQL installed on my quad-core Atom 1.6Ghz server to see if that helps but get same times.

These times are same on my laptop (Thinkpad T410i, quad-core 2.4Ghz) and desktop (Intel i7 1.8Ghz)

[Edit] Forgot to mention, there are 682 movies in the database.

Any help or tips would be appreciated.

Stephan
find quote
Avigrace Offline
Senior Member
Posts: 132
Joined: Mar 2009
Reputation: 0
Post: #30
I have set this up and sync my thumbnails between all PC's at regular intervals.

If I enter movies it takes a second or two to load up the movies which is fine (occasionally it takes 20-30 seconds), after that it scrolls through them all as fast as if they were locally connected which is good. If I then go back to the main menu XBMC seems to slow to an incredible crawl for several minutes (maybe as many as 5) after which it's back to being normal. Has anyone else experienced this? I am only using the database for movies and not music and my clients are all Revo's with Win 7.
find quote
Post Reply