How to change MySQL root password to default?
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
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