Tuesday 8 February 2022

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= --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

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= \
--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.




No comments: