Background

When running a batch file on MySQL the login credentials must be taken from some file. The file choices are to use a clear-text options file or the more secure obfuscated login-file generated by the MySQL program mysql_config_editor (which comes installed with the MySQL Community edition). The documentation for the mysql_config_editor can be found at https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html (MySQL V. 8.0). Provided here is an example of mysql_config_editor being used.

Setup

The starting point for this example will be to mention that the MySQL installation does not automatically add a .mylogin.cnf file into your directory (Figure 1).

MySQL mysql_config_editor Screenshot
Figure 1 – The MySQL installation does not automatically create a .mylogin.cnf file

Next, even though the default login-path will be “client” there does not automatically exist a login-path with that name (Figure 2).

MySQL mysql_config_editor Screenshot
Figure 2 – Since there is no .mylogin.cnf file there are no default login-paths

The print –all statement will come up again later in the example. For now, it’s important to note that it doesn’t return anything.

The Set Command

Now that some setup has been taken care of, the example can continue with creation of the first login-path. From the MySQL documentation it is known the program command format is as follows:

“mysql_config_editor set –login-path=LoginPathName –host=HostName –user=UserName –password ”

where additional options such as –port –socket and –help can be included.

The example in Figure 3 uses the above format. For the first login-path “client” is used, which will be what the mysql client program defaults to when no login-path is provided.

When the mysql_config_editor program is run with the “set” command for the first time, a .mylogin.cnf file is generated (Figure 3).

MySQL mysql_config_editor Screenshot
Figure 3 – A .mylogin.cnf file has been created after the use of the “set” command in mysql_config_editor

For this example, a second login-path will be created with the name “someLoginPath” as a generic stand-in label.

MySQL mysql_config_editor Screenshot
Figure 4 – A second login-path is added to the .mylogin.cnf file

The Print Command

Now the “print” command can be used to view all stored login-paths when paired with the –all option (Figure 5).

MySQL mysql_config_editor Screenshot
Figure 5 – One way to view all login-paths stored in the .mylogin.cnf file is to use the command “print” with the –all option

Alternatively, should only a particular login-path be required the “print” command can be passed with a specific login-path as shown in Figure 6.

MySQL mysql_config_editor Screenshot
Figure 6 – A “print” command for only one specific login-path

In order to confirm that the .mylogin.cnf file is not a clear-text file it can be opened with any text editor. The results for this example’s obfuscated .mylogin.cnf file are shown in Figure 7. The .mylogin.cnf file in Figure 7 is definitely not a clear-text file.

MySQL mysql_config_editor Screenshot
Figure 7 – The obfuscated .mylogin.cnf file contents

The Remove Command

Should a need to overwrite a login-path arise, this can be done one of two ways. The first is to run the “set” command with the new information which will overwrite the original contents of the passed login-path (Figure 8). The other way to change a login-path is by using the “remove” command passing the login-path name as an option along with any desired changes; this will remove any passed options from the login-path. If no options other than login-path are passed, the entire login-path is removed (Figure 9). After the removal of a login-path it will have to then be recreated using the “set” command.

MySQL mysql_config_editor Screenshot
Figure 8 – The “set” command can be used to overwrite an existing login-path. A warning will show up and ask for confirmation. The “set” command will always overwrite the entire login-path meaning individual options cannot be added.
MySQL mysql_config_editor Screenshot
Figure 9 – The “remove” command can remove entire login-paths or individual options for a login-path.

The Reset Command

In order to remove all login-paths the “reset” command is used. This will remove all login-paths from the .mylogin.cnf file, however the file will still exist (Figures 10 and 11).

MySQL mysql_config_editor Screenshot
Figure 10 – The “reset” command removes all login-paths from the .mylogin.cnf file, but does not remove the file itself.
MySQL mysql_config_editor Screenshot
Figure 11 – The .mylogin.cnf file after the “reset” command has been run. Clearly there is still information in this file, but it is not any login-path.

The Help Command and Option

The order of the help command matters. It can be passed as an option to any of the mysql_config_editor commands, or as an option to the mysql_config_editor (See Figures 12 – 14).

MySQL mysql_config_editor Screenshot
Figure 12 – The help command run for the mysql_config_editor program.
MySQL mysql_config_editor Screenshot
Figure 13 – The “help” option as applied to the “set” command specifically.
MySQL mysql_config_editor Screenshot
Figure 14 – Although a –help option is allowed on the “set” command, without the leading hyphens a login-path named “help” can accidentally be created.