4.2.13 Causes of Access denied Errors

If you encounter Access denied errors when you try to connect to the MySQL server, the following list 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. See section 4.3.4 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.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 an incorrect password. See section 4.3.7 Setting Up Passwords. If you have forgot the root password, you can restart mysqld with --skip-grant-tables to change the password. See section A.4.2 How to Reset a Forgotten Root Password. If you get the above error even if you haven't specified a password, this means that you have an incorrect password in some my.ini file. See section 4.1.2 `my.cnf' 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 section 4.3.3 When Privilege Changes Take Effect.
• 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 ... IDENTIFIED BY statement or the mysqladmin password command. See section 4.3.7 Setting 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 using a MySQL version prior to 3.23.27 that uses MIT-pthreads (localhost connections are made using Unix sockets, which were not supported by MIT-pthreads at that time). 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. See section 5.5.5 How MySQL uses 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 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 -h 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=''. 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. See section F 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. See section 4.1.2 `my.cnf' 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! See section 4.1.2 `my.cnf' 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 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. See section E.1.2 Creating 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. See section 1.7.1.3 How to Report Bugs or Problems. In some cases you may need to restart mysqld with --skip-grant-tables to run mysqldump.

User Comments

I was getting access denied when trying to select
from a table, where I had specifically given the
user select privs (in tables_priv table). turned
out to be an issue with case sensitivity: if you
grant privileges on a table using upper case for
db name, but then try to access the db in
lowercase (as I had done), you will get the
"denied" message. This is actually in the
documentation under "how privileges work', but
easy to miss.

For Windows users:
Problem: getting an access denied message when
trying to use the GRANT command while logged in
as root user.
Background: had data from a previous version of
Mysql in c:\mysql\data.
Solution: renamed c:\mysql\data\mysql to
c:\mysql\data\old_mysql and then reinstalled the
binary. (I could not find
any 'mysql_fix_privilege_tables' script for
windows in the distribution nor online)

One case not mentioned here and you get an 1045 "Access denied"
error is when you don't have the privilege to alter system
tables
using "LOCK TABLES". The connect succeeds, but a "LOCK TABLES"
query fails. Then you should ask the MySQL administrator to
extend your privileges. The "LOCK TABLES" command is
writing to the system tables. If the MySQL administrator
decided to grant you all privileges only on your database, say
foo.*, this won't be sufficient. You will need write access to
'mysql' database. If you don't have that, you will get a MySQL
error "#1045 access denied" at the "LOCK TABLES" query. So, the
case of "LOCK TABLE"
privilege in MySQL breaks a privilege system where
administrators decided to grant privileges ordered by database
hierarchy.

I started getting access denied errors this morning. Some of them are 1044 and some are 1045. I have tried everything I can find and nothing seems to work. I can connect using root as the user and the root password but it still tells me that access denied for user: 'root@localhost'.

I have tried to run all of the scripts and none of them will run because access is denied. I can't change the host files or anything because access is denied.

You have to put single quotes around your password.
e.g. mysql -u root -p'xxxxxx' [databases name]
P.S. Drove me crazy too :-\

i have a Q. i use php and mysql to do a website.
when i use register function in localhost, then i can insert the data to db. But when i my friend use distance computer, then it can not add the new record to db.
$db = mysql_connect("localhost", "");
mysql_select_db("bookshop",$db);
$sql = "INSERT INTO ctinf (usn,paw,given,family,addre,email,phone,ccard)
VALUES ('$uid','$psw','$gvn','$fyn','$addre','$ema','$phn','$ccad')";
$result = mysql_query($sql);
this is my code. and u try to use ("localhost", "root"). also can not. plese tell me what should i do?
thx a lot!

I was able to get on by

user% sudo mysql -u root -p db_name_whatever (hit enter)
password: your_pass

ta da!

I have a problem in connecting to the remote Mysql Server;The ERROR #2013 , what mean? Is anybody else can tell me the above Error # meaning?

thank q !!

MySQL 4.0.12-nt

Installed, ran mysqladmin -u root password 'newpass' and mysqladmin -h localhost flush-privileges all without a problem. When I run mysql -h localhost -u root -p I get:

ERROR 1045: Access denied for user: 'root@127.0.0.1' (Using password: YES)

There is no user root@127.0.0.1 only a user root@localhost. localhost is in my \windows\hosts as well as my windows\system32\drivers\etc\hosts.

System is XP Pro.

I have a connection error but I can't find where is the problem came from. Do anyone know, please give me some suggestion.

MySQL Connection Failed: Access denied for user: 'mysql@domain_name' (Using password: YES) in /usr/local/apache/htdocs/test7.php on line 3
unable to connect

essential information for Microsoft Access MySQL users:

sometimes, when you use Microsoft Access to update tables, you get unexpected "access denied for user: '@YOUR_IP_ADDRESS'" errors, which is to be solved in the following way:

Microsoft Access 2000/2002 Jet/ODBC database engine uses default anonymous logins by default, and you should tweak the following registry key so that the ODBC DSN's options were used, instead:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC]
"TryJetAuth"=dword:00000000

hope this helps,

Andrew Eigus
Astros Information Technologies - Riga

I have a small network in my home. My RH8.something server has DHCP and DNS for my small domain (.home.tesmer.org). I do not have DNS names for the hosts on DHCP.

When trying to run MySQLCC in WinXP on a DHCP'd host, I continually got "[gummy] ERROR 2013: Lost connection to MySQL server during query" (gummy is the dns name of the host running the MySQL instance).

I added skip-name-resolve under [mysqld] in my.cnf and restarted the mysqld using the init script, and viola, it worked.

Why in the world does a fresh install om mysql seem to look for a errmsg.sys file in the location of an deleted program while the file is exactly where it's supposed to be in its own directory? Better yet, how do I let mysql know this? It doesn't connect to the database because of it.

Ric

my situation is as follows:
Microsoft OLE DB Provider for ODBC Drivers ���� '80004005'

[MySQL][ODBC 3.51 Driver]Access denied for user: 'nelson@192.168.0.2' (Using password: YES)

/forum/inc_header.asp, ��60

what i've done is that i backup all the data files in my database and then format and reinstall the window. After that i install the mysql server and add the original [data] back to the folder. However i do not succeed.

DO you know what's the problem, and how to deal with this?

as per bug http://bugs.mysql.com/bug.php?id=488, I am stuck with <<Host ... is not allowed to connect to this MySQL server>> - could you provide examples what to do to add the described rows.
MySQL is an underlying tool, after being able to set it up, I no longer need to know SQL commands.

Joerg Prante, I could kiss you. I thought it was something like that. Your solution was what I was looking for: I reinstalled the binaries, ran mysqld, and now seem good to go (although it now appears to be hanging later on in the script, huh?) And thank you for inserting the Windows perspective in a breadcrumb trail that leads from a Windows error to a thread on Unix. I say it again: This documentation is in very poor shape.

when you are simply trying to:
C:\mysql\bin>mysql -uroot -p mysql

and you get:

ERROR 1044: Access denied for user: '@127.0.0.1' to database 'mysql'

Here is what I do. The key is to supply your real ip address for the -h (host) parameter. On windows, from the command prompt type 'ipconfig' to see your ip address. Once you have that, do the following:

C:\mysql\bin>mysql -h 192.168.0.1 -u root -p mysql
Enter password: ****************

// then I explicitly add root@127.0.0.1 to the user table, so after this I can log in as you would expect

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'root-password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO root@127.0.0.1 IDENTIFIED BY 'root-password' WITH GRANT OPTION;

// delete anon accounts

DELETE FROM mysql.user WHERE password='';

FLUSH PRIVILEGES;

It works for me

One other way is when you set the initial password for the root user, also set it on 127.0.0.1:

SET PASSWORD FOR root@localhost=PASSWORD('new_password');
SET PASSWORD FOR root@127.0.0.1=PASSWORD('root-password');
FLUSH PRIVILEGES;

Add your own comment.