mysql 密码正确 被拒绝http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

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, 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> mysqlERROR 2003: Can't connect to MySQL server on 'host_name' (111)shell> mysqlERROR 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 script. For details, see         Section 2.12.1, “Unix Postinstallation Procedures”.       

  To determine whether you need to initialize the grant tables,          look for amysql 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 nameduser.MYD in          the mysql database directory. If not,          execute themysql_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.12.2, “Securing the Initial MySQL Accounts”.       

• If you have updated an existing MySQL installation to a newer          version, did you run themysql_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> mysqlClient does not support authentication protocol requestedby server; consider upgrading MySQL client

            For information on how to deal with this, see          Section 5.3.2.3, “Password Hashing in MySQL”, and          Section C.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.3.3, “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          incorrectroot password:       

  shell> mysqladmin -u root -pxxxx verAccess 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, seeSection C.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         GRANT statements or the         mysqladmin password command. Each of those          automatically usesPASSWORD()          to encrypt the password. See         Section 5.5.5, “Assigning Account Passwords”, and         Section 12.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 localmysqld 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 withmysql -u          user_name, you may have a          problem with theuser 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 theuser          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 theuser 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 theglibc library than          the one you are using. In this case, you should either upgrade          your operating system orglibc, 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 verAccess 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 name cache. SeeSection 7.9.8, “How MySQL Uses DNS”.       

  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 tolocalhost.              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 inAccess          denied (where          your_hostname is the actual host          name of the local host), you may not have the correct name for          your host in theuser table. A common          problem here is that theHost 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 theHost column value.          (Alternatively, you could add an entry to the         user table 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 butmysql -u          user_nameother_db does not, you          have not granted access to the given user for the database          namedother_db.       

•           If mysql -u          user_name works when          executed on the server host, butmysql -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 user name from the remote          host.       

•           If you cannot figure out why you get Access          denied, remove from theuser          table all entries that have Host values          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 with         Host='localhost' and         User=''. Because that          entry has aHost value          'localhost' that is more specific than         '%', it is used in preference to the new          entry when connecting fromlocalhost! 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. See          alsoSection 5.4.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 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 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 root password          with anUPDATE 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 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 withmysql -u          user_namedb_name ormysql          -u user_name-pyour_passdb_name. If you are able          to connect using themysql 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, executemysqladmin          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          thedb or host table:       

  Access to database denied

            If the entry selected from the db table has          an empty value in theHost 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 thehost 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         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 themysqldump mysql command. 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          restartmysqld with         --skip-grant-tables to run         mysqldump.       

Previous /Next / Up / Table of Contents

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 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 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 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 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 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 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 Mustali Kachwala on March 24 2011 11:40am[Delete] [Edit]

ERROR 1045 (28000): Access denied for user

If you get following error while executing a OUTFILE query

mysql> select field1,count(*) into outfile '/tmp/temp.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from table group by field1;
ERROR 1045 (28000): Access denied for user 'test'@'10.0.3.%' (using password: YES)

then the most probable cause reason is that the entry for the username used by you does not have the FILE privilege enabled in the user table.

Read Full Details @ http://mustalikachwala.blogspot.com/2011/03/mysql-error-1045-28000-access-denied.html