Error2003-CantConnectToMySQLServer
[edit] Error 2003: Can't Connect to MySQL server on 'hostname'
[edit] Nature of problem
This client error appears when no MySQL server responds to a connection attempt. That is, a client attempts to connect to MySQL Server at a specified host and port number, but the connection is not answered at all. This is not an error that comes from a server, since no server was reached. The error is generated by the client (like all MySQL error numbers in the 2000's).
There are many possible causes for this error. The best way to diagnose and correct the error is to test these possibilities and eliminate them one by one. The guide below describes tests and remedies for some of the potential causes.
Also see the page of the MySQL manual that pertain to this error: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html
[edit] Troubleshooting Causes
[edit] MySQL Server is not running on the server host
Make sure you have started the MySQL Server on the server host. If it is not running, it cannot respond to client connection requests.
Note that the MySQL Server is a different program than the mysql client. MySQL Server is a service; just because you have started the mysql client does not mean that the MySQL Server is running.
[edit] Verify that MySQL Server is running on Windows
Windows has a command sc with which you can query the state of services. Open a command shell (Start->Run->cmd) and use the sc command as follows:
C:\> sc query MySQL
SERVICE_NAME: MySQL
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
An important detail in that output is that the service is RUNNING. If it were not running, the STATE would be STOPPED.
Note that "MySQL" is the default name for the Windows service. If you chose a different service name when you installed, specify it instead in the sc command.
You can also get information about the configuration of the MySQL service:
C:\> sc qc MySQL
[SC] GetServiceConfig SUCCESS
SERVICE_NAME: MySQL
TYPE : 10 WIN32_OWN_PROCESS
START_TYPE : 2 AUTO_START
ERROR_CONTROL : 1 NORMAL
BINARY_PATH_NAME : "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt"
--defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini" MySQL
LOAD_ORDER_GROUP :
TAG : 0
DISPLAY_NAME : MySQL
DEPENDENCIES :
SERVICE_START_NAME : LocalSystem
You can see in this output the location of the MySQL Server binary, and the arguments it is given when it is started. Note the location of the my.ini file. This is useful for examining other server configuration options.
If the MySQL service is not running, you can start it:
C:\> sc start MySQL
SERVICE_NAME: MySQL
TYPE : 10 WIN32_OWN_PROCESS
STATE : 2 START_PENDING
(NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x7d0
PID : 2840
FLAGS :
You can also start and stop services in the Windows control panel GUI. Open Start->Control Panel->Administrative Tools->Services.
[edit] Verify that MySQL Server is running on Linux/UNIX
You can view a listing of the processes running on the server at a command prompt with the ps command:
# ps -ef | grep mysqld ... root 2435 2342 0 15:49 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/var/ --user=mysql mysql 2480 2435 0 15:49 pts/1 00:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/var/ --user=mysql ... root 2624 2342 0 15:57 pts/1 00:00:00 grep mysqld ...
Note that the processes include the mysqld_safe script that starts the service, the mysqld executable itself with various options specified (yours may appear different from the example above), and the grep command which also contains the word mysqld.
You can also use the netstat command to list running services:
# netstat -lnp | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2480/mysqld unix 2 [ ACC ] STREAM LISTENING 8101 2480/mysqld /tmp/mysql.sock
Note in the above example two important values. 3306 is the network port on which the MySQL Server is listening. /tmp/mysql.sock is the socket file MySQL Server is using for local connections.
If the MySQL daemon is not running, the output of the commands above won't show lines for mysqld. So you need to start it.
# /etc/init.d/mysql start
Some versions of Linux (e.g. Redhat) also have a convenience program called service which you can use. It effectively does the same thing as running the respective script in the /etc/init.d directory.
# service mysql start
[edit] MySQL Server is running, but not on the default port
Clients can connect to the MySQL Server locally or remotely.
A remote connection uses TCP/IP networking. It is used by default on Windows, and always when the client is a Java applications, and when the client and the server are on different hosts. Even if the client and the server are on the same host, a connection using TCP/IP is called a remote connection. In this mode, the client and server must agree on the network port number.
The default port on which MySQL Server listens is 3306. This can be configured on a per-instance basis. So the server may be listening on a different port, even if the client assumes that it is using the default 3306. If the client and the server do not agree on the port to use, connections cannot succeed.
Check what port the server is using. If you have a running MySQL Server instance and you can connect to it, you can use the following statement to inquire the port it is listening to.
SHOW VARIABLES LIKE 'port';
It will probably respond 3306.
If you can't connect, you can still find the port number by checking the my.ini or my.cnf configuration file on the server. Look for the port option within the [mysqld] section:
[mysqld] ... port=3306 ...
The port can also be specified on the command-line. This overrides the entry in the options file. So double-check the arguments to the mysqld program, which you can see in the ps output. You can also read the output of netstat to see the port that the server process is listening to.
[edit] MySQL Server is running, but not with the default socket file
A local connection is an optimization available on UNIX/Linux using a socket, which is another mechanism of interprocess communication. This kind of socket is faster than TCP/IP, but it only works when both client and server are on the same host. Like the port number for remote connections, a client and server connecting locally must agree on the path to the socket file. This path is typically something like /tmp/mysql.sock or /var/lib/mysql.sock. The path to the socket file is configurable, so it may be different on different instances of MySQL Server.
Check what socket file path the server is using. If you can connect and issue a query, use this:
SHOW VARIABLES LIKE 'socket';
The socket file path is also part of the output of the netstat -lnp command we used above. It can also appear in the my.cnf configuration file:
[mysqld] ... socket=/tmp/mysql.sock ...
It can also appear on the command-line, so check the ps output for an option to mysqld like --socket=/tmp/mysql.sock.
As noted earlier, the client and the server must agree on the location of the socket file. The socket file can appear in the my.cnf file, in the [mysql] client section (this is different from the [mysqld] server section):
[mysql] ... socket=/tmp/mysql.sock ...
You can also specify the socket path on the command-line:
# mysql --socket=/tmp/mysql.sock ...options...
[edit] MySQL Server accepts clients only on same host
For security reasons, some MySQL Server instances allow remote clients only if the client is running on a single specified host. For instance, typically the same host, so web applications can connect to MySQL Server on the same host, even if the connection is made via TCP/IP and therefore is a remote connection.
There is a configuration option to restrict client connections to a single originating IP address. This option can appear in the my.cnf:
[mysqld] ... bind_address=127.0.0.1 ...
The option can also be specified on the command-line, but it is spelled with a hyphen instead of an underscore: mysqld ... --bind-address=127.0.0.1
The address 127.0.0.1 is a convention that is used on all TCP/IP hosts; it means "this host". The configuration option means that only clients connecting from a host at the specified IP address are permitted to connect. All other clients are refused. This helps block unauthorized attackers from connecting to your instance of MySQL Server, but it also prevent direct connections from any other host in your network.
To disable this restriction, comment out or delete the line from the configuration file, and restart MySQL Server. But be cautious before doing this, because permitting clients from other hosts to connect could expose your MySQL Server to attackers from outside your network.
[edit] MySQL Server accepts no remote clients, only local clients
Another configuration option restricts MySQL Server so that only clients using the local connection method are allowed. Any clients using the remote connection method, even clients running on the same host, are refused. This option appears in the my.cnf:
[mysqld] ... skip_networking ...
The option can also be specified on the command-line, but it is spelled with a hyphen instead of an underscore: mysqld ... --skip-networking
[edit] A firewall blocks incoming connections to the server host
Firewalls are services that intercept and block network connection attempts.
A firewall on the MySQL Server host can block incoming connections. The client fails to connect, and returns the same error as if any of the preceding causes had prevented the client from connecting to the server.
Firewall can be software running on the host running MySQL Server, or another host that routes network traffic between your host and the host running MySQL Server. A firewall can also be a dedicated network device.
[edit] A firewall on the client host blocks outgoing connections
A firewall on the client host can also block outgoing connections. This is not uncommon these days to run client-side firewalls, because automated spam software can hijack your desktop computer to send spam. Blocking unauthorized programs from initiating outgoing network activity can help to reduce this risk.
It also means that legitimate applications are denied access to the network, until they are authorized by some deliberate action that you need to do. The effect is that a client can be blocked from making the connection, and instead returns the same error as if any of the preceding causes had prevented the client from connecting to the server.
There are many such client firewall products, and it is beyond the scope of this FAQ to tell you how to use all of them. You must be aware of what software is running on your computer, and how to configure the client firewall software to block the connections of unauthorized applications, but permit legitimate applications.
Here are links to common firewall software:
- Netfilter/iptables (Linux) http://www.netfilter.org/
- IPFW or PF (BSD UNIX, Mac OS X) http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/firewalls-ipfw.html
- Microsoft Windows Firewall (Windows XP SP2) http://www.microsoft.com/windowsxp/using/networking/security/winfirewall.mspx
- ZoneLabs ZoneAlarm http://www.zonelabs.com/
- McAfee Internet Security Suite http://www.mcafee.com/
- Symantec Norton Personal Firewall http://www.symantec.com/
- Many others. See http://en.wikipedia.org/wiki/Firewall_(networking)#Implementations