4

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 0:08
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?

  • 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 1: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 1:04
  • Sorry, I tried to login with: "mysqladmin -u root -p password", couldn't edit the comment – Scarass Dec 26 '16 at 1:07
  • Sorry for too much text in a comment – Scarass Dec 26 '16 at 1:11
  • 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 1:59
1

You should run :

# mysql_secure_installation

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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