install mariadb and change database directories

mariadb on osmc stretch.md

https://discourse.osmc.tv/t/wiki-install-and-configure-mysql-server-and-clients/19170/17 https://www.itzgeek.com/how-tos/linux/debian/how-to-install-mariadb-on-debian-9.html https://www.linuxbabe.com/debian/install-lamp-stack-debian-9-stretch

sudo apt install mariadb-server mariadb-client

Now run the post installation security script.

cd
sudo mysql_secure_installation

press 'enter' as old 'root' password and give a new password 2 times. Press Y or Enter to accept all the security defaults

Now try connet to mariadb server (non root users can't login by default):

sudo mariadb -u root

Basic osmc user configuration

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

You should be able to connet with the created user and password:

mariadb -u osmc -p

(optional) enable external access

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 mariadb restart

create new data location and mount point

sudo apt-get install bindfs

cd /media/Elements
mkdir data
mkdir data/mariadb

cd /media
sudo mkdir data-mariadb
chown root: data-mariadb

# https://manurevah.com/blah/en/p/Bindfs-Mount-files-as-another-user
# test
sudo bindfs -u mysql -g mysql  -p 0660,ug+D /media/Elements/data/mariadb/ /media/data-mariadb/
# append to /etc/fstab
/media/Elements/data/mariadb /media/data-mariadb fuse.bindfs nofail,force-user=mysql,force-group=mysql,perms=0660:ug+D 0 0

move databases

https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

sudo systemctl stop mariadb

we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while-v provides verbose output so you can follow the progress.

sudo rsync -av /var/lib/mysql/ /media/data-mariadb/

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

change datadir to new directory datadir = /media/data-mariadb

restart and check:

sudo systemctl start mariadb
sudo mariadb
select @@datadir;

Permission

https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

$ sudo mariadb -u root -p
> SELECT User, Host, plugin FROM mysql.user;

gives your osmc user hability to connect with system credentials:

> USE mysql;
> SELECT User, Host, plugin FROM mysql.user;
> CREATE USER 'osmc'@'localhost' IDENTIFIED BY '';
> GRANT ALL PRIVILEGES ON *.* TO 'osmc'@'localhost';
> UPDATE user SET plugin='unix_socket' WHERE User='osmc';
>  FLUSH PRIVILEGES;

or allow root to login with pass:

UPDATE user SET plugin='mysql_native_password' WHERE User='root';