DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Coding
  3. Languages
  4. Use MySQL Shell Securely From Bash

Use MySQL Shell Securely From Bash

See how you can use MySQL shell securely from Bash, including the scripts you need to get that up and running.

Peter Zaitsev user avatar by
Peter Zaitsev
·
Oct. 10, 16 · Tutorial
Like (2)
Save
Tweet
Share
3.97K Views

Join the DZone community and get the full member experience.

Join For Free

The Bourne shell is everywhere. It is part of the most basic Linux install. You will find it on the biggest SPARC machines down to a Raspberry Pi. It is nice to know it will always be there. Unlike other, more complex scripting environments such as Perl and Python, it doesn’t require any additional dependencies to be installed.

Anyone that has automated a MySQL task using a Bourne shell such as Bash will be familiar with the following message:

Warning: Using a password on the commandline interface can be insecure.

This semi-ominous warning describes a security flaw in passing credentials on a process command line. Any unprivileged user on the system can use a command like ps aux to find these credentials. While the MySQL shell has added some additional protections to hide these credentials, other information such as database user names, host names, ports and sockets can still be determined by process scanning.

The recommended approach to get around this warning is to use a configuration file to store these credentials. However, in the case of a self-contained script, we may not want to require the user to create a credential file. It would need to be maintained, and might interfere with other tools that use the MySQL shell. For creating automated tools in the Percona Development Services department, I came up with a couple of methods for integrating the MySQL shell into Bash (or any other Bourne Shell) securely.

This first script demonstrates the shell function mysql_exec(). This is for use with small queries that are normally passed to the MySQL shell via the -e parameter.

Mysql_securely.sh

#!/bin/sh
# call mysql client from shell script without
# passing credentials on command line
# This demonstrates small single queries using
# the -e parameter.   Credentials and connection
# info are sent through standard input.
# david . bennett @ percona . com - 9/24/2016
mysql_user=root
mysql_password=password
mysql_host=127.0.0.1
mysql_port=3306
mysql_database=test

mysql_exec(){
    local query="$1"
    local opts="$2"
    mysql_exec_result=$(
    printf"%s\n"\
    "[client]"\
    "user=${mysql_user}"\
    "password=${mysql_password}"\
    "host=${mysql_host}"\
    "port=${mysql_port}"\
    "database=${mysql_database}"\
|    mysql--defaults-file=/dev/stdin"${opts}"-e"${query}"
    mysql_exec"select 'Hello World' as Message"
    echo"${mysql_exec_result}"

The above script allows the specification of credentials and connection information via variables in the script. As with any other shell script, these can be moved into a configuration file and secured with chown/chmod, then included with the source or . command. The mysql_exec() function creates a default my.cnf [client] on the fly and passes it to the MySQL shell via –defaults-file=/dev/stdin. The configuration is never written to disk, which makes this method a bit more secure.

Sometimes, you need to process too many queries to pass on the command line. In this case, there is another technique for passing the credentials.

Mysql_securely_mktemp.sh

mysql_exec_from_file()c {
    local query_file="$1"
    local opts="$2"
    local tmpcnf="$(mktemp)"
    chmod600"${tmpcnf}"
    printf"%sn"
    "[client]"
    "user=${mysql_user}"
    "password=${mysql_password}"
    "host=${mysql_host}"
    "port=${mysql_port}"
    "database=${mysql_database}"
    >"${tmpcnf}"
    mysql_exec_from_file_result=$(
    mysql--defaults-file="${tmpcnf}""$opts"<"${query_file}"
    rm"${tmpcnf}"

This technique uses a temporary file, which allows the queries to be passed from a file or input device. Restrictive permissions are set on the file before the configuration is written. The temporary configuration is removed immediately after the shell exits.

While other languages may offer cleaner ways to access your MySQL database, you’ll always know that you’ll be able to execute your shell-based MySQL job scripts across all of the Unix machines in your enterprise.

You can download these scripts directly from my GitHub account.

shell MySQL Bash (Unix shell)

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Understanding gRPC Concepts, Use Cases, and Best Practices
  • Iptables Basic Commands for Novice
  • What Should You Know About Graph Database’s Scalability?
  • Kotlin Is More Fun Than Java And This Is a Big Deal

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: