How to have MySQL entitle the machine’s root user

centosMySQLpermissionsroot

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.

Best Answer

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

Related Question