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.