[Wiki] Install and configure mysql server and clients

HowTo
1

On the server

Install mysql server, this will ask you to create a password, remember it and keep it safe!

sudo apt-get install mysql-server

Log in to mysql and add the user kodi and give it relevant permissions:

mysql -u root -p CREATE USER 'osmc'@'%' IDENTIFIED BY 'osmc’; GRANT ALL PRIVILEGES on *.* TO 'osmc'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; \q

Edit the mysql config file to allow access from other devices:
sudo vim /etc/mysql/my.cnf

Fine the line starting ‘bind-address’ and change it to the below, this will allow access from more than localhost

bind-address = 0.0.0.0

Restart the mysql server:
sudo service mysql restart

All done on the server!

On the client(s)

nano ~/.kodi/userdata/advancedsettings.xml

Paste this after replacing XXX.XXX.XXX.XXX with the IP address of the server that you’ve installed mysql-server on:

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>XXX.XXX.XXX.XXX</host>
    <port>3306</port>
    <user>osmc</user>
    <pass>osmc</pass>
  </videodatabase> 
  <musicdatabase>
    <type>mysql</type>
    <host>XXX.XXX.XXX.XXX</host>
    <port>3306</port>
    <user>osmc</user>
    <pass>osmc</pass>
  </musicdatabase>
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>

Press ctrl + x to save the file.

If this is a second client being connected to the mysql server copy the sources.xml file over, on the client that’s already setup:

scp ~/.kodi/userdata/sources.xml osmc@XXX.XXX.XXX.XXX:~/.kodi/userdata/

Where XXX.XXX.XXX.XXX is the IP address of the device you have just setup to connect to the mysql server.

Done (hopefully)

3 Likes
2

Please edit your post to point out that the client setup must use the ip address of the server and not just copy/paste your text. Also please add music database

3

Cheers for the pointers, updated!

4

Isn’t MySQL server in the app store?

5

I looked and couldn’t find it, I’ll check again and if so I’ll delete this thread

6

I am not clos the to my OSMC so couldn’t check. But I just had the gut feeling it has

7

I don’t think it is, not sure why.

8

There is an experimental LAMP app, which includes MySQL but it is not publicly in the Store.

I can add it with a default password of OSMC if people want

1 Like
9

Adding it would be nice thank you, the main reason for this mini tutorial though was to show what needed to be done once you had installed mysql and how to configure the clients and servers.

10

ALL of this info/direction is provided by Kodi itself in the wiki as none of it is quite specific to OSMC. I really don’t have a problem with adding it to our HowTo sub but I think you should also provide the official link from Kodi as well.

http://kodi.wiki/view/MySQL

11

Can put that link on there, I found I had to tweak the mysql slightly to get it to work, and the instructions could be a little vague for some osmc users, I had to change the service restart code, and the scp of the advancedsettings.xml wasn’t included. No major chsnges and nothing complex, just a little easier imo.

My idea was to make this a mini tutorial for osmc users so they don’t have to go digging around, just like all the other howto’s on here.

12

Looks good. You can also configure SQL via My OSMC, which is handy for some users who are shy of the command line

I’ll send you some info about contributing to the Wiki, I need all the help I can get there!

Sam

13

4 posts were split to a new topic: mySQL woes

14

Thank you for your post.
Could you please change this line in your post:

CREATE USER ‘osmc’@‘%’ IDENTIFIED BY 'osmc’;

to

CREATE USER 'osmc'@'%' IDENTIFIED BY 'osmc';

’ instead of ’

Thank you in advance.

15

:vulcan: heh?

16

3 posts were split to a new topic: MySQL settings not working - Moved to a help request topic

17

I’ve been trying to follow the guide and and it seems things have changed meanwhile a bit…
So the steps I needed to take as of 13th January 2018 on OSMC 17.6 with a Debian Stretch fork:
Install MySQL (MariaDB in fact as replacement of MySQL)
sudo apt-get install mysql-server
confirm the extra packages and wait the installation to complete

Login the DB
sudo mysql

Create DB user
create user 'osmc' identified by 'osmc';

Give grants
grant all on *.* to 'osmc';

Reload privs
flush privileges;

Exit the DB console
quit

Now we need to change MariaDB conf file to allow access from other hosts than localhost
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
locate the line
bind-address = 127.0.0.1
and change the IP
bind-address = 0.0.0.0
Ctrl+X to exit, confirm you want to save the file

Restart the service
sudo service mysql restart

Follow the client setup of the original guide

2 Likes
18

Thanks Kyblik, your instructions were the best I could find.

I’ve improved on them further by finding instructions on adding Apache, Python and phpmyadmin to administer the MySQL.

Install OSMC, add SSH & FTP servers

Make sure OSMC web server is not set to Port 80, change to 8080

sudo apt-get update
sudo apt-get dist-upgrade

sudo apt-get install apache2 -y (then browse to the Pi’s IP address to confirm)

sudo apt-get install php7.0 libapache2-mod-php7.0 -y
sudo apt-get install mysql-server php7.0-mysql -y

If not asked for a password for Mysql -
sudo mysql_secure_installation
Enter root password as ‘root’

sudo service apache2 restart

sudo mysql
create user ‘kodi’ identified by ‘kodi’; (this is the username I’ve historically used)
grant all on . to ‘kodi’;
flush privileges;
quit

sudo apt-get install python-mysqldb
sudo apt-get install phpmyadmin
select Apache 2 server, then say Yes to configure database with dbconfig-common
Enter root password

sudo nano /etc/apache2/apache2.conf
at the bottom add this line
Include /etc/phpmyadmin/apache.conf
Exit by Ctrl-X
sudo /etc/init.d/apache2 restart

Then check phpmyadmin is working by going to 192.168.1.xxx/phpmyadmin
login with kodi/kodi

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
locate the line
bind-address = 127.0.0.1
and change the IP
bind-address = 0.0.0.0
Ctrl+X to exit, confirm you want to save the file

Restart the service
sudo service mysql restart

1 Like
19

So after this then what?
The osmc used as the server has original library wiped, the souces were still there so I rescanned them. Is the rescanned info being added to maridb now? Edit: yes they were but you should backup your old video database before switching to mysql/maria so all the watched statuses are kept, then you can import it from the media menu in osmc settings after switching to mysql.

The second osmc still has the old library info so it’s not using the mariadb of the first. I’ve rebooted but it’s still just showing the old scanned database :frowning:

When install Mariadb I didn’t get asked to create a password, is that needed to connect to the first osmc and database?

Thanks.

20

Well this command have set the password as osmc so did you follow that?

Are you sure the database on the first device actually works?