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