Kodi Shared MySQL / MariaDB Database

Kodi MySQL Database on Arch Linux

A centralized MySQL or MariaDB database allows any number of Kodi clients to share the same media library and to be constantly in sync with each other. This also has potential to greatly improve scraping performance since only a single database needs to be updated. Most useful feature is probably the synced watch and resume counter which allows resuming of paused media from any client.

 

Prior to setting up a shared database make sure you have prerequisite applications installed on yout chosen distribution.

 

Arch Linux

Install Kodi on Arch Linux

Install MySQL / MariaDB on Arch Linux

 

Fedora

Install Kodi on Fedora Linux

Install MariaDB / MySQL on Fedora Linux

 

Configure MySQL Database

Open the settings file of the standalone installation.

sudo nano /var/lib/kodi/.kodi/userdata/advancedsettings.xml

Standard Desktop instalation settings file is located in ~/.kodi/userdata/advancedsettings.xml.

 

Copy the sample settings.

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>192.168.0.100</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>*****</pass>
  </videodatabase> 
  <musicdatabase>
    <type>mysql</type>
    <host>192.168.0.100</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>*****</pass>
  </musicdatabase>
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>

Video and music metadata is stored separate databases which are defined within <videodatabase> and <musicdatabase> tags. Default <type> tag value is sqlite and the only valid alternative is mysql which is the correct option for both MySQL and MariaDB. The IP address and port of the database host is held within <host> and <port> tags respectively. Similarly the database credentials are stored in <user> and <pass> tags. Optionally the <name> tag could be added to explicitly define the database name otherwise the default MyVideos and MyMusic names followed by the database number will be used.

Video library specific options are defined within <videolibrary> tag.  Tags <importwatchedstate> and <importresumepoint> are used to import previous resume points, play dates and play counts from the .nfo files.

 

Setup Kodi MySQL Database

Connect to the database.

mysql -u root -p

Specify MySQL user with -u option. If the user has a password add the -p option. Enter the password used during MySQL configuration when prompted.

 

Create a new database user.

CREATE USER 'kodi' IDENTIFIED BY '*****';

Username is defined with CREATE USER statement while the password is specified with IDENTIFIED BY statement.

 

Grant permissions on video and music databases to the new user.

GRANT ALL ON `MyVideos%`.* TO 'kodi';
GRANT ALL ON `MyMusic%`.* TO 'kodi';

All possible permissions are granted with GRANT ALL ON statement to both Kodi video and music databases. User that is being granted permissions is specified with the TO statement.

 

Exit the database command line tool.

quit

 

Backup Kodi SQL Databases

Connect to the database.

mysql -u root -p

 

List all databases.

SHOW DATABASES;

This is only needed to identify database names.

 

Exit the database command line tool.

quit

 

Export video and music databases to a backup directory.

sudo mysqldump --user="root" --password="***********" MyVideos99 > /mnt/backup/MyVideos99.sql
sudo mysqldump --user="root" --password="***********" MyMusic56 > /mnt/backup/MyMusic56.sql

 

Restore Kodi SQL Databases

First connect to and create the database user and grant permissions as outlined in setup Kodi MySQL Database section.

 

Create new empty videos database.

CREATE DATABASE MyVideos99;

 

Create new empty music database.

CREATE DATABASE MyMusic56;

 

Exit the database command line tool.

quit

 

Import video and music databases from the backup directory.

mysql --user="root" --password="***********" MyVideos99 < /mnt/backup/MyVideos99.sql
mysql --user="root" --password="***********" MyMusic56 < /mnt/backup/MyMusic56.sql

 

Alternatively MySQL / MariaDB databases can be exported and imported with the help of GUI tools. Install phpMyAdmin on Arch Linux or install phpMyAdmin on Fedora Linux.

  • daxxi13

    aren’t only for nfo files?

    • You are right, both importwatchedstate and importresumepoint are only needed when importing previously stored values in .nfo files. I edited the post to reflect this, thanks!