Page 1 of 1

setting up a lamp is now a nightmare

Posted: Fri May 03, 2019 3:45 am
by Rasilon
Everything works fine until mysql setup.


sudo apt install mysql-server php-mysql [works]

then
sudo mysql --user=root

everything go's to hell from here.

DROP USER 'root'@'localhost';
CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'

So anyone have to actual correct way of doing this, because every tutorial i can find is exactingly the same

Re: setting up a lamp is now a nightmare

Posted: Fri May 03, 2019 4:21 am
by fbe

Code: Select all

sudo mysql
then

Code: Select all

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'mypassword';
This creates a new user with superuser privileges in your database. Whenever a tutorial says:

Code: Select all

mysql -u root -p
type

Code: Select all

mysql -u admin -p

Code: Select all

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'mypassword';
This doesn't grant any new privilege to root (root already has all privileges), but this enables password authentication and sets a pasword for root (the IDENTIFIED BY clause does it). If you try to install phpmyadmin later the installation will fail. I also heard about a logrotate job for mysql to fail...

Code: Select all

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED with unix_socket;
will restrore the unix socket authentication.
Later MariaDB versions will support an "ALTER USER" statement to modify the authentication plugin.

Re: setting up a lamp is now a nightmare

Posted: Fri May 03, 2019 6:54 pm
by Rasilon
thank you, although still would not let me grant privileges, I was able to install phpmyadmin and took it from there.

Re: setting up a lamp is now a nightmare

Posted: Sun May 05, 2019 12:11 am
by Rasilon
Now it is a mess again. I give up with mariondb Simply cannot get it to work on Raspi stretch

Re: setting up a lamp is now a nightmare

Posted: Sun May 05, 2019 12:46 am
by procount
The latest LAMP version behaviour has changed with respect to the root user. Did you try my tutorial which I've just rewritten for the latest Stretch and incorporated some comments/fixes from fbe?

viewtopic.php?f=29&t=218354#p1460237 (also see earlier posts on that thread)

Re: setting up a lamp is now a nightmare

Posted: Sun May 05, 2019 6:01 am
by Rasilon
Brilliant thank you:) My eyes were starting to bleed.

Re: setting up a lamp is now a nightmare

Posted: Sun May 05, 2019 8:17 pm
by Rasilon
I spoke to soon, after getting back to it today, I find I have no permissions to do anything.

Re: setting up a lamp is now a nightmare

Posted: Mon May 06, 2019 7:33 pm
by fbe
Sorry, forgot the "grant option" privilege when creating a superuser.

Code: Select all

pi@headless-pi:~ $ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all privileges on *.* to 'admin'@'localhost' identified by 'secret' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit;
Bye
pi@headless-pi:~ $ mysql -uadmin -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all privileges on *.* to 'admin2'@'localhost' identified by 'secret' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>