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
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