On to next challenge : MySQL and XBMC! =)

[SilverJS]

Not sure if this belongs in plugins, since it's not a plugin per se. (If not, mods, please feel free to move to appropriate section.) It's kind of a request that pertains to an add-on, I guess - and from the little I've read, it'll have to be dealt with through the jail construct and such.

Thing is, if I got my FreeNAS box to also host my MySQL XBMC database, my setup would now be absolutely complete. =)

First : There is a (Light, as I understand it) version of MySQL as part of FreeNAS as it is. Correct?

Second : Is there any hope of a MySQL plugin being developed for us FreeBSD retards?

Third : FYI, the MySQL setup part, for running an XBMC database, only includes these two lines in the MySQL command line :

CREATE USER 'xbmc' IDENTIFIED BY 'xbmc'
GRANT ALL ON *.* TO 'xbmc'

After that, it's a question of pointing XBMC to the IP address of the computer hosting the MySQL database (via its advancedsettings.xml file), and creating necessary exceptions in the client computer's firewall, but all that's a cinch. So, my third question is : any way to enter these two lines of code into the MySQL database that I presume is already in my FreeNAS box?

I'm assuming that once those two lines of code were put in, the rest would just flow very nicely - point XBMC to the ip address of my FreeNAS box (to be precise, the JAIL IP address, as I assume this MySQL would be run from within the jail) and off I go?

Thanks a lot in advance! You guys rock!!!

 

[Joshua Parker Ruehlig]

freenas doesn't use mysql. I'm pretty sure it used sqlite3, so adding the 'two lines of code' isn't an option. I also want to run mysql on freenas(in the jail) for xbmc.

This is pretty much how you do it. (I'll test this Monday.. I may have the package/service names wrong below)
*ssh into your freenas box
___
jexec -u root 1 csh #enter your jail
pkg_add -r mysql
echo 'mysql_enable="YES"' >> /etc/rc.conf #add mysql to jail startup
service mysql-server start
___
At this point I'd secure mysql (there's a command for this) and add xbmc databases / users

Once all the plugin stuff is pretty set I'll make a pbi for it.

 

[SilverJS]

Thanks Joshua,

echo 'mysql_enable="YES"' >> /etc/rc.conf #add mysql to jail startup

I don't understand this line. Do I paste just this (without the quotes) : "echo 'mysql_enable="YES"' >> /etc/rc.conf" ? The two >> arrows tell freeBSD to put "mysql_enable="YES"" in the /etc/rc.conf?

At this point I'd secure mysql (there's a command for this) and add xbmc databases / users

Once all the plugin stuff is pretty set I'll make a pbi for it.

Cool! I'll have to research what securing MySQL means, but if you're up for developing a PBI, you'll have a legion of admirers. Well, starting with me, anyways. =)

How would I secure, and then access the command line, for MySQL, within FreeNAS?

 

[Joshua Parker Ruehlig]

Also yes, that command add's the line 'mysql_enable="YES"' to the jail's /etc/rc.conf file. This means it is enabled whenever the jail is rebooted / started up.

The command I meant is 'mysql_secure_installation'
I think it's built into freebsd's copy of mysql. It does a few things to close up security holes the default mysql install leaves, like an empty user and no password for the root mysql user.

 

[SilverJS]

Also yes, that command add's the line 'mysql_enable="YES"' to the jail's /etc/rc.conf file. This means it is enabled whenever the jail is rebooted / started up.

The command I meant is 'mysql_secure_installation'
I think it's built into freebsd's copy of mysql. It does a few things to close up security holes the default mysql install leaves, like an empty user and no password for the root mysql user.

OK. So - how does one issue commands to the MySQL server from within the shell/jail? Like for adding users and such?

 

[SilverJS]

OK, so I got mysql installed. The actual command was 'pkg_add -r mysql51-server'. To login to mysql, first access the jail (jexec <jailID> csh) and then type 'mysql -u root'. (Remember - I'm typing this for the guys who are totally new at this, as I am.=)) I then input the two lines of code referred to above, and successfully created the user XBMC and granted all to it.

I'm now in the process of trying it out with XBMC.

 

[SilverJS]

No go. XBMC just hangs at startup. Not sure why. This is probably way beyond me now. =)

 

[Joshua Parker Ruehlig]

check the .xbmc/temp/xbmc.log, it'll log any database problems there. make sure the xbmc user you created can be accessed from your xbmc computer's ip. Not sure if those sql commands you put up there allow xbmc to connect from any ip or only local host.

Also I had this successfully working before (though I was running mysql on freenas nativly, not a jail) so it definitely is possible.

 

[SilverJS]

OK, I'll have a look. As a question, why are we even running MySQL inside of the jail? Why not run it natively?

 

[Joshua Parker Ruehlig]

cause freenas runs off a usb stick, it does alot of things to freebsd to make it more enbeded / stable / portable, so normal packages don't work as normal. most of the fielsystem is unwritable by default and is loaded into ram upon boot.

In the end a jail is a much more elegant apporach to run something like mysql. I think theres no performance penalty for running mysql in a jail, so why not. I'm gonna play with this tonight and see where I get

 

[duckman]

If you got a spare box, you could install phpmyadmin (a webapp, if you have a web server running) or mysql workbench on it, then try connecting using that - should give some info on what's going wrong.

To allow a user to login from any host, use the % wildcard as the allowed hosts parameter on the server setup (it defaults to localhost or . which only allows connections from localhost - usually 127.0.0.1)
Or, use a specific IP to only allow that one to connect, or a subnet, eg 192.168.1.0/24 (you best check that syntax!) to allow anyone from the 192.168.1.* network.

 

[Joshua Parker Ruehlig]

well I got everything working perfectly. 2 xbmc installs, syncing their media sources, thumbnails, and video databases.
NOTE - jail ip=192.168.1.30, freenas(nfs) ip=192.168.1.4

_______________________________________________________________________________
#in freenas shell
jexec 1 csh

#in jail
pkg_add -r mysql55-server
pkg_add -r mysql55-client
mysql_secure_installation

vi /etc/rc.conf
#add in
mysql_enable="YES"
#
service mysql-server start

mysql -u root -p
#in mysql
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
GRANT ALL ON *.* TO 'xbmc';
quit;

_______________________________________________________________________________
### on my xbmc boxes ( .xbmc/userdata/advancedsettings.xml )
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.30</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>
<pathsubstitution>
<substitute>
<from>special://masterprofile/Thumbnails/</from>
<to>nfs://192.168.1.4/mnt/RAID/Media/xbmc/Thumbnails/</to>
</substitute>
<substitute>
<from>special://masterprofile/sources.xml</from>
<to>nfs://192.168.1.4/mnt/RAID/Media/xbmc/sources.xml</to>
</substitute>
<substitute>
<from>special://masterprofile/mediasources.xml</from>
<to>nfs://192.168.1.4/mnt/RAID/Media/xbmc/mediasources.xml</to>
</substitute>
</pathsubstitution>
</advancedsettings>
_______________________________________________________________________________

 

[SilverJS]

OK. Just looking at this at work, but I installed 5.1, and you've got 5.5. Does that make a big difference? I also haven't installed the client, maybe I should have.

How do I remove a package? If I want to remote 5.1 and install 5.5? Because for the rest, we're exactly the same - I don't even have to do path substitutions, my movies are already all on the NAS anyways.

 

[Joshua Parker Ruehlig]

I believe it's pkg_remove. I would go with 5.5, no reason either way other than it's newer =P

The path substitution isn't for the movies, its for the thumbs/fanart. My movie were added the standard way, by adding the network location.

client just allows me to edit mysql options from the jail, otherwise you gotta open up root logins remotely to mess with stuff. It's not needed though

 

[SilverJS]

OK. I'll try this tonight. For thumbnails/fanart, I have a symlink in every client PC's that links to a folder called Thumbnails that's already on my NAS. Everything is already set up, as I'm already running a MySQL XBMC setup, but the MySQL app is on one of my client PC's. I'm just trying to move that to the NAS.

Thanks for the info, I'll look at it later tonight.

 

[Joshua Parker Ruehlig]

Just some thoughts...
did you start the mysql-server service in your jail?
are you using your jail ip in your advanced settings file?
can you ping your jail ip from another computer in your network?

anyway, now that I got this working I got an itch to settup freenas as an isci server and boot my openelec(embeded xbmc) boxes over the network. I don't know why i do this to myself... lol

 

[SilverJS]

Yep to the first two, negative on the third - because I hadn't tried. =) Since I removed server-5.1 and installed 5.5, it gave me a weird error when trying to create the user (turns out it already existed), and when setting permissions too...anyhow, I'll start fresh by re-intalling the jail, and Transmission (and make sure everything still works!), and then try this again. I should have thought of the XBMC logs earlier, cuz now I'm really curious as to what was causing it to hang. What happens if I can't ping the jail IP from another computer?

P.S. - TOTALLY get you on the projects piling up....=))))

 

[SilverJS]

*NO* idea what I did different this time (other than install 5.5 instead of 5.1...?), but everything works like a charm now. Couldn't get the command to secure the installation to work, though.

 

[Joshua Parker Ruehlig]

well guess it doesn't matter. it's a mysql install only for xbmc, and running in a jail so it doesn't matter to bad if it gets compromised. glad you got it working, mines working great too. working on the PXE boot right now, pretty much wasted the whole day on it lol

 

[biffter]

Just wanted to say thanks to Joshua for the post above with the details to get this all setup. Been meaning to do it for ages, but just not got round to it, thought it was going to be a nightmare, with loads of problems, but took about 30 mins to get both my XBMC's synced to a MySQL database sitting on my freenass.

SilverJS I had a problem running the secure install command too, but when i logged out of the jail and back into it, it seemed to work the second time, so maybe give it a try and run it again now and see what happens :)