Register for the iXsystems Community to get an ad-free experience and exclusive discounts in our eBay Store.

XBMC mysql for a centralised XBMC watched and progress library

Western Digital Drives - The Preferred Drives of FreeNAS and TrueNAS CORE
Status
Not open for further replies.

rm-r

Member
Joined
Jan 7, 2013
Messages
166
Hi Guys,

Just thought is post here for how to set-up 9.1.1 with mysql for a centralised XBMC watched and progress library

http://wiki.xbmc.org/index.php?titl...ySQL/Setting_up_MySQL#tab=FreeNAS_and_freeBSD

I collated information from around the place and put in one guide - works wonderfully!

I have put a copy below for safe keeping / link death

Cheers
RM-r

----------------------------------------------------------------------------------------------------------------------


=== Make a jail ===

create a "STANDARD" Jail via the FreeNAS webui - if unsure see here http://doc.freenas.org/index.php/Adding_Jails

make a note of the jail IP address shown - you will need this to put in the advancedsettings.xml file on the XBMC machine later

=== Prepare the jail ===


ssh into FreeNAS
If you have a windows PC use putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html)
(easiest) click the "shell" button in the left of the FreeNAS webui


type the following commands

Code:
jls


Take note of the JID of the jail you created, use it in the command below and replace n with the number of the jail you just created

Code:
jexec n csh


=== Install mysql ===
You are now inside your jail

Code:
pkg install mysql55-server


Code:
pkg install nano

(nano is a text editor you will need shortly)

We now need to enable mysql in rc.conf

Code:
nano /etc/rc.conf


insert the line below into the rc.conf file

Code:
mysql_enable="YES"


save the file and exit (see bottom of nano edit page for keystrokes info)

Code:
service mysql-server start


Optional step below - this is an automated script that will secure you mysql instance - it will prompt you for answers to questions. Its up to you if you do this.

Code:
mysql_secure_installation


=== Configure mysql ready for xbmc to use ===

type in

Code:
mysql -u root -p



You are now in mysql administration


Code:
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';

Code:
GRANT ALL ON *.* TO 'xbmc';

Code:
quit;


that's it - proceed to the next step below and configure XBMC to use your FreeNAS hosted XBMC MYSQL database - I love mine!
 

ljstronge

Neophyte
Joined
Jun 27, 2013
Messages
7
This works perfectly. Thanks very much for this. I have had the mysql running on one of my XBMC clients which was a real pain and something I had to leave on. Now I can have both components on the same kit.

Good work :)
 

CMO Tchibo

Newbie
Joined
Jan 9, 2014
Messages
3
Hi

do I need a additional repository to install the mysql55-server package?
because running this line:
Code:
pkg install mysql55-server


brings me this:
installing the Nano package worked

root@mysql_1:/ # pkg install mysql55-server Updating repository catalogue pkg: No packages matching 'mysql55-server' has been found in the repositories
installing the Nano package worked
 

Yatti420

Neophyte Sage
Joined
Aug 12, 2012
Messages
1,437
I like this idea.. I have some questions.. The versions of XBMC matter it looks like.. I don't think I'd be able to use this for differing versions of XBMC or an xbmc4xbox install..
 

CMO Tchibo

Newbie
Joined
Jan 9, 2014
Messages
3
Sorry, apparently I can not edit my old posts.
ok, my post in a better form with more information.

I'm running FreeNAS 9.1.1 (x64)
created a Jail
Jail: "mysql_1"
Autostart: True
Status: Running
Type: pluginjail


when I use the Configsite Shell-Access to enter the jail
I can install the nano package as described,
but when I run the command to install the mysql55 package I get this error message:
Code:
root@mysql_1:/ # pkg install mysql55-server
Updating repository catalogue
pkg: No packages matching 'mysql55-server' has been found in the repositories
 

rm-r

Member
Joined
Jan 7, 2013
Messages
166
Hi,

it works for me.... just made a new jail and tried... are you using FreeNAS 91.1 or higher?

Code:
root@mysql-xbmc:/ # pkg install mysql55-server
pkg: PACKAGESITE in pkg.conf is deprecated. Please create a repository configuration file
Updating repository catalogue
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
digests.txz                                                                  100% 1081KB 540.4KB/s 200.9KB/s  00:02
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
packagesite.txz                                                              100% 5038KB 503.8KB/s 197.5KB/s  00:10
Incremental update completed, 23127 packages processed:
22906 packages updated, 365 removed and 221 added.
New version of pkg detected; it needs to be installed first.
The following 1 packages will be installed:
 
        Upgrading pkg: 1.2.1 -> 1.2.4_1
 
The installation will require 767 kB more space
 
1 MB to be downloaded
 
Proceed with installing packages [y/N]: y
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
pkg-1.2.4_1.txz                                                              100% 1772KB  37.7KB/s 331.6KB/s  00:47
Checking integrity... done
[1/1] Upgrading pkg from 1.2.1 to 1.2.4_1... done
If you are upgrading from the old package format, first run:
 
  # pkg2ng
pkg: PACKAGESITE in pkg.conf is deprecated. Please create a repository configuration file
Updating repository catalogue
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
digests.txz                                                                  100% 1081KB 360.3KB/s 660.9KB/s  00:03
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
The following 2 packages will be installed:
 
        Installing mysql55-client: 5.5.35
        Installing mysql55-server: 5.5.35
 
The installation will require 104 MB more space
 
7 MB to be downloaded
 
Proceed with installing packages [y/N]: y
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
mysql55-client-5.5.35.txz                                                    100% 1492KB 497.4KB/s 202.3KB/s  00:03
pkg: Warning: use of http:// URL scheme with SRV records is deprecated: switch to pkg+http://
mysql55-server-5.5.35.txz                                                    100% 6141KB 153.5KB/s 551.4KB/s  00:40
Checking integrity... done
[1/2] Installing mysql55-client-5.5.35... done
[2/2] Installing mysql55-server-5.5.35...===> Creating users and/or groups.
Creating group 'mysql' with gid '88'.
Creating user 'mysql' with uid '88'.
done
************************************************************************
 
Remember to run mysql_upgrade the first time you start the MySQL server
after an upgrade from an earlier version.
 
************************************************************************
root@mysql-xbmc:/ #
 

Yatti420

Neophyte Sage
Joined
Aug 12, 2012
Messages
1,437
With UPNP-Pictures working as of 12.3 i'm going to give this a shot.. This only applies to the standard xbmc.org project.. XBMC4XBOX isn't compatible..

Edit: Can't be used on xbmc4xbox.. So that leaves Windows/Ubuntu setups etc.. Any issues running 2 clients (different OS) XBMC 12.3 frodo simultaneously?? Should be fine.. I noticed nano was recommended.. Why is ee (easy editor) not used - as it is integrated?
 

rm-r

Member
Joined
Jan 7, 2013
Messages
166
sure you can use ee - i just use nano from when i use linux - habit i guess :)
 

Yatti420

Neophyte Sage
Joined
Aug 12, 2012
Messages
1,437
It's like a whole new world when you start using the PC builds versus the original xbox builds :) Now does anybody have two clients or more looking at the same database/library on the server? Once everything finishes rebuilding I'm gonna boot up the client on my lappy.. Both are 12.3 frodo now.. One Ubuntu/One Windows..
 

CMO Tchibo

Newbie
Joined
Jan 9, 2014
Messages
3
OK it worked now.
I created the wrong JAIL.

I created a "pluginjail" at first and the mysql installation didn't work
I now created a "standard" Jail and the installation worked fine.

But I realized a mistake in your last SQL statement:
  1. GRANT ALL ON . TO 'xbmc';
needs to be
  1. GRANT ALL ON *.* TO 'xbmc';
 

rm-r

Member
Joined
Jan 7, 2013
Messages
166
But I realized a mistake in your last SQL statement:
  1. GRANT ALL ON . TO 'xbmc';
needs to be
  1. GRANT ALL ON *.* TO 'xbmc';
fixed thanks!

I now created a "standard" Jail and the installation worked fine.
Now does anybody have two clients or more looking at the same database/library
yes thats the whole purpose, so you can move around the house and then watch when you left off! longe to kitchen / kids room etc - works great!
 

Yatti420

Neophyte Sage
Joined
Aug 12, 2012
Messages
1,437
I have it working.. I'm not sure if it's working perfectly.. Bookmark thumbnail's don't seem to be working.. I'm assuming thumbnails would have to be stored on the NAS aswell.. These are wireless clients.. Hoping I can test wired soon..

Edit: Does anybody else have this issue with xbmc bookmark thumbnails.. I rebuilt my library from scratch and got everything setup nicely.. No issues with basics.. Testing substitution for more advanced items now.. rss playlist sources etc..
 

SilverJS

Senior Member
Joined
Jun 28, 2011
Messages
255
Hi there,

I had all this running perfectly on my FreeNAS 8.2 system - until I moved to my wife's place, and all hell broke loose because I couldn't change my jail's IP...anyhow, ended up installing 9.2.

To the OP : I have a problem that I've never run into before. I created the MySQL, just like I have a hundred times, but when I switch my XBMC install over to it, I can't add anything to the library! If I point to my exported library, nothing happens. If I try to manually add a source, nothing happens. Kinda confused....any ideas? Seems like the MySQL won't let XBMC 'write' to it, almost.

To the poster above : What I've always done is, store the Thumbnails on the NAS, and create symbolic links in the XBMC userdata directory that points to the Thumbnails folder on the NAS, and you're set.
 

rm-r

Member
Joined
Jan 7, 2013
Messages
166
Check the XBMC logs for sql issues, point you in the right direction

Sent from my GT-I9300 using Tapatalk
 

SilverJS

Senior Member
Joined
Jun 28, 2011
Messages
255
Log file :

http://xbmclogs.com/show.php?id=158614

advancedsettings.xml contents :

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>172.16.1.70</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>172.16.1.70</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
</advancedsettings>

...ideas? Why is it saying that 172.16.1.71 has been blocked or what not in the logs? That's not the address I have in the advanced settings...
 

rm-r

Member
Joined
Jan 7, 2013
Messages
166
Have you tried the flush host command as suggested in the log?

Sent from my GT-I9300 using Tapatalk
 

SilverJS

Senior Member
Joined
Jun 28, 2011
Messages
255
I was just researching exactly how to do that. Turns out the actual command is :

FLUSH HOSTS;

...that is, once logged in via the usual :

  1. mysql -u root -p
 

SilverJS

Senior Member
Joined
Jun 28, 2011
Messages
255
But yes, XBMC is now adding the movies from my collection normally, so it should work.
 

rm-r

Member
Joined
Jan 7, 2013
Messages
166
Great

Sent from my GT-I9300 using Tapatalk
 
Status
Not open for further replies.
Top