On to next challenge : MySQL and XBMC! =)

[bcrowder]

This works, Wonderfully.

Thanks,
Bill

 

[bcrowder]

Ok, anyone wanting to set up Freenas as an XBMC server. This works absolutely perfectly using Joshua Parker Ruehlig's post, here is the changes I have made. Every computer in the house runs XBMC identically this way with all media and most of the settings stored on the Nas.

After setting up the jail pbi correctly, and there is complete instructions including videos to do this, follow the instructions below. If any one see's something wrong with these pleas YELL. I left the 'mysql_secure_installation' line, as Joshua suggests, but I don't see that the matters for a home setup. If you do run it, you need to reboot Freenas and go back into the jail using 'jexec 1 csh' to get it to work. The '1' is the ID to the jail if you have more then one run jls.

If anyone has suggestions to make this work better, yell out!!! Really happy with how it works now!
__________________________________________________ _____________________________
#in freenas shell
jexec 1 csh

#in jail
pkg_add -r mysql55-server
pkg_add -r mysql55-client
mysql_secure_installation # ----<not necessary, but not a bad idea.

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;

__________________________________________________ _____________________________

This is the advancedsettings.xml file that is stored in %appdata%\Roaming\XBMC\userdata\ on Windows 7. You will have to create this file, copy and paste, then change the IP address's. 192.168.1.203 is my Jail IP and is only used in the first 2 setting, 192.168.1.201 is my Freenas IP, change them to your settings. You will also need to point the directory structure '/Media/xmbc/*' to a valid share/directory on Freenas system.

________________________________________________________________________________

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.203</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>192.168.1.203</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>

<pathsubstitution>
<substitute>
<from>special://masterprofile/Thumbnails/</from>
<to>smb://192.168.1.201/Media/xmbc/Thumbnails/</to>
</substitute>

<substitute>
<from>special://masterprofile/playlists/</from>
<to>smb://192.168.1.201/Media/xmbc/Settings/playlists/</to>
</substitute>

<substitute>
<from>special://masterprofile/addon_data/</from>
<to>smb://192.168.1.201/Media/xmbc/Settings/addon_data/</to>
</substitute>

<substitute>
<from>special://masterprofile/sources.xml</from>
<to>smb://192.168.1.201/Media/xmbc/sources.xml</to>
</substitute>

<substitute>
<from>special://masterprofile/mediasources.xml</from>
<to>smb://192.168.1.201/Media/xmbc/Settings/mediasources.xml</to>
</substitute>

<substitute>
<from>special://masterprofile/RssFeeds.xml</from>
<to>smb://192.168.1.201/Media/xmbc/Settings/RssFeeds.xml</to>
</substitute>

<substitute>
<from>special://masterprofile/favourites.xml</from>
<to>smb://192.168.1.201/Media/xmbc/Settings/favourites.xml</to>
</substitute>

<substitute>
<from>special://masterprofile/profiles.xml</from>
<to>smb://192.168.1.201/Media/xmbc/Settings/profiles.xml</to>
</substitute>
</pathsubstitution>
</advancedsettings>

____________________________________________________________________________________________

Thanks,
Bill

 

[Joshua Parker Ruehlig]

are your xbmc boxes actually syncing addons? that would be nice to have a consistent experience, like similar youtube searches across all boxes.

 

[bcrowder]

I believe they are, 'addon' caches and settings are stored in '/Media/xmbc/Settings/addon_data/'. Everything seems to be synced up and the addons seem to play nice this way. Youtube and Vevo seem to be the same across all the boxes, and are actively using the "substituted" directory. The local 'addon' directories are no longer updating. Really happy with this setup.

Thanks,
Bill

 

[FreeNASUser5129]

Hey Guys,

Thanks for the tips on how to get MySQL installed into the FreeNAS jail.

I'm admittedly not very knowledgable about FreeBSD, but when I follow the instructions given, I get an error when I attempt to run MySQL_Secure_Installation:

It pops down into the installation menu and asks me to Current Root Password (I've installed MySQL before, so I know this is normal). When I hit Enter (since it's a brand new install), it gives me:

Error 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Has anyone seen this before? Googling produces lots of results, but I'm still not certain how to resolve.

Thanks!

 

[Joshua Parker Ruehlig]

did you check if mysql was running in your jail. If it can't connect then you mysql is either not started, refusing your connection (doubtful using a unix socket), or listening on tcp socket instead.

Code:

service mysql-server status #check if mysql is running
service mysql-service start #start it if it is not

 

[FreeNASUser5129]

Ah, thanks for pointing me in the right direction Joshua.

FYI for anyone who is struggling, here are the exact steps that worked for me:

#(Log into the jail)
jexec 1 csh

#(install MySQL)
pkg_add -r mysql55-server
pkg_add -r mysql55-client
echo 'mysql_enable="YES"'>>/etc/rc.conf
service mysql-server start
#(at this point I had to exit out of the jail/cmd prompt and re-enter before I was able to run the MySQL setup)
mysql_secure_installation
#(run through the install)

Everything now appears to be up and running. Thanks all!

 

[Rks]

Need help to retrace

I had a working sql installation and I was experimenting to get a secure installation. so i did a service mysql stop, and attempted the mysql_secure_installation. I did not let me get through with a enter at the password prompt. Now I have a peculiar problem. pkg_add says older files are present. pkg_delete cannot find the older files and doing a
$make install in the /etc/ports/databases/mysql-server is giving errors. I am still trying. Is there an easy way out to start all over again.

 

[Joshua Parker Ruehlig]

Well if your jail has nothing else you care about it just delete it and readd a new jail. Also you could revert to a snapshot of your jail if you took them.

 

[Kendog]

This is really, really helpful and cool. I used the advancedsettings.xml file from bcrowder which I liked because it centralized just about all of the media metadata onto the SMB share, but had an issue I just figured out - the mysql database is setup and working perfectly, but the artwork isn't showing up. I checked my smb paths and found the difference (just a typo).

If you use the file from bcrowder as a template, be sure to change the paths from:

smb://server.IP/Media/xmbc/Thumbnails/

to:

smb://server.IP/Media/xbmc/Thumbnails/

if you want to fix the typo. Otherwise you can just create the 'xmbc' folder and go from there.

Thanks again for all the help!

 

[bmcclure937]

Will there be a PBI setup for this in the future? Should I wait for that or just give this a whirl?

Also, it may be useful to update the OP with the most current preferred configuration method. This is helpful for those that stumble upon these threads. Luckily this thread is a short one (only 3 pages right now)... so it is easy to catch up! ;)

 

[Joshua Parker Ruehlig]

Will there be a PBI setup for this in the future? Should I wait for that or just give this a whirl?

Also, it may be useful to update the OP with the most current preferred configuration method. This is helpful for those that stumble upon these threads. Luckily this thread is a short one (only 3 pages right now)... so it is easy to catch up! ;)

I don't plan on it. Someone else might but I'd just give it a whirl

 

[nogi]

When I ran mySQL on my old QNAP, I also had schduled daily backups of the database. How are you guys backing up the database?

 

[Joshua Parker Ruehlig]

I'm currently not (my xbmc info isn't too important to lose) but when backuping up my important mysql databases I run mysql as a slave on a less busy server, stop the slave thread when taking a mysqldump (to produce point in time backups), then dump the slaves databases to a file and compress it. I script this all up and make it a cronjob.

Only crappy part is you need to expose your mysql password in the script you setup for everything to work without user interaction.

 

[metroplex009]

I am having some issues. For some reason my jail won't allow access to port 3306. I can't connect to mysql instance other than localhost within the jail. Any suggestions?

 

[bcrowder]

Anyone have any idea why after upgrading to 2.0 p1 I'm getting the following error from pkg_add? I deleted the jail and started over.

Thanks,
Bill

>>> USER anonymous
<<< 331 Guest login ok, send your email address as password.
>>> PASS root@software
<<< 230 Guest login ok, access restrictions apply.
>>> PWD
<<< 257 "/" is current directory.
>>> CWD pub/FreeBSD/ports/amd64/packages-8.2-release/Latest
<<< 250 CWD command successful.
>>> MODE S
<<< 200 MODE S accepted.
>>> TYPE I
<<< 200 Type set to I.
binding data socket
>>> PORT 192,168,1,203,220,136
<<< 200 PORT command successful.
initiating transfer
>>> RETR mysql55-client.tbz
<<< 425 Can't build data connection: Operation timed out.
Error: Unable to get ftp://ftp.freebsd.org/pub/FreeBSD/ports/amd64/packages-8.2-
release/Latest/mysql55-client.tbz: Can't open data connection
pkg_add: unable to fetch 'ftp://ftp.freebsd.org/pub/FreeBSD/ports/amd64/packages
-8.2-release/Latest/mysql55-client.tbz' by URL
pkg_add: 1 package addition(s) failed
software#

 

[Joshua Parker Ruehlig]

metroplex
might be permissions, maybe your config is allowing only localhost. but weird I got it allowing everyone with just the few commands listed

bcrowder
can you ping out? maybe try ping google.com and see if your jail can resolve hostnames

 

[bcrowder]

Yes, it logs into the FTP server you can see that with pkg_add -v -r mysql55-server command in the screen shot I posted, just cant open a data connection. I down loaded the files manually and installed them. Everything's back up and working now. Just curious why "-r" remote won't work now...

Thanks,
Bill

 

[Joshua Parker Ruehlig]

Yes, it logs into the FTP server you can see that with pkg_add -v -r mysql55-server command in the screen shot I posted, just cant open a data connection. I down loaded the files manually and installed them. Everything's back up and working now. Just curious why "-r" remote won't work now...

Thanks,
Bill

Hmm, not sure. Weird

 

[bcrowder]

Hmm, not sure. Weird

Don't know, just deleted the jail again to see if that would fix the problem. It didn't, installed mysql manually and rebuilt the xmbc data bases again. Xbmc Frodo x 4 clients.

Thanks,
Bill