Causes of `Access denied' Errors
--------------------------------
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:
* After installing MySQL, did you run the `mysql_install_db' script
to set up the initial grant table contents? If not, do so. Note:Default 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.MYD' in the MySQL database
directory. Ordinarily, this is `PATH/var/mysql/user.MYD', 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. That is also a security risk, so
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 an entry for your
host.
* If you get an error like the following:
shell> mysqladmin -u root -pxxxx ver
Access denied for user: 'root@localhost' (Using password: YES)
It means that you are using a wrong password. Note:Passwords.
If you have forgot the root password, you can restart `mysqld' with
`--skip-grant-tables' to change the password. You can find more
about this option later on in this manual section.
If you get the above error even if you haven't specified a
password, this means that you a wrong password in some `my.ini'
file. Note:Option files. You can avoid using option files with
the `--no-defaults' option, as follows:
shell> mysqladmin --no-defaults -u root ver
* If you updated an existing MySQL installation from a version
earlier than Version 3.22.11 to Version 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 Version 3.22.11
when the `GRANT' statement became functional.
* 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 Note:Privilege
changes.
* 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. Note: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. For
example if you get an error message that contains `Using password:
NO', this means that you tried to login without an password.
* 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', `db', or `host' table
for the user/hostname combination from which you are trying to
connect and then execute `mysqladmin flush-privileges'. If you are
not running MySQL Version 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.)
Another reason for this error on Linux is that you are using a
binary MySQL version that is compiled with a different glibc
version than the one you are using. In this case you should
either upgrade your OS/glibc or download the source MySQL version
and compile this yourself. A source RPM is normally trivial to
compile and install, so this isn't a big problem.
* If you get an error message where the hostname is not shown or
where the hostname is an IP, even if you try to connect with a
hostname:
shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user: 'root('Using password: YES)
This means that MySQL got some error when trying to resolve the IP
to a hostname. In this case you can execute `mysqladmin
flush-hosts' to reset the internal DNS cache. Note:DNS.
Some permanent solutions are:
- Try to find out what is wrong with your DNS server and fix
this.
- Specify IPs instead of hostnames in the MySQL privilege
tables.
- Start `mysqld' with `--skip-name-resolve'.
- Start `mysqld' with `--skip-host-cache'.
- Connect to `localhost' if you are running the server and the
client on the same machine.
- Put the client machine names in `/etc/hosts'.
* 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 wild card--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
wild cards (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'=`'''. 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 remove
the entry 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. Note: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. Note: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! Note:Option files.
* If you make changes to the grant tables directly (using an
`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 re-read the privilege tables. Otherwise your
changes have no effect until the next time the server is
restarted. Remember that after you set the `root' password with
an `UPDATE' command, you won't need to specify it until after you
flush the privileges, because the server won't know you've changed
the password yet!
* 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, there is a problem with
your program and not with the access privileges. (Note 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 use
the `-p' option alone, MySQL will prompt you for the password.)
* 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 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. Note:Making
trace files.
* 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. Note:Bug reports. In some cases you may
need to restart `mysqld' with `--skip-grant-tables' to run
`mysqldump'.