Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

Container detached from docker network

Introduction One fine day you find out that one of your docker containers is running but is not currently attached to the container network that you created using docker network command. Ideally a container should be detached from the network when there is some error in it. However, if you set the restart: on-failure in the docker-compose.yml  in that case the container should restart and still be attached to the network. In such a weird situation follow the steps documented in this blog. Possible reasons Listed below are the possible reasons for this issue Storage space issue Memory issue ( We will cover this in next blog ) Storage space issue We need to check the space utilisation at the server level first then drill down to the docker engine. Server level utilisation In most cases the server is ubuntu system hence check the disk usage using the disk free command (i.e. df -h ). It will show you the current storage space utilisation of the server. df -h Docker engine space utilis...
Post a Comment
Read more

TOP down or bottom UP approach to Learning

      All of us have unique learning ability. Learning new thing has two of many approaches ( i.e TOP-DOWN or BOTTOM-UP ). TOP down approach is from Generalisation to Specialisation approach and BOTTOM up is inverse. Which approach to follow depends on the individuals choice and level of experience. In both the cases you can start small then broaden your learning and then narrow it down for easier storage and retrieval. Let's do a comparative analysis today of these approaches and understand which approach is better.     Learning something new requires lot of efforts and if you do it wrong then you tend to confuse yourself and lose confidence. Let's take an example of the BOTTOM up approach, if you are learning a new programming language and you start with the datatypes, reserved keywords, access specifiers, etc. then you will need more efforts to first store all what you learnt then club them together to create a logical whole. Most of us will argue s...
Post a Comment
Read more