Hi,
I've always been a bit puzzled by MySQL's initial setup. Right after installing MySQL, no password is set, so that's the first thing I care about. I know there's a way to do that with mysqldadmin, but hey, different ways lead to Saint-Bauzille-de-Montmel.
# chkconfig mysqld on # service mysqld start # mysql -u root mysql> use mysql; mysql> select user, host, password from user; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | root | localhost | | | root | buildbox | | | root | 127.0.0.1 | | | | localhost | | | | buildbox | | +------+-----------+----------+ 5 rows in set (0.00 sec)
This shows that in my initial setup, I have no less than five initial users. A root user for three different hosts (localhost, buildbox and 127.0.0.1), and an empty user for two different hosts (localhost and buildbox).
I don't know if I'm expected to set five passwords (five times the same? five different passwords?) for these five entities, but what I usually do is just get rid of every initial user except root@localhost. In that case:
mysql> delete from user where user = ''; Query OK, 2 rows affected (0.01 sec)
mysql> select user, host, password from user; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | root | localhost | | | root | buildbox | | | root | 127.0.0.1 | | +------+-----------+----------+ 3 rows in set (0.00 sec)
Here goes the empty user. Now for the remaining two:
mysql> delete from user where host = 'buildbox'; Query OK, 1 row affected (0.01 sec)
mysql> delete from user where host = '127.0.0.1'; Query OK, 1 row affected (0.00 sec)
Which leaves me with only root@localhost:
mysql> select user, host, password from user; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | root | localhost | | +------+-----------+----------+ 1 row in set (0.00 sec)
And now I can define a password for that single initial user:
mysql> set password for root@localhost = password('my_secret_password');
mysql> select user, host, password from user; +------+-----------+------------------+ | user | host | password | +------+-----------+------------------+ | root | localhost | 2d97271970b60f82 | +------+-----------+------------------+ 1 row in set (0.00 sec)
mysql> quit;
And from now on, I can safely connect to the MySQL monitor using mysql -u root -p.
I've done things like this for the past two or three years, and just now I wonder: am I doing something silly here? After all, maybe these other initial users have some obscure reason to be there in the first place?
So I thought: why not ask?
Niki