Failed to Connect to MySQL at localhost:3306 with user root

Question

I use Mysql Workbench to connect my database,[ Hostname Port and Username are as shown in figure ,and password is right[1]

Hostname Port and Username are as shown in figure ,and password is right.When I click Test Connection ,it show as above.But if i use 3307 in place of 3306 as port,it connect sucessfully. What matter lead that and how I fix it?

I use macbook pro and I don't know check which my.cnf. I use sudo vim /usr/local/mysql/my.cnf in terminal .
And the my.cnf is as below:

[mysqld] 
 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

And I rewite it as below:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M 
[mysqldump]
quick

But it does not work.

please check your mysql configuration `my.cnf` the port should be `port=3306` [https://dev.mysql.com/](https://dev.mysql.com/doc/refman/5.1/en/option-files.html) — ichadhr, Aug 15 '15 at 07:47
@ichadhr I use macbook pro and I don't know check which my.cnf. I use sudo vim /usr/local/mysql/my.cnf in terminal . And the my.cnf is as below: [mysqld] sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES And I rewite it as below: [client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock key_buffer_size=16M max_allowed_packet=8M [mysqldump] quick But — shengfu zou, Aug 15 '15 at 08:20
@Sajad You must be kidding. That is the standard port for an HTTP server. This is a database. — user207421, Aug 15 '15 at 08:39
@shengfuzou you can specify the option file you have created by using the `--defaults-file` parameter. e.g `--defaults-file=[cnf path]` [dev.mysql](http://dev.mysql.com/doc/refman/5.1/en/option-file-options.html) — ichadhr, Aug 15 '15 at 09:05
@shengfuzou also make sure no other programs running on same port `netstat -lnp | grep 3306` — ichadhr, Aug 15 '15 at 09:10

score 42 · Answer 1 · answered Oct 10 '17 at 13:09

42

Steps:

1 - Right click on your task bar -->Start Task Manager

2 - Click on Services button (at bottom).

3 - Search for MYSQL57

4 - Right Click on MYSQL57 --> Start

Now again start your mysql-cmd-prompt or MYSQL WorkBench

answered Oct 10 '17 at 13:09

Abhishek Singh

421
4
3

8

mysql is not coming in services. What to do now? – Kunal Tyagi Jan 23 '20 at 15:26
which task bar?.... – MartianMartian Jan 04 '22 at 15:46
@Martian2049 THE Task bar. On Windows you can only have one Task bar. – FireFuro99 Jan 17 '22 at 22:51
1

@FireFuro99 To be fair, there are also various programs with their own task bars in them. So, to take away ambiguity, it probably should have said `Right click on the Windows Task Bar`. – kojow7 May 08 '23 at 15:41

score 7 · Answer 2 · answered Jun 02 '18 at 16:32

7

Open System Preference > MySQL > Initialize Database > Use Legacy Password Encription

answered Jun 02 '18 at 16:32

Hau Le

121
1
5

score 7 · Answer 3 · answered Jun 04 '20 at 17:06

I had this exact problem after my last windows update and none of the above solutions worked. I know it's an old question, but if anyone has this problem in the future:

I managed to solve it by going to windows control panel > administrative tools > Component Services and finding "MySQL57" in the Services(local) list (I assume your server might have a slightly different name).

There I went to properties by right clicking and changed the Startup Type to Automatic under the General tab. Then I went on the Log On tab and checked "Allow Service to interact with Desktop".

If none of that works, you can try checking the "This account" box and filling both password fields with the root password you set up after installing the server (leave account blank).

I know it's a lot to do and most of these steps must be innefective, but I did it all at once and frankly have no clue which one solved the problem. Good luck I hope it helps.

This issue if its not your password. Is most certainly that the MySQL80 services is not running. So, that's the one that solves the issue. ...checkout out your services window and start service for MySQL80 — kings, Jul 04 '23 at 10:01

Darshan Patel · Answer 4 · 2018-11-21T05:23:23.243

6

Try to execute below command in your terminal :

mysql -h localhost -P 3306 -u root -p

If you successfully connect to your database, then same thing has to happen with Mysql Workbench.

If you are unable to connect then I think 3306 port is acquired by another process.

Find which process running on 3306 port. If required, give admin privileges using sudo.

netstat -lnp | grep 3306

Kill/stop that process and restart your MySQL server. You are good to go.

Execute below command to find my.cnf file in macbook.

mysql --help | grep cnf

You can change MySQL port to any available port in your system. But after that, make sure you restart MySQL server.

edited Nov 21 '18 at 05:23

answered Aug 15 '15 at 08:26

Darshan Patel

2,839
2
25
38

I use your command and show as below : ` order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf ` I check the four file ,and all then are empty.Why and what to do for me? – shengfu zou Aug 15 '15 at 08:53
I use mysql -h localhost -P 3306 -u root -p to connect , but it show Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I use mysql --help | grep cnf it show nothing. – shengfu zou Aug 15 '15 at 09:38
then use `netstat` commnad to find out which process is running for port `3306` and also execute `mysql -h localhost -P 3307 -u root -p`. If you got success with 3307 then your MySQL server is running on 3307 port. – Darshan Patel Aug 15 '15 at 09:59
This works well on PC, but the command is : netstat -a -n -o ... on the command line (you may need admin privs) ... and then take the PID from the last column and look it up on the details pane of Task Manager (CTL+SHIFT+ESC) I was able to determine that yes, MySql is on that port ... Sadly, that doesn't mean that I actually solved the problem, but it's a great diagnostic step. ++ – Sean Munson Nov 19 '18 at 16:55
1

This worked for CLI but I still can't connect in workbench. What might my issue be? – Daniel C Jacobs May 04 '20 at 23:08
Check port is open or not if you are using AWS. Another possible configuration you might check similar to this https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql – Darshan Patel May 05 '20 at 09:00

score 3 · Answer 5 · edited Sep 20 '20 at 11:50

3

It worked for me this way:

Step1: Open System Preference > MySQL > Initialize Database.

Step2: Put password you used while installing MySQL.

Step3: Start MySQL server.

Step4: Come back to MySQL Workbench and double connect/ create a new one.

edited Sep 20 '20 at 11:50

iminiki

2,549
12
35
45

answered Sep 19 '20 at 18:43

Ishan Dixit

31
3

score 2 · Answer 6 · answered Aug 29 '18 at 01:40

2

Go to system preferences, then "MySQL". Click on "Start MySQL Server".

answered Aug 29 '18 at 01:40

almawhoob

382
3
5

score 2 · Answer 7 · answered Nov 02 '22 at 15:33

2

On window, work for me.

1 - Open "Service"

2 - Search for MYSQL

3 - Right Click on MYSQL80 --> Start

Hope it work in your MySQL Workbench

Thank you!!!

answered Nov 02 '22 at 15:33

cng.buff

405
4
5

score 1 · Answer 8 · answered Aug 15 '15 at 08:25

MySQL default port is 3306 but it may be unavailable for some reasons, try to restart your machine. Also sesrch for your MySQL configuration file (should be called "my.cnf") and check if the used port is 3306 or 3307, if is 3307 you can change it to 3306 and then reboot your MySQL server.

score 1 · Answer 9 · answered Sep 28 '19 at 08:01

1

Go to >system preferences >mysql >initialize database

-Change password -Click use legacy password -Click start sql server

it should work now

answered Sep 28 '19 at 08:01

Ronald Milligan

11
1

score 1 · Answer 10 · answered Aug 29 '22 at 14:27

I re-downloaded many times and found out it's so simple!

THERE ARE TWO DOWNLOADS NEEDED

1. SQL Community Server https://dev.mysql.com/downloads/mysql/ This is what shows up in "System Preferences" and allows you to start the instance!

2. MYSQL Workbench https://dev.mysql.com/downloads/workbench/ This is what you're trying to fix.. Once the Instance is on you can successfully connect :)

(for macOS system users)

score 0 · Answer 11 · answered Jun 03 '18 at 07:14

0

At rigth side in Navigator -> Instance-> Click on Startup/Shutdown -> Click on Start Server

It will work surely

answered Jun 03 '18 at 07:14

Tobia John

1
2

score 0 · Answer 12 · edited Feb 07 '19 at 23:12

0

go to apple icon on the top left corn and click "System Preference"
find "Mysql" at the bottom and click it
"start Mysql server"

edited Feb 07 '19 at 23:12

Haris Bouchlis

2,366
1
20
35

answered Feb 07 '19 at 19:03

Yan Xu

11
3

score 0 · Answer 13 · answered Jul 27 '19 at 23:58

0

It failed because there is no server install on your computer. You need to Download 'MySQL Community Server 8.0.17' & restart your server.

answered Jul 27 '19 at 23:58

Jackie Santana

1,290
14
15

score 0 · Answer 14 · answered Aug 17 '20 at 15:59

set root user to mysql_native_password

$ sudo mysql -u root -p # I had to use "sudo" since is new installation

mysql:~ USE mysql;
mysql:~ SELECT User, Host, plugin FROM mysql.user;
mysql:~ UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql:~ FLUSH PRIVILEGES;
mysql:~ exit;

$ service mysql restart

score 0 · Answer 15 · answered Nov 14 '21 at 10:28

I got the same problem on MACOS. I did the following. Make sure MySQL Server is installed.

Command + Space -> Type mysql. Open the option mysql.prefPane
Click on the button Stop MySQL Server. Let the server stop.
Click on Initialize Database button
A new Popup will open. Type the password you want for root user.
Once the password is long enough, OK button will get enabled. Click on OK button
Do Test Connection from MySQL WorkBench and enter the password you set in above step. It should work.

score 0 · Answer 16 · answered Jan 11 '22 at 07:55

For those of you getting this on an SQL Server container on docker, when you do the run command, be sure you put -p 3306:3306 so for example, my run command looks like docker run --name test-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=your_password -d mysql

score -1 · Answer 17 · answered Mar 04 '23 at 15:47

-1

in my.ini check option skip-networking under [mysqld]. It should be off.

[mysqld]
skip-networking = OFF

answered Mar 04 '23 at 15:47

MrKirill1232

1

1

see: https://superuser.com/questions/578678/mysql-cant-turn-off-skip-networking. The `= OFF` is not needed, this is default and `skip-networking` is also not needed because default this option is disabled (see: [skip-networking](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_networking) ) – Luuk Mar 04 '23 at 16:02
When I install maria db I turned off this feature and got this problem. When I look in doc - I found this valiarble and after look in my ini and found it with ON value. – MrKirill1232 Mar 04 '23 at 16:44
The linke `skip-networking` does not need not exist, when you need network access to MySQL. There is only a need for adding `skip-networking` when you set it to `ON` (or `1`) – Luuk Mar 04 '23 at 16:51

Failed to Connect to MySQL at localhost:3306 with user root

17 Answers17

Linked