24

How can I delete my password for MySQL? I dont want to have a password to connect to the database. My server is running Ubuntu.

treckstar
  • 103
  • 1
  • 1
  • 4
Ben
  • 775
  • 4
  • 9
  • 19
  • Just as a matter of interest, why don't you want a password? – John Gardeniers May 20 '10 at 02:31
  • For Example I run into errors when I run "dpkg-reconfigure phpmyadmin". It stops with error 1045 with access denied root@localhost "password: no" ending. –  Oct 26 '17 at 13:33

6 Answers6

40

Personally, I think instead it's better to set a password and save it in /root/.my.cnf:

First:

mysqladmin -u root password 'asdfghjkl'

Then edit root's .my.cnf file:

[client]
password = asdfghjkl

Make sure to chmod 0600 .my.cnf.

Now you have a password but you're no longer prompted for it. My default MySQL server install is a totally random unique password for each MySQL server, saved in the .my.cnf file like this.

freiheit
  • 14,544
  • 1
  • 47
  • 69
  • 1
    I strongly agree with the setting your environment to automatically authenticate you vs removing the password. – Zoredache May 19 '10 at 23:37
  • 3
    You may need quotes around that password in the .my.cnf. It didn't work for me without. – user67641 Mar 12 '11 at 21:07
  • 1
    @user67641: Might depend on version and what's in your password... Mine are all long random strings of alphanumerics (no special characters) and haven't needed quotes. – freiheit Mar 12 '11 at 21:09
  • 2
    If you need to create /root/.my.cnf, it's worth explicitly setting 0600 permissions on it to be sure no other user can snoop. Most of the time normal users can't descend into /root, but the extra paranoia is almost always warranted. – Dale C. Anderson Sep 08 '16 at 16:44
  • Missing `mv ./+%Y%m%d`.$db.sql $OUTPUT` between mysqldump and gzip – Siddharth Jun 08 '19 at 06:26
18

Yes, less passwords can be a good thing. But don't just open the database for everybody.

via unix_socket:

grant usage on *.* to 'root'@'localhost' identified via unix_socket;

This gives you passwordless mysql acces for a locally logged in root user. Btw. this is the default in recent ubuntu / debian releases.

some_user@box: mysql -u root  # denied
root@box: mysql               # good
some_user@box: sudo mysql     # good, if "some_user" has sudo rights.

Explanatory slides: https://www.slideshare.net/ottokekalainen/less-passwords-more-security-unix-socket-authentication-and-other-mariadb-hardening-tips.

Matthias Bloch
  • 281
  • 2
  • 2
  • 4
    Welcome to SF! Lovely first answer: clear, a good summary, a link for further exploration, and it adds something to the existing corpus of answers. I'm delighted to be your first upvote, and I hope you stay around to write more answers like this one. – MadHatter Jun 27 '18 at 09:06
  • 1
    The slides are okay, but this is the actual documentation: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/ – JonnyJD Jul 04 '18 at 15:47
10

If you DO have a password set for MySQL, follow the instructions at Recover MySQL root Password, and then set your password to null:

For 5.7.6 and later

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'

For 5.7.5 and earlier

update user set password=PASSWORD("") where User='root';

* needs a DB restart (see instructions at the link) for this to take effect.

sudo service mysql restart
Jonathan
  • 272
  • 1
  • 13
Stefan Lasiewski
  • 23,667
  • 41
  • 132
  • 186
  • in this case, dont forget to "flush privileges;" – Marco Ramos May 19 '10 at 22:51
  • @meyosef - please mark an answer as answered if it solved your problem (click on the green checkmark under the number of votes). – jneves May 19 '10 at 22:52
  • I thought I could use empty password just for Unix socket and not loopback, but it's not possible. The `unix_socket` plugin does a slightly different thing. – basin Aug 06 '18 at 10:20
5

You can also do:

grant all privileges on *.* to 'root'@'localhost' identified by '';
jkff
  • 293
  • 1
  • 5
  • 10
3

The IDENTIFIED VIA unix_socket statement is only for MariaDB.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket;

For MySQL, the same is achieved by IDENTIFIED WITH auth_socket.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED WITH auth_socket;

Only a difference of two words, but they're really not the same.

iBug
  • 1,212
  • 2
  • 13
  • 23
2

I'm pretty sure that by default there is no password if your the admin user and accessing it locally. Are you finding something different than that?

Does this work?

#> mysqladmin -u root password ''
djangofan
  • 4,182
  • 10
  • 46
  • 59