Configuration management for MySQL client applications

 

 

If you run any MySQL client application (mysql, mysqldump, ...) and pass password via --password command line argument, this application will show a warning:

 

$ docker run -i mysql /usr/bin/mysql --host=172.17.0.2 --port=3306 --user=root --password=root
mysql: [Warning] Using a password on the command line interface can be insecure.

It's not a good practice to pass the password from the command line as it is saved in the ~/.bash_history file and can be read by other applications.

 

The preferred way is to store MySQL DB configuration(s) (including credentials) in a file and then make MySQL clients read it (via --defaults-file or --defaults-extra-file command line argument).

This file can be created by mysql_config_editor tool or manually. If created by the tool, it will be named ~/.mylogin.cnf and its content would be obfuscated. 

Alternatively, it is possible to create and populate ~/.my.cnf file (or /path/to/arbitrary_name.cnf) manually and set desired read/write permissions on it e.g. to make it readable to me only: 

 

$ chmod 0600 ~/.my.cnf

This is the setup that worked for me:

We can create a configuration file for each database. E.g.:

$ cat ~/mysql/configs/my_db.cnf
[client]
user=my_username
password=my_password
#port=3306

#socket=my_socket
#database=my_schema

We can then share this file with Docker container and specify it as --defaults-extra-file for MySQL client (I didn't set any special read permissions but Docker user should be able to read and copy it):

$ docker run \

-i \

-v ~/mysql/configs/my_db.cnf:/etc/mysql/my_db.cnf \

mysql \

/usr/bin/mysqldump \

--defaults-extra-file=/etc/mysql/my_db.cnf  \

--host=172.17.0.2 \

--port=3306 \

my_schema my_table_01 my_table_02 > dump_​​$(date +%Y%m%d_%H%M%S).sql

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

 

References:

 

MySQL :: MySQL 8.0 Reference Manual :: 4.6.7 mysql_config_editor — MySQL Configuration Utility