|
If you encounter Access denied errors when you try to connect to the
MySQL server, the list below indicates some courses of
action you can take to correct the problem:
-
Did you run the
mysql_install_db script after installing
MySQL, to set up the initial grant table contents? If not, do
so. 6.10 Setting up the initial MySQL privileges. Test the initial privileges by
executing this command:
shell> mysql -u root test
The server should let you connect without error. You should also make sure
you have a file `user.ISD' in the MySQL database directory.
Ordinarily, this is `PATH/var/mysql/user.ISD', where PATH is the
pathname to the MySQL installation root.
-
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. Since that is also a security risk, setting the
root password is something you should do while you're setting up
your other MySQL users.
If you try to connect as root and get this error:
Access denied for user: '@unknown' to database mysql
this means that you don't have an entry in the user table with a
User column value of 'root' and that mysqld cannot
resolve the hostname for your client. In this case, you must restart the
server with the --skip-grant-tables option and edit your
`/etc/hosts' or `\windows\hosts' file to add a entry for your
host.
-
If you updated an existing MySQL installation from a pre-3.22.11
version to 3.22.11 or later, did you run the
mysql_fix_privilege_tables script? If not, do so. The structure of
the grant tables changed with MySQL 3.22.11 when the GRANT
statement became functional.
-
If you make changes to the grant tables directly (using
INSERT or
UPDATE statement) and your changes seem to be ignored, remember that
you must issue a FLUSH PRIVILEGES statement or execute a
mysqladmin flush-privileges command to cause the server to reread the
tables. Otherwise your changes have no effect until the next time the server
is restarted. Remember that after you set the root password, you
won't need to specify it until after you flush the privileges, because the
server still won't 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 superuser has changed them. Reloading the grant tables affects new
client connections, but it also affects existing connections as indicated in
6.9 When privilege changes take effect.
-
For testing, start the
mysqld daemon with the
--skip-grant-tables option. Then you can change the MySQL
grant tables and use the mysqlaccess script to check whether or not
your modifications have the desired effect. When you are satisfied with your
changes, execute mysqladmin flush-privileges to tell the mysqld
server to start using the new grant tables. Note: Reloading the
grant tables overrides the --skip-grant-tables option. This allows
you to tell the server to begin using the grant tables again without bringing
it down and restarting it.
-
If you have access problems with a Perl, 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, there is a problem with your program and not with the
access privileges. (Notice that there is no space between -p and the
password; you can also use the --password=your_pass syntax to specify
the password.)
-
If you can't get your password to work, remember that you must use
the
PASSWORD() function if you set the password with the
INSERT , UPDATE or SET PASSWORD statements. The
PASSWORD() function is unnecessary if you specify the password using
the GRANT ... INDENTIFIED BY statement or the mysqladmin
password command.
6.12 How to set up passwords.
-
localhost is a synonym for your local hostname, and is also the
default host to which clients try to connect if you specify no host
explicitly. However, connections to localhost do not work if you are
running on a system that uses MIT-pthreads (localhost connections are
made using Unix sockets, which are not supported by MIT-pthreads). To avoid
this problem on such systems, you should use the --host option to name
the server host explicitly. This will make a TCP/IP connection to the
mysqld server. In this case, you must have your real hostname in
user table entries on the server host. (This is true even if you are
running a client program on the same host as the server.)
-
If you get an
Access denied error when trying to connect to the
database with mysql -u user_name db_name , you may have a problem
with the user table. Check this by executing mysql -u root
mysql and issuing this SQL statement:
mysql> SELECT * FROM user;
The result should include an entry with the Host and User
columns matching your computer's hostname and your MySQL user name.
-
The
Access denied error message will tell you who you are trying
to log in as, the host from which you are trying to connect, and whether
or not you were using a password. Normally, you should have one entry in
the user table that exactly matches the hostname and user name
that were given in the error message.
-
If you get the following error when you try to connect from a different host
than the one on which the MySQL server is running, then there is no
row in the
user table that matches that host:
Host ... is not allowed to connect to this MySQL server
You can fix this by using the command line tool mysql (on the server
host!) to add a row to the user table for the user/hostname combination
from which you are trying to connect. If you are not running MySQL
3.22 and you don't know the IP number or hostname of the machine from which
you are connecting, you should put an entry with '%' as the
Host column value in the user table and restart mysqld
with the --log option on the server machine. After trying to connect
from the client machine, the information in the MySQL log will
indicate how you really did connect. (Then replace the '%' in the
user table entry with the actual hostname that shows up in the log.
Otherwise, you'll have a system that is insecure.)
-
If
mysql -u root test works but mysql -h your_hostname -u root
test results in Access denied , then 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 entry specifies an unqualified hostname,
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
'tcx' in the user table, but your DNS tells MySQL that
your hostname is 'tcx.subnet.se' , the entry will not work. Try adding
an entry to the user table that contains the IP number 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, 'tcx.%' . However, use of hostnames ending with `%' is
insecure and is not recommended!)
-
If
mysql -u user_name test works but mysql -u user_name
other_db_name doesn't work, you don't have an entry for other_db_name
listed in the db table.
-
If
mysql -u user_name db_name works when executed on the server
machine, but mysql -u host_name -u user_name db_name doesn't work when
executed on another client machine, you don't have the client machine listed
in the user table or the db table.
-
If you can't figure out why you get
Access denied , remove from the
user table all entries that have Host values containing
wildcards (entries that contain `%' or `_'). A very common error
is to insert a new entry with Host ='%' and
User ='some user' , thinking that this will allow you to specify
localhost to connect from the same machine. The reason that this
doesn't work is that the default privileges include an entry with
Host ='localhost' and User ='' . Since 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
remove the entry with with Host ='localhost' and
User ='' .
-
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 there are one or more corresponding entries in
the host table specifying which hosts the db table entry
applies to.
If you get the error when using the SQL commands SELECT ...
INTO OUTFILE or LOAD DATA INFILE , your entry in the user table
probably doesn't have the file privilege enabled.
-
Remember that client programs will use connection parameters specified in
configuration files or environment variables. If a client seems to be
sending the wrong default connection parameters when you don't specify them
on the command line, check your environment and the `.my.cnf' file in
your home directory. You might also check the system-wide MySQL
configuration files, though it is far less likely that client connection
parameters will be specified there. 4.15.4 Option files. If you get
Access denied when you run a client without any options, make sure you
haven't specified an old password in any of your option files!
4.15.4 Option files.
-
If everything else fails, start the
mysqld daemon with a debugging
option (for example, --debug=d,general,query ). This will print host and
user information about attempted connections, as well as information about
each command issued. G.1 Debugging a MySQL server.
-
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. As always, post your problem using
the mysqlbug script. In some cases you may restart mysqld with
--skip-grant-tables to be able to run mysqldump .
|