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.

2
  • Just as a matter of interest, why don't you want a password? May 20, 2010 at 2: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.
    – user402059
    Oct 26, 2017 at 13:33

6 Answers 6

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.

5
  • 1
    I strongly agree with the setting your environment to automatically authenticate you vs removing the password.
    – Zoredache
    May 19, 2010 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, 2011 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, 2011 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. Sep 8, 2016 at 16:44
  • Missing mv ./+%Y%m%d.$db.sql $OUTPUT` between mysqldump and gzip
    – Siddharth
    Jun 8, 2019 at 6: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.

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, 2018 at 9:06
  • 1
    The slides are okay, but this is the actual documentation: mariadb.com/kb/en/library/authentication-plugin-unix-socket
    – JonnyJD
    Jul 4, 2018 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
3
  • in this case, dont forget to "flush privileges;" May 19, 2010 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, 2010 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 6, 2018 at 10:20
5

You can also do:

grant all privileges on *.* to 'root'@'localhost' identified by '';
4

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.

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 ''

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .