You are not logged in.

#1 2018-06-02 16:02:41

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

[SOLVED] Ubu18: Unable to set mysql root password

Hi there everyone!

Like almost everything I do in linux anymore, my apache2/mysql install didn't go as planned and I've been left trying to install and set up all the various bits and pieces so i can have a working install for development purposes.

Although mysql server was supposed to install during apache install, it did not and I had to install it separately.

It's service is able to be successfully started and stopped.

Root login fails with the password I tried to set during apache install as well as with no password

Currently, I'm stuck at setting a root user password so i can use phpmyadmin. 

I've searched a lot and tried all the various suggestions but everything is ending in errors and failures so I thought I'd ask;

How do I set the mysql root password, not knowing it's current password in Ubuntu?

Thanks for your time!

Last edited by schwim (2018-06-03 01:18:06)


Schw.im! A social site with an identity crisis.

Offline

#2 2018-06-02 16:44:42

brontosaurusrex
Middle Office
Registered: 2015-09-29
Posts: 1,594

Re: [SOLVED] Ubu18: Unable to set mysql root password

Offline

#3 2018-06-02 16:59:33

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

Ah yes, this was the search result I tried on my 5th or 6th attempt at it.  I'd be happy to describe the points of failure, if it will help.

schwim@schwim-vm-ubob:/home/schwim$ mysqladmin -u root password xxxxxxxxxxxx
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost''
schwim@schwim-vm-ubob:/home/schwim$ sudo mysqladmin -u root password xxxxxxxxxxx
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
schwim@schwim-vm-ubob:/home/schwim$

Which led me to search "Using a password on the command line interface can be insecure."  That led me to the solution, which was to add my root user and password to my.conf in my user directory.

Which would be cool..... if I had a password to provide for the root user.

Last edited by schwim (2018-06-02 17:00:13)


Schw.im! A social site with an identity crisis.

Offline

#4 2018-06-02 18:39:16

tynman
Member
Registered: 2015-10-13
Posts: 72

Re: [SOLVED] Ubu18: Unable to set mysql root password

Did you (OP) follow the instructions in the link provided by brontosaurusrex, in the section with the title "Recover your MySQL password"? If not, I suggest giving it a try.

(Step 1 refers to a service named "mysql". In my experience, the mysql service is typically named "mysqld", but has an alias defined with name = "mysql", so the command given in Step 1 should work. Otherwise try substituting "mysqld" as the service name.)

If it doesn't work, report back with any error messages thrown.

Offline

#5 2018-06-02 18:41:15

Head_on_a_Stick
Member
From: London
Registered: 2015-09-29
Posts: 8,759
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

Thread moved to Help & Support (Other).


“Et ignotas animum dimittit in artes.” — Ovid, Metamorphoses, VIII., 18.

Forum Rules   •   How to report a problem   •   Software that rocks

Offline

#6 2018-06-02 19:20:39

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

tynman wrote:

Did you (OP) follow the instructions in the link provided by brontosaurusrex, in the section with the title "Recover your MySQL password"? If not, I suggest giving it a try.

(Step 1 refers to a service named "mysql". In my experience, the mysql service is typically named "mysqld", but has an alias defined with name = "mysql", so the command given in Step 1 should work. Otherwise try substituting "mysqld" as the service name.)

If it doesn't work, report back with any error messages thrown.

Yes, as it just so happens, I did.

schwim@schwim-vm-ubob:~$ sudo service mysql stop
schwim@schwim-vm-ubob:~$ sudo mysqld_safe —skip-grant-tables —skip-networking &
[1] 4646
schwim@schwim-vm-ubob:~$ 2018-06-02T19:18:44.767186Z mysqld_safe Logging to syslog.
2018-06-02T19:18:44.770189Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-06-02T19:18:44.772794Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  sudo mysqld_safe —skip-grant-tables —skip-networking
schwim@schwim-vm-ubob:~$

Last edited by schwim (2018-06-02 19:21:27)


Schw.im! A social site with an identity crisis.

Offline

#7 2018-06-02 19:23:39

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

I found this in /etc/mysql/debian.cnf:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = xxxxxxxxxxxx
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = xxxxxxxxxxx
socket   = /var/run/mysqld/mysqld.sock

This user/pass is able to log into mysql and phpmyadmin but has no privileges associated with the mysql table. I seem unable to do anything useful with that user.


Schw.im! A social site with an identity crisis.

Offline

#8 2018-06-02 22:05:09

tynman
Member
Registered: 2015-10-13
Posts: 72

Re: [SOLVED] Ubu18: Unable to set mysql root password

I just tried following the instructions from https://www.techrepublic.com/article/ho … -password/
I got exactly the same results as you sad.
It occurred to me that I couldn't recall what my MySQL-root password was on my computer. I read somewhere on the interweb that if you don't set a root password during MySQL installation, then the root Id simply works without any password. So I tried logging into MySQL-root without a password and it worked. I.e.,

$ mysql -u root

Since you also don't recall your mySQL-root password, could it be that you simply never set one? (That would be a good reason you don't remember it.)

Offline

#9 2018-06-02 23:02:08

twoion
ほやほや
Registered: 2015-08-10
Posts: 2,228

Re: [SOLVED] Ubu18: Unable to set mysql root password

Ah, yes, Debian/Ubuntu with stoopid defaults again. Create a separate user with all privileges and use that. Assuming phpmyadmin really needs superuser privileges. Otherwise, you should create the user with the minimal amount of privileges needed.

sudo mysql --defaults-extra-file=/etc/mysql/debian.cnf
create user 'php'@'localhost' identified by '<secure password>';
grant all privileges on *.* to 'php'@'localhost' with grant option;
flush privileges;
\q

Use the user php with the given password.

(Untested.)


Im grünen Wald, dort wo die Drossel singt…

Offline

#10 2018-06-02 23:26:39

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

tynman wrote:

I just tried following the instructions from https://www.techrepublic.com/article/ho … -password/
I got exactly the same results as you sad.
It occurred to me that I couldn't recall what my MySQL-root password was on my computer. I read somewhere on the interweb that if you don't set a root password during MySQL installation, then the root Id simply works without any password. So I tried logging into MySQL-root without a password and it worked. I.e.,

$ mysql -u root

Since you also don't recall your mySQL-root password, could it be that you simply never set one? (That would be a good reason you don't remember it.)

Hi there again, Tynman,

schwim@schwim-vm-ubob:~$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
schwim@schwim-vm-ubob:~$

Brand new install, so it should have been easy but it seems not.


Schw.im! A social site with an identity crisis.

Offline

#11 2018-06-03 00:31:26

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

twoion wrote:

Ah, yes, Debian/Ubuntu with stoopid defaults again. Create a separate user with all privileges and use that. Assuming phpmyadmin really needs superuser privileges. Otherwise, you should create the user with the minimal amount of privileges needed.

sudo mysql --defaults-extra-file=/etc/mysql/debian.cnf
create user 'php'@'localhost' identified by '<secure password>';
grant all privileges on *.* to 'php'@'localhost' with grant option;
flush privileges;
\q

Use the user php with the given password.

(Untested.)

Hi there Twoion, thanks for your help!

I did follow your instructions and I am logged into phpmyadmin and able to view all installed dbs which is a win!

I set root's password via phpmyadmin and changed the auth method from unix socket to mysql, which is what was keeping me from being able to log in as root.

Perhaps you or someone else can help with this: I do see this at the bottom of a phpmyadmin page.

mysqli_real_connect(): (HY000/1045): Access denied for user 'phpmyadmin'@'localhost' (using password: YES)

Connection for controluser as defined in your configuration failed.

It tells me "You can set more settings by modifying config.inc.php, eg. by using Setup script."  I click the link, which leads me to an htaccess login, where neither the mysql or OS user's passwords work.

I've searched this a lot. In /etc/phpmyadmin, both config-db.php and config-inc.php are empty files.  I'm not sure how to resolve this.

Any thoughts are welcome.

Last edited by schwim (2018-06-03 01:03:50)


Schw.im! A social site with an identity crisis.

Offline

#12 2018-06-03 01:17:15

schwim
Member
From: Western NC, US
Registered: 2015-09-29
Posts: 183
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

I added the user phpmyadmin with a password I supplied earlier when the script said it was adding the user phpmyadmin and after flushing the permissions, the error has disappeared.

8 solid hours to get mysql/phpmyadmin working. I'm too spent to work on the actual thing I needed mysql for smile

Thanks for your help!


Schw.im! A social site with an identity crisis.

Offline

#13 2018-06-07 19:56:03

doxanthropos
Member
Registered: 2016-01-26
Posts: 26
Website

Re: [SOLVED] Ubu18: Unable to set mysql root password

I hope everything is working fine for you now.
Just an addon to the solutions that are listed here:
I found myself in a very similar situation with the same error message, but a much easier solution.
I had installed mysql via "sudo apt install .." as one would do in Debian, but then blindly used the setup command found in a tutorial:

$ mysql_secure_installation

With this result:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The solution was using sudo also for the mysql_secure_installation. This let me set the root user password without any additional problems.

Offline

Board footer

Powered by FluxBB