[HOW TO] Setup NAS4Free with MySQL for centralized XBMC DB

[iainmacleod]

I am not sure if this is the right section to put this, if not feel free to indicate where it should be or request a moderator to move it.

I decided to centralize my XBMC DB so that I can manage a single library and stop and start content in several rooms. Here is the guide I put together based on several pieces of information I found.

http://geekfreely.blogspot.com/2014/02/ ... db-on.html

Comment on the blog, or here if you wish any assistance or would like to give feedback. Blog might be better, as it is linked from XBMC forums too.

[lindsay]

Change this

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

# mysql_install_db
# mysql_upgrade

To

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

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

[iainmacleod]

Ok, thank you very much for your input. I take it you had success?

[lindsay]

Yes without cd directory it do not find the mysql_install and upgrade

[iainmacleod]

Great, thanks again!

[lindsay]

One thing you can add with cron is mysqlcheck -Aos -u xbmc -pxbmc

Then the tables will be optimized as it will be fragmented, do not pay attention about the error Table does not support optimize, doing recreate + analyze instead because of innoDB not MyISAM. They will be optimized but the errors still shows up when you paste this in putty

[iainmacleod]

I understand:

mysqlcheck -A -u xbmc -p xbmc

but what is the os?

I see, optimize and silent.

I tried your command and got the following error:

You should give only options, no arguments at all, with option --all-databases. Please see mysqlcheck --help for more information.

It was a syntax thing, I got the following to work:
mysqlcheck -os -u xbmc -pxbmc --all-databases

[lindsay]

The A is all databases
o is optimize
the s is silent

[lindsay]

I was finding my script to optimize tables

Code: Select all

#!/bin/bash
 
MYSQL_LOGIN='-u root --password=your password'
 
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN  >/dev/null
                echo "done."
        done
done

[compi]

my Setup 9.2.0.1 - Shigawire (Revision 943)


If I follow the instructions.
I get this error message.

Code: Select all

nas: bin # mysql_install_db

FATAL ERROR: Could not find ./bin/my_print_defaults

If you compiled from source, you need to run 'make install' to
copy the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.

Until then there is no problem
one can help me?

compi

[lindsay]

It depends where you type the command in the folder structure

did you do

cd /usr/local/bin
mysql_install_db

[compi]

Yes i did

[compi]

make it work

Code: Select all

# /usr/local/bin/mysql_install_db --basedir=/usr/local 

[laster13]

hi

excuse my english, i french

thanks for you tuto

i tried to install mysql but i have thes error, i create group and user... the system is install on hdd

nas4free: ~ # mkdir -p /mnt/disque1/db/mysql
nas4free: ~ # chmod 777 /mnt/disque1/db/mysql
nas4free: ~ # ln -s /mnt/disque1/db/mysql /var/db/mysql
nas4free: ~ # pkg_add -r mysql55-server
Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports ... server.tbz...
/var: write failed, filesystem is full
libexec/mysqld: Write failed

thx

[dreamcat4]

nas4free: ~ # pkg_add -r mysql55-server
Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports ... server.tbz...
/var: write failed, filesystem is full
libexec/mysqld: Write failed

That is old ways. Do not use 'pkg_add' anymore. The version you will get of the software will be old / out-of-date.

First you need to be on NAS4Free 9.2. Like from here:

http://www.zoonsweb.nl/Latest_NAS4Free_Builds/

Then (on Nas4Free 9.2), you can install "Finch" 3rd party tool. Look in the 'jails' forum.

After Finch is installed, then you can type: "pkg install mysql-server".

[lindsay]

Have not tested it but check out http://howto.biapy.com/en/debian-gnu-li ... sql-tables
This is nice because of innodb do not work with optimize

But you could easy download the databases and change innoDB to MyISAM

[birthman]

Hello to all.

I've updated my Nas4Free to 11.2.0.4 - Omnius (revision 6177). Trying to configure the mysql in order to share the Kodi database with some Kodi clients I've followed this post that worked for me in my older version of Nas4Free.

First surprise is that when I tried these command in order to create default databases and tables

make it work

Code: Select all

# /usr/local/bin/mysql_install_db --basedir=/usr/local 

I've obtained this error message:

/usr/local/bin/mysql_install_db --basedir=/usr/local
Installing MariaDB/MySQL system tables in './data' ...

/: write failed, filesystem is full
2018-11-13 1:37:49 34424840192 [ERROR] InnoDB: preallocating 50331648 bytes for file ./ib_logfile101 failed with error 28
2018-11-13 1:37:49 34424840192 [ERROR] InnoDB: Cannot set log file ./ib_logfile101 size to 50331648 bytes
2018-11-13 1:37:49 34424840192 [ERROR] InnoDB: Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.
2018-11-13 1:37:49 34424840192 [ERROR] Plugin 'InnoDB' init function returned error.
2018-11-13 1:37:49 34424840192 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-11-13 1:37:49 34424840192 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-11-13 1:37:49 34424840192 [ERROR] Aborting

/: write failed, filesystem is full
2018-11-13 1:37:49 34436901888 [Warning] mysqld: Disk is full writing '/root/data/aria_log.00000001' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
2018-11-13 1:37:49 34436901888 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs

Some insights?

Thanks in advance

[ms49434]

Many steps of the above instructions (from 2014) are no longer required because MariaDB is part of XigmaNAS.
You are also trying to install mysql in an embedded environment which is not a good idea. The result is obvious.

[birthman]

Thanks ms49434 for your quick answer.

Then, it's not possible to setup a embedded environment of Nas4free with mysql for centralized Kodi database? I need the full version of Nas4free?

Thanks again

[ms49434]

Thanks ms49434 for your quick answer.

Then, it's not possible to setup a embedded environment of Nas4free with mysql for centralized Kodi database? I need the full version of Nas4free?

Thanks again

Use MariaDB instead, it is part of XigmaNAS (Services -> MariaDB).
- Configure MariaDB (In order to do that you should create a dataset or a folder for the home directory / database first).
- Run mysql_secure_installation from the console.
- Follow the instruction on https://kodi.wiki/view/MySQL/Setting_up_MySQL.

[birthman]

Thanks again for your answers ms49434
I've followed your instructions.

Use MariaDB instead, it is part of XigmaNAS (Services -> MariaDB).
- Configure MariaDB (In order to do that you should create a dataset or a folder for the home directory / database first).

I've created a dataset for the home directory (/mnt/Pool_1/Mariadb_data/)

- Run mysql_secure_installation from the console.

I've run 'mysql_secure_installation' from the console (added password to the mysql root user, ...)

- Follow the instruction on https://kodi.wiki/view/MySQL/Setting_up_MySQL.

I've followed these instructions:

1) typed in the console

>> mysql -u root -p #Using the password setted in 'mysql_secure_installation'

2) In Mariadb administration

>> CREATE USER 'kodi' IDENTIFIED BY 'kodi';
>> GRANT ALL ON *.* TO 'kodi';
>> flush privileges;
>> quit;

3) Created the avancesettings.xml and copied to the 'userdata' folder of all my Kodi clients

4) Imported my library in one of my clients

The library has been shared and all worked fine initially, but when I restarted XigmaNas the library is gone in Kodi. It's like all the stuff (all instructions I've followed) is gone. When I've typed again in the console ' mysql -u root -p ' the root password setted in the previous 'mysql_secure_installation' has not worked. It's like the settings done in 'mysql_secure_installation' are not permanent. Why?

I've read in https://kodi.wiki/view/MySQL/Setting_up_MySQL:

Also: Some versions of MySQL set their passwords to expire by default, even the root password! If the password expires, Kodi cannot access the database and the TV Series and Movies libraries will disappear.
To avoid this and/or fix it after it happens, make sure the account's password does not expire. (See: https://dev.mysql.com/doc/refman/5.7/en ... olicy.html )

Following the instructions provided by the link, I've added

[mysqld]
default_password_lifetime=0

to my.conf file, but the problem persists

I don't know how to proceed from here. Where is the problem? Maybe the embedded version of XigmaNas is the problem?

Thanks in advance for your time

[ms49434]

I am running an embedded installation.

My database configuration:

services_mariadb.php.png

You should enable MariaDB service AFTER you created the home folder. The startup script of MariaDB will perform the basic database installation when no mysql folder exists in the home folder.
Verify that your home folder contains database related files and the my.cnf file.
Verify that the folder /var/db/ contains a symlink ('mysql') to the home folder.

If you didn't perform step 1 properly a reboot might have solved your issue already. You need to rerun the mysql_secure_installation script and create the kodi database user.

[birthman]

Hello, first of all, sorry for the delay, they have been very busy days (many trips, Christmas holidays, ...).

Finally I opted to perform an installation from scratch of Xigmanas (XigmaNAS-x64-embedded-11.2.0.4.6315) and the first problem ('kodi' user was gone if restarting Xigmanas) was solved. I think the problem was conflicts between MariaDB and a previous installation of MySQL server.

By the way, after this I've followed all the steps posted in my previous answer, then the user 'kodi' was created and was permanent (checked with phpmyadmin) but when Kodi tried to create the database it reports an error

ERROR: unable to create new database

. Searching in the KODI forums some people reported a solution for this error: add the line

bind_address = 0.0.0.0

to 'my.cnf' file. I've added this line manually to 'my.cnf' but my surprise was when restarted Xigmanas 'my.cnf' was rewritten and this line was deleted.

Finally, looking at your configuration I realized that additional lines for 'my.cnf' have to be added at Xigmanas webgui in 'Services>MariaDB>Settings' in the 'Additional Parameters' box.

Now everything works perfectly.

Thanks for your time and your answers.

PS: Do you think is a good idea to write a new HOW TO with all the steps? I can write it. Or this post is enought for the people who wants to setup Xigmanas with mysQL for centralized Kodi DB?

[ms49434]

Hello, first of all, sorry for the delay, they have been very busy days (many trips, Christmas holidays, ...).

Finally I opted to perform an installation from scratch of Xigmanas (XigmaNAS-x64-embedded-11.2.0.4.6315) and the first problem ('kodi' user was gone if restarting Xigmanas) was solved. I think the problem was conflicts between MariaDB and a previous installation of MySQL server.

By the way, after this I've followed all the steps posted in my previous answer, then the user 'kodi' was created and was permanent (checked with phpmyadmin) but when Kodi tried to create the database it reports an error

ERROR: unable to create new database

. Searching in the KODI forums some people reported a solution for this error: add the line

bind_address = 0.0.0.0

to 'my.cnf' file. I've added this line manually to 'my.cnf' but my surprise was when restarted Xigmanas 'my.cnf' was rewritten and this line was deleted.

Finally, looking at your configuration I realized that additional lines for 'my.cnf' have to be added at Xigmanas webgui in 'Services>MariaDB>Settings' in the 'Additional Parameters' box.

Now everything works perfectly.

Thanks for your time and your answers.

PS: Do you think is a good idea to write a new HOW TO with all the steps? I can write it. Or this post is enought for the people who wants to setup Xigmanas with mysQL for centralized Kodi DB?

Feel free to write and publish a new HOW-TO configure MariaDB service as a centralized database for Kodi.

[raulfg3]

+1 , always is good to have a guide or [HOWTO] writed for someone that really use the service to help others to configure.

[birthman]

Done!! I accept suggestions to improve the [HOW TO]

Enjoy it