We are starting CHRISTMAS Sale. Get 30% OFF on lifetime licenses with code: FYSKK72

Can't connect to MySQL localhost (how to)

Questions regarding the Database Server
MySQL, PostgreSQL, MariaDB, Percona Server, phpMyAdmin, phpPgAdmin
Aldoseri
Posts: 7
Joined: Fri Nov 25, 2016 12:08 pm

Can't connect to MySQL localhost (how to)

Postby Aldoseri » Sat Dec 10, 2016 11:07 pm

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
VestaCP Team
Posts: 7807
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

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

Postby skurudo » Mon Feb 27, 2017 9:34 pm

Nice one! Thanks.

PS: Topic it's sticky now.
-> DigitalOcean competition - please, support us
-> fix for phpmyadmin - nice and sweet now

sifur
Posts: 24
Joined: Mon Dec 28, 2015 10:43 pm

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

Postby sifur » Mon Aug 14, 2017 11:29 am

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
Posts: 1
Joined: Sat Oct 21, 2017 11:18 am

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

Postby gyomnorris » Sat Oct 21, 2017 11:38 am

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
Moderators
Posts: 832
Joined: Sat Sep 06, 2014 9:58 pm
Contact:

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

Postby mehargags » Sat Oct 21, 2017 2:16 pm

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

brianbws
Posts: 8
Joined: Mon Aug 21, 2017 6:56 am

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

Postby brianbws » Fri Oct 27, 2017 9:00 pm

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
Moderators
Posts: 832
Joined: Sat Sep 06, 2014 9:58 pm
Contact:

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

Postby mehargags » Mon Oct 30, 2017 8:45 pm

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
Posts: 8
Joined: Mon Aug 21, 2017 6:56 am

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

Postby brianbws » Wed Nov 01, 2017 9:11 pm

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


Return to “Database Server”



Who is online

Users browsing this forum: No registered users and 3 guests