Cannot connect to Database server (mysql workbench)

Question

Could you help me solve this problem ?

When I try to click "query database" under database menu in Mysql workbench. it gives me an error:

Cannot Connect to Database Server

Your connection attempt failed for user 'root' from your host to server at 127.0.0.1:3306:Can't connect to mysql server on '127.0.0.1'(10061)

Please:

Check that mysql is running on server 127.0.0.1

Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)

Check the root has rights to connect to 127.0.0.1 from your address (mysql rights define what clients can connect to the server and from which machines)

Make sure you are both providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you're connecting from

related http://dba.stackexchange.com/questions/44485/i-cant-get-mysql56-service-to-start-after-i-stopped-it — Adriano, Sep 05 '13 at 09:56
This answer worked for me. http://stackoverflow.com/questions/16129399/mysql-workbench-cannot-connect-to-database-server — mcaleaa, Nov 08 '13 at 10:55
In my case simple restart help - but I firstly check @sergio suggestion - https://stackoverflow.com/a/7875732/6705161. — dannydedog, Mar 02 '18 at 16:18
On windows, make sure you log on as a local system rather than as a network service. FULL INSTRUCTIONS HERE: https://stackoverflow.com/a/73968917/6495588 — iamgr007, Oct 06 '22 at 05:17

score 138 · Answer 1 · answered Oct 27 '16 at 18:08

138

The issue is likely due to socket authentication being enabled for the root user by default when no password is set, during the upgrade to ubuntu 16.04.

The solution is to revert back to native password authentication. You can do this by logging in to MySQL using socket authentication by doing:

sudo mysql -u root

Once logged in:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

which will revert back to the native (old default) password authentication.

Now use password as the password whenever required by MySQL.

answered Oct 27 '16 at 18:08

Ayan

8,192
4
46
51

3

It worked for me, I'm surprised, I've already configured the strict password while installing the mysql and same strict password used with given alter query. I'm curios to know why it didnt worked in first attempt itself – Vipul Patil Sep 27 '19 at 13:51
1

Thanks a lot! Is this behavior only created for the benefit of the unix root user doing the login to mysql without enter the password? – Igor de Lorenzi Mar 01 '20 at 03:13
1

How to do this on Windows? After I set a password I am getting the same problem. Weird that MySQL get's an error after setting the password, it is like they don't want anyone to use their DB framework. – Franco Mar 09 '22 at 19:58
3

If you are using `snap` to install mysql workbench, try this: `sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service ` to allow sandboxed package to access password service. https://askubuntu.com/a/1242777/1621549 – Rohim Chou Aug 21 '22 at 04:18
I tried using this command on MariaDB 10.1.48 and got the error "You have an error in your SQL syntax;" :/ – Raul Chiarella Aug 26 '22 at 17:17

score 65 · Answer 2 · edited Dec 05 '19 at 08:47

65

Try opening services.msc from the start menu search box and try manually starting the MySQL service or directly write services.msc in Run box

edited Dec 05 '19 at 08:47

Nico Griffioen

5,143
2
27
36

answered Nov 01 '12 at 01:51

Colin

719
6
3

1

Did not work for me. running directly from the command line ´C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe --console´ (notice the "d" between "mysql" and ".exe" did work though (when opening the command line "as administrator"). So I supose finding a way to start the service "as administrator" would solve this. – Adriano Sep 05 '13 at 10:45
7

Where is the start menu search box? – Everyone_Else Jul 28 '16 at 16:06
My solution was run Mysql-Workbench with sudo: `sudo mysql-workbench` . Didn't know where to find services.msc – Luigi Lopez Nov 06 '17 at 19:48

score 38 · Answer 3 · edited Jun 20 '20 at 09:12

38

It looks like there are a lot of causes of this error.

My Cause / Solution

In my case, the cause was that my server was configured to only accept connections from localhost. I fixed it by following this article: How Do I Enable Remote Access To MySQL Database Server?. My my.cnf file had no skip-networking line, so I just changed the line

bind-address = 127.0.0.1

to

bind-address = 0.0.0.0

This allows connections from any IP, not just 127.0.0.1.

Then, I created a MySql user that could connect from my client machine by running the following terminal commands:

# mysql -u root -p
mysql> CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'password';
    -> GRANT ALL PRIVILEGES ON *.* TO 'username'@'1.2.3.4' WITH GRANT OPTION;
    -> \q

where 1.2.3.4 is the IP of the client you are trying to connect from. If you really have trouble, you can use '%' instead of '1.2.3.4' to allow the user to connect from any IP.

Other Causes

For a fairly extensive list, see Causes of Access-Denied Errors.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 06 '14 at 15:38

AJ Richardson

6,610
1
49
59

AFAIK, the creation of the user is necessary because the user root should only be used for admin work and sensibly that should be limited to localhost. what user you create beyond that is up to you! – user3791372 Apr 10 '17 at 19:00
Link to Causes of Access-Denied Errors is now broken. – Matt Coubrough May 09 '18 at 00:46
1

i was facing similar issue ,what worked for me is mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'Password123#@!'; -> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION; -> \q i didn't had to do binding thing, my password was set to Medium, you can check using "SHOW VARIABLES LIKE 'validate_password%';" Note: this is my dev ubuntu setup – souradeep majumdar Aug 02 '21 at 18:55

score 22 · Answer 4 · answered Oct 24 '11 at 12:38

22

Did you try to determine if this is a problem with Workbench or a general connection problem? Try this:

Open a terminal
Type mysql -u root -p -h 127.0.0.1 -P 3306
If you can connect successfully you will see a mysql prompt after you type your password (type quit and Enter there to exit).

Report back how this worked.

answered Oct 24 '11 at 12:38

Sergio

4,537
4
33
41

Hi, It says "mysql is not recognized as an internal or external command, operable program or batch file." Thank you so much. – user948950 Oct 24 '11 at 14:21
1

You might want to add [the mysql bin directory](http://dev.mysql.com/doc/refman/5.5/en/windows-installation-layout.html) (the `bin` directory within the main MySQL directory) to [the system path](http://geekswithblogs.net/renso/archive/2009/10/21/how-to-set-the-windows-path-in-windows-7.aspx). Then the mysql client program will be available from anywhere. Or you can just run that command after cd-ing to the MySQL bin directory. Both should work. – Sergio Oct 24 '11 at 21:52
2

When i connecting to mysql with this command $> mysql -u root -p -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@localhost ~]# mysql -u root -p -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@localhost ~]# mysql -u admin -p -h 127.0.0.1 -P 3306 Enter password: ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) [root@localhost ~]# – Sumit Munot Nov 29 '13 at 13:13
Worked for me!! Thx a lot! – igor Nov 23 '20 at 15:10

score 20 · Answer 5 · answered Jun 21 '18 at 01:43

I had a similar issue on Mac OS and I was able to fix it this way:

From the terminal, run:

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

Then, I was asked to enter the password. I just pressed enter since no password was setup.

I got a message as follows:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 181. Server version: 8.0.11 Homebrew.

If you succeeded to log into mysql>, run the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

You should get a message like this:

Query OK, 0 rows affected (0.19 sec)

Now, your password is "password" and your username is "root".

Happy coding :)

This will probably no longer work with newer installations as anonymous log ins are disabled. — en_lorithai, Mar 24 '20 at 20:13

score 13 · Answer 6 · edited Oct 02 '19 at 16:53

13

Run the ALTER USER command. Be sure to change password to a strong password of your choosing.

sudo mysql # Login to mysql`

Run the below command

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Now you can access it by using the new password.

Ref : https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04

edited Oct 02 '19 at 16:53

Giacomo1968

25,759
11
71
103

answered Sep 05 '18 at 10:25

Athira Ramachandran

643
8
11

score 7 · Answer 7 · edited Sep 15 '16 at 06:31

The error occur because the mysql server is not starting on your computer. You should start it manually. Do following steps:

Download and install wamp server according to your bit version(32bit or 64bit) in your computer(http://wampserver-64bit.en.softonic.com/) this link allows you to download wamp server for 64bit.
As soon as you install it you can double click and run it..(you can see a icon in the right hand of the taskbar.It may be hidden .so you can click the arrow which show you the hide apps runing).So click the icon and go to Mysql
Then go to Service and there you can find Start/Resume Services click on it..
And now it is done.Open mysql workbench and see.It will work..

score 6 · Answer 8 · answered Dec 23 '15 at 19:48

I had to start Workbench as Administrator. Apparently it didn't have the required permissions to connect to my localhost database server process.

Right-click the Workbench shortcut and select Run as Administrator. In the shortcut's Properties window, you can click on "Advanced" and tick the box next to "Run as Administrator" to always run the Workbench with Admin privileges.

score 6 · Answer 9 · answered Jul 12 '21 at 06:34

6

I had same problem with Workbench on Ubuntu, problem was with permission.

Find Workbench app
Click button Permissions
Give all permission for this app

answered Jul 12 '21 at 06:34

Dario Vranjković

71
2
6

Perfect!. If the server runs and all you get is the error msg, then this might be the issue – Abdifatah Mohamed Nov 08 '22 at 18:06

score 5 · Answer 10 · edited Oct 23 '22 at 21:32

5

Even I faced a similar error when I opened MySQL Workbench. The solution that worked for me was:

Click on the Start button on a Windows machine
Type services and press Enter
Find MySQL and click on it
On the top side of the left panel, you will find an option as Start the service
Click on Start which is visible as a hyperlink

edited Oct 23 '22 at 21:32

Peter Mortensen

30,738
21
105
131

answered Mar 28 '21 at 14:20

Jay Shah

161
1
2

(Sorry. Edit blunder for revision 3 (pasting into the wrong answer...). Less likely is a system glitch.) – Peter Mortensen Oct 23 '22 at 21:33

score 4 · Answer 11 · answered May 08 '13 at 14:29

I struggled with this problem for awhile and did several reinstalls of MySQL before discovering this.

I know that MySQL server was running OK because I could access all my DB's using the command line.

Hope this works for you.

In MySQL Workbench (5.2.47 CE)

click Mange Server Instances (bottom right corner)

click Connection

in the Connection box select:

Local Instance ($ServerName) - root@127.0.0.1:3306 '<'Standard(TCP/IP)>

click Edit Selected...

under Parameters, Hostname change localhost or 127.0.0.1 to your NetBIOS name

click Test Connection

If this works for you, great. If not change the hostname back to what it was.

THIS worked for me. NetBIOS name is the computer's name, you can read it from the system properties, 'Computer name' tab, 'full computer name'. — NeonMan, Nov 01 '13 at 01:13

score 3 · Answer 12 · edited Oct 02 '19 at 16:52

3

Forr me reason was that I tried to use newest MySQL Workbench 8.x to connect to MySQL Server 5.1 (both running on Windows Server 2012).

When I uninstalled MySQL Workbench 8.x and installed MySQL Workbench 6.3.10 it successfully connected to localhost database

edited Oct 02 '19 at 16:52

Giacomo1968

25,759
11
71
103

answered Dec 14 '18 at 08:27

alex

187
1
9

score 3 · Answer 13 · answered Mar 02 '20 at 06:56

3

For those who ignored that the initial error message displaying is the following one:

The name org.freedesktop.secrets was not provided by any .service files

Make sure to install gnome-keyring using the following

sudo apt install gnome-keyring

answered Mar 02 '20 at 06:56

Bruno Kos

645
2
8
18

score 3 · Answer 14 · edited Oct 23 '22 at 21:30

The problem is that MySQL server is not installed.

You can get the installer from here.

Then watch this 6-minute installation tutorial.

If then creating a new connection in MySQL Workbench is not working, make sure you run that connection as root as show below:

If you don't find your .ini file, check this answer (also written below).

Enter "services.msc" on the Start menu search box.

Find MySQL service under Name column, for example, MySQL56.

Right click on MySQL service, and select Properties menu.

Look for "Path To Executable" under General tab, and there is your .ini file, for instance, "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56

Akash · Answer 15 · 2021-08-22T17:42:46.347

3

I tried these steps -

Step 1 : Go to Ubuntu Software Center

Step 2 : Searched for MySql Workbench

Step 3 : And Click on Permissions

Step 4 : Enable Read, add, change or remove saved passwords

Then, enter Login Password

And then, I found that my problem solved

edited Aug 22 '21 at 17:42

answered Aug 22 '21 at 17:25

Akash

139
5

score 1 · Answer 16 · answered Sep 01 '15 at 21:35

I was in similar situations before and last time I found it was some Windows update issue(not sure). This time, I opened MySQL workbench and found no connection to my local database. I cannot see my tables, but yesterday I could connect to the database.

I found that my cause is that, after letting my computer sleeping for some time and wake it again, the mysql service is not running. My solution: restart the service named "mysql" and rerun the workbench. Restarting the service takes a while, but it works.

AbbasAli Hashemian · Answer 17 · 2016-01-20T09:20:22.427

To be up to date for upper versions and later visitors :

Currently I'm working on a win7 64bit having different tools on it including python 2.7.4 as a prerequisite for google android ...

When I upgraded from WB 6.0.8-win32 to upper versions to have 64bit performance I had some problems for example on 6.3.5-winx64 I had a bug in the details view of tables (disordered view) caused me to downgrade to 6.2.5-winx64.

As a GUI user, easy forward/backward engineering and db server relative items were working well but when we try to Database>Connect to Database we will have Not connected and will have python error if we try to execute a query however the DB server service is absolutely ran and is working well and this problem is not from the server and is from workbench. To resolve it we must use Query>Reconnect to Server to choose the DB connection explicitly and then almost everything looks good (this may be due to my multiple db connections and I couldn't find some solution to define the default db connection in workbench).

As a note : because I'm using latest Xampp version (even in linux addictively :) ), recently Xampp uses mariadb 10 instead of mysql 5.x causes the mysql file version to be 10 may cause some problems such as forward engineering of procedures which can be resolved via mysql_upgrade.exe but still when we try to check a db connection wb will inform about the wrong version however it is not critical and works well.

Conclusion : Thus sometimes db connection problems in workbench may be due to itself and not server (if you don't have other db connection relative problems).

score 1 · Answer 18 · edited Oct 02 '19 at 16:53

1

In my case I have just installed MySQL Workbench but after uninstalling MySQL Workbench and installing MySQL installer and is same for both 32 and 64 bit then after it working like a charm. Hope it could be useful.

edited Oct 02 '19 at 16:53

Giacomo1968

25,759
11
71
103

answered Apr 10 '18 at 17:55

Elias

664
2
11
23

score 1 · Answer 19 · edited Oct 23 '22 at 21:00

1

I just use:

sudo snap connect mysql-workbench-community:ssh-keys

sudo snap connect mysql-workbench-community:password-manager-service

edited Oct 23 '22 at 21:00

Peter Mortensen

30,738
21
105
131

answered Aug 06 '22 at 23:43

Cleyton Alves

11
1

score 0 · Answer 20 · answered Sep 05 '13 at 14:27

I also struggled with this problem for quite a while.

I came accross this interesting thread from MySQL forum: http://forums.mysql.com/read.php?11,11388,11388#msg-11388

I also came accross (obviously) some good SO Q/A.

It seems that the message mentioned in "user948950" 's question can be coming from a wide range of reasons: log file too big, incorrect mysql.ini file values, spaces in the file path, security/acl issue, old entries in the registry, and so on.

So, after trying for 3h to fix this... I abandonned and decided to do a good old re-install.

This is where this post from (again) this MySQL thread came in useful, I quote:

Gary Williams wrote: Hi Guys,

I've had exactly the same problem and this is how I got it working for me, starting with a non working installation.

Stop the windows service for any existing mysql installation.

Uninstall Mysql.

As with most uninstalls, old files are left behind. If your directory is C:\mysql\ etc then delete the innob, etc, files but leave the directories themselves as well as any existing databases in 'data'. If your directory is C:\Program Files\ etc, delete all the mysql directories.

It's now worth running regedit to make sure the old registry entries are deleted as well by the uninstall. If not, delete them.

It's ok to use the new .msi installer (essential files only), however ....

Do not use their default install path! Some genius set a path with spaces in it! Choose the custom install and select a sensible path, ie, C:\mysql (note from Adrien: C:\mysqldata for ... the data)

Do not choose to alter the security settings. Uncheck the relevant box and the install will complete without having to set a root password.

I think I have remembered everything.

Good luck

Gary

I did get into troubles when simply copy/pasting the databases I had in my previous "data" directory to the new one. So the work around I found was to export each database (I know... a lot of fun) and then re-import them one by one.

FYI: I used the following command to import C:/<MySQLInstallDir>/My SQL Server x.x/bin/mysql -u root -p <dbName> < "<dirPathOfDump>\<dumpName>.sql", that is for instance C:/mysql/MySQL Server 5.6/bin/mysql -u root -p mySupaCoolDb < "C:\mySupaCoolDbDump20130901.sql"

score 0 · Answer 21 · answered Nov 03 '16 at 07:09

0

My problem was that the MySQL server wasn't actually installed. I had run the MySQL Installer, but it didn't install the MySQL server.

I reran the installer, click "Add", and then added MySQL server to the list. Now it works fine.

answered Nov 03 '16 at 07:09

Pikamander2

7,332
3
48
69

score 0 · Answer 22 · answered Jul 14 '22 at 03:35

0

2022 and beyond this works for me:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

change

bind-address            = 127.0.0.1

to

bind-address            = 0.0.0.0

Then remember to restart mysql sudo systemctl restart mysql

answered Jul 14 '22 at 03:35

Michael Nelles

5,426
8
41
57

This presumes some kind of Linux system. Can you [add](https://stackoverflow.com/posts/72974902/edit) information about it to your answer (distribution, version, [systemdness](https://en.wikipedia.org/wiki/Systemd), etc.)? But ********* ***without*** ********* "Edit:", "Update:", or similar - the answer should appear as if it was written today. – Peter Mortensen Oct 23 '22 at 21:02

score 0 · Answer 23 · edited Oct 23 '22 at 20:48

0

Initially I was confused, but I was able to solve the problem.

If your workbench was downloaded through the Snap package manager you could easily use this command to solve the problem, very simple:

sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service

edited Oct 23 '22 at 20:48

Peter Mortensen

30,738
21
105
131

answered Aug 25 '22 at 01:42

Gabriel soft

432
3
7

This presumes some kind of Linux system. Can you [add](https://stackoverflow.com/posts/73481045/edit) information about it to your answer (distribution, version, etc.)? But ********* ***without*** ********* "Edit:", "Update:", or similar - the answer should appear as if it was written today. – Peter Mortensen Oct 23 '22 at 20:52

score 0 · Answer 24 · edited Oct 23 '22 at 20:53

0

Go to services.msc and find MySQL.

If "Log on As" section is 'Network Service', then:

Double click on the MySQL row to open a pop-up
go to "Log on" tab
Select Log on as: "Local System account" and check "Allow service to interact with desktop"

Voilà, now you can start your service on localhost and make sure you enter correct root/user password on your MySQL Workbench, and you are good to go.

edited Oct 23 '22 at 20:53

Peter Mortensen

30,738
21
105
131

answered Oct 06 '22 at 05:15

iamgr007

966
1
8
28

This seems to presume some kind of Windows system. Can you [add](https://stackoverflow.com/posts/73968917/edit) information about it to your answer (edition, Windows Server/desktop, version, etc.)? But ********* ***without*** ********* "Edit:", "Update:", or similar - the answer should appear as if it was written today. – Peter Mortensen Oct 23 '22 at 20:55

score 0 · Answer 25 · answered Dec 23 '22 at 11:49

If you are using snap to install mysql workbench, try this:

sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service  to allow sandboxed package to access password service

askubuntu.com/a/1242777/1621549

This Solution was made as a comment before by Rohim Chou

score 0 · Answer 26 · answered Mar 31 '23 at 05:37

go to services find mySql (from start)

start the service once the server starts you can start working in sql workbench

if you don't find my sql in services than follow the below steps open cmd(run as administrator otherwise it won't work) go to the following path (write this command)

C:\Program Files\MySQL\MySQL Server 8.0\bin(check the path from your local)

dir mysqld.exe /s /p(this will find the path of mysqld) (if you dont have mysqld install it) mysqld.exe --install mysqld.exe --initialize

this will do the work now go to sdervices and start the server

score 0 · Answer 27 · edited May 06 '23 at 01:49

0

I Faced this issue for MAC recently and here is the solution:

Search MySql globally on your MAC in System Settings and open it.
Choose the Active instance and Stop MySql Server.
Initialize Database and configure a new password and save it.
Now relaunch your Workbench and test the connection with the new password and it will work.

edited May 06 '23 at 01:49

user16217248

3,119
19
19
37

answered May 02 '23 at 08:55

Ravi Sourav

1

Cannot connect to Database server (mysql workbench)

27 Answers27

My Cause / Solution

Other Causes

The problem is that MySQL server is not installed.

Linked