How to make a mysql root user to login
interactive with mysql cli passwordless
I'm using access to the mysql servers via localhost with mysql cli
on daily basis.
With time I've figured out that it's pretty unahandy to always
login with my root mysql password, I mean each time to enter it,
e.g.:
root@mysql-server:~# mysql -u root
Enter password:
...
Thus to make my life a way easier I decided to store my mysql root
password in order to allow my root admin user to be able to login
to my mysql server without asking for password. This saves time and
nerves, as I'm not supposed to look up for the password file I
store my server mysql root pass.
To allow my mysql cli interface, to login passwordless to the SQL
server I had to create the file
/root/.my.cnf readable only
for my root user and store my MySQL username and password
there.
Here is a sample
/root/.my.cnf file:
root@mysql-server:~# cat /root/.my.cnf
[client]
user="root"
pass="mysecretMySQLPasswordgoeshere"
Now next time I use the mysql console interface to access my mysql
server I don't have to supply the password, here is how easier is
the mysql login afterwards:
root@mysql-server:~# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3520
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The only downside of using
.my.cnf to store permanently the
mysql server root and password is from security standpoint.
If for instance somebody roots my servers, where I have stored my
root user/pwds in
.my.cnf , he will be able immediately to
get access to the MySQL server.
Another possible security flaw with using the mysql passwordless
login "trick" is if somebody forgets to set proper file permissions
to,
.my.cnf
Once again the file should possess the permissons of:
root@mysql-server:~# ls -al /root/.my.cnf
-rw------- 1 root root 90 Apr 2 00:05
/root/.my.cnf
Any other permissons might allow non-privileged users to read the
file and gain unathorized admin access to the SQL
server.