sysadmin - How to change MySQL root password to default?

Question

My initial installation for the MySQL had no password for root. I assigned a password for root and everything worked fine. Due to some reason (don't ask why) I had to revert back to the original settings where root didn't have any password.

I changed the root password to '' (empty string). The problem now is that MySQL doesn't run with 'mysql -uroot', it expects a password. On running 'mysql -uroot -p' and hitting enter on the password prompt gets me into the db, but is not same as the default setting.

Is there any other flag/setting that I am missing to set/unset which tells mysql to not expect a password?

Thanks

Answer 1

How to Reset MySQL root password:

To reset it, see How to Reset the Root Password in the MySQL manual.

How to run MySQL without password controlled access

To run MySQL with the password controls disabled, check out the --skip-grant-tables option.

How to avoid typing credentials on the command line

If you simply want to avoid having to type the password on the mysql command line, create a file called .my.cnf (note leading period on that filename!) in your home directory. In your case, for the root user, probably /root

Add the following entries to the file

[client]
host=localhost
user     = root
password = mypassword
database = mydatabase

You'd typically chmod 600 this file so that only you can see it, and MySQL will ignore a .my.cnf which is world writable anyway.

See the manual page Using Option Files for more information