setting up a lamp is now a nightmare

[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

[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.

[Rasilon]

thank you, although still would not let me grant privileges, I was able to install phpmyadmin and took it from there.

[Rasilon]

Now it is a mess again. I give up with mariondb Simply cannot get it to work on Raspi stretch

[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)

[Rasilon]

Brilliant thank you:) My eyes were starting to bleed.

[Rasilon]

I spoke to soon, after getting back to it today, I find I have no permissions to do anything.

[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)]>