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 '
host_name' (111) shell>
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
--skip-networking, it will
not accept TCP/IP connections at all. If the server was
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.12.1, “Unix Postinstallation Procedures”.
To determine whether you need to initialize the grant tables,
look for a
mysql 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
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.12.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.8, “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 requested by 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 C.5.2.4, “
Client does not support authentication protocol”.
Remember that 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 get
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
mysqladmin --no-defaults -u root version
If you get the following error, it means that you are using an
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
password, see Section C.5.4.1, “How to Reset the Root Password”.
If you change a password by using
UPDATE, you must encrypt the
password using the
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 connect afterward:
SET PASSWORD FOR 'abe'@'
host_name' = 'eagle';
Instead, set the password like this:
SET PASSWORD FOR 'abe'@'
host_name' = PASSWORD('eagle');
PASSWORD() 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 specifying a
--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
, you may have a
problem with the
user table. Check this by
mysql -u root mysql and issuing
this SQL statement:
SELECT * FROM user;
The result should include a row with the
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
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
value in the
user table. After trying to
connect from the client machine, use a
USER() query to see how you really did connect. Then
'%' 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
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
column value of
'root' and that
mysqld cannot resolve the host name for
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 8.9.8, “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
/etc/hosts on Unix or
\windows\hosts on Windows.
On Unix, if you are running the server and the client on
the same machine, connect to
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
. use a named
pipe rather than TCP/IP.
mysql -u root test works but
mysql -h results in
your_hostname 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
'pluto' in the
user table, but your DNS tells MySQL that
your host name is
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
value that contains a wildcard; for example,
'pluto.%'. However, use of
Host values ending with
insecure and is not
mysql -u works but
does not, you
have not granted access to the given user for the database
executed on the server host, but
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
If you cannot figure out why you get
denied, remove from the
table all entries that have
containing wildcards (entries that contain
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
'%', it 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
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
Access denied message whenever you issue a
SELECT ... INTO
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
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
UPDATE 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
-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
the password; you can also use the
syntax to specify the password. If you use the
--password option with no
password value, MySQL prompts you for the password.)
For testing purposes, start the mysqld
server with the
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 you get the following error, you may have a problem with
Access to database denied
If the entry selected from the
db table has
an empty value in the
Host column, make
sure that there are one or more corresponding entries in the
host table specifying which hosts the
db table entry applies to. This problem
occurs infrequently because the
is rarely used.
If everything else fails, start the mysqld
server with a debugging option (for example,
prints host and user information about attempted connections,
as well as information about each command issued. See
Section 22.4.3, “The DBUG Package”.
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
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices