MariaDB - enable root login via both UNIX_SOCKET and TCP

With MariaDB by default the root user has the plugin unix_socket enabled:

SELECT Plugin FROM user WHERE User = "root";

| Plugin      |
| unix_socket |

From the documentation:

  • In this example, a user is already logged into the system and has full shell access. Because he has identified himself to the operating system, he does not need to do it again for the database — MariaDB trusts operating system credentials. But he cannot connect to the database as another user.

This is useful. However we also run PHPMyAdmin which requires the root username and password entering to connect to MariaDB, and this login is failing because the root user has the unix_socket plugin specified.

If we remove the unix_socket from the root user, PHPMyAdmin then works with a regular username/password combination, but we also have to enter the username and password each time when connecting to MariaDB from the command line.

Is there a solution where both can be used in-conjunction with each other?

For security we don’t really want to store the root password in plaintext in a ~/.my.cnf file.

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Accepted Answer

Hi @DigitalCarrot

Why don’t you create two root users? As long as the Host+User is unique, then it’s allowed.

But from a security point, I would would say /root/.my.cnf is a clear winner compared to phpMyAdmin. If you look at how many security flaws there has been in PMA, PHP, Apache and MySQL combined versus the chmod/chown commands of the Unix-world, then you wouldn’t doubt how it works.

I’m guessing you’re running PMA over HTTPS, and storing all company passwords in a password manager - if not, then that’s a much bigger security problem.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel