B.3.2.2 Can’t connect to [local] MySQL server

Repository owner

locked and limited conversation to collaborators

Aug 30, 2015

Я тоже долгое время боролся с этой проблемой.

Я прошел через этот интересный поток из форума MySQL: http://forums.mysql.com/read.php?11,11388,11388#msg-11388

Я также набрал (очевидно) некоторый хороший SO Q/A.

Здесь этот пост из (снова) этот поток MySQL пришел в полезное, я цитирую:

Гэри Уильямс писал (а): Привет, ребята,

У меня была точно такая же проблема, и вот как я ее работаю для меня, начиная с нерабочей установки.

• Остановите службу Windows для любой существующей установки mysql.

Как и при большинстве удалений, старые файлы остаются позади. Если ваш каталог это C:\mysql\etc, то удалите файлы innob и т.д., но оставьте сами каталоги, а также любые существующие базы данных в «данных». Если ваш каталог — C:\Program Files\etc, удалите все mysql каталоги.

• Теперь стоит запустить regedit, чтобы удалить старые записи реестра, а также удалить. Если нет, удалите их.

• Не следует изменять параметры безопасности. Снимите флажок в соответствующем поле, и установка завершится без установки root пароль.

Думаю, я все вспомнил.

FYI: я использовал следующую команду для импорта C:/<MySQLInstallDir>/My SQL Server x.x/bin/mysql -u root -p <dbName> < "<dirPathOfDump>\<dumpName>.sql", например C:/mysql/MySQL Server 5.6/bin/mysql -u root -p mySupaCoolDb < "C:\mySupaCoolDbDump20130901.sql"

The hard data first:
Local machine: Xubuntu 17.10, with MySql-Workbench 6.3
Remote Machine: Ubuntu 16.04, with MySql-Client 5.7 and MySql-Server 5.7

The connection method is: Standard TCP/IP over SSH

Then it gives me a list with things to check.

1 Check that mysql is running on server 127.0.0.1

2 Check that mysql is running on port 3306

3 Check the root has rights to connect to 127.0.0.1 from your address

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

There is no bind-address or skip-networking directive set.

4 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

Password and stuff is all fine. Passwords are set, they are typed correctly when logging in.

When I do, what Workbench is supposed to do

• ssh to the remote machine (ssh user@remotemachine)
• login to mysql server (mysql -u root -p)

It works flawlessly
I just can’t do via MySql-Workbench

Help is much appreciated because I already tried everything I was able to research.

Topic solved
This topic has been marked as solved and requires no further attention.

Could not open required defaults file: /etc/mysql/my.cnf
Fatal error in defaults handling. Program aborted
 * Checking mysqld configuration for mysql ...
Could not open required defaults file: /etc/mysql/my.cnf
Fatal error in defaults handling. Program aborted
 * mysql config check failed                                                        [ !! ]
 * ERROR: mysql failed to start

B.3.2.2 Can’t connect to [local] MySQL server

A MySQL client on Unix can connect to the
server in two different ways: By
using a Unix socket file to connect through a file in the file
system (default /tmp/mysql.sock), or by
using TCP/IP, which connects through a port number. A Unix
socket file connection is faster than TCP/IP, but can be used
only when connecting to a server on the same computer. A Unix
socket file is used if you do not specify a host name or if
you specify the special host name
localhost.

If the MySQL server is running on Windows, you can connect
using TCP/IP. If the server is started with the
named_pipe system variable
enabled, you can also connect with named pipes if you run the
client on the host where the server is running. The name of
the named pipe is MySQL by default. If you
do not give a host name when connecting to
, a MySQL client first tries to
connect to the named pipe. If that does not work, it connects
to the TCP/IP port. You can force the use of named pipes on
Windows by using . as the host name.

The error (2003) Can't connect to MySQL server on
'server' (10061)
indicates that the network connection has been refused. You
should check that there is a MySQL server running, that it has
network connections enabled, and that the network port you
specified is the one configured on the server.

$> mysqladmin version
$> mysqladmin variables
$> mysqladmin -h `hostname` version variables
$> mysqladmin -h `hostname` --port=3306 version
$> mysqladmin -h host_ip version
$> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

Make sure that the server has not been configured to ignore
network connections or (if you are attempting to connect
remotely) that it has not been configured to listen only
locally on its network interfaces. If the server was started
with the skip_networking
system variable enabled, it cannot accept TCP/IP connections
at all. If the server was started with the
bind_address system variable
set to 127.0.0.1, it listens for TCP/IP
connections only locally on the loopback interface and does
not accept remote connections.

Here are some reasons the Can't connect to local
MySQL server error might occur:

• is not running on the local
  host. Check your operating system’s process list to ensure
  the process is present.

• You’re running a MySQL server on Windows with many TCP/IP
  connections to it. If you’re experiencing that quite often
  your clients get that error, you can find a workaround
  here:
  Section B.3.2.2.1, “Connection to MySQL Server Failing on Windows”.

• You have started the server with
  the
  --socket=/path/to/socket
  option, but forgotten to tell client programs the new name
  of the socket file. If you change the socket path name for
  the server, you must also notify the MySQL clients. You
  can do this by providing the same
  --socket option when you
  run client programs. You also need to ensure that clients
  have permission to access the
  mysql.sock file. To find out where
  the socket file is, you can do:

  $> netstat -ln | grep mysql

  See Section B.3.3.6, “How to Protect or Change the MySQL Unix Socket File”.

• You are using Linux and one server thread has died (dumped
  core). In this case, you must kill the other
  threads (for example, with
  ) before you can restart the MySQL
  server. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.

• The server or client program might not have the proper
  access privileges for the directory that holds the Unix
  socket file or the socket file itself. In this case, you
  must either change the access privileges for the directory
  or socket file so that the server and clients can access
  them, or restart with a
  --socket option that
  specifies a socket file name in a directory where the
  server can create it and where client programs can access
  it.

• Check whether the server is running on that host by
  executing telnet some_host 3306 and
  pressing the Enter key a couple of times. (3306 is the
  default MySQL port number. Change the value if your server
  is listening to a different port.) If there is a MySQL
  server running and listening to the port, you should get a
  response that includes the server’s version number. If you
  get an error such as telnet: Unable to connect to
remote host: Connection refused, then there is
  no server running on the given port.

• If you are running under Linux and Security-Enhanced Linux
  (SELinux) is enabled, see Section 6.7, “SELinux”.

B.3.2.2.1 Connection to MySQL Server Failing on Windows

When you’re running a MySQL server on Windows with many
TCP/IP connections to it, and you’re experiencing that quite
often your clients get a Can't connect to MySQL
server error, the reason might be that Windows
does not allow for enough ephemeral (short-lived) ports to
serve those connections.

The purpose of TIME_WAIT is to keep a
connection accepting packets even after the connection has
been closed. This is because Internet routing can cause a
packet to take a slow route to its destination and it may
arrive after both sides have agreed to close. If the port is
in use for a new connection, that packet from the old
connection could break the protocol or compromise personal
information from the original connection. The
TIME_WAIT delay prevents this by ensuring
that the port cannot be reused until after some time has
been permitted for those delayed packets to arrive.

It is safe to reduce TIME_WAIT greatly on
LAN connections because there is little chance of packets
arriving at very long delays, as they could through the
Internet with its comparatively large distances and
latencies.

• Windows through Server 2003: Ports in range
  1025–5000

• Windows Vista, Server 2008, and newer: Ports in range
  49152–65535

With a small stack of available TCP ports (5000) and a high
number of TCP ports being open and closed over a short
period of time along with the TIME_WAIT
status you have a good chance for running out of ports.
There are two ways to address this problem:

• Reduce the number of TCP ports consumed quickly by
  investigating connection pooling or persistent
  connections where possible

• Tune some settings in the Windows registry (see below)

1. Start Registry Editor
   (Regedt32.exe).

2. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

3. Value Name: MaxUserPort
   Data Type: REG_DWORD
   Value: 65534

4. Value Name: TcpTimedWaitDelay
   Data Type: REG_DWORD
   Value: 30

   This sets the number of seconds to hold a TCP port
   connection in TIME_WAIT state before
   closing. The valid range is between 30 and 300 decimal,
   although you may wish to check with Microsoft for the
   latest permitted values. The default value is 0x78 (120
   decimal).

5. Quit Registry Editor.

6. Reboot the machine.

Note: Undoing the above should be as simple as deleting the
registry entries you’ve created.

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: 1. Check that mysql is running on server 127.0.0.1 2. Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed) 3. 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) 4. 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

sudo mysql -u root

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

bind-address = 127.0.0.1

bind-address = 0.0.0.0

# 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

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

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

• View
• Add Comment
• Files
• Developer
• Edit Submission
• View Progress Log
• Contributions

Description:----[For better reports, please attach the log file after submitting. You can find it in C:\Users\123\AppData\Roaming\MySQL\Workbench\log\wb.log]
I receibed this message in first page of the workbench:
Cannot Connect to Database Server
Your connection attempt failed for user 'root' to the MySQL server at localhost:3306:
SSL connection error: Failed to set ciphers to useHow to repeat:Just start the workbench application and click on MySQL Connections Local Instance MySQL80
wb.log
17:43:05 [ERR][ Workbench]: Console redirection failed.
17:43:05 [INF][ Workbench]: Starting up Workbench
17:43:05 [INF][ Workbench]: Current environment:	Command line: "C:\Program Files\MySQL\MySQL Workbench 8.0\MySQLWorkbench.exe"	CurrentDirectory: C:\Program Files\MySQL\MySQL Workbench 8.0	HasShutdownStarted: False	OSVersion: Microsoft Windows NT 6.2.9200.0	SystemDirectory: C:\WINDOWS\system32	TickCount: 65277343	UserInteractive: True	Version: 4.0.30319.42000	WorkingSet: 51257344
17:43:05 [INF][ Workbench]: Environment variables:	COMPUTERNAME = XPS8700	USERPROFILE = C:\Users\123	HOMEPATH = \Users\123	LOCALAPPDATA = C:\Users\123\AppData\Local	PSModulePath = C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules	PROCESSOR_ARCHITECTURE = AMD64	CommonProgramW6432 = C:\Program Files\Common Files	CommonProgramFiles(x86) = C:\Program Files (x86)\Common Files	ProgramFiles(x86) = C:\Program Files (x86)	PROCESSOR_LEVEL = 6	LOGONSERVER = \\XPS8700	PATHEXT = .COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC	HOMEDRIVE = C:	SystemRoot = C:\WINDOWS	SESSIONNAME = Console	ALLUSERSPROFILE = C:\ProgramData	DriverData = C:\Windows\System32\Drivers\DriverData	APPDATA = C:\Users\123\AppData\Roaming	Path = C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files\dotnet\;C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\;C:\Program Files\MySQL\MySQL Server 8.0\bin;C:\Program Files\MySQL\MySQL Shell 8.0\bin\;C:\Users\123\AppData\Local\Programs\Python\Python39\Scripts\;C:\Users\123\AppData\Local\Programs\Python\Python39\;C:\Users\123\AppData\Local\Microsoft\WindowsApps;C:\Users\123\.dotnet\tools	USERNAME = 123	OneDrive = C:\Users\123\OneDrive	CommonProgramFiles = C:\Program Files\Common Files	OS = Windows_NT	USERDOMAIN_ROAMINGPROFILE = XPS8700	PROCESSOR_IDENTIFIER = Intel64 Family 6 Model 60 Stepping 3, GenuineIntel	OneDriveConsumer = C:\Users\123\OneDrive	USERDOMAIN = XPS8700	SystemDrive = C:	TEMP = C:\Users\123\AppData\Local\Temp	ProgramFiles = C:\Program Files	NUMBER_OF_PROCESSORS = 8	ComSpec = C:\WINDOWS\system32\cmd.exe	TMP = C:\Users\123\AppData\Local\Temp	ProgramData = C:\ProgramData	ProgramW6432 = C:\Program Files	windir = C:\WINDOWS	PROCESSOR_REVISION = 3c03	PUBLIC = C:\Users\Public
17:43:05 [INF][ Workbench]: Current version given by meta info is: 8.0.25
17:43:05 [INF][ Workbench]: Setting PATH to: C:\WINDOWS\system32;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files\dotnet\;C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\;C:\Program Files\MySQL\MySQL Server 8.0\bin;C:\Program Files\MySQL\MySQL Shell 8.0\bin\;C:\Users\123\AppData\Local\Microsoft\WindowsApps;C:\Users\123\.dotnet\tools
17:43:05 [INF][ mforms managed]: Initializing mforms wrapper
17:43:06 [INF][ WBContext UI]: Initializing workbench context UI with these values:	base dir: C:\Program Files\MySQL\MySQL Workbench 8.0	plugin path: C:\Program Files\MySQL\MySQL Workbench 8.0	struct path:	module path: C:\Program Files\MySQL\MySQL Workbench 8.0/modules	library path: C:\Program Files\MySQL\MySQL Workbench 8.0	user data dir: C:\Users\123\AppData\Roaming\MySQL\Workbench	open at start:	open type:	run at startup:	run type:	Force SW rendering: No	Force OpenGL: No	quit when done: No
17:43:06 [INF][ WBContext]: WbContext::init
17:43:07 [INF][ WBA]: Looking for extension modules for WBA...
17:43:07 [INF][ WBA]: 0 extension modules found
17:43:07 [WRN][ grt]: Duplicate plugin name wb.tools.cmdlineClient	There is more than one plugin with the name wb.tools.cmdlineClient (in PyWbUtils and PyWbUtils).
17:43:07 [WRN][ grt]: C:\Users\123\AppData\Roaming\MySQL\Workbench\connections.xml:32: link '{10DD9CF4-DD79-4D2D-8172-C057661877A0}' <object GrtObject> key=owner could not be resolved
17:43:07 [WRN][ grt]: Duplicate plugin name wb.tools.cmdlineClient	There is more than one plugin with the name wb.tools.cmdlineClient (in PyWbUtils and PyWbUtils).
17:43:07 [INF][ WBContext]: System info:	MySQL Workbench Community (GPL) for Windows version 8.0.25 CE build 788958 (64 bit)	Configuration Directory: C:\Users\123\AppData\Roaming\MySQL\Workbench	Data Directory: C:\Program Files\MySQL\MySQL Workbench 8.0	Cairo Version: 1.17.4	OS: Microsoft Windows 10 Home Single Language	CPU: 8x Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz, 12.0 GiB RAM	Active video adapter NVIDIA GeForce GTX 650 Ti	Installed video RAM: 1024 MB	Current video mode: 1824 x 1026 x 4294967296 colores	Used bit depth: 32	Driver version: 27.21.14.6109	Installed display drivers: C:\WINDOWS\System32\DriverStore\FileRepository\nv_dispi.inf_amd64_3621da861144492b\nvldumdx.dll,C:\WINDOWS\System32\DriverStore\FileRepository\nv_dispi.inf_amd64_3621da861144492b\nvldumdx.dll,C:\WINDOWS\System32\DriverStore\FileRepository\nv_dispi.inf_amd64_3621da861144492b\nvldumdx.dll,C:\WINDOWS\System32\DriverStore\FileRepository\nv_dispi.inf_amd64_3621da861144492b\nvldumdx.dll	Current user language: Español (México)
17:43:07 [INF][ Workbench]: UI is up
17:43:08 [INF][ Workbench]: Running the application
17:43:20 [INF][ WBContext UI]: Opening Migration Wizard...
17:43:20 [WRN][ mforms]: Resource file not found: migration_win.png
17:43:20 [WRN][ grt]: C:\Program Files\MySQL\MySQL Workbench 8.0/modules/data\msaccess_rdbms_info.xml:8: link 'com.mysql.rdbms.msaccess.driver.odbc' <object > key=defaultDriver could not be resolved
17:43:20 [WRN][ grt]: in com.mysql.rdbms.sql92.driver.odbc_dsn: unserialized XML contains invalid member db.mgmt.PythonDBAPIDriver::accessibilityNameLoaded 0/0 new non-MySQL connections
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:21 [WRN][ mforms]: Resource file not found: migration_check_open_win.png
17:43:48 [INF][ WBContext UI]: Opening Migration Wizard...
17:44:02 [ERR][SQL Editor Form]: SqlEditorForm: exception in do_connect method: Exception: SSL connection error: Failed to set ciphers to use
17:44:02 [ERR][ GRTDispatcher]: exception in grt execute_task, continuing: Exception: SSL connection error: Failed to set ciphers to use
17:44:02 [ERR][ GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.SSL connection error: Failed to set ciphers to use
17:44:02 [ERR][ WQE backend]: Got an exception during connection: SSL connection error: Failed to set ciphers to use
17:44:02 [ERR][SQL Editor Form]: SQL editor could not be connected: SSL connection error: Failed to set ciphers to use
17:44:02 [ERR][SQL Editor Form]: Your connection attempt failed for user 'root' to the MySQL server at localhost:3306: SSL connection error: Failed to set ciphers to use
Please:
1 Check that MySQL is running on address localhost
2 Check that MySQL is reachable on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the user root has rights to connect to localhost from your address (MySQL rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for localhost connecting from the host address you're connecting from
17:44:47 [ERR][SQL Editor Form]: SqlEditorForm: exception in do_connect method: Exception: SSL connection error: Failed to set ciphers to use
17:44:47 [ERR][ GRTDispatcher]: exception in grt execute_task, continuing: Exception: SSL connection error: Failed to set ciphers to use
17:44:47 [ERR][ GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.SSL connection error: Failed to set ciphers to use
17:44:47 [ERR][SQL Editor Form]: SQL editor could not be connected: SSL connection error: Failed to set ciphers to use
17:44:47 [ERR][SQL Editor Form]: Your connection attempt failed for user 'root' to the MySQL server at localhost:3306: SSL connection error: Failed to set ciphers to use
Please:
1 Check that MySQL is running on address localhost
2 Check that MySQL is reachable on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the user root has rights to connect to localhost from your address (MySQL rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for localhost connecting from the host address you're connecting from
17:45:03 [ERR][SQL Editor Form]: SqlEditorForm: exception in do_connect method: Exception: SSL connection error: Failed to set ciphers to use
17:45:03 [ERR][ GRTDispatcher]: exception in grt execute_task, continuing: Exception: SSL connection error: Failed to set ciphers to use
17:45:03 [ERR][ GRTDispatcher]: worker: task 'execute sql queries' has failed with error:.SSL connection error: Failed to set ciphers to use
17:45:04 [ERR][SQL Editor Form]: SQL editor could not be connected: SSL connection error: Failed to set ciphers to use
17:45:04 [ERR][SQL Editor Form]: Your connection attempt failed for user 'root' to the MySQL server at localhost:3306: SSL connection error: Failed to set ciphers to use
Please:
1 Check that MySQL is running on address localhost
2 Check that MySQL is reachable on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the user root has rights to connect to localhost from your address (MySQL rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for localhost connecting from the host address you're connecting from