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:
shell> mysql
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
shell> 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:
shell> 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 program. 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 data or 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_dbprogram. After running this program
and starting the server, test the initial privileges by executing this command:
shell> 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:
shell> 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, seeSection 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:
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client
For information on how to deal with this, see Section 6.1.2.4,
“Password Hashing in MySQL”, and Section B.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 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:
shell> mysqladmin --no-defaults -u root version
The option files that clients use are listed in Section 4.2.6, “Using
Option Files”. Environment variables are listed inSection 2.14, “Environment
Variables”.
If you get the following error, it means that you are using an
incorrect root password:
shell> 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 6.3.5,
“Assigning Account Passwords”.
If you have lost or forgotten the root password,
see Section B.5.4.1,
“How to Reset the Root Password”.
If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the
password using thePASSWORD() 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');
The PASSWORD() function
is unnecessary when you specify a password using the CREATE USER or GRANTstatements or the mysqladmin password command. Each of those
automatically uses PASSWORD() to
encrypt the password. See Section 6.3.5,
“Assigning Account Passwords”, and Section 13.7.1.1,
“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 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 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 user_name, 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 Host and 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 user 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 Host column value in
the user table. After trying to
connect from the client machine, use aSELECT 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:
shell> 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 the user table
with a User 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 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.
§ Start mysqld with
the --skip-name-resolve option.
§ Start mysqld with
the --skip-host-cache option.
§ 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 . use a named pipe rather
than TCP/IP.
If mysql -u root test works
but mysql -h your_hostname -u root
test results in Access denied(where 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 '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 usertable
with a Host value that contains a
wildcard; for example, 'pluto.%'. However, use of Host values
ending with “%” is insecure and is not recommended!)
If mysql -u user_name test works
but mysql -u user_name other_db does
not, you have not granted access to the given user for the database named other_db.
If mysql -u user_name works
when executed on the server host, but mysql -h host_name -u user_namedoes
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 Access denied,
remove from the user table all entries that
have Hostvalues containing wildcards
(entries that contain '%' or '_' characters).
A very common error is to insert a new entry with Host='%' and User='some_user',
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 withHost='localhost' and User=''.
Because that entry has a Host value 'localhost' that
is more specific than'%', it is used in preference to
the new entry when connecting from localhost! The
correct procedure is to insert a second entry with Host='localhost' and User='some_user', or
to delete the entry withHost='localhost' and User=''. 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 Access denied message
whenever you issue aSELECT ...
INTO OUTFILE or LOAD DATA
INFILE statement, your entry in the user table
does not have theFILE privilege
enabled.
If you change the grant tables directly (for example, by
using INSERT, UPDATE, or DELETE statements) and your
changes seem to be ignored, remember that you must execute a FLUSH PRIVILEGES statement or
amysqladmin 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 rootpassword with an 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 mysql -u user_name db_name or mysql -u user_name -pyour_pass db_name. 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 --password=your_pass syntax
to specify the password. If you use the -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 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
the db or host table:
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 host table
is rarely used.
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 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 mysqlcommand. 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.
No comments :
Post a Comment