Skip navigation links

Additional languages
5.4.8 Password Hashing as of MySQL 4.1 »
Section Navigation      [Toggle]

5.4.7. Causes of Access denied Errors

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 running, you cannot connect to it. For example, if you attempt to connect to the server and see 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 also 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

  • 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 script. For details, see Section 2.10.2, “Unix Post-Installation Procedures”.

    One way to determine whether you need to initialize the grant tables is to 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 you do not, execute the mysql_install_db script. After running this script 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.10.3, “Securing the Initial MySQL Accounts”.

    MySQL Enterprise.  The MySQL Enterprise Monitor enforces security-related best practices. For example, subscribers are alerted whenever there is any account without a password. For more information see http://www.mysql.com/products/enterprise/advisors.html.

  • 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, see Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • 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 5.4.8, “Password Hashing as of MySQL 4.1”, and Section B.1.2.4, “Client does not support authentication protocol”.

  • 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 hostname for your client: 

    Access denied for user ''@'unknown' to database mysql

    In this case, you must restart the server with the --skip-grant-tables option and edit your /etc/hosts file or \windows\hosts file to add an entry for your host.

  • 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 your environment and any applicable option files. 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.3.2, “Using Option Files”. Environment variables are listed in Section 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 5.5.5, “Assigning Account Passwords”.

    If you have lost or forgotten the root password, you can restart mysqld with --skip-grant-tables to change the password. See Section B.1.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 the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work. For example, the following statement sets 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 GRANT or CREATE USER statements, or the mysqladmin password command. Each of those automatically uses PASSWORD() to encrypt the password. See Section 5.5.5, “Assigning Account Passwords”, and Section 12.5.1.1, “CREATE USER Syntax”.

  • 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.

    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 hostname of the local host. In this case, the hostname must be specified in a user table row on the server host, even though you are running the 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, 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 computer's hostname and your MySQL username.

  • 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 hostname and username 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 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 hostname and username that you are using when trying to connect.

    If you do not know the IP number or hostname 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 a SELECT USER() query to see how you really did connect. (Then change the '%' in the user table row to the actual hostname that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given username.)

    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 hostname when trying to connect, but get an error message where the hostname is not shown or is an IP number, it means that the MySQL server got an error when trying to resolve the IP number of the client host to a name: 

    shell> mysqladmin -u root -pxxxx -h some_hostname ver
Access denied for user 'root'@'' (using password: YES)

    This indicates a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS hostname cache. See Section 7.5.10, “How MySQL Uses DNS”.

    Some permanent solutions are:

    • Determine what is wrong with your DNS server and fix it.

    • Specify IP numbers rather than hostnames in the MySQL grant tables.

    • Put an entry for the client machine name in /etc/hosts or \windows\hosts.

    • 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 hostname . (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 hostname 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 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 does 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 does not, you have not granted database access for other_db_name to the given user.

  • If mysql -u user_name works when executed on the server host, but mysql -h host_name -u user_name does not work when executed on a remote client host, you have not enabled access to the server for the given username from the remote host.

  • If you cannot 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 allows 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 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 delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.

  • 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.

  • If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA INFILE statement, your entry in the user table does not have the FILE 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 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 change the root password with an UPDATE command, you won't need to specify the new password until after you flush the privileges, because the server 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 MySQL administrator has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in Section 5.4.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 --passwordoption with no password value, MySQL prompts you for the password.)

  • For testing, 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 start using the new grant tables. (Reloading the grant tables overrides the --skip-grant-tables option. This enables you to tell the server to begin using the grant tables again without stopping and restarting it.)

  • 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 MySQL Internals: Porting.

  • 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. 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.


User Comments

Posted by Andrew Eigus on May 2 2003 12:19am[Delete] [Edit]

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

Posted by [name withheld] on May 4 2003 4:19am[Delete] [Edit]

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.

Posted by [name withheld] on July 30 2003 11:58pm[Delete] [Edit]

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;

Posted by David Morris on December 13 2003 9:16am[Delete] [Edit]

+++SIMPLE CPANEL SOLUTION++++
If you get this error message
"Access denied for user: 'root@localhost' (Using password: YES)"

Then this may work for you.

When connecting to the MySQL server from a remote location You have to specify the "hosts"(remote location IP's) that you will be accessing the database from.

You must have access to CPANEL and have MySQL features enabled for this solution to work. A simple way to check if you have these features is to go into the CPANEL and look for the MySQL Databases Section.

If you have it listed, go into it.

Down towards the bottom of the page that loads you will see a heading called "Access Hosts".

By default you will only see "localhost" listed below this heading. This means only database access from the localhost(yourserver) is allowed.

You can add more hosts to this list by using the text box provided with the title "Host:"

For Example: if you wanted to give access to the MySQL server to a remote computer with this static IP address: 211.233.2.24 all you would simply do is type in the IP address in the textbox and hit the "add host" button.

If you had an entire network that you needed to give access to the MySQL server simply use the wildcard "%" symbol eg. 211.233.2.%

SECURITY WARNING: If you do not have a static IP address, using a wildcard "%" may open a security hole that can lead to unauthorized access to the MySQL server from a remote address. Make certain you delete all added hosts if you are not intending on using them and only set up wildcards if no one outside your network has access to the IP range you have specified.

Hope this helps - I know it did for me. :)


Posted by [name withheld] on March 2 2004 1:19am[Delete] [Edit]

I had problems connecting to MySQL over tcp/3306
on debian stable, localhost. It was not enabling networking
in my.cnf, but it was a missing entry in /etc/hosts.allow

I added the following to /etc/hosts.allow:

mysqld 127.0.0.1

I discovered the problem when trying to connect from Java
using the latest stable J/Connector. The error given
was "Communication link failure: null" which was not so
specific.

Since Java only allows you to connect over tcp, you have
no other alternative. If you experience similar problems
from Java, please test to do the following:

telnet localhost 3306

If you are connected and immediately after that disconnected
before the mysqld answers, it might be the missing entry in
hosts.allow .

You can also see if your tcp connection works by forcing
the mysql client to connect over tcp/3306:

mysql -u root -h 127.0.0.1 -P 3306 -p

The error given if you have a network problem, is:
ERROR 2013: Lost connection to MySQL server during query

If you run into this, using debian, then my advice is
to add a line accordingly to /etc/hosts.allow

The reason for this happening on debian, is that
the apt-get package for mysql-server is compiled
with tcp-wrapper support. This is not mentioned
in the file README.debian, so it may come as a surprise
to you. It did to me, to say the least.
Rikard

Posted by April S- on July 24 2004 6:33am[Delete] [Edit]

I spent DAYS trying to track this problem down! :-)

Anyway, with a fresh install I was trying to connect to MySQL from a remote server - but I was getting messages that it could not connect.

I made sure I had users with correct priveleges, made sure mysqld was running on and listening to port 3306..... and it was ALL because of one line in my my.cnf file!!

bind-address=127.0.0.1

I removed that line - restarted mysql and now I have no problem connecting to MySQL from anywhere but localhost!

Perhaps "bind-address" should be listed as one of the potential causes. ;-)

Posted by [name withheld] on August 10 2004 3:27pm[Delete] [Edit]

The "2003 CR_CONN_HOST_ERROR" seems to occur while rapidly opening and closing connections across a network...so keep the connection open.

Posted by Ronald on September 28 2004 1:17pm[Delete] [Edit]

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,

Ronald

Posted by Dagon Midgard on September 28 2004 5:40pm[Delete] [Edit]

For
ERROR 2003: Can't connect to MySQL server on '127.0.0.1' (111)

and no entries in the error.log or mysql.log

If it's not bind-address then it's probably skip-networking. Turn that off and it worked for me running debian - the /etc/hosts.allow option didn't help me with that on!

Posted by Ronald on October 11 2004 11:21am[Delete] [Edit]

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,

Ronald

Posted by israel olalla on October 20 2004 6:54pm[Delete] [Edit]

Hi.

If your have problems conneecting to your mysql server in a debian installation the problem can be that the networking is disabled for security reasons.

I detected the problem cause i was unable to connect using jdbc to my local mysql server, i had always a java.net.ConnectException MESSAGE: Connection refused.

To solve it you should comment the line containing skip-networking:
#skip-networking
After doing this you should reboot the mysql server with:
/etc/init.d/mysql restart
And voila everything will work fine.

Hope this helps

Posted by Dan Williams on October 23 2004 11:58pm[Delete] [Edit]

I was setting up MySQL from the instructions from the O'Reilly PHP and MySQL book and encountered "ERROR 1045: Access denied for user: 'username@localhost' (Using password: YES)". Well the instruction said to add the 'username' with the SQL statement as:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestores.* TO username@127.0.0.1 IDENTIFIED by 'password';

After doing this as root, I quit and tried to log back in with the new user. I got the ERROR 1045.

I fixed it by logging back in as root and reissuing the SQL statement again except with 'username@localhost'. It worked!

I don't know why? Maybe it is the difference between IPs of '127.0.0.1' and 'localhost'???

Hope this helps!

Posted by Kevin Pratt on December 4 2004 11:00pm[Delete] [Edit]

I was having problems logging into my database, getting a 1045 error. I thought everything was in place (the user was in the table, had priveleges, and so on). Maybe this was obvious to everyone but me, but I had to specify a host for the user in order for it to work.

When I first put the user into the db, I left it as the any host wildcard '%', but in order to be able to login with the user I had to specify the users host as 'localhost'.

Theres probably a perfectly good reason for that, but I'm relatively new to MySQL, so I have no idea what it is. I'll just leave my tip to help out anybody else that gets stuck on this, and leave the explanation to people much wiser than myself.

(If anyone can explain this, I'd love to know why this is the case.)

Posted by Ryan Carter on December 9 2004 7:01pm[Delete] [Edit]

I edited the "TryJet..." registry setting which did help, and I also found that the problem for me had to do with the ODBC driver not retaining the port# (which happened to not be 3306, the default) once the ODBC connection settings dialog was closed. I went to "HKEY_LOCAL_MACHINE\Software\Microsoft\ODBC\MyConnectionName" and added a string value for PORT with the new port number value. This has worked ever since.

Posted by Sam Boles on January 5 2005 1:06pm[Delete] [Edit]

Error 1045:Access denied for user 'user_name'@'host_name' (using password: YES).

Occurs when connecting via PHP, does not occur when using other MySQL clients.

Solution: in your script try using single quotes on the arguments in the call to the connection function:

$mysqli = new mysqli('host_name' , 'user_name', 'password', 'database');

or

$dblink = mysql_pconnect ('host_name' , 'user_name', 'password')

Issue Environment:
Apache/2.0.52 (Win32) PHP/5.0.3
MySQL client API version 4.1.7
MySQL version 4.1.7 -nt
Windows 2000 Server Service Pack 4

Almost all of the documentation and examples I have found in the PHP and MySQL materials both in print and online uses double quotes for the connection function arguments. {$mysqli = new mysqli("host_name", "user_name", "password", "database");} However, I found that using the double quote syntax in the above enviroment casues the connection function to return a 1045 error.

Changing this to single quote syntax corrected it.

This affects both mysql and mysqli functions, however I do not know if this affects PHP and MySQL on all platforms, as I have only tried it in the environment listed above.

Hope this helps.


Posted by Dave Pawson on January 27 2005 12:31pm[Delete] [Edit]

Moving from a windows testbed, 4.0.18 to Redhat Enterprise with
MySQL 4.1.9, I repeatedly failed to connect, getting not allowed errors,
when connecting from Tomcat 5.0.28

This was resolved when I swapped the order in /etc/hosts
for the localhost.

Now reads
127.0.0.1 localhost localhost.localdomain


Posted by Johannes Ullrich on February 15 2005 3:42pm[Delete] [Edit]

I narrowed this down to the 'wait_timeout' setting. Interestingly, if I do a 'show variables' , the wait_timeout does always show up as 28800 seconds, even if it is setup differently using 'set global' or the respective setting in my.cnf.

Little test script to see if this is the issue:
use DBI;
$dbh=DBI->connect("DBI:mysql...(insert your paramters)");
$sth=$dbh->prepare("select now()");
$sth->execute();
($start)=$sth->fetchrow_array();
$sth->execute;
($now)=$sth->fetchrow_array();
print "$start $now\n";
sleep 70;
$sth->execute;
($now)=$sth->fetchrow_array();
print "$start $now\n";


just keep changing the number of seconds in the 'sleep' line, and see at what point the error comes up.

Posted by Eric Snyder on June 12 2005 3:56pm[Delete] [Edit]

If you are having problems connecting to the server over a Windows network check you firewall settings. THe SP2 firewall comes set with pretty restrictive settings and will block some connections to the server from other computers on the same network.

Posted by gilles moreau on July 18 2005 11:02am[Delete] [Edit]

I try "LOCK TABLES (table name) WRITE" and i get:
Error executing request.
MySQL message: Access denied for user: '@localhost' to database '(table_name)'

this is because mysql seek by hostname first and try specific users in priority.
MySQL begins with ''@localhost user which haven't LOCK privilege.

so you had to create an user and give him lock privileges:
Commands needed are:

grant all on (database_name).* to (user)@localhost identified by '(password)';

If you are just running one machine you can use localhost for you hostname.Else for all others client you need:


grant all on (database_name).* to (user)@'%' identified by '(password)';

The parentheses are not needed, I use them to indicate where you have to put in your specific values.

You can now execute LOCK tables queries by using this user account.
MySQL will consult "db" table in mysql database, which describes the acces rights for user (user) to database
(database_name) and allow LOCK queries.

notice:
You don't had to have write access to
'mysql' database.
So, the case of "LOCK TABLE" privilege in MySQL don't breaks privilege system where
administrators decided to grant privileges ordered by database hierarchy.

Posted by Josh Nathanson on August 23 2005 12:55am[Delete] [Edit]

I did an install on Windows, and also had to comment out the skip-networking line, to get it to connect succesfully. On Windows it's in the file /yourinstalldir/my.ini

Posted by John Doorty on September 30 2005 3:37pm[Delete] [Edit]

Help and solution to error 10060 and 10065 "cannot connect to mysql server @...."

This could be related to Firewall on the Host machine

If the Host is a Windows XP machine and you don't wish to turn off the built-in Firewall try adding port 3306 to the Exceptions list.

This works, however, I am not sure what other issues it raises for the security of your machine.


Posted by Warwick Hehir on January 14 2006 6:20am[Delete] [Edit]

There has been several posts on
Essential information for Microsoft Access MySQL users:
ERROR: "access denied for user: '@YOUR_IP_ADDRESS'" errors, which is to be solved in the following way:
WITH SOLUTION:
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

OTHER OPTIONS - PEOPLE THOUGHTS
I have another option and wondering if others could test as well please

Put in the code of the form before doing a database connection the following
DBEngine.SetOption "TryJetAuth", 0

Based on the Setoption command format:
DBEngine.SetOption parameter, newvalue

Cheers

W

Posted by Ajaz Ahmed on June 23 2006 7:40am[Delete] [Edit]

By the way ..... although many /or all of the above comments are valid ...

It is also important to note that the host_name (used in GRANT PRIVILEGES) does NOT USE THE CIDR (/24, /29 etc...) notation for specifying subnets !!! - therefore you must use the full 255.255.255.x subnet notation for specifying host ip addresses !!!

Took me a alot of time and digging to figure this one out ! and this was all it was to get remote connections working - even after making sure all the other points mentioned above were correct and in-place !

Posted by Jeff P on August 23 2006 11:32pm[Delete] [Edit]

I spent several hours trying to figure out why I couldn't connect to the mysql server from a remote machine. The only message I got was "ERR 2003 Couldn't connect to server." Fortunately, I came across the tip on this page by by April S- on July 24 2004. The one thing that worried me was her comment about not being able to connect to localhost after her change.

The good news is that on Ubuntu Dapper Drake, commenting out the "bind-address......." line in my.cnf allows local AND remote connections, which is exactly what I need. (MySql 5.0)

Posted by Tim Hecktor on October 25 2006 10:49am[Delete] [Edit]

If you have Problems connecting to an MySQL Server running on a different port (or socket), this may be helpful:

I ran into Problems connecting to a MySQL5 server i have setup on port 3307 in addition to a MySQL4 Server running on 3306.

kimiko:~ # mysql -P3307 -ptest
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This is not possible, as the client always uses a socket to connect to "localhost" (which is the default host). For a sucessful connection either the combination of localhost + socket or 127.0.0.1 + port must be specified.

kimiko:~ # mysql -h127.0.0.1 -P3307 -ptest
kimiko:~ # mysql -S/path/to/mysql.sock -ptest

Posted by Matthew Schroebel on October 28 2006 3:23pm[Delete] [Edit]

If you are having trouble trying to connect to a remote server, then make sure your reverse lookup of the IP address matches the hostname on the machine you are connecting from.

If your hostname on the local machine is my.domain.com and has ip of 200.1.1.0, then check the reverse lookup on the remote server of 200.1.1.0. If the name doesn't match my.domain.com, then add a host table entry to resolve the ip to my.domain.com.

The hint that this is a problem, is if you try to connect from my.domain.com, and the access denied message returns a different host name.

Don't be tempted to change host names you're using on the grant statements to match the value the reverse lookup returns on the remote server. It won't work since the forward lookup name is being provided to the remote server.

Posted by adam b on December 11 2006 4:24pm[Delete] [Edit]

If you're experiencing the error:
ERROR 1045 (28000): Access denied for user 'some_user'@'ip_addr_of_remote_client' (using password: YES)

when you're trying to connect to mysql via a remote client, this thread is helpful:

http://www.snort.org/archive-5-1686.html

Even after you "grant" privileges for specific IP addrs, mysql may still lock you out. The solution this guy found - and also worked for me - is to reset the password for your new user/ip entries with the following command:

mysql>SET PASSWORD FOR some_user@ip_addr_of_remote_client=PASSWORD('some_password');

I performed this when logged into mysql as root. It reset the hash for the password and all my remote connections started "magically" working.

Posted by briosky N/A on February 21 2007 11:00am[Delete] [Edit]

ERROR 1045 (28000): Access denied for user
'root'@'localhost'
and/or
'ODBC'@'localhost'

On my box with XP pro sp2 (behind a router/NAT) the following worked:
in /windows/system32/drivers/etc/hosts
over than 127.0.0.1 localhost
add:
0.0.0.0 localhost
or
255.255.255.255 localhost

best wishes

Posted by c w on February 23 2007 6:42pm[Delete] [Edit]

As always, don't forget to check your firewall settings :)

Posted by Terry Cales on April 10 2007 6:23pm[Delete] [Edit]

re: Access denied for user 'root@localhost' etc.

This worked for me:
at the prompt type: mysql -u root --password

when asked for password, enter your password if you have one.

I had already set up a password for root user after installation.

Posted by Jacob Montgomery on July 2 2007 5:32pm[Delete] [Edit]

In regards to commenting out bind-address...

If you want to retain access locally and remotely, just set host for root as % and save - restart server, and you should be good to go. I have my remote account host as 192.168.1.% and local host for root to % - I tried setting the host for root as the servers IP address but that did not work... I know this is a security issue, but I am sure there is an easy work around, but this allows me to do exactly what I need so I am happy :)

Posted by Angel Eyes on September 20 2007 7:30am[Delete] [Edit]

I happened to experience that when upgraded to WAMP5 1.7 using my old scripts, the reason is, when you pass connection variables and those variables came from an external include file, the variables cannot be read, try to create the variables at the same source file you used to connect to the database and it will be ok, however this is not true if you are using PHP 4.10 or upgraded to WAMP5 1.7.3.

The point is why a middle version will have a big problem like this while the old and latest versions both runs fine. Even the global variable cannot be read cross-file with the register globals is turned on.

Angel Eyes
http://slashingedge.com

Posted by Gorjan Dzundev on December 19 2007 1:48pm[Delete] [Edit]

One cause can be if for example you create a user:

CREATE USER 'some_user'@'%' IDENTIFIED BY 'some_pass';

AND there is another row in users that says:

user: ANY
host: %
pass: NO

You have to delete this row in order to use passwords.

Posted by Paul Gullett on February 16 2008 7:04pm[Delete] [Edit]

This was definitely a privileges issue for me. I had read all of the above to try and solve the issue with MySQL Administrator not allowing me to connect to my mysql from a different system.

Error message was
Cannot connect to host '192.168.0.1'
MySQL Error Nr. 1045
Access denied for user 'root'@'192.168.0.1' (Using password: YES)

and having been through much of the above was struggling. I then used phpMyAdmin to check the privileges table for the server and their it was clear that although I had added the host and thought I'd changed the privilages using the command line options listed above, phpMyAdmin showed password and grant set to No. Setting those through phpMyAdmin solved the issue.


Posted by Dave Stoddard on May 24 2008 3:41pm[Delete] [Edit]

I recently upgraded my OS from FreeBSD 5.5 to FreeBSD 7.0, and upgraded MySQL from 5.0.12 to 5.0.51a. I have a batch job in /etc/crontab that runs mysqlcheck each night to verify the database. The mysqlcheck program uses a userid and password provided through a .my.cnf file under the user account that runs the job. After the upgrade, I started getting the following error message:

/usr/local/bin/mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

The problem was complicated by the fact that mysqlcheck runs just fine at the command prompt, but as a cronjob running under the same user it failed with the error message above.

After digging around, I found a new option that must be specified on the command line to get mysqlcheck to use the .my.cnf file - the option is "--defaults-file=/root/.my.cnf". Without this, the .my.cnf file will not be read by mysqlcheck.

If you are looking for a handy line to drop in /etc/crontab to check your database for consistency every night on FreeBSD, try the following:

30 4 * * * root /usr/local/bin/mysqlcheck --defaults-file=/root/.my.cnf --all-databases --auto-repair 2>&1 | grep -v "OK" | mail -E -s "Database Problem" root

This cronjob will only notify you when there is a problem, which keeps the email "signal to noise ratio" down when you are running several servers.

Dave

Posted by richard targe on November 29 2008 7:42pm[Delete] [Edit]

I had the same problem for remote connections: Access denied with error code 1045. My solution was (similar to adam b's comment) to re-affirm the password with set password command (and make sure to encrypt the password with the built-in password() utility. Just to be on the safe side, each time you create a new user, who will connect remotely to the server, after creating proper entries in the grants table, make sure to set the password again with set password command.

Add your own comment.