Featured Post

HOW TO: Install VPN server on Ubuntu when server is behind firewall

The whole point in this post is to run your own VPN service, and allow you to connect remote devices to your home network. To start off yo...

Feb 8, 2014

Installing a centralized MySQL DB on NAS4Free for XBMC library sharing

I have updated these guides for NAS4Free version 10.2.x, check them out here:
http://www.geekfreely.com/2015/12/howto-mysql-install-guide-for-nas4free.html
http://www.geekfreely.com/2015/12/howto-configuring-kodi-to-use-nas4free.html

So I have just set this up, and I have not tested it thoroughly yet, but I wanted to get it down on paper so I don't forget.  As with some of my other posts this is the merging of other peoples work and findings.

NOTE THIS IS FOR A FULL INSTALL OF NAS4FREE

For MySQL setup on NAS4Free I followed this guide:
https://sites.google.com/site/aganimkarmiel/home/free-time/freenas-extensions

And for XBMC setup I followed this guide:
http://wiki.xbmc.org/index.php?title=HOW-TO:Share_libraries_using_MySQL/Setting_up_XBMC

Here is all that information put into a single guide:

Installing and Setting Up MySQL

Create a directory to store MySQL data files, in my case I create a sub-directory in /mnt/data/db/mysql to store database files.

# mkdir -p /mnt/data/db/mysql

where /data - is Mount point name.  Provide full rights to this location:

# chmod 777 /mnt/data/db/mysql

By default the MySQL server on NAS4Free stores database files in /var/db/mysql which does not exist at this point, so let's make a symbolic link in that directory:

# ln -s /mnt/data/db/mysql /var/db/mysql

Create the group and user 'mysql' using the NAS4Free WebGUI, it is important because NAS4Free will forget about the users created on command line after reboot.

Group
Name: mysql
ID: 88

User
Name: mysql
Fullname: mysql
UserID: 88
Primary Group: mysql
NO PASSWORD

Now install MySQL 5.5:

# pkg_add -r mysql55-server
# rehash

Execute the following command to create default databases and tables, and upgrade:

# cd /usr/local/bin
# mysql_install_db
# mysql_upgrade

Give all permissions to the user and group mysql for /mnt/data/db

# chown -R mysql:mysql /mnt/data/db/mysql/

Execute 

# mysqld_safe &
Now, this created  .err and .pid into /mnt/data/db/mysql folder.  Check errors written into .err

Add mysql_enable=”Yes” into /etc/rc.conf.  GUI is preferable to make this change (select System | Advanced | rc.conf), but the following command works just as well:

# echo 'mysql_enable="YES"' >> /etc/rc.conf

I found that after a NAS4Free upgrade the command line option no longer existed.  I recommend doing it via GUI.

Reboot your box and type the following command to make sure MySQL is running:

# top

If it is not running, type the following command to start MySQL:

# /usr/local/etc/rc.d/mysql-server start

Secure MySQL using the following wizard:

# mysql_secure_installation

Log into MySQL:

# mysql -u root -p

You are now in mysql administration, conduct the following commands in MySQL to allow XBMC access.

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

Configuring XBMC to use NAS4Free MySQL DB

Export your music and your video directories, you can to this as separate or single file, I tried to do separate which is the suggested, but I have no idea where the files where put - so I just started afresh.  If you want to try and retain your current "master" DBs, follow the steps here:


Otherwise you can just continue on with this guide.

Create (or add to, if you already have one) an advancedsettings.xml file in your XBMC userdata folder.
Using nano copy and paste the following information into the advancedsettings.xml file, if there is already content, merge the sections appropriately:

advancedsettings.xml configuration found in the following location:
http://wiki.xbmc.org/index.php?title=HOW-TO:Share_libraries_using_MySQL/Setting_up_XBMC

and should look like this:
<advancedsettings>
    <videodatabase>
        <type>mysql</type>
        <host>***.***.***.***</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
    </videodatabase>

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

     <videolibrary>
          <importwatchedstate>true</importwatchedstate>
     </videolibrary>
</advancedsettings>

Replace the two instances of ***.***.***.*** with local network IP address of your NAS4Free server. It is recommended not to use its NetBIOS name, as not all devices may be able to resolve them.

Save the file as advancedsettings.xml.  Copy this advancedsettings.xml file you just created to the userdata folder of every XBMC install you want to sync with.

If you exported your existing DBs, go ahead and import them.  If you created a fresh DB, scan all your content again, make sure to use the NAS4Free server IP rather than NetBIOS for your shares, as mentioned - not all devices may resolve the NetBIOS.

One of the first changes you’ll want to make, on your XBMC systems, is to alter what XBMC does in response to you pressing play on a file. By default XBMC simply plays the file from the beginning. Since we now have an XBMC system that remembers our place across multiple machines, we want XBMC to prompt us.

Navigate to Settings | File Lists and set the Default select action to Choose. We want XBMC to ask us what to do when we’re opening a file instead of automatically playing it from the start.

As a final note, specifically with Openelec and Raspberry Pi, although Openelec suggest this for WiFi boxes, set Wait for network before starting XBMC under Openelec addon settings, network.
That is it...you should be good to go.  Let me know how it goes for you, or if you have any issues.

Updates from lindsay added: http://forums.nas4free.org/viewtopic.php?f=71&t=6026&p=33121#p33119

Additional updates from lindsay:

Optimizing the DB using a cron, I used the following command to test before making the cron:
# mysqlcheck -os -u xbmc -pxbmc --all-databases