[Wiki] Install and configure mysql server and clients

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

4 posts were split to a new topic: mySQL woes

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.

:vulcan: heh?

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

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

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

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.

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?

Yes I did that command.
The database works fine on the first device.
The second device saw the database after I deleted all the sources and library info that was already on it plus I Copied these files over from the first device favourites.xml ", " sources.xml ", " mediasources.xml ", " passwords.xml "

The video files play (on nas) but the music files (on sdcard in first device) don’t play on the second device.

Get this error in the log.

DEBUG: CPlayerCoreFactory::GetPlayers: matched 0 rules with players
11:03:02.121 T:1925896704 DEBUG: CPlayerCoreFactory::GetPlayers: adding audiodefaultplayer (PAPlayer)
11:03:02.121 T:1925896704 DEBUG: CPlayerCoreFactory::GetPlayers: for video=0, audio=1
11:03:02.121 T:1925896704 DEBUG: CPlayerCoreFactory::GetPlayers: for video=1, audio=1
11:03:02.121 T:1925896704 DEBUG: CPlayerCoreFactory::GetPlayers: adding player: VideoPlayer
11:03:02.121 T:1925896704 DEBUG: CPlayerCoreFactory::GetPlayers: added 2 players
11:03:02.133 T:1718354688 DEBUG: Skin Helper Service --> Kodi_Monitor: sender xbmc - method: Playlist.OnAdd - data: {“item”:{“id”:918,“type”:“song”},“playlistid”:0,“position”:3}
11:03:02.154 T:1925896704 ERROR: Init: Error opening file musicdb://albums/406/918.mp3?albumid=406
11:03:02.154 T:1925896704 ERROR: CAudioDecoder: Unable to Init Codec while loading file musicdb://albums/406/918.mp3?albumid=406
11:03:02.154 T:1925896704 WARNING: PAPlayer::QueueNextFileEx - Failed to create the decoder
11:03:02.154 T:1925896704 DEBUG: OnQueueNextItem: play state was 1, starting 1
11:03:02.154 T:1925896704 DEBUG: OnPlayBackStopped: play state was 1, starting 0
11:03:02.155 T:1925882624 DEBUG: CAnnouncementManager - Announcement: OnStop from xbmc
11:03:02.155 T:1925896704 ERROR: Playlist Player: skipping unplayable item: 3, path [musicdb://albums/406/918.mp3?albumid=406]
11:03:02.155 T:1925896704 DEBUG: Playlist Player: no more playable items… aborting playback
11:03:02.155 T:1925882624 DEBUG: GOT ANNOUNCEMENT, type: 1, from xbmc, message OnStop
11:03:02.184 T:1659372288

Well for them to play you would first need to share them (e.g. via samba) and then either add them to the Library on both devices via the Network Share name or alternatively use path substitution on the second device

I’ve installed samba from the myosmc program addon but not sure if I need to change anything after that as adding the source on second device smb://(ip address of first device)/home/osmc/Music throws up error “file exits” I’ve tried it without a password, with the default osmc password and also the password I made for remote control via http.

When adding the network location i’ve got the shared folder set as home/osmc/Music

I’ve rebooted raspberry pi but still them same error

Thanks.

I think you might have mixed up things with copying your sources and password files. Also it might be best if you use fstab based mounts as you anyhow need to find a way to align both systems (either by using same mounted folder name or via the path substitution)

17 posts were split to a new topic: Phpmyadmin root login not possible

A post was merged into an existing topic: Phpmyadmin root login not possible

2 posts were merged into an existing topic: Phpmyadmin root login not possible

So here we are at the end of 2020. The command
sudo apt-get install mysql-server
no longer seems to work. Any hints for a current command that might work today?

I’m probably not the best source here but I assume you want to install mariadb-server now.

sudo apt update
sudo apt install mariadb-server

Thanks, that worked like a charm. The rest of the guide is still working as described by Kyblik previously.

In the documentation that I found it was recommended to secure your MariaDB installation after first install, like this:

sudo mysql_secure_installation
First prompt will ask you to enter the current database root password. Press Enter to indicate ‘none’. Next prompt will ask if you’d like to set up a database root password. Type N and Enter. From there, press Y + Enter to accept the defaults.

Just thought I’d mention it for prosperity.