Secure login to MySQL for bash scripts

Introduction

In this blog we will connect to a mysql via CLI from a bash script for generating a db dump. A cleaner approach for the same is using mysql config editor. In this blog we are taking an example of a mysql container running inside docker and we have a script for taking a db backup using mysqldump. We will not use a password as a parameter to mysqldump command instead we will use something called as login path.

MySQL config editor

It is a utility to store authentication credentials in a obfuscated login path file named as .mylogin.cnf.

How to do it ?

View existing configurations

To view existing configurations if there are any use below command. The command will output existing configurations if there are otherwise it won't print anything.

mysql_config_editor print --all

Sample output

In the output below you can see that the password is obfuscated and is not readable directly. It is handled by the mysql utilities to use this password as per the flags passed.

[client]
[local]
user = root
password = *****
host = localhost

Add new configurations

Use below command to add new configurations. You can connect to the container shell (also called as Integrated terminal) from the docker desktop. You need to click the container. In the container details section you will see multiple tabs. One of the tabs is the Exec tab. It is used for running the shell commands inside the container.  Checkout the screenshot below.

MySQL docker container details showing integrated terminal

It will ask for the password when you execute this command. Enter the same and it will be saved in the .mylogin.cnf file. The location of the file is as per the documentation mentioned here.

mysql_config_editor set --login-path=local --host=localhost --user=root --password

MySQL DB dump from a docker container

Use below command from the host machine to generate the mysqldump for a mysql container. The command uses the login-path=local we created in previous step. Please note below points

• CONTAINER_NAME: Is the mysql container name you have given in the docker-compose.yml file. (Ex: testdb )
• BACKUP_FILE_PATH: Is the absolute path with the filename on the host machine wherein the file will be generated. (Ex: /opt/backup)

docker exec <CONTAINER_NAME> sh -c 'exec mysqldump --login-path=local --all-databases' > <BACKUP_FILE_PATH>

For example

docker exec testdb sh -c 'exec mysqldump --login-path=local --all-databases' > /opt/backup/mysql_backup.sql

Summary

We can now securely run a database backup script without exposing the password in the CLI. Although there are other ways but this one suited my requirement and use case. You can explore more on the official mysql documentation.