Page 1 of 2

Can't connect to MySQL localhost (how to)

Posted: Sat Dec 10, 2016 11:07 pm
by 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

Re: Can't connect to MySQL localhost (how to)

Posted: Mon Feb 27, 2017 9:34 pm
by skurudo
Nice one! Thanks.

PS: Topic it's sticky now.

Re: Can't connect to MySQL localhost (how to)

Posted: Mon Aug 14, 2017 11:29 am
by 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.

Re: Can't connect to MySQL localhost (how to)

Posted: Sat Oct 21, 2017 11:38 am
by 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

Re: Can't connect to MySQL localhost (how to)

Posted: Sat Oct 21, 2017 2:16 pm
by mehargags
You can try to reset your MySQL root password and then set it in your my.cnf

Re: Can't connect to MySQL localhost (how to)

Posted: Fri Oct 27, 2017 9:00 pm
by 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?

Re: Can't connect to MySQL localhost (how to)

Posted: Mon Oct 30, 2017 8:45 pm
by 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

Re: Can't connect to MySQL localhost (how to)

Posted: Wed Nov 01, 2017 9:11 pm
by 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

Re: Can't connect to MySQL localhost (how to)

Posted: Thu Apr 19, 2018 3:26 pm
by 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.

Re: Can't connect to MySQL localhost (how to)

Posted: Tue Jul 31, 2018 9:12 pm
by mikhailnov
Here is a full manual in Russian:
https://nixtux.ru/619