(Dec 29) New version 0.9.8-18 has been released. More you can find here

Can't connect to MySQL localhost (how to)

Questions regarding the Database Server
MySQL, PostgreSQL, MariaDB, Percona Server, phpMyAdmin, phpPgAdmin
Forum rules
Before creating a new topic or reply on the forum you should fill out additional fields "Os" and "Web" in your profile section.
In case of violation, the topic can be closed or response from the support will not be received.
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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: 900
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: 900
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 2 guests

cron