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.