Reputation: 781

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES)

I've been trying to create a new database connection on workbench. However, every time I test a connection there is an error message that says

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES)

I don't know what's the cause of this error. I have tried uninstalling and re-installing my workbench 6.1 and mysql server 5.6 but the error is still unsolved.

Upvotes: 78

Answers (30)

Sagar S Chiplunkar

Reputation: 1

Go to System Services

Go to Properties of "MySQL80"

Change the start-up type to Automatic

I hope this helps!

Upvotes: -1

ITselect

Reputation: 153

The error reads Failed to connect to mysql at 127.0.0.1:3306, which means your mysql workbrench is asking to connect via port 3306 as in the picture below

But your MySQL Server is configured with a different port (may be 3301). Therefore, there's a conflict here. So, head to your mysql installer and click on reconfigure to reconfigure your MySQL Server as below

When you click on reconfigure, it'll take you to the screen below where you have to change the port to 3306 to match the port on your Workbrench as below

Then everything will work and you'll connect your Workbrench successfully.

Upvotes: 0

Alla Kopiichenko

Reputation: 1

I had the same issue (I use Mac, 14.4.1 (23E224)). I went to System Setting, and MySQL is at the sidebar menu (bottom of it, right after Printers & Scanners). In instances, there are the active instance, installed instances, and data directories. I went to the active instance, stopped the MySQL server, and entered a new password. And then activated it again. When I returned to the workbench it asked for a password and I entered the new one, that I had just created.

Upvotes: 0

Muhammad Monis Mazhar

Reputation: 29

For anyone who have install mysql using homebrew you should be able to redefine password for your root user using the following command.

Make sure your mysql server is running

mysql.server status

Now, run this command you should be able to set the password and set other configurations and then continue on....

mysql_secure_installation

Upvotes: 1

Aman Jha

Reputation: 544

I was to facing the same database connections issue in MySQL workbench.

Install MySQL workbench latest i am using MySQL Workbench 8.0 setup all the things as default.

Install MySQL Community Server MySQL Community Server 8.2.0 Innovation given then link enter link description here

setup all of the things as default.

set the password as per your wish

than connect the database and it's done

Upvotes: 0

Ilario Forcherio

Reputation: 1

EASY WAY

When you've been asked to enter the password after clicking Test Connection, you need to enter your root password. This means you have to enter the password of your main user (the password you use to log into the operating system). This will grant you access to the connection.

Upvotes: 0

chitraarasu

Reputation: 140

If you are a mac user try this method

Go to System Preferences > MYSQL > Initialize Database > Enter password

Hope this helps 😊

Upvotes: 1

ASH

Reputation: 1

First check if MySQL Server is installed on your system or just MySQL workbench.

If MySQL server is not installed first install it and then you can go to Settings -> environment variables -> path and add the MySQL path from program files where MySQL has been installed.

This must resolve your issue.

Upvotes: 0

Osama Sultan

Reputation: 21

I have read all the above solutions and none of them worked in my case, So the main error was "MySQL server was not running or installed, Workbench could not find any server" I installed XAMPP server and started MySQL server. The connection then established on Workbench

Upvotes: 1

cng.buff

Reputation: 575

In sone case, not working with Workbench but is worked with Datagrip.

Upvotes: 0

janadari ekanayaka

Reputation: 5116

https://i.sstatic.net/AHYaI.png - click on "ADD"

https://i.sstatic.net/pzSOD.png - select a server from the given list

next -> next -> add a password and click "finish"..

check the connection using the workbench

Upvotes: 0

Zehra N.

Reputation: 25

I was also faced with this problem. But I handle this solution.

Just click right to MySQL Workbench app>>
then open file location. And here you can find Installer for Window >>
open and start the installer. This is the install configuration.

Open it and then choose your MySQL Server and reconfig.

After it, you should fill authen.

Use legacy Auth.. >>
Roles>>
set pass. >> then don't change anything.. go and execute it. That's it.

Upvotes: 0

user15259565

Reputation:

This is a very edge case, but you cannot use passwords with an apostrophe ('). The password I created while setting up MySQL Server had an apostrophe (') in it, and it was always giving me the error you mentioned.

As soon as I changed to a password without the apostrophe, it worked.

Upvotes: 1

Nishita Giduturi

Reputation: 11

I also faced the same issue when trying to create a mySQL connection on workbench. Problem with me was I downloaded incompatible mySQL .dmg file from mysql.com. Re-installing 'macOS 11 (x86, 64-bit), DMG Archive' instead of 'macOS 11 (ARM, 64-bit), DMG Archive' solved my issue. I suggest you to try re-installing the compatible dmg file for your OS

Thanks.

Upvotes: 1

MarcL

Reputation: 3593

I just had the same problem, but the underlying issue was sort of differen... I tried to connect to localhost inside MySQL Workbench, but it did not even find a connected SQL Server instance, the problem was, that the mysql service was not installed under windows. So I opened cmd as administrator and typed mysqld --install, restarted the workbench and mySQL was properly detected, I could connect and execute all other mysql related commands...

Upvotes: 0

Tincho

Reputation: 11

I had the same problem with MySQL version 8.0.23.

I solved it in the following way:

1. Opening again the msi installer for windows.

2. In MySQL Server I clicked on "Reconfigure".

3. Then I clicked on the following steps until I got to "Accounts and Roles" and there I set the password for "root".

4. Finally in SQL Workbench, I went to Manage Server Connections and clicked on "store in Vault..." where I entered the same password as in step 3.

PS: This procedure also creates the MySQL Service on windows which also helps to solve this problem.

I hope this is helpful

Upvotes: 1

Shaon Talukder Dwip

Reputation: 11

Don`t worry about it. just open MySql Installer Community from start menu or your program file and reconfig "Mysql Server" and "Samples and Example" focus on Samples and Example config... and tik mark on Mysql Server, enter your password, click on check and finally click on next and finish. Hope you will see the success message. Thank You

Upvotes: 1

Abdelrahman Tareq

Reputation: 2297

In my case, the solution was to open MySQL installer community then under quick actions press reconfigure the settings then it worked fine

Upvotes: 0

Ragip Gashi

Reputation: 41

MySQL Installer Community -> MySQL Server Reconfigure -> Next -> Next -> Connection Name [Local instance or any other name] -> Click on Password - Store in Vault... and add a password and then -> Test connection. It works for me :)

Upvotes: 4

Yash Sharma

Reputation: 11

I am also facing the same problem and finally, I got the solution...

Open MySQL Installer.
After that you see the Reconfigure option in the front of the MySQL Server. Click on it.
After that you have to click on the Next buttons without changing any default settings.

Upvotes: 0

Esmael Ebrahim

Reputation: 31

i think the problem is we are trying to connect to a local server that is not running.

we need to first run the MySQL server then connect to it.

just Go to task manager > services find MYSQL80 and then start the service.

enter image description here

Upvotes: 3

Khalaf Eso

Reputation: 51

Just go back to your installer and add SQL servers, if its already there just reconfigure it and add password from there,

Go back to your workbench and delete the current users and create a new one with the same password you used during server configuration

Upvotes: 5

Ahmad Shbita

Reputation: 73

Check that Mysql server is started. It can be run by : CMD => "MySQL Installer"

See video: https://www.youtube.com/watch?v=nlefNPcb_Qg&ab_channel=NateBoorsma

Upvotes: 0

Aleksandra

Reputation: 21

Solution for me on Windows10:

Go to Services and find the MYSQL service, than STOP it.
Open Notepad and enter the following line:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

than save the file to the root of your hard drive (C:\ ). Choose a filename that makes sense, such as mysql-init.txt.

Navigate to the MySQL directory using the command prompt (run as administrator):

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Enter the following:

mysqld --init-file=C:\mysql-init.txt

Now, you can log into your MySQL server as root using the new password and you can delete the C:\mysql-init.txt file.
Go to Services and START your MYSQL service.

Upvotes: 0

Dushyanth Sunny

Reputation: 49

I faced this issue after using Vast cleanup. Basically mysql instance starts on PC startup and it is active in background all the time even when not in use.

Mistakenly I've stopped it

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES)

Solution which worked for me:

type in mysql command line client. As soon as you open you will be prompted with password

-> mysql -u root -p -h 127.0.0.1 -P 3306

and finally it works even when you switch back to workbench.

Upvotes: 0

Zaid Pathan

Reputation: 16820

For Mac,

I fixed it by re-entering root password.

System Preferences > MYSQL > Initialize Database > Enter password

Upvotes: 0

Mohamad Alhamid

Reputation: 376

Check out if you are install MySQL server, I've faced this issue and resolve it by this steps:

1- Windows > MySQL installer - community 2- Then click on Add > then select MySQL Server and next. .....

I hope that resolve your issue

Upvotes: 0

Marcos QP

Reputation: 772

I had same problem, but it worked for me.

check if you have mysql installed

If you don't have mysql installed, download from this link: https://dev.mysql.com/downloads/mysql/

follow this instructions to install https://dev.mysql.com/doc/mysql-osx-excerpt/5.7/en/osx-installation-pkg.html
You can test the connection without any problem.

(Sorry for my english, I agree fix me please)

I Hope I've helped. Greetings.

Upvotes: 2

Eric Korolev

Reputation: 743

Access the database as a user administrator (root user maybe). Check the right one user privileges to the database.

mysql >SHOW GRANTS FOR <username>;

Assign full user privileges to the database from localhost

mysql> GRANT ALL PRIVILEGES ON <databasename>.* TO '<username>'@'localhost' IDENTIFIED BY '<password>';
mysql> FLUSH PRIVILEGES;

in case of error in connecting to the database from third-party programs, for example MySQL WORKBENCH, assign privileges to the user to the database from any host MYSQL Grant all privileges to database from any host

mysql> GRANT ALL PRIVILEGES ON <databasename>.* TO '<username>'@'%' IDENTIFIED BY '<password>';
mysql> FLUSH PRIVILEGES;

Upvotes: 0

Kai

Reputation: 1769

On mac the answers above unfortunately didn't work causing a ERROR 1045 (28000): Access denied for user 'root'@'localhost'

Doing the following worked for me:

sudo su
mysql -uroot
set password = password("password");

Took me embarassingly long to figure out hope it helps someone else.

Upvotes: 0

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user &#39;root&#39;@&#39;localhost&#39;(using password:YES)

Answers (30)

Related Questions

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES)