is mysql password update dependent on the user account on linux?

Hi Mysql and Linux gurus!

I have run into a weird behavior issue with Mysql server install on my local machine. Here is the history -

• I normally operate as a non root user, and use su to get root priviledges for installs.

• I installed MySQL 5.1 server on my Fedora Linux machine 13

• I made it necessary for MySQLs root user to have a password when connecting using the mysql client from localhost

• This is what i need - I need to test a shell script which calls the mysql client without username and password, I need to remove the password for mysqls root user. Also (importantly), the script should run from the root user of my computer.

  mysql -D dbname < script.sql

• This is what i did - From my non-root linux account i started mysql client, logged in as root@localhost, and ran the following command to remove the root@localhost password

  update user set password = PASSWORD('') wh开发者_开发技巧ere user = 'root' and host = 'localhost';

• The issue! - Now from my non-root unix account when i run just mysql it successfully logs me in. (so far so good). But when i do su, login as linux root (super user) and run mysql - I get

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Post Script - I have logged in with an alternate account from the super user account and re-run the query to update the user, It still didn't work. How come I can run mysql client without username and password as a non-root user, but cannot do the same as a linux super user???

Any help will be appreciated

After updating GRANTS or the mysql tables directly, many people forget to execute FLUSH PRIVILEGES.

Only after FLUSH PRIVILEGES, changes take effect.

MySQL accounts are completely independent from system accounts. They may coincidentally have the same usernames (like the default root account being the system AND mysql super-user accounts).

Once you've set a password on an account in mysql, you have to force the mysql monitor (mysql command) to prompt for a password (-p) option, and possibly specify your MySQL username (-u option) if your MySQL account name doesn't match your Linux username:

In other words:

mysql -u root -p

will tell MySQl to try and log you into the mysql root account, and to prompt for the account's password.