Grad106
Posts: 2
Joined: Sun Oct 22, 2017 4:13 pm

MySQL root password?

Sun Nov 05, 2017 5:55 pm

I can’t figure this out...i was never asked to set up a root password but also can’t access mysql without one.... I have removed and installed mysql a couple of times and it makes no difference. Any help here much appreciated.

Thank you!

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

Re: MySQL root password?

Sun Nov 05, 2017 6:15 pm

I guess you are using Raspbian Stretch and installed MariaDB. In a terminal type:

Code: Select all

sudo mysql -u root -p

RonR
Posts: 849
Joined: Tue Apr 12, 2016 10:29 pm
Location: US

Re: MySQL root password?

Mon Nov 06, 2017 3:41 am

Grad106 wrote:
Sun Nov 05, 2017 5:55 pm
I can’t figure this out...i was never asked to set up a root password but also can’t access mysql without one.... I have removed and installed mysql a couple of times and it makes no difference.
Your experience is the opposite of mine. No matter what I do, I can't set a MYSQL root password. MySQL always lets me in regardless of what, if any, password I enter. It works as expected under Jessie, but under Stretch, nothing matters. Fortunately for me, MySQL is being used with software that wants no root password set, so it's not causing me any problems as is. But it surely can't be right...

Prior to installing MySQL I run:

Code: Select all

apt-get -y install debconf-utils
echo "mysql-server-5.5 mysql-server/root_password password passw0rd" | debconf-set-selections
echo "mysql-server-5.5 mysql-server/root_password_again password passw0rd" | debconf-set-selections
This is to keep the MySQL installation from bugging me for a password.

After MySQL is installed, I run:

Code: Select all

mysqladmin -u root -ppassw0rd password ''
To clear the password.

All this works as expected under Jessie, but appears to have no effect under Stretch where the root password is simply ignored.

slimplynth
Posts: 21
Joined: Tue Dec 27, 2011 12:50 am

Re: MySQL root password?

Sun Jul 08, 2018 6:20 pm

fbe wrote:
Sun Nov 05, 2017 6:15 pm
I guess you are using Raspbian Stretch and installed MariaDB. In a terminal type:

Code: Select all

sudo mysql -u root -p
Just set up nginx, php7 and mariadb on an rpi zero w - thought that was going to be a tough night but went surprisingly well.. though Mariadb asking for the root password felt like a fresh install might have been on the cards.. stumbled on the above, thank you kindly..

These are the steps I took to install from image to webserver from linux.. I don't think i did it exactly in the right order but it might help someone.

Code: Select all

>> wget https://downloads.raspberrypi.org/raspbian_lite_latest

>> lsblk
>> umount /dev/mmcblk0*


>> unzip -p 2018-04-18-raspbian-stretch.zip | sudo dd of=/dev/sdX bs=4M conv=fsync

copy wpa_supplicant and ssh files to boot folder... power on

>> sudo apt-get update && sudo apt-get upgrade

>> sudo apt-get install mariadb-server mariadb-client

>> sudo apt-get install php7.0 libapache2-mod-php7.0

>> sudo apt-get install php7.0-mysql

>> sudo apt-get install phpmyadmin

>> sudo apt-get install nginx

>> sudo reboot now

>> sudo systemctl start nginx

>> hostname -I

Goto the host's webpage and don't worry if you see the Apache default index page

>> sudo apt-get remove apache2

>> sudo apt-get install php7.0-fpm

>> sudo nano /etc/nginx/sites-enabled/default
find:
index index.html index.htm;

add: index.php

find:
===================================================
#location ~ \.php$ {
        #       include snippets/fastcgi-php.conf;
        #
        #       # With php5-cgi alone:
        #       fastcgi_pass 127.0.0.1:9000;
        #       # With php5-fpm:
        #       fastcgi_pass unix:/var/run/php5-fpm.sock;
        #}

====================================================

replace with:
====================================================
location ~ \.php$ {
               include snippets/fastcgi-php.conf;
               fastcgi_pass unix:/var/run/php/php7.0-fpm.sock;
        }


and save/exit

>> sudo systemctl reload nginx

test it..

>> sudo nano /var/www/html/index.php

<?php phpinfo(); ?>

Save and exit..

http://pi.ip.address.here

>> sudo mysql -u root -p

(add password to mariadb)

end...

SenorContento
Posts: 1
Joined: Tue Jul 24, 2018 2:11 am

Re: MySQL root password?

Tue Jul 24, 2018 2:21 am

As far as I can tell, it appears to mysql server wants you to launch the command using sudo in order to login. This only works when trying the root account. If I try logging in without sudo, even setting a password for root (using passwd) and using it, it does not let you in. If you try logging in as root with sudo, you can log in with any password. Sudo does not help you with any other account. I don't know why this is this way and I am still in the process of trying to figure out how to set proper passwords.

Code: Select all

[email protected]:~ $ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
[email protected]:~ $ sudo !!
sudo mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

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

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

MariaDB [(none)]> 
MariaDB [(none)]> exit
Bye
[email protected]:~ $ sudo mysql -u pi -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'pi'@'localhost'

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

Re: MySQL root password?

Tue Jul 24, 2018 6:02 pm

Apologies for the stupid "sudo mysql -u root -p", that I introduced above. It didn't help understanding anything. It follows the bad tradition to keep on doing things that used to work ("mysql -u root -p" used to work before Debian Stretch), and if something doesn't work, just add "sudo", to give a working or non-working command a better karma.

Code: Select all

sudo mysql
Is the way to login as root to MariaDB when you are using Raspbian Stretch. No password is used for authentication of user root. This is the way, the Debian package builders configure MariaDB. You can change that, but you breaks things, if you do so.

First read /usr/share/doc/mariadb-server/README.Debian.gz, to figure out, what is going on with the root account.
Start here https://mariadb.com/kb/en/library/passw ... n-plugins/ to read about authentication plugins.
Start here https://mariadb.com/kb/en/library/accou ... -commands/ to read about creating users and granting privileges.
Avoid tutorials, that advise to run mysql_secure_installation on a MariaDB database, installed on Debian/Raspbian Stretch. These tutorials are copied by people, that don't know what they are doing, from tutorials that may work for other distibutions or used to work for MySQL on Debian Jessie or earlier versions.

You can create a local user that is authenticated by unix socket plugin with the statements:

Code: Select all

[email protected]:~ $ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

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

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

MariaDB [(none)]> create user [email protected] identified with 'unix_socket';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit;
Bye
[email protected]:~ $
Now try to set a password for pi. The account will behave like the root account. "sudo mysql -u pi -p" won't work. You can login only when you are "pi", that means only without "sudo".
Login as root again and

Code: Select all

MariaDB [(none)]> drop user [email protected];
To remove the [email protected] account.
Now create a user that can login with a password:

Code: Select all

MariaDB [(none)]> create user [email protected] identified by 'secret';
(replace secret by a good password.) To make [email protected] a real administrator with the same privileges as [email protected]:

Code: Select all

MariaDB [(none)]> grant all privileges on *.* to [email protected] with grant option;
You should do so and try to login as admin, before you mess up the [email protected] account.

Now you should be able to drop the root account and recreate it with password authentication. Try to install phpmyadmin after you "fixed" your root account this way. It will fail, because the Debian packagers don't expect, that a password could be required to login as root. Now you have the choice to recreate the root acount with unix socket authentication, enable logon for root without supplying a password by other means or run the database installation part for phpmyadmin manually.

You could leave the [email protected] account untouched instead and whenever asked for credencials with administrator privileges supply "admin" and the secret password.

If you are an expert, drop the [email protected] account and use your admin account only for administrative purposes.

Good luck.

thrashercharged
Posts: 2
Joined: Tue Jan 22, 2019 11:37 pm

Re: MySQL root password?

Wed Jan 23, 2019 1:09 am

Sorry to revive such an old thread but I figured adding to this one was better than starting a new one. I'm having exactly the same problem and have followed the advice given here and other advice found on the internet and still can't access mysql.

I'm running the latest Raspbian Stretch, PHP 7.0.33, and installed MySQL using the following command:
sudo apt-get install mysql-server php-mysql -y

Just like the OP of this thread, I was never asked for a root password. I've verified there is no password by doing this:
sudo nano /etc/shadow

I searched for mysql and found this:
mysql:*:xxxxx:x:xxxxx:x:::
And apparently the : *: verifies that there is no password.

Back in the terminal command line when I type:
sudo mysql -u root -p
The RPi responds with Enter Password, I press <enter> since there should be no password and I get and Error 2002:
Can’t connect to local MySQL server through socket ‘var/run/mysqld/mysqld.sock’ (111 “Connection Refused”)

I tried
sudo mysql
which does not ask for password but gives the same error.

So I tried giving it a root password with the following:
sudo service mysql stop

Then I killed all processes:
sudo killall mysqld mysqld_safe
sudo killall -9 mysqld mysqld_safe

and ran mysql again skipping the grant tables
sudo mysqld_safe --skip-grant-tables &

I then logged into mysql with:
mysql

updated the password to NEW-PASSWORD:
UPDATE mysql.user SET Password=PASSWORD('NEW-PASSWORD') WHERE User='root';

Flushed privileges and exited.
I still can't access MySQL even using 'NEW-PASSWORD' as the password. Note that I've done variations of this procedure several times as there are slight variations of this as solutions on the internet and used different passwords each time to no avail.

I'm hoping someone can help? Thanks - Tom

spock
Posts: 221
Joined: Sun Oct 02, 2011 10:33 am

Re: MySQL root password?

Fri Mar 15, 2019 2:41 pm

i had the same problem.
https://www.linode.com/docs/databases/m ... -centos-7/
this how-to worked for me (it's for centos but the steps starting from "harden mariaDB server" worked fine for me on raspbian).

Veeej
Posts: 10
Joined: Mon Mar 04, 2019 7:40 pm

Re: MySQL root password?

Mon Apr 08, 2019 12:38 pm

Hey
So i had the same issue and nothing really worked for me. So i simply uninstalled it using:

Code: Select all

[sudo apt-get remove --purge mysql\*
re installed it using:

Code: Select all

sudo apt-get install mysql-server

sudo apt-get install mysql-client

mysql -uroot -p
when prompted for a password i entered mysql (which i did the first time and didn't work). I guess it just needed to be installed again

User avatar
DougieLawson
Posts: 37648
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

Re: MySQL root password?

Tue Apr 09, 2019 2:00 am

The default install on DebIan and Raspbian doesn't need a password for root access to MariaDB.

https://www.digitalocean.com/community/ ... untu-18-04 that works on Raspbian.
Note: Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

Any DMs sent on Twitter will be answered next month.
All non-medical doctors are on my foes list.

User avatar
randybonnette
Posts: 3
Joined: Wed May 29, 2019 5:30 am

Re: MySQL root password?

Wed May 29, 2019 5:37 am

Thanks for sharing. I've been searching for the solution and nothing worked for me.. I got this error all the time: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

User avatar
randybonnette
Posts: 3
Joined: Wed May 29, 2019 5:30 am

Re: MySQL root password?

Wed May 29, 2019 5:40 am

spock wrote:
Fri Mar 15, 2019 2:41 pm
i had the same problem.
https://www.linode.com/docs/databases/mariadb/install mariadb_write my paper_how to
this how-to worked for me (it's for centos but the steps starting from "harden mariaDB server" worked fine for me on raspbian).
Btw, the solution is mentioned by someone in the comments to this how to article as well.
Thanks,
Randy

Return to “Beginners”