0

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.

Community
  • 1
jww
  • 11,918
  • 44
  • 119
  • 208

2 Answers2

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

u1686_grawity
  • 426,297
  • 64
  • 894
  • 966
  • 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 02 '16 at 11:25
0

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

Olle Kelderman
  • 1,053
  • 1
  • 9
  • 14
  • *"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 02 '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 02 '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 02 '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 02 '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 02 '16 at 12:07
  • You are authenticated with your *linux account* NOT your *MySQL account* those two are NOT the same – Olle Kelderman Apr 02 '16 at 12:08
  • *"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). – jww Apr 02 '16 at 12:13
  • ok, that was absolutely not clear for me while reading the question, but sure, I'm guessing the answer by @grawity will probably work – Olle Kelderman Apr 02 '16 at 12:15
  • 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). – jww Apr 02 '16 at 12:28
  • @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. – u1686_grawity Apr 02 '16 at 13:26