If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.
Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:
mysqlERROR 2003: Can't connect to MySQL server on '
mysqlERROR 2002: Can't connect to local MySQL server through socket'/tmp/mysql.sock' (111)
It might be that the server is running, but you are trying to connect using a
TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To
correct this when you invoke a client program, specify a
--port option to indicate the proper port number, or a
--socket option to indicate the proper named pipe or Unix socket file.
To find out where the socket file is, you can use this command:
netstat -ln | grep mysql
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
--skip-networking, it will not accept TCP/IP connections at all. If
the server was started with
--bind-address=127.0.0.1, it will listen for TCP/IP connections only
locally on the loopback interface and will not accept remote connections.
Check to make sure that there is no firewall blocking access to MySQL. Your firewall may be configured on the basis of the application being executed, or the port number used by MySQL for communication (3306 by default). Under Linux or Unix, check your IP tables (or similar) configuration to ensure that the port has not been blocked. Under Windows, applications such as ZoneAlarm or the Windows XP personal firewall may need to be configured not to block the MySQL port.
The grant tables must be properly set up so that the server can use them for access
control. For some distribution types (such as binary distributions on Windows, or RPM distributions on
Linux), the installation process initializes the
mysql database containing
the grant tables. For distributions that do not do this, you must initialize the grant tables manually
by running the mysql_install_db script. For details, see Section 2.10.1, "Unix Postinstallation
To determine whether you need to initialize the grant tables, look for a
directory under the data directory. (The data directory normally is named
var and is located under your MySQL installation directory.) Make
sure that you have a file named
user.MYD in the
mysql database directory. If not, execute the mysql_install_db script. After running this
script and starting the server, test the initial privileges by executing this command:
mysql -u root test
The server should let you connect without error.
After a fresh installation, you should connect to the server and set up your users and their access permissions:
mysql -u root mysql
The server should let you connect because the MySQL
root user has no
password initially. That is also a security risk, so setting the password for the
root accounts is something you should do while you're setting up your
other MySQL accounts. For instructions on setting the initial passwords, see Section
2.10.2, "Securing the Initial MySQL Accounts".
If you have updated an existing MySQL installation to a newer version, did you run the mysql_upgrade script? If not, do so. The structure of the grant tables changes occasionally when new capabilities are added, so after an upgrade you should always make sure that your tables have the current structure. For instructions, see Section 4.4.7, "mysql_upgrade — Check and Upgrade MySQL Tables".
If a client program receives the following error message when it tries to connect, it means that the server expects passwords in a newer format than the client is capable of generating:
mysqlClient does not support authentication protocol requestedby server; consider upgrading MySQL client
For information on how to deal with this, see Section
22.214.171.124, "Password Hashing in MySQL", and Section
Client does not support authentication protocol".
client programs use connection parameters specified in option files or environment variables. If a
client program seems to be sending incorrect default connection parameters when you have not specified
them on the command line, check any applicable option files and your environment. For example, if you
Access denied when you run a client without any options, make sure that
you have not specified an old password in any of your option files!
You can suppress the use of option files by a client program by invoking it with the
--no-defaults option. For example:
mysqladmin --no-defaults -u root version
If you get the following error, it means that you are using an incorrect
mysqladmin -u root -pAccess denied for user 'root'@'localhost' (using password: YES)
If the preceding error occurs even when you have not specified a password, it means that you have an
incorrect password listed in some option file. Try the
--no-defaults option as described in the previous item.
For information on changing passwords, see Section 6.3.5, "Assigning Account Passwords".
If you have lost or forgotten the
root password, see Section
C.5.4.1, "How to Reset the Root Password".
If you change a password by using
you must encrypt the password using the
PASSWORD() function. If you do not use
PASSWORD() for these statements, the password will not work. For
example, the following statement assigns a password, but fails to encrypt it, so the user is not able to
SET PASSWORD FOR 'abe'@'
host_name' = 'eagle';
Instead, set the password like this:
SET PASSWORD FOR 'abe'@'
host_name' = PASSWORD('eagle');
function is unnecessary when you specify a password using the
CREATE USER or
GRANT statements or the mysqladmin password command. Each of those
to encrypt the password. See Section 6.3.5, "Assigning
Account Passwords", and Section 126.96.36.199, "
CREATE USER Syntax".
localhost is a synonym for your local host name, and is
also the default host to which clients try to connect if you specify no host explicitly.
To avoid this problem on such systems, you can use a
--host=127.0.0.1 option to name the server host explicitly. This will
make a TCP/IP connection to the local mysqld server. You can also use TCP/IP by
--host option that uses the actual host name of the local host. In
this case, the host name must be specified in a
user table row on the
server host, even though you are running the client program on the same host as the server.
Access denied error message tells you who you are
trying to log in as, the client host from which you are trying to connect, and whether you were using a
password. Normally, you should have one row in the
user table that exactly
matches the host name and user name that were given in the error message. For example, if you get an
error message that contains
using password: NO, it means that you tried to
log in without a password.
If you get an
Access denied error when trying to
connect to the database with
mysql -u , you may have a problem with the
user table. Check this by executing
mysql -u root
mysql and issuing this SQL statement:
SELECT * FROM user;
The result should include a row with the
User columns matching your client's host name and your MySQL user name.
If the following error occurs when you try to connect from a host other than the
one on which the MySQL server is running, it means that there is no row in the
table with a
Host value that matches the client host:
Host ... is not allowed to connect to this MySQL server
You can fix this by setting up an account for the combination of client host name and user name that you are using when trying to connect.
If you do not know the IP address or host name of the machine from which you are connecting, you
should put a row with
'%' as the
column value in the
user table. After trying to connect from the client
machine, use a
SELECT USER() query to see how you really did connect.
Then change the
'%' in the
user table row
to the actual host name that shows up in the log. Otherwise, your system is left insecure because it
permits connections from any host for the given user name.
On Linux, another reason that this error might occur is that you are using a binary MySQL version
that is compiled with a different version of the
glibc library than the
one you are using. In this case, you should either upgrade your operating system or
glibc, or download a source distribution of MySQL version and compile
it yourself. A source RPM is normally trivial to compile and install, so this is not a big problem.
If you specify a host name when trying to connect, but get an error message where the host name is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:
mysqladmin -u root -pAccess denied for user 'root'@'' (using password: YES)
If you try to connect as
root and get the following error, it means
that you do not have a row in the
user table with a
User column value of
'root' and that mysqld cannot resolve the host name for your
Access denied for user ''@'unknown'
These errors indicate a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS host cache. See Section 188.8.131.52, "DNS Lookup Optimization and the Host Cache".
Some permanent solutions are:
Determine what is wrong with your DNS server and fix it.
Specify IP addresses rather than host names in the MySQL grant tables.
Put an entry for the client machine name in
on Unix or
\windows\hosts on Windows.
On Unix, if you are running the server and the client on the same
machine, connect to
localhost. Unix connections to
localhost use a Unix socket file rather than TCP/IP.
On Windows, if you are running the server and the client on the same
machine and the server supports named pipe connections, connect to the host name
. (period). Connections to
a named pipe rather than TCP/IP.
mysql -u root test works but
-h results in
your_hostname -u root test
Access denied (where
is the actual host name of the local host), you may not have the correct name for your host in the
user table. A common problem here is that the
Host value in the
user table row specifies
an unqualified host name, but your system's name resolution routines return a fully qualified domain
name (or vice versa). For example, if you have an entry with host
user table, but your DNS tells MySQL that your host name is
'pluto.example.com', the entry does not work. Try adding an entry to the
user table that contains the IP address of your host as the
Host column value. (Alternatively, you could add an entry to the
user table with a
Host value that contains a
wildcard; for example,
'pluto.%'. However, use of
values ending with "
%" is insecure and
is not recommended!)
mysql -u works but
does not, you have not granted access to
the given user for the database named
mysql -u works when executed on the server host, but
mysql -h does not work when executed on a remote
client host, you have not enabled access to the server for the given user name from the remote host.
If you cannot figure out why you get
remove from the
user table all entries that have
Host values containing wildcards (entries that contain
'_' characters). A very common error
is to insert a new entry with
thinking that this enables you to specify
localhost to connect from the
same machine. The reason that this does not work is that the default privileges include an entry with
''. Because that entry has a
'localhost' that is more specific than
is used in preference to the new entry when connecting from
correct procedure is to insert a second entry with
', or to delete the entry with
''. After deleting the entry, remember to issue a
FLUSH PRIVILEGES statement to reload the grant tables. See also Section 6.2.4, "Access Control,
Stage 1: Connection Verification".
If you are able to connect to the MySQL server, but get an
denied message whenever you issue a
SELECT ... INTO OUTFILE or
LOAD DATA INFILE statement, your entry in the
user table does not have the
FILE privilege enabled.
If you change the grant tables directly (for example, by using
DELETE statements) and your changes seem to be ignored, remember that you
must execute a
FLUSH PRIVILEGES statement or a mysqladmin flush-privileges command to cause the
server to reload the privilege tables. Otherwise, your changes have no effect until the next time the
server is restarted. Remember that after you change the
root password with
statement, you will not need to specify the new password until after you flush the privileges, because
the server will not know you've changed the password yet!
If your privileges seem to have changed in the middle of a session, it may be that a MySQL administrator has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in Section 6.2.6, "When Privilege Changes Take Effect".
If you have access problems with a Perl, PHP, Python, or ODBC program, try to
connect to the server with
mysql -u or
mysql -u . If you are able to connect using the mysql client, the problem lies with your program,
not with the access privileges. (There is no space between
-p and the
password; you can also use the
to specify the password. If you use the
--password option with no password value, MySQL prompts you for the
For testing purposes, start the mysqld server with the
--skip-grant-tables option. Then you can change the MySQL grant tables and
use the mysqlaccess script to check whether your
modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to reload the privileges. This enables
you to begin using the new grant table contents without stopping and restarting the server.
If everything else fails, start the mysqld server with a debugging option (for example,
This prints host and user information about attempted connections, as well as information about each
command issued. See
If you have any other problems with the MySQL grant tables and feel you must post
the problem to the mailing list, always provide a dump of the MySQL grant tables. You can dump the
tables with the mysqldump
mysql command. To file a bug report, see the instructions at Section
1.7, "How to Report Bugs or Problems". In some cases, you may need to restart mysqld with
--skip-grant-tables to run mysqldump.