MySQL Connection Errors Troubleshooting Information
This page contains information about the underlying causes for common MySQL connection errors, and steps to take to resolve the errors.
When connecting to MySQL via a tool that communicates over TCP/IP such as RazorSQL, if there
is a problem connecting to the MySQL database, a general purpose error such as the following
is often displayed:
Error: Connection Timed Out or Communications Link Failure
There are several reasons this error may occur. The reasons are listed below with links on the steps to take to fix the connection error.
1. The MySQL Server is not Running
The MySQL server needs to be started before it can accept connections from clients. Typically on Windows systems the MySQL service can be started using the services control panel. On Linux / Unix / Mac OS X environments, MySQL can be started using one of the mysql programs in the MySQL bin directory such as mysqld or mysqld_safe.
2. The MySQL server is not listening on the default port or is listening on a port different from the port specified when connecting
The default port that MySQL listens on is 3306. However, this port can be changed for each MySQL instance. If the MySQL server is not listening on port 3306, the port will need to be specified when configuring a client connection to MySQL. To find the port on which MySQL is listening, look in the mysql configuration file, my.ini or my.cnf, in the mysqld section, for example, port=3306.
3. The skip-networking configuration option is enabled in the MySQL configuration file
If the skip-networking option is not commented out in the MySQL configuration file, clients that connect via TCP/IP such as JDBC clients will not be able to connect to the MySQL instance. The skip-networking configuration option is enabled by default in many pre-packaged MySQL products. To turn off the skip-networking option, the line containing the text skip-networking can either be removed or commented out (append with a #) from the MySQL configuration file (my.ini or my.cnf).
4. The bind_address configuration option is set to 127.0.0.1
If the bind_address values is set to 127.0.0.1 in the MySQL configuration file, the MySQL server will not accept connections from remote hosts. The bind_address line in the MySQL configuration file (my.ini or my.cnf) can either be removed or commented out to allow remote connections.
5. The user name used to connect does not have privileges to connect from their host
In order for a user to be able to connect to a MySQL server, the user has to have the
appropriate connection privileges. The privileges are specified by host name when creating
/ altering the user. For example, the following statement will give the user "testuser" all privileges including
privileges from the local MySQL host:
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';
However, if the above user tries to connect to MySQL remotely, the connection will fail. To give the user all privileges and the ability to connect remotely, the following command can be used:
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'testpass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%' IDENTIFIED BY 'testpass' WITH GRANT OPTION;
6. The user name used to connect does not have permissions to connect to the database specified in the connection string
If the user has not been granted privileges to connect to a specific database, and the user tries
to use that database in the MySQL connection string, the connection will fail. For example, if the
user has not been granted privileges on the sample database, but tries to connect to sample, the connection
will fail. The GRANT ALL PRIVILEGES on *.* statement referenced above will give users the ability to connect
to any database in the MySQL server. To specify a specific database, the following syntax can be used:
GRANT ALL PRIVILEGES ON sample.* TO 'testuser'@'localhost';
GRANT ALL PRIVILEGES ON sample.* TO 'testuser'@'%';
7. A firewall blocks incoming connections to the MySQL host or outgoing connections from the client host
In this case, the firewall must be configured to allow transmission via the port on which MySQL is listening. If the port is blocked by the firewall, the client will not be able to connect.
8. The web host / ISP does not allow remote connections to the MySQL database.
Many web hosts do not allow remote access to their MySQL servers, or require special configuration to allow a user to be able to connect to MySQL remotely. In these cases, users can either work with their web hosts to allow remote access, or they can use a brige or tunnel option to connect to MySQL. The bridge / tunnel uses a program running remotely on the web host as a proxy for all communication to the MySQL server. This way the actual connection to MySQL is done locally. RazorSQL provides a PHP bridge that can be deployed on the web host to allow this kind of communication with MySQL.