Adding MySQL Server in FreeNAS for XBMC/KODI

by mtecheasy on September 16, 2014

This is the setup I have used on FreeNAS 9.2.1.7 to setup a MySQL server to function for XBMC soon to be KODI.

Here we go!

Step 1. We want to create a Jail within the FreeNAS webGUI.

– Jails –> Add Jails

Freenas

The 3 highlighted areas are the only things that need to be addressed.
1. Naming the jail, in this case MySQL.
2. Make sure you pick standard from the drop down list.
3. Pick an IP address that will correspond to the service.

 

Step 2. Logging via SSH to FreeNAS, I currently use Putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html)

Here we are going to install mysql and nano (a text editor), and get the service started.

jls 

This will give you the ID of the newly created jail

jexec ID csh

Where ID is the number of your jail

pkg install mysql55-server
pkg install nano

Installing both MySQL and Nano

Now let’s enable MySQL in rc.conf

nano /etc/rc.conf
mysql_enable="YES"

Save and exit the file

service mysql-server start
mysql_secure_installation

This will start the service and secure the installation of mysql

Step 3. Creating a user with access for XBMC / KODI

mysql -u root -p
GRANT USAGE ON *.* TO 'xbmc'@'%' IDENTIFIED BY 'xbmc';
flush privileges;

Now here is the part where we give the user access to create and modify any database needed by XBMC / KODI

INSERT INTO `mysql`.`db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`) VALUES ('%', '%MyVideo%', 'xbmc', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

INSERT INTO `mysql`.`db` (`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`) VALUES ('%', '%MyMusic%', 'xbmc', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

flush privileges;

Step 4. Setting up XBMC / KODI to use the new database.
Simply open your advancedsetting.xml located in the userdata folder of xbmc / kodi

And enter the following:

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.200</host>

           <port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>

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

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

Just change the IP and save the changes, now reboot your xbmc / kodi device or restart the software.

Now you should be ready to start adding content with a centralized databse on FreeNAS using MySQL.

Cheers!

Leave a Reply