Can't connect to MySQL localhost (how to)

[Aldoseri]

Hi guys,

I thought this might help someone.

If you get this message from VestaCP ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Then this means your root password for MySQL has changed for example after installing MariaDB. like in my case where I used a password different from when I first Installed VestaCP.

Steps to fix the problem.

1- Login to your server using FTP
2- Update your password in the following files.

/root/.my.cnf
/usr/local/vesta/conf/mysql.conf

You don't need to restart your server or MySQL service.

If you don't know your root password or would like to change it then continue to step 3

3- Follow the instructions below:

login as root using terminal ( I use Mac, maybe you have something else )

~# /etc/init.d/mysqld stop
~# mysqld_safe –skip-grant-tables &
~# mysql -u root
~# mysql> use mysql;
~# mysql> update user set password=PASSWORD(“Your-new-password-here”) where User=’root’;
~# mysql> flush privileges;
~# mysql> quit
~# /etc/init.d/mysqld stop
~# /etc/init.d/mysqld start

Now it should work

Thanks

[skurudo]

Nice one! Thanks.

PS: Topic it's sticky now.

[sifur]

I followed your all steps above, but it's not working for me.

I'm having issue with creating new database in VestaCP.
It's showing error message:

Code: Select all

Error: Connection to localhost failed

What I did?
I don't have much knowledge on server of course, and by curious-
> I changed DB password from Server > Configure > DB > Password. I thought as this options is here it should change MySQL password without problem.
> After 3/4 days when I try add new Database, I'm getting this error.
> Then I searched here and google then tried many things. to reset mysql root password.
> Here when I try this mysql -u root it's showing this error Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I'm using Ubuntu 16 with latest vestacp installed 3 months ago.
Now I have 5 sites running on the server and that's why I can't simply uninstall or reinstall vesta again.

[gyomnorris]

Hello,

I have similar problem, when trying to login to mysql with ssh or phpmyadmin :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I checked the password in

/root/.my.cnf
/usr/local/vesta/conf/mysql.conf

This is the good one I'm using. But I cannot login.

It's happened since I run a "sudo dpkg-reconfigure phpmyadmin" to fix "#1146 - Table 'phpmyadmin.pma_table_uiprefs' doesn't exist" as explaned there : https://stackoverflow.com/questions/207 ... esnt-exist

I'm running vesta on a OVH VPS on Debian 8 64 bits.

I will really appreciate your help.

Thanks,

Gyom

[mehargags]

You can try to reset your MySQL root password and then set it in your my.cnf

[brianbws]

I cannot get this to work. Ubuntu 16.04.

There is no /etc/init.d/mysqld. /etc/init.d/mysql stop gives an error.

Code: Select all

root@vps:~# /etc/init.d/mysqld stop
-bash: /etc/init.d/mysqld: No such file or directory
root@vps:~# /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.
root@vps:~# mysqld_safe –skip-grant-tables &
[1] 29745
root@vps:~# 2017-10-27T20:55:17.282647Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-10-27T20:55:17.284664Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

How do I change the mysql root password? Why is this so hard?

[mehargags]

Ubuntu 16.x / Debian 8.x use systemd instead of init.d so you would use

Code: Select all

systemctl stop mysql

same for apache2, nginx, etc. and replace stop with start or restart.

To reset MySQL root password, start the mysqld configuration :

Code: Select all

sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE user SET Password=PASSWORD('sam12sony') WHERE User='root'; FLUSH PRIVILEGES; exit;

systemctl restart mysql

[brianbws]

Oops. I just saw your reply, but I think I fixed it.

'mysqld_safe --skip-grant-tables &' was giving me an error too, so i think I fixed it this way:

Code: Select all

$ /etc/init.d/mysql stop
$ mkdir -p /var/run/mysqld
$ chown mysql:mysql /var/run/mysqld
$ mysqld_safe --skip-grant-tables &
mysql -u root;
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysql> exit;
$ /etc/init.d/mysql start

[mikhailnov]

I did all this, plus

Code: Select all

mysql> use mysql;
​mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';
​mysql> flush privileges;
​mysql> quit

and

Code: Select all

mysqladmin -u root password NEWPASSWORD

(https://www.techrepublic.com/article/ho ... -password/)
End eventually It worked. Thanks to everyone.

[mikhailnov]

Here is a full manual in Russian:
https://nixtux.ru/619