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:
$> mysql ERROR 2003: Can't connect to MySQL server on 'host_name' (111) $> mysql ERROR 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 the
skip_networking
system variable enabled, no TCP/IP connections are accepted. If the server was started with thebind_address
system variable set to127.0.0.1
, it listens for TCP/IP connections only locally on the loopback interface and does 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 Windows 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 and DEB distributions on Linux), the installation process initializes the MySQL data directory, including the
mysql
system database containing the grant tables. For distributions that do not do this, you must initialize the data directory manually. For details, see Section 2.9, “Postinstallation Setup and Testing”.To determine whether you need to initialize the grant tables, look for a
mysql
directory under the data directory. (The data directory normally is nameddata
orvar
and is located under your MySQL installation directory.) Make sure that you have a file nameduser.MYD
in themysql
database directory. If not, initialize the data directory. After doing so and starting the server, you should be able to connect to the server.After a fresh installation, if you try to log on to the server as
root
without using a password, you might get the following error message.$> mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
It means a root password has already been assigned during installation and it has to be supplied. See Section 2.9.4, “Securing the Initial MySQL Account” on the different ways the password could have been assigned and, in some cases, how to find it. If you need to reset the root password, see instructions in Section B.3.3.2, “How to Reset the Root Password”. After you have found or reset your password, log on again as
root
using the--password
(or-p
) option:$> mysql -u root -p Enter password:
However, the server is going to let you connect as
root
without using a password if you have initialized MySQL using mysqld --initialize-insecure (see Section 2.9.1, “Initializing the Data Directory” for details). That is a security risk, so you should set a password for theroot
account; see Section 2.9.4, “Securing the Initial MySQL Account” for instructions.If you have updated an existing MySQL installation to a newer version, did you perform the MySQL upgrade procedure? 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 Chapter 3, Upgrading MySQL.
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:
$> mysql Client does not support authentication protocol requested by server; consider upgrading MySQL client
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
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
The option files that clients use are listed in Section 6.2.2.2, “Using Option Files”. Environment variables are listed in Section 6.9, “Environment Variables”.
If you get the following error, it means that you are using an incorrect
root
password:$> mysqladmin -u root -pxxxx ver Access 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 8.2.14, “Assigning Account Passwords”.
If you have lost or forgotten the
root
password, see Section B.3.3.2, “How to Reset the Root Password”.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.You can use a
--host=127.0.0.1
option to name the server host explicitly. This causes 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 auser
table row on the server host, even though you are running the client program on the same host as the server.The
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 theuser
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 containsusing 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 withmysql -u
, you may have a problem with theuser_name
user
table. Check this by executingmysql -u root mysql
and issuing this SQL statement:SELECT * FROM user;
The result should include a row with the
Host
andUser
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
user
table with aHost
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 theHost
column value in theuser
table. After trying to connect from the client machine, use aSELECT USER()
query to see how you really did connect. Then change the'%'
in theuser
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 orglibc
, 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 -pxxxx -h some_hostname ver Access 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 theuser
table with aUser
column value of'root'
and that mysqld cannot resolve the host name for your client: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 7.1.12.3, “DNS Lookups 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.Start mysqld with the
skip_name_resolve
system variable enabled.Start mysqld with
--host-cache-size=0
.On Unix, if you are running the server and the client on the same machine, connect to
localhost
. For connections tolocalhost
, MySQL programs attempt to connect to the local server by using a Unix socket file, unless there are connection parameters specified to ensure that the client makes a TCP/IP connection. For more information, see Section 6.2.4, “Connecting to the MySQL Server Using Command Options”.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.
If
mysql -u root
works butmysql -h
results inyour_hostname
-u rootAccess denied
(whereyour_hostname
is the actual host name of the local host), you may not have the correct name for your host in theuser
table. A common problem here is that theHost
value in theuser
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 a row with host'pluto'
in theuser
table, but your DNS tells MySQL that your host name is'pluto.example.com'
, the row does not work. Try adding a row to theuser
table that contains the IP address of your host as theHost
column value. (Alternatively, you could add a row to theuser
table with aHost
value that contains a wildcard (for example,'pluto.%'
). However, use ofHost
values ending with%
is insecure and is not recommended!)If
mysql -u
works butuser_name
mysql -u
does not, you have not granted access to the given user for the database nameduser_name
some_db
some_db
.If
mysql -u
works when executed on the server host, butuser_name
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.host_name
-uuser_name
If you cannot figure out why you get
Access denied
, remove from theuser
table all rows that haveHost
values containing wildcards (rows that contain'%'
or'_'
characters). A very common error is to insert a new row withHost
='%'
andUser
='
, thinking that this enables you to specifysome_user
'localhost
to connect from the same machine. The reason that this does not work is that the default privileges include a row withHost
='localhost'
andUser
=''
. Because that row has aHost
value'localhost'
that is more specific than'%'
, it is used in preference to the new row when connecting fromlocalhost
! The correct procedure is to insert a second row withHost
='localhost'
andUser
='
, or to delete the row withsome_user
'Host
='localhost'
andUser
=''
. After deleting the row, remember to issue aFLUSH PRIVILEGES
statement to reload the grant tables. See also Section 8.2.6, “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 aSELECT ... INTO OUTFILE
orLOAD DATA
statement, your row in theuser
table does not have theFILE
privilege enabled.If you change the grant tables directly (for example, by using
INSERT
,UPDATE
, orDELETE
statements) and your changes seem to be ignored, remember that you must execute aFLUSH 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 theroot
password with anUPDATE
statement, you do not need to specify the new password until after you flush the privileges, because the server does not know until then that you have changed the password.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 8.2.13, “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
oruser_name
db_name
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 betweenuser_name
-ppassword
db_name
-p
and the password; you can also use the--password=
syntax to specify the password. If you use thepassword
-p
or--password
option with no password value, MySQL prompts you for the password.)For testing purposes, start the mysqld server with the
--skip-grant-tables
option. Then you can change the MySQL grant tables and use theSHOW GRANTS
statement 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,
--debug=d,general,query
). This prints host and user information about attempted connections, as well as information about each command issued. See Section 7.9.4, “The DBUG Package”.If you have any other problems with the MySQL grant tables and ask on the MySQL Community Slack, 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.6, “How to Report Bugs or Problems”. In some cases, you may need to restart mysqld with
--skip-grant-tables
to run mysqldump.