Hackviking He killed Chuck Norris, he ruled dancing so he took up a new hobby…

20Mar/170

Kodi central db backup

Using a central database for all your Kodi media players is convenient. Only one of them need to scan for new content or you can even update the database straight away. It holds state across all the devices like paused movies, watched episodes etc. If you have a large library it takes time to scan it all again so you should keep it backed up. I didn't but now I do!

Continue reading...

16Feb/160

Raspberry PI: Central kodi database

When you run more then one Kodi device with a shared media repository you should also run a shared library. With a shared library you only need to update the library on one of the Kodi devices when new media is added. There are a few other great benefits of running a shared library like the ability to stop media on one device and continue watching on another. It will also show what has been watched regardless of which device you actually watched it on. I run OpenELEC on Raspberry Pi's for all my media stations so this guide is focused on a Raspberry Pi implementation but the principal is the same for all devices capable of running Kodi and MySQL.

Preparations

Download the latest raspian image from https://www.raspberrypi.org/downloads/raspbian/ and put it on an sd-card with Win32DiskImager. Then put the sd-card in the Raspberry Pi and power it on. You can connect a monitor, mouse and keyboard for the initial setup. I usually just wait for it to boot up and check my routers DHCP list for it to show up and connect to it over SSH. This article is based on SSH terminal access but you can do the same on the Pi with a connected monitor.

Basic setup of the Raspberry Pi

First we need to configure the Raspberry Pi. Connect over SSH and login as pi with password raspberry. We want to:

sudo raspi-config
  • Expand Filesystem - Expand the pi file system to use the entire SD-card.
  • Change User Password - Change the default raspberry password for the pi user.
  • Boot Options - Select B1 Console - Text console, requiring user to login
  • Advanced - Some additional settings
    • Hostname - Select a hostname for your pi. I user PISRV
    • Memory Split - Set to 16 since we will not use a monitor or run any other graphics on this machine.
  • Finish - Exit the raspi-config and reboot.

When the pi has booted up again we can login with our new password we configured. When in the console run:

sudo apt-get update

Update the package lists from all the repositories.

sudo apt-get dist-upgrade

Upgrade all installed packages.

Install MySQL

Install MySQL server. It will ask you to select a root password.

sudo apt-get install mysql-server

We need to be able to access the MySQL server from other locations then localhost. Open up the MySQL config file.

sudo nano /etc/mysql/my.cnf

And change:

bind-address = 127.0.0.1

To:

bind-address = 0.0.0.0

Then restart the MySQL server.

sudo /etc/init.d/mysql restart

Setup the databases

Now we need to setup the databases for our video and music library. Login to MySQL, you will be prompted for your password.

mysql -uroot -p

What ever you do don't create any databases. Kodi will use the name that we supply later but just as a prefix, it will add an additional identifier. We need to create a user for the kodi machines. You can use whatever username and password you like but once again make a note of it.

 CREATE USER 'kodi' IDENTIFIED BY 'password';

The first time Kodi connects it needs to be able to create it's databases, we need to grant the account full access. We will lock that down later to secure our MySQL if we want to use it for other things then Kodi as well.

GRANT ALL ON *.* TO 'kodi';

Test to connect to the database from another machine. Either via MySQL command line tool, the same we used on the Pi, or download MySQL Workbench and test the connection. If all is working just type quit and press enter to return to the main shell.

Setup kodi

Then we need to make a backup of our current media library. I'm using OpenELEC but most Kodi versions should be the same. In the Kodi UI goto System/Settings -> Video -> Library and select Export library. If you don't see the last options make sure that Settings Oprions is set to Advanced in the lower left corner of the screen. Select Multiple files, this will create .nfo files along side all the media files. I already use this setup since I scan all my libraries With local information only. This is by far the safest way to migrate your library otherwise you have to scrape all the media again. So if you use local information only on your scrapes your good to go. Otherwise do the export!

Now we need to setup Kodi to use the MySQL server. Connect to the Kodi with SSH, username and password depends on the distribution you used to install your Kodi. The location of the userdata folder also varies from different distributions. I have one XBMC installed on a Raspberry Pi where the path is /home/pi/.kodi/userdata. On the OpenELEC installs I'm doing this for the userdata folder is located in /storage/.kodi/userdata/.

When you have found the folder you need to create a file named advancedsettings.xml. You can also do this via smb share if that is enabled on your Kodi machine. I prefer to do it over SSH to prevent any encoding issues. If you want to you can try it out by going \\{ip of kodi}\userdata.

sudo advancedsettings.xml

In this file we will put the configuration for accessing our MySQL server.

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>{IP address or FQDN of your MySQL server}</host>
    <port>3306</port>
    <name>{prefix of your db name, I used kodi_video}</name>
    <user>kodi</user>
    <pass>{password you selected}</pass>
  </videodatabase> 
  <musicdatabase>
    <type>mysql</type>
    <host>{IP address or FQDN of your MySQL server}</host>
    <port>3306</port>
    <name>{prefix of your db name, I used kodi_music}</name>
    <user>kodi>/user>
    <pass>{password you selected}</pass>
  </musicdatabase>
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>

Save the file and reboot your Kodi machine. Depending on distribution you may need to do sudo reboot. Once it recycles you can scan your locations again, it will use the local .nfo files you already had or the once created during your export.

Securing MySQL again

We don't want the kodi MySQL user to have full access going forward. If your not using your MySQL for anything else then you can leave it be, but I want to secure mine. So back to the SSH console on the MySQL server.

mysql -uroot -p

You will once again be prompted for your MySQL root password and then dropped into the MySQL console. So now we check the name of the databases.

mysql&amp;amp;amp;gt; show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kodi_music52 |
| kodi_video93 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

So we have the kodi_ databases named after the <name> parameter in our advancedsettings.xml. Now we revoke all the access we gave the kodi account.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'kodi';

And then only grant it full access to the two kodi_ databases.

GRANT ALL ON `kodi%`.* TO 'kodi';
flush privileges;

Setting up the other Kodi

Once all this is up and running setting up the second one is really easy. If you enabled SMB shares on OpenELEC like I did you just connect to the first kodi like \\kodi\UserData copy the advancedsettings.xml and sources.xml to the other kodies UserData share. If you don't have SMB enabled you have to edit the files via SSH.

 

4Feb/150

Move MySQL database storage location

It's always a good idea to keep storage away from the boot device. If you run out of space on the boot device the system will halt. If you make a new install it's easy enough to move your storage and you can do it from a cloud-init script like this:

- mkdir /var/db
- chown -R mysql:mysql /var/db
- sed -i 's:datadir=/var/lib/mysql:datadir=/var/db:g' /etc/my.cnf
- service mysqld start

If the installation is all ready up and running you have to add steps for stopping the MySQL server and copy the database files:

mkdir /var/www/db
service mysqld stop
mv /var/lib/mysql/* /var/db
chown -R mysql:mysql /var/db
sed -i 's:datadir=/var/lib/mysql:datadir=/var/db:g' /etc/my.cnf
service mysqld start

In these examples I have user /var/db where I mounted the second storage device. You can however use any location you see fit. Points of interest in the command sequence.

chown -R mysql:mysql /var/db

Make sure that the mysql deamon have access to the storage location.

sed -i 's:datadir=/var/lib/mysql:datadir=/var/db:g' /etc/my.cnf

sed is a simple tool for search and replace inside text/config files directly from the command line. Here it searches for the line specifying the MySQL datadir location and replaces it with the new value.

3Feb/150

Unattended use of mysql_secure_installation

After installing MySQL on any Linux distribution you run the mysql_secure_installation script, or at least you should! It will prompt you to set a new root password, remove anon access and a few other things. But if you want this configuration to be done in a deployment or cloud-init script? The mysql_secure_installation command/script doesn't accept any parameters, so it can't be used for unattended install. How ever you can execute the same commands via the mysql command line tool as long as the service is started.

mysql -e "UPDATE mysql.user SET Password=PASSWORD('{input_password_here}') WHERE User='root';"
mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
mysql -e "DELETE FROM mysql.user WHERE User='';"
mysql -e "DROP DATABASE test;"
mysql -e "FLUSH PRIVILEGES;"

I use this to provision new MySQL servers in the Amazon EC2 environment and it works like a charm. If this is used in a cloud-init script make sure to execute the sudo service mysqld start first!