How to Configure MariaDB Client with Host-Specific Credentials

clientmariadbmariadb-10.2

How can I configure my MariaDB client (mysql) so that I don't have to enter a password to connect, regardless of which host I am connecting to?

I can't just put a password in the [client] section of ~/.my.cnf because that would apply all hosts.

For example, I'd like to be able to connection with mysql -h myhost mydatabase, ie. without specifying username or password. Even better would be the ability to specify a "host alias" instead of the actual (awkwardly long) hostname.

It sounds like this is possible in MySQL with the mysql-config-editor but MariaDB does not have this feature.

Best Answer

In ~/.my.cnf make sections like:

[clientmy_host_name_1]

[clientmy_host_name_2]

Then make an alias (assuming Linux-like OS - depending on shell, you can perhaps put the alias in ~/.bashrc_local):

alias mysql="mysql --defaults-group-suffix=\`hostname --short\`"

Now you can connect without specifying the password to any of the hosts for which you have stored the credentials in ~/.my.cnf. (These would be my_host_name_1 and my_host_name_2 in my example above.)

Related Solutions

MariaDB PAM Authentication – Configure and Enable Remote Login from Toad

I have downloaded HeidiSQL and confirmed that its use of libmysql.dll means that it can use the PAM authentication. It looks like toad for MySQL utilises myodbc.dll which cannot handle PAM authentication

MariaDB – How to Configure Logging for Row Deletions Only

This is mostly copied from the docs:

Trigger Example - Logging deletions

Scenario: We want to keep a log file containing data from rows that have been deleted from the BOOKS Table. Here's a Trigger definition that accomplishes this:

CREATE TRIGGER Books_Delete
AFTER DELETE ON Books                 /* See note 1 */
  REFERENCING OLD ROW AS Old          /* See note 2 */
FOR EACH ROW                          /* See note 3 */
  INSERT INTO Books_Deleted_Log       /* See note 4 */
      VALUES (Old.title);             /* See note 5 */

The Trigger action has to be AFTER, since the Trigger action includes a SQL-data change statement. You should also replace Books with your table name
It is conventional to use the alias "Old" or "Old_Row" for the old row.
No log will occur for a DELETE statement that affects zero rows.
Replace Books_Deleted_Log with the name of the table you're logging the deleted rows into. Make sure this table has all the columns you need to log the information you need.
OLD is an alias for a single old row, so OLD.TITLE is the scalar value derived from the TITLE Column of that old row. You will have to change Title to a column you want to track. You can also add more columns by following the Old.<column name> pattern using commas as separators.

Best Answer

Related Solutions

MariaDB PAM Authentication – Configure and Enable Remote Login from Toad

MariaDB – How to Configure Logging for Row Deletions Only

Related Question