0

I have mysql.user table with this content:

select host, user, password from user;

| localhost  | root | *75267AEB7355CEEE80EAB92D19FF50095AD9BB3E |
| linux      | root |                                           |
| localhost  |      |                                           |
| linux      |      |                                           |

Is is safe to remove last 3 lines? since I never login to database without password? or those are default users for MySQL on windows that should not be deleted?

1 Answer 1

2

Disclaimer: If you have important data on your server, backup before you perform any of these operations. I did not exhaustively test my solution.

To answer this, I did the following on Windows and Linux

(example shown is for Linux)

  • Logged on - selected from user table.

  • Deleted all users except the root one with password.

  • Logged out, then logged back in again.

  • Checked that I could perform various operations - they all worked (more than shown - e.g. create table).

So, the answer to your question is that yes, you can do what you ask.

But, I would also say that logging on as root on a regular basis is poor practice (if your data is important). If it's a dev/test server, that's not an issue.

Operations Performed.

mysql> select host, user, password from user;
+-----------------------+----------+-------------------------------------------+
| host                  | user     | password                                  |
+-----------------------+----------+-------------------------------------------+
| localhost             | root     | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
| localhost.localdomain | root     |                                           |
| 127.0.0.1             | root     |                                           |
| ::1                   | root     |                                           |
| localhost             |          |                                           |
| localhost.localdomain |          |                                           |
| localhost             | bugs     | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
| %                     | bugs     |                                           |
| localhost             | puser    | *9F8D1F4C5ECC93C1D04F6BF04508FEDE4A9CF7F3 |
| localhost             | MY_USER  | *B678380CFA4459BB48E7B66AE1341F5F5B97D14E |
| localhost             | testuser | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
| 12.23.*.*             |          |                                           |
| 85.*.*.*              |          |                                           |
+-----------------------+----------+-------------------------------------------+
13 rows in set (0.00 sec)

mysql> 

So then I did (deleting all records except the first one).

mysql> delete from user where host <> 'localhost';
Query OK, 7 rows affected (0.00 sec)
mysql> delete from user where user <> 'root';
Query OK, 5 rows affected (0.00 sec)

And the I selected from the user table.

mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

Exited and then logged in again.

[pol@localhost inst]$ !904
./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root -p
Enter password:     
mysql> use test;

Database changed
mysql> show tables like 'z%';
+---------------------+
| Tables_in_test (z%) |
+---------------------+
| zsample             |
| zz                  |
+---------------------+
2 rows in set (0.02 sec)

mysql> 

So, I can still select - but can I perform other operations?

mysql> select * from zsample;
+------+
| x    |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

mysql> delete from zsample where x = 3;
Query OK, 1 row affected (0.05 sec)

mysql> select * from zsample;
+------+
| x    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.