Use linux account to get secure connection to MySQL and MariaBD without password

No need to remember your password to connect to a linux server, SSH keys provide authentication. We can have the same thing on MySQL and MariaDB.
With a plugin, the database server trusts the credentials of the linux system, you only need mysql account with the same name as the linux account and the password disappears from the connection. It is ideal on a development server with multiple users and it greatly facilitates the management of accounts, even if we forget to delete the mysql account, deletion of the linux account is enough.

This article will cover MySQL and MariaDB. There are similar but query syntax have minor difference. The auth_socket plugin is available from MySLQ 5.5 and MariaDB 5.2. I tested query with MySQL 5.5 and MariaDB 5.5 and 10.1.

First we need to enable the auth_socket plugin; a simple query is enough and must be run with root user.

MariaDB [(none)]> INSTALL SONAME 'auth_socket.so';
 
mysql> INSTALL PLUGIN auth_socket  SONAME 'auth_socket.so';

We can check that auth_socket plugin is enable with this query:

SELECT * FROM mysql.plugin;

Then you need to create user using the plugin, password is not needed.

MariaDB [(none)]> CREATE USER 'ulrich'@'localhost' IDENTIFIED VIA unix_socket;
MariaDB [(none)]> CREATE USER 'muriel'@'localhost' IDENTIFIED VIA unix_socket;
MariaDB [(none)]> FLUSH PRIVILEGES;
 
mysql> CREATE USER 'ulrich'@'localhost' IDENTIFIED WITH auth_socket;
mysql> CREATE USER 'muriel'@'localhost' IDENTIFIED WITH auth_socket;
mysql> FLUSH PRIVILEGES;

That's all, now linux user ulrich and muriel can connect to MySQL or MariaDB without password.

ulrich@eeebox:~$ whoami
ulrich
 
ulrich@eeebox:~$ mysql -uulrich
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 233
Server version: 5.5.39-MariaDB-1~wheezy-log mariadb.org binary distribution
 
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> exit
Bye
 
ulrich@eeebox:~$ mysql -umuriel
ERROR 1698 (28000): Access denied for user 'muriel'@'localhost'

It's so easy to setup, it's avoid create password for user they will forget. Of course this can't be apply if you are using software like phpmyadmin or adminer to access your database.