I'm trying to run mysqlcheck. Its failing with:

$ sudo su -
# mysqlcheck --auto-repair --all-databases
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

Other questions and answer say to modify or reset MySQL passwords. I don't really want to do that since I don't know the impact. If there are any scripts using it, then I will break them. At minimum, I have to share it with two other [part-time] system administrators, so its mostly a pain.

What I would like is for MySQL to recognize the machine's root user (uid 0) and entitle it with all the privileges bestowed upon root.

I'm working on CentOS 7.2. How do I have MySQL entitle the root user?


Here are some related questions. They all lead back to modifying the password.

In MySQL 5.5+ you can use the auth_socket authentication plugin for this:

CREATE USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

MariaDB 5.2.0+ has a similar unix_socket plugin – as far as I know, it is even active by default:

INSTALL PLUGIN unix_socket SONAME 'auth_socket';
CREATE USER root IDENTIFIED VIA unix_socket;

Similarly, PostgreSQL has the "local" auth method in its pg_hba.conf and enables it by default.

On Unixes, the general mechanism is often called "peercred", as in SO_PEERCRED. (The Windows equivalent is often called "Windows Native Authentication".)

  • Thanks @Grawity. You're knowledge of Linux and Unix is amazing. I need to check on doing the above (naively, I though I'd flip a bit in mysql.conf). I'll have to get back to you an the accept. – jww Apr 2 '16 at 11:25

Based on what you said I am going to make the assumption that your MySQL user 'root'@'localhost' has a password since you mentioned sharing with other people. And because of that I'm als assuming you still know that password.

Now let's have a look at what the error-message says:

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

So what I am seeing here is you are trying to connect to MySQL without providing a password while the mysql-user has a password. Obviously that does not work.

I bet mysqlcheck --auto-repair --all-databases -p will work. It'll ask for the password and you should be good to go.


Having said all that I strongly advise against using the MySQL-root account for other things than administrating the MySQl server (since you mentioned If there are any scripts using it), just make another MySQL-user for that script with the appropriate permissions. And if you ever really need to reset your root-password, telling two other colleagues shouldn't be that much of an issue right? If it is I would evaluate your companies communication-methods....

  • "using the MySQL-root account ..." - Actually, its the machine's root user; and not a MySQL account. Effectively, I'm asking for some MySQL integration into the local Linux installation's security mechanisms. – jww Apr 2 '16 at 12:02
  • sure its the machines root user, but that does not change the fact that MySQL just uses whatever the machines username is if none provided. Effectively you still use the MySQL root account – Olle Kelderman Apr 2 '16 at 12:04
  • "mysqlcheck --auto-repair --all-databases -p" - that prompts for a password even when running as the root user with sudo su -. – jww Apr 2 '16 at 12:04
  • 1
    So basically this question is "How do I login to MySQL with an MySQL-user that has a password without using the password"? – Olle Kelderman Apr 2 '16 at 12:05
  • Well, I already used the password with sudo su -. The elevation has been authenticated, and the new security context is available. What I would like is for MySQL to honor or use it. – jww Apr 2 '16 at 12:07

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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