LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)
-   -   MySQL Initial User Logon ID (https://www.linuxquestions.org/questions/linux-software-2/mysql-initial-user-logon-id-746757/)

swamprat 08-11-2009 11:35 AM

MySQL Initial User Logon ID
 
I'm running Suse 11.1.

MySQL is installed.

I downloaded and installed the software for MySQLAdmin and MySQLBrowser, this was one software package so both got installed at the same time.

My question is this; When I log into MySQL to use the Admin Tools it doesn't like my root password. So I log in as 'localhost' with the id of 'root' and no password and I get right into the main Admin menu.

Why doesn't it accept the root password and how can I change the root login to need to have a password.

Will just changing the password in MySQL Admin do the trick?

Thanks

Wim Sturkenboom 08-11-2009 12:30 PM

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.

swamprat 08-12-2009 12:20 PM

Your answer and description regarding my issue is just terrific.

One could only hope that everyone who posts replies makes things this clear and with examples to explain the details.

Much thanks.

chrism01 08-12-2009 09:47 PM

Removing the test db is just for tidiness. It doesn't do anything. I think they just supply it for convenience.

Wim Sturkenboom 08-13-2009 12:01 AM

Quote:

Originally Posted by swamprat (Post 3640674)
Your answer and description regarding my issue is just terrific.

One could only hope that everyone who posts replies makes things this clear and with examples to explain the details.

Much thanks.

Thanks.

You were lucky. I did write a setup document once for a server so it was a matter of copy and paste. Else it probably would not have been as complete as now.

swamprat 08-13-2009 11:49 AM

Wim,

That's what it's all about; being at the right place at the right time.


All times are GMT -5. The time now is 06:44 PM.