Configuration to stop typing MySQL / MariaDB passwords

Since a few months I'm full-time devops and I spend a lot of my time juggling between the different MariaDB servers. So I constantly have to type extended commands with the host, the user, the name of the database to connect to different instances.

To connect to dev database I have to type this command.

~$ mysql -hsql.local.dev -uulrich -p blog

TIPS: never put your password in command line, it will be exposed in your server log or in the process list via ps command.

But it's possible to shorten this command with use of .my.cnf file at the rootdir of your user account.

[mysql]
pager  = grcat ~/.grcat
 
[client]
user = ulrich
password = "none!why?"
database = blog
host = sql.local.dev
 

If you are curious of the first line, you can read my article about how to colorize your MySQL/Mariabd client.
"client" group will only be read by MySQL or MariaDB client. So my previous command could be shorten to:

~$ mysql

[mysql]
pager  = grcat ~/.grcat
 
[clientdev_blog]
user = ulrich
password = "none!why?"
database = blog
host = sql.local.dev
 
[clientprod_blog]
user = ulrich
password = "it'sSoUseless"
database = blog
host = sql.exemple.com
 

This time there are two groups with "client" prefix to be sure that only MySQL client read them. On each group, I've defined user, password, database name and host.
to use this setup, I have to add --defaults-group-suffix tomy command line:

~$ mysql --defaults-group-suffix=dev_blog

Now I only need to remember the name of the group and the long option name. Because I'm lazy, I add a small snippet to my .bashrc file to avoid remember the option and only have to type the group name:

function mysqlgr {
     mysql --defaults-group-suffix="$1"; 
}

So my new command is:

~$ mysqlgr dev_blog

That's nice, but it's have some limits. I can't use this to query the database with option like -e. This command will do nothing:

~$ mysqlgr dev_blog -e "show create table test;"

There is still one big problem. Your passwords are readable in the file, so you have to take care of changing rights on the file with chmod 600 or 400, so other user can't access it.

Everything in this article can be use with MySQL and MariaDB and maybe other client like mycli or percona.
There is one more solution with mysql_editor_config. This tool wlll create a .mylogin.cnf file instead of .my.cnf. The big plus is that your passwords will be ciphered in the file. Unfortunately we can't associate a user to a database and it's only working with MySQL.

Another approach could be to use MySQL, MariaDB authentication based on linux account.