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'

Answer 1

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

Answer 2

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