Config file revamp

[tsroten]

In mycli, managing the config has been challenging for awhile since we have five places a user can configure their experience, three of which are config files.

1. The ~/.my.cnf file ([client] section)
2. The login path file, e.g. ~/.mylogin.cnf
3. The mycli config file, ~/.myclirc
4. The command-line arguments
5. The commands in mycli

This issue documents the overall revamp of the config that we're trying to accomplish for version 2.

Tasks

• Use CLI Helpers to read and validate the config files, see Add config file reading/writing cli_helpers#39
• Store config files on macOS and Linux according to the XDG spec. Windows config files should be in a Windows-appropriate spot and not in the home folder.
• Make config file options consistent across pgcli and mycli, see Consolidate config options pgcli#835
• Add missing configuration options to mycli's config file, e.g. ssl, pager command, etc.
• Add a migration tool that migrates config settings from .my.cnf and .mylogin.cnf to the mycli config file (allows user input to override this feature and control password migration)
• Remove support for .my.cnf and .mylogin.cnf -- "one config file to rule them all"
• Store log and audit log files according to the XDG spec.
• Automatic config file permission checks -- warn or reject files that are group/world readable
• Fix favorite query config file reading/writing: Favorite queries not loaded from or saved to config file #669

Missing options in mycli's config file to consider adding

These options are currently supported in mycli via the .my.cnf file, so we need to consider adding them to mycli's config file.

• socket
• default-character-set
• ssl-ca
• ssl-cert
• ssl-key
• ssl-cipher
• ssl-mode
• local-infile
• pager (the command itself)

Assorted Background Notes

Previous config-related discussions:

• Read more options from my.cnf #281
• System wide myclirc #161
• Support for my.cnf #17

The desire is to balance these three things well:

1. Mycli's config is user-friendly
2. Switching to mycli from MySQL is a seamless experience
3. Mycli and pgcli have a consistent experience

There has been extensive discussion about the false sense of security that the encrypted login path file provides users. Many users have noted how it's not a security feature. In fact, if you have a copy of someone's login path file, you can decrypt it with a single command:
python -c 'from mycli.config import *;print(read_config_file(open_mylogin_cnf(get_mylogin_cnf_path())))'

Mycli now supports storing host/port/database information in its config file: http://www.mycli.net/loginpath#dsn

Another long-term goal is keyring support so that users can use their system's password manager with mycli.

[rixx]

Any timeline on this? Seeing as mycli just fails completely if it is not allowed to create ~/.myclirc is a bit annoying, especially since you can't set the location via an env variable (only via a command flag). (Same goes for log files.)

[greenskeleton]

Removing support for my.cnf does not make sense to me, for a MySQL tool. I find it very useful to use the same configuration file with both mycli and the mysql command line client in scripts. This is also a nice selling point to new users who are mostly familiar with the standard client.
Without support for my.cnf, I would consider a fork or some alternative software. I would rather continue using mycli, as it is a very nice alternative to the standard client.

[pasenor]

@greenskeleton removing my.cnf was never a deliberate decision, it stopped working because of a bug. We just released version 1.24.1 to pypi that should have this functionality restored.

[greenskeleton]

@pasenor I did happen to catch the commit in master that fixed my.cnf prior to the release today, thanks! I was referring to the task from first issue comment:

* [ ]  Remove support for `.my.cnf` and `.mylogin.cnf` -- "one config file to rule them all"

[danisztls]

@rixx You can move ~/.myclirc to ./config/mycli/myclirc or $XDG_CONFIG_HOME/mycli/myclirc as implemented by #808.

Following that. Shouldn't the app also look for .mycli.log and .mycli-history at $XDG_DATA_HOME/mycli? As far as I know this last behavior is not implemented.

[darioseidl]

Removing support for my.cnf does not make sense to me, for a MySQL tool. I find it very useful to use the same configuration file with both mycli and the mysql command line client in scripts. This is also a nice selling point to new users who are mostly familiar with the standard client.
Without support for my.cnf, I would consider a fork or some alternative software. I would rather continue using mycli, as it is a very nice alternative to the standard client.

I agree with this. Please don't remove support for the .my.cnf file. I have many .my-project.cnf files with the correct defaults for my different projects, and I symlink .my.cnf to .my-project.cnf to use mysql, mycli, mysqldump without having to specify credentials, default database, and other project specific options. If mycli would stop reading the .my.cnf file I would have to create a separate mycli config for each project which would be a real pain.

Edit: I came here looking for a way to set a default database for mycli in the .my.cnf file. The problem is mycli only reads the [client] section, but, for example, mysqldump doesn't accept a database=foo option in under [client], only under [mysql].

IMO, reading the .my.cnf file is a great feature, to make transitioning from the default mysql client easy and to support using multiple tools (mysql, mycli, mysqldump) with the same defaults. For that to work properly, mycli should treat the file the same way as the other tools, e.g. database must go under [mysql], and mycli should read it from there. There was a discussion about this here too.

Edit2: an easy workaround to read the database from the .my.cnf file [mysql] section and pass it to mycli:

mycli() {
  if [ "$#" -eq 0 ]; then
    database="$(grep --only-matching --max-count=1 --perl-regexp '(?<=^database=)[^;]+' ~/.my.cnf)"
    /usr/bin/mycli $database
  else
    /usr/bin/mycli "$@"
  fi
}