root password for mysql

15 posts / 0 new
Last post
#1 Wed, 09/26/2018 - 07:41
musicmouse

root password for mysql

I have the problem that for some reason my password for the root user for Mysql doesn't work. I try it with phpMyAdmin but I keep getting "#1698 - Access denied for user 'root'@'localhost'". I reset my password inside Webmin but that didn't help.

I can login on my domain specific account with the same password. But that doesn't help when you want to create new domains.

Wed, 09/26/2018 - 08:55
adamjedgar

Not sure what operating system you are running however...i found a couple of options on ask ubuntu that may be of assistance. Below is the one i like best (seems simplest to me)

First stop the mysql server

sudo service mysql stop
Run the following command that will start the server again and skip the grant tables which store the passwords.

mysqld_safe --skip-grant-tables
Mysql server should now start. Next we log into root without a password.

mysql -u root
And then press enter

Now we run the following commands in mysql to reset the password

update user set
Password=PASSWORD('new-password')
where user='root';
flush privileges;
exit;
Now restart the mysql server

sudo service mysql restart
You should now be able to log in with the password you just set

mysql -u root -p
Press enter and you should get a prompt for root password and it should be accepted

here is the link to the ask ubuntu forum post... https://askubuntu.com/questions/567590/mysql-server-not-recognizing-correct-password

I dont tend to play around with this kind of thing much, but it might be worth stopping for a minute and decide whether or not the root mysql user and password should match webmin root user and password or whether its going to have no password? (i have so many passwords in my life i cant remember them all...but i think this is where one turns to "howtoforge The perfect server setup" with your operating system and webserver choice for advice)

AJECreative is the home of $5 webhosting, $15/month VPS servers (1cpu,1gb RAM, 25GB storage)
Centos7, Debian9, or Ubuntu18LTS
Available Control Panels = Centos-Webpanel, Cyberpanel, or Virtualmin

https://ajecreative.com.au

Mon, 10/01/2018 - 08:04
musicmouse

The sudo service mysql stop command returned nothing

But when I then ran : mysqld_safe --skip-grant-tables

I got 2018-10-01T12:51:27.373841Z mysqld_safe Logging to syslog. 2018-10-01T12:51:27.376970Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2018-10-01T12:51:27.381229Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

I tried logging in with mysql - u root But I got ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Looking at the file system I saw that /var/run redirects to /run and that /run has no mysqld subdirectory

Mon, 10/01/2018 - 09:55
musicmouse

I tried to understand what is going on but it looks like one big mess:

- I rebooted the server so that Mysql was loaded again.
- I logged in with Putty. On the command line I typed "mysql -u root" and I was at the mysql prompt. No password asked.
- I type "use Mysql"
- I typed "update user set password=PASSWORD("MyPassword") where User='root';" I got the error message: ERROR 1054 (42S22): Unknown column 'password' in 'field list'
- I tried "set password='Montessori79';". Reaction: Query OK, 0 rows affected, 1 warning (0.00 sec). And indeed the password still doesn't work.
- I had a look at the content of the user table. It doesn't have a column "password". Instead it has a column "authentication_string"
- So I tried "update user set authentication_string=PASSWORD("MyPassword") where User='root';". Reaction: 2 selected; 0 changed; 1 warning.
- Some posts on internet suggested using SET CREDENTIALS or ALTER USER 'root' IDENTIFIED BY. It didn't work either
- One puzzling aspect of my user database table is that there is no user "root" with host "localhost". Instead there is a user "root" with host "%"

This is really maddening

Mon, 10/01/2018 - 10:42
musicmouse

With this post I achieved some level of success: https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-...

But I remain puzzled why Virtualmin makes such a mess of things.

Mon, 10/01/2018 - 11:00
andreychek

Howdy,

Hmm, what distro/version are you using? And what MySQL version are you using with that?

What you're seeing there isn't typical (usually MySQL works with no trouble at all), so I'm considering some non-typical things that might cause that.

We've seen some unusual things happen when MySQL versions coming from a third party are installed in place of what comes with the distribution that's being used, I'm curious if maybe you have a third party MySQL version there?

If that's the case, it's not to say that can't be done, though some extra steps may need to be taken to get that upgrade working cleanly.

-Eric

Mon, 10/01/2018 - 14:56
adamjedgar

I dont wish to inflame a frustrating situation and my comment here does nothing to fix your problem, but i am convinced that when using control panels of any type on servers, one must ensure they do not stuff around ie "go outside the documentation and advice" of said control panels developers. These guys know their product well and this comes from me being a newbie who has stumbled across almost every hurdle a newbie can find in Virtualmin, ispconfig, vestacp, ispmanager, boxbilling, whmcs, blesta, google cloud, azure, and of course wordpress.

If your myssql install is doing weird stuff, if you can, make a backup and reinstall mysql from scratch...i wonder if that might be a way forward?

AJECreative is the home of $5 webhosting, $15/month VPS servers (1cpu,1gb RAM, 25GB storage)
Centos7, Debian9, or Ubuntu18LTS
Available Control Panels = Centos-Webpanel, Cyberpanel, or Virtualmin

https://ajecreative.com.au

Tue, 10/02/2018 - 05:53
unborn
unborn's picture
  • guys always say what os you are using - always.. please. cheers.

Configuring/troubleshooting Debian servers is always great fun

Tue, 10/02/2018 - 06:23
musicmouse

This is just a fresh Ubuntu 16.04 at Digital Ocean with only the most basic settings done before Virtualmin was installed. I installed Virtualmin previously a few years ago on Ubuntu 14.04 without problems. So I am amazed about all the serious trouble I run into (this is the second serious problem).

Just today I ran in Virtualmin System Settings-> Re-check configuration. And I got the result: MySQL does not appear to be installed and running on your system, or has not yet been set up properly in Webmin's MySQL Database module. If your system does not use MySQL, it should be disabled in Virtualmin's module configuration page. .. your system is not ready for use by Virtualmin. I didn't install Mysql - Virtualmin did. And in other menu options of Virtualmin I can see the database settings, change users, etc.

Tue, 10/02/2018 - 06:26
unborn
unborn's picture

ah ubuntu.. well I guess the best person to talk to is Eric here. I do not use ubuntu as server due to this and other problems. - dont get me wrong ubuntu is great os, however its just not for me. If you would need any help with debian, im here.. keep in mind ubuntu is debian based but its not debian. well - good luck mate.

Configuring/troubleshooting Debian servers is always great fun

Tue, 10/02/2018 - 07:59
musicmouse

It looks like by changing the password directly I got those Virtualmin Mysql problems. Since a reboot Mysql is no longer loaded. Yet another problem....

Tue, 10/02/2018 - 08:22
musicmouse

The last problem turned out to be easy. Just doing the same for root@localhost as I had done for root@%

Tue, 10/02/2018 - 14:37
adamjedgar

Whilst i also run debian, i dont recall having mysql problems like this with ubuntu 16.04. If i were you i would have performed a fresh install on another droplet by now. Have you posted this issue on the ask ubuntu forums? (Also digital ocean have a pretty good forum too)

AJECreative is the home of $5 webhosting, $15/month VPS servers (1cpu,1gb RAM, 25GB storage)
Centos7, Debian9, or Ubuntu18LTS
Available Control Panels = Centos-Webpanel, Cyberpanel, or Virtualmin

https://ajecreative.com.au

Tue, 10/02/2018 - 14:42
musicmouse

No, I haven'posted this on other forums. And I tried three droplets - all with the same result.

Thu, 10/04/2018 - 01:33
musicmouse

Problems don't stop. Now I can't import Mysql exports. It crashes on the first escaped quote.

Topic locked