First of all it's important to understand that the
system root and the
mysql root don't have any relationship.
In a default MySQL install, the MySQL root user does not have a password. It would be quite useless to have one as you would not know it so you have to contact MySQL. Or it's public knowledge so it does not secure anything.
There are a few steps that are often advised (or at least I have seen them show up on a number of occasions). The first one is to set a password for root. The second one is to remove the anonymous user and the third one is to remove the test database.
Below the steps using the normal mysql client. I don't have experience with mysqladmin.
Code:
root@webserver:/etc/rc.d# mysql
mysql> use mysql;
^^
You're now using the mysql database that holds the permissions.
Note that I don't specify root with the -u option as the mysql client will try to determine the user based on his/her current system login. I think this only works in Linux/Unix and not in Windows but not 100% sure.
Code:
mysql> select host, user, password from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| webserver | root | |
| webserver | | |
| localhost | | |
+-----------+------+----------+
4 rows in set (0.00 sec)
mysql> delete from user where user <> 'root';
Query OK, 2 rows affected (0.01 sec)
^^
The first query shows all currently defined users and the second one deletes the 'anonymous' users.
Code:
mysql> update user set password=password('your_password’);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
^^
You have now set a password for the two root users (and they are identical).
Code:
mysql> delete from user where host <> 'localhost';
Query OK, 1 row affected (0.01 sec)
^^
I prefer to delete the root user that is not tied to localhost as there is, in my opinion, no need to access a mysql server as root from an external machine. One can always use e.g. ssh and run the mysql client from there.
Code:
mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *58364B2092FC2BF7A21616D9442D8250D2627B5B |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)
^^
Just to verify the result.
Code:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
^^
Flush the privileges so they are reflected; the alternative is to restart the mysql server.
You can now try to login as the root user with the new password.
Code:
root@webserver233:/etc/rc.d# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@webserver233:/etc/rc.d# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.37 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
PS
I do not really know why it's advised to remove the test database.
