These forums are locked and archived, but all topics have been migrated to the new forum. You can search for this topic on the new forum: Search for root password for mysql on the new forum.
Web Hosting and Cloud Computing Control Panels
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-passwordI 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)
https://ajecreative.com.au
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
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
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.
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
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?
https://ajecreative.com.au
Configuring/troubleshooting Debian servers is always great fun
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.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
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....
The last problem turned out to be easy. Just doing the same for root@localhost as I had done for root@%
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)
https://ajecreative.com.au
No, I haven'posted this on other forums. And I tried three droplets - all with the same result.
Problems don't stop. Now I can't import Mysql exports. It crashes on the first escaped quote.