Using MariaDB with xbvr

Introduction

xbvr uses SQLiteDB by default for easy installation. As more and more scenes are scraped and added to xbvr, the performance of SQLite may not be acceptable. MariaDB is supported as a high performance alternative and is strongly recommended for larger xbvr collections.

Installing MariaDB for xbvr on Synology

1. Install Synology MariaDB 10 package from Synology Package Manager, setting the MariaDB root password when prompted
2. Open MariaDB in Synology Package Manager and allow TCP access - if you want to connect to the DB remotely, or are hosting xbvr on a different device
3. ssh to Synology and edit the MariaDB config file at /var/packages/MariaDB10/etc/my.cnf

[mysqld]
max_connections=400
innodb_buffer_pool_size=1024M
tmp_table_size=64M
max_heap_table_size=64M
query_cache_size=64M
innodb_strict_mode=OFF
sql_mode = "NO_ENGINE_SUBSTITUTION"

Installing MariaDB in a container

1. MariaDB Container Documentation

Basic Docker Compose with MariaDB

Alternatively, below is a simple docker-compose.yml example to get XBVR up and running with MariaDB. Note that you will want to modify the my.cnf as described above.

services:
  mariadb:
    image: mariadb
    environment:
      - MARIADB_RANDOM_ROOT_PASSWORD=1
      - MARIADB_USER=xbvr
      - MARIADB_PASSWORD=MyPassword123
      - MARIADB_DATABASE=xbvr
    volumes:
      - ./mariadb:/var/lib/mysql
    command: --sql_mode="NO_ENGINE_SUBSTITUTION"
    restart: unless-stopped
  xbvr:
    image: ghcr.io/xbapps/xbvr
    environment:
      - DATABASE_URL=mysql://xbvr:MyPassword123@mariadb:3306/xbvr?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=True&loc=Local
    ports:
      - 9999:9999
    volumes:
      - ./videos:/videos/
      - ./config:/root/.config/
    restart: unless-stopped

Migrating existing data

1. Export all data using xbvr's built-in export function (Data import/export)
2. Once you have xbvr running with MariaDB, import all data. This may take many hours, depending on the number of scraped scenes.

Connecting xbvr to MariaDB

1. (from ssh) Log into MariaDB using the root password set earlier mysql -u root -p
2. Use the following SQL commands to create the xbvr database, create an xbvr user, and allow the xbvr user full access from any IP. Note the password should be changed!

CREATE DATABASE xbvr CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'xbvr'@'%' IDENTIFIED BY 'MyPass.456';
GRANT ALL ON xbvr.* to 'xbvr'@'%' IDENTIFIED BY 'MyPass.456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT User, Host FROM mysql.user;

3. Start xbvr with MariaDB connection string defined, changing the password and IP as needed If using Docker commandline, add this snippet -e DATABASE_URL='mysql://xbvr:[email protected]:3306/xbvr?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=True&loc=Local' \

For GUI management, add DATABASE_URL as an environment variable in the xbvr container, with the string above as the value.

Managing MariaDB

Monitoring the MariaDB logs

Synology example, uses Synology hostname for log file name: tail -F /volume1/@appdata/MariaDB10/mariadb10/{hostname}.log

Backing up MariaDB

Backup Method 1

mysqldump -u root -p xbvr > xbvr.sql

Restore Method 1

mysql -u root -p
CREATE DATABASE xbvr CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'xbvr'@'%' IDENTIFIED BY 'MyPassword123';
GRANT ALL ON xbvr.* to 'xbvr'@'%' IDENTIFIED BY 'MyPassword123' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT User, Host FROM mysql.user;
mysql -u xbvr -p xbvr < xbvr.sql

Backup Method 2

To fully backup a MariaDB database, you need both the database files and transaction logs. Paths below are for a Synology Package based MariaDB installation. Assumes /volume1/.backup exists.

cp -r /volume1/@appdata/MariaDB10/mariadb10/xbvr /volume1/.backup
cp /volume1/@appdata/MariaDB10/mariadb10/xbvr/../ibdata1 /volume1/.backup/xbvr/
cp /volume1/@appdata/MariaDB10/mariadb10/xbvr/../ib_logfile0 /volume1/.backup/xbvr/
cp /volume1/@appdata/MariaDB10/mariadb10/xbvr/../ib_logfile1 /volume1/.backup/xbvr/

Restore Method 2

Copy the files back to the MariaDB location, overwriting the existing files.

Checking peak connections

The configuration above uses 400 connections, which should be more than enough for most xbvr use cases. A very large number of custom scrapers may require more. Using MariaDB on low memory devices may need to adjust the number of connections down to save memory. To see the peak number of connections, connect to the database and use the command: SHOW STATUS LIKE '%connect%';

Changing xbvr DB user password

If you want to change the xbvr user's password, connect to the database and use the commands:

SET PASSWORD FOR 'xbvr'@'%' = PASSWORD('MyPass.456');
FLUSH PRIVILEGES;