MySQL root account without password?

Question

I've just installed MySQL server with no password on root account. I was prompted to enter root password 3 times and I left it blank all three times because I don't want to use password. But now it seems to me that may not be such a great idea.

I try to connect from Workbench (with now password entered) and I get error: Access denied for user 'root'@'localhost'. Why if there is no password??

I realize now, that if I only have had provided some dummy password on installation I would have no problems no, so I try to set it by typing: mysqladmin -u root -p password; enter blank password, and again, get the following:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost'

If I just type "mysql" into console I get:

Access denied for user 'me'@'localhost' (using password: NO)

If I can have root account without password, and if I won't have much problems in that case, than I'd like to leave it without pass, but if that isn't good, than please tell me how set password. And if it's good to leave it without password then please tell me how to connect without pass :)

EDIT: Linux distro: Mint Cinnamon 64-bit; MySQL version: 5.7

Add please MySQL version too. – Rui F Ribeiro Dec 26 '16 at 00:08 — Rui F Ribeiro, Dec 26 '16 at 00:08

score 7 · Answer 1 · edited Apr 13 '17 at 12:36

7

By default Debian/Ubuntu/Mint has a user with administrative accesses, namely used to shutdown MySQL, called debian-sys-maint.

Go to the file /etc/mysql/debian.cnf which has as the format:

[client]
host     = localhost
user     = debian-sys-maint
password = XXXXXXXX
socket   = /var/run/mysqld/mysqld.sock

Take from there the password, that is randomly generated when installing MySQL (e.g. it differs between servers);

And login into your MySQL with:

mysql -udebian-sys-maint -pXXXXXXXX

After that, you can change your root password.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Be aware that this changed slightly in MySQL 5.7. Have a look at How to change MySQL 'root' password using MySQL v5.7?

edited Apr 13 '17 at 12:36

Community

1

answered Dec 26 '16 at 00:22

Rui F Ribeiro

56,709
26
150
232

I got into MySQL monitor with the password you from the file you provided, got error: "Using a password on the command line interface can be insecure."; Okay moving on, – Scarass Dec 26 '16 at 01:04
then I typed: "update user set authentication_string=password('root') where user='root';", which returned: "Query OK, 1 row affected, 1 warning (0,00 sec) Rows matched: 1 Changed: 1 Warnings: 1" So "warning: 1" but I see no actual warning. Exited MySQL monitor, tried to login with: "update user set authentication_string=password('XXXX') where user='root';", got error: "Access denied for user 'mladen'@'localhost' (using password: NO)" – Scarass Dec 26 '16 at 01:04
Sorry, I tried to login with: "mysqladmin -u root -p password", couldn't edit the comment – Scarass Dec 26 '16 at 01:07
Sorry for too much text in a comment – Scarass Dec 26 '16 at 01:11
Have you used the method to change root password I am advising? – Rui F Ribeiro Dec 26 '16 at 01:12
Yes, "SET PASSWORD FOR 'root' = PASSWORD('root');" returned: "Can't find any matching row in the user table;" – Scarass Dec 26 '16 at 01:21
changed SET PASSWORD – Rui F Ribeiro Dec 26 '16 at 01:24
what? what do you mean? – Scarass Dec 26 '16 at 01:26
Edited the answer. – Rui F Ribeiro Dec 26 '16 at 01:29
Let us continue this discussion in chat. – Scarass Dec 26 '16 at 01:31
1

After changing/resetting the root password that way you have to either restart the mysql server/daemon (lazy way) or tell it to reload the permissions/authorization tables a simple FLUSH PRIVILEGES at the command prompt for mysql will do it. – ivanivan Dec 26 '16 at 01:59
can you provide a command that'll do it? tnx – Scarass Dec 26 '16 at 12:57

score 1 · Answer 2 · answered Dec 26 '16 at 05:33

1

You should run :

# mysql_secure_installation

You can set a root password for fresh installation of mysql server.

answered Dec 26 '16 at 05:33

supriady

167

1

I enter that command, asks me for root password, I enter it, get access denied. The problem is, that after all this, I'm not really sure what is the root password. I tried entering blank, the one from /etc/mysql/debian.cnf – Scarass Dec 26 '16 at 11:38
and the one from /etc/mysql/debian.cnf and the one I tried to set in MySQL monitor – Scarass Dec 26 '16 at 11:39
The root password should be blank when you didnt set any root password yet on mysql-server. mysql_secure_installation helped you to set root password for new installation of mysql server. – supriady Dec 26 '16 at 14:05

MySQL root account without password?

2 Answers2

Linked