How to have MySQL entitle the machine's root user?

Question

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.

• mysqldump Error 1045 Access denied despite correct passwords etc
• mysqldump: Got error: 1045 Access denied
• mysql how to fix Access denied for user 'root'@'localhost'

Solution

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".)

Comments

[jww]

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]

"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.

[Olle Kelderman]

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

[jww]

"mysqlcheck --auto-repair --all-databases -p" - that prompts for a password even when running as the root user with sudo su -.

[Olle Kelderman]

So basically this question is "How do I login to MySQL with an MySQL-user that has a password without using the password"?

[jww]

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.

[Olle Kelderman]

You are authenticated with your linux account NOT your MySQL account those two are NOT the same

[jww]

"You are authenticated with your linux account ..." - right. That's what this question is about :) How do I get my SQL to honor or use the security context. (And I realize they are not the same).

[Olle Kelderman]

ok, that was absolutely not clear for me while reading the question, but sure, I'm guessing the answer by @grawity will probably work

[jww]

Yeah, the uid=0 was the indicator that I was trying to use the local security context because of the confusion between local accounts and MySQL accounts. How do you suggest I reword it? Or maybe, make an edit so its abundantly clear? (Grawity's peercred did not even turn up in my searches).

[user1686]

@Olle: FWIW, several other database systems do consider these to be nearly the same – e.g. Postgres has been using peercred by default since long ago, and MS SQL likewise uses Windows' native authentication.