Rasilon
Posts: 105
Joined: Wed Mar 11, 2015 7:30 pm

setting up a lamp is now a nightmare

Fri May 03, 2019 3:45 am

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
Posts: 648
Joined: Thu Aug 17, 2017 9:08 pm

Re: setting up a lamp is now a nightmare

Fri May 03, 2019 4:21 am

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
Posts: 105
Joined: Wed Mar 11, 2015 7:30 pm

Re: setting up a lamp is now a nightmare

Fri May 03, 2019 6:54 pm

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

Rasilon
Posts: 105
Joined: Wed Mar 11, 2015 7:30 pm

Re: setting up a lamp is now a nightmare

Sun May 05, 2019 12:11 am

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

User avatar
procount
Posts: 2310
Joined: Thu Jun 27, 2013 12:32 pm
Location: UK

Re: setting up a lamp is now a nightmare

Sun May 05, 2019 12:46 am

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)
PINN - NOOBS with the extras... https://www.raspberrypi.org/forums/viewtopic.php?f=63&t=142574

Rasilon
Posts: 105
Joined: Wed Mar 11, 2015 7:30 pm

Re: setting up a lamp is now a nightmare

Sun May 05, 2019 6:01 am

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

Rasilon
Posts: 105
Joined: Wed Mar 11, 2015 7:30 pm

Re: setting up a lamp is now a nightmare

Sun May 05, 2019 8:17 pm

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

fbe
Posts: 648
Joined: Thu Aug 17, 2017 9:08 pm

Re: setting up a lamp is now a nightmare

Mon May 06, 2019 7:33 pm

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

Return to “Troubleshooting”