Introduction

Executing a MySQL command from a shell script is usually just a matter of calling the mysql client with the right credentials and SQL. The hard part is not syntax. It is doing it safely, especially around passwords, quoting, and error handling. This guide shows the standard patterns and the security tradeoffs that matter in real automation.

The Basic One-Line Pattern

The mysql client can run a single SQL statement with the -e flag.

1#!/usr/bin/env bash
2set -euo pipefail
3
4mysql -u myuser -p'mypassword' -D mydatabase -e "SELECT NOW();"

This works, but putting the password directly on the command line is often a bad idea because it can leak through shell history or process inspection.

Prefer an Option File for Credentials

A better pattern is to use a MySQL option file.

Example ~/.my.cnf:

1[client]
2user=myuser
3password=mypassword
4host=127.0.0.1

Then your script becomes much cleaner:

1#!/usr/bin/env bash
2set -euo pipefail
3
4mysql -D mydatabase -e "SELECT NOW();"

This is simpler and safer than embedding secrets in the script itself. The file should have restrictive permissions.

Execute Multiple Statements with a Here-Document

For longer SQL, a here-document is often clearer than cramming everything into one quoted string.

1#!/usr/bin/env bash
2set -euo pipefail
3
4mysql mydatabase <<'SQL'
5SELECT COUNT(*) FROM users;
6UPDATE jobs SET status = 'done' WHERE finished_at IS NOT NULL;
7SQL

This is easier to read, easier to maintain, and less fragile than deeply nested quoting.

Capture Query Output in the Script

Sometimes you need the result inside a shell variable.

1#!/usr/bin/env bash
2set -euo pipefail
3
4count=$(mysql -N -s mydatabase -e "SELECT COUNT(*) FROM users;")
5echo "User count: $count"

Useful flags here are:

• '-N to skip column names'
• '-s for silent output that is easier to parse'

That makes the result much cleaner for script consumption.

Pass Host and Port Explicitly When Needed

If the database is not local or you want predictable connection behavior, specify host and port directly.

mysql -h 127.0.0.1 -P 3306 -u myuser -D mydatabase -e "SHOW TABLES;"

Using 127.0.0.1 rather than localhost can matter because some MySQL clients treat localhost as a Unix socket connection rather than TCP.

Error Handling in Shell Scripts

At minimum, use strict shell settings and fail fast.

1#!/usr/bin/env bash
2set -euo pipefail
3
4mysql mydatabase -e "DELETE FROM sessions WHERE expires_at < NOW();"
5echo "Cleanup completed"

If the MySQL command fails, the script exits immediately because of set -e. That is usually better than continuing with partial state.

Using Environment Variables Carefully

If you cannot use an option file, environment variables are another possibility.

1#!/usr/bin/env bash
2set -euo pipefail
3
4export MYSQL_PWD='mypassword'
5mysql -u myuser -D mydatabase -e "SELECT NOW();"
6unset MYSQL_PWD

This is still not ideal, and the MySQL ecosystem itself discourages long-term reliance on MYSQL_PWD, but it is sometimes used in controlled automation contexts.

Practical Automation Example

A simple backup metadata script might look like this:

1#!/usr/bin/env bash
2set -euo pipefail
3
4backup_name="nightly-$(date +%F)"
5
6mysql mydatabase <<SQL
7INSERT INTO backup_log(name, created_at)
8VALUES ('$backup_name', NOW());
9SQL

If variables are interpolated into SQL, be careful with quoting and escaping. For untrusted input, a shell script is usually the wrong layer for complex query construction.

Common Pitfalls

• Putting the database password directly on the command line in production scripts.
• Forgetting to quote SQL correctly when mixing shell variables and SQL strings.
• Parsing default mysql output without using flags such as -N and -s.
• Assuming localhost and 127.0.0.1 behave identically for MySQL connections.
• Continuing after a failed query because the script does not enable strict error handling.

Summary

• Use mysql -e for simple one-line commands from a shell script.
• Use a here-document for multi-line SQL.
• Prefer a MySQL option file over inline passwords.
• Use -N and -s when you need script-friendly output.
• Treat quoting, credentials, and error handling as part of the real solution, not as minor details.