How to Write a Secure Shell Script to Run MySQL Queries via Cron

Shell scripting is one of the most powerful tools available on Linux and UNIX-like systems, allowing developers and system administrators to automate repetitive tasks, run SQL queries, and maintain servers efficiently. One common use case is updating database records automatically using cron jobs. In this tutorial, we’ll walk you through creating a shell script that executes a MySQL UPDATE query on a daily schedule and implement best security practices to keep your credentials safe.

---

🔹 Section 1: Basics of Shell Scripting for MySQL with Cron

📌 What You’ll Learn:

• How to write a shell script to run a MySQL query
• How to schedule that script using cron
• A working example to update a BLOB column to “online”

✅ Step-by-Step Example

Let’s say you have a MySQL table called mw_option where the value column is of type BLOB. You want to set the value to 'online' where the key is 'site_status' and the category is 'system.common'.

Here’s a simple shell script to do that:

#!/bin/bash

# MySQL credentials
DB_USER="your_username"
DB_PASS="your_password"
DB_NAME="your_database"

# MySQL query execution
mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" <<EOF
UPDATE option
SET value = CAST('online' AS BINARY)
WHERE \`key\` = 'site_status' AND category = 'system.common';
EOF

Save this script as update_status.sh and give it execute permission:

chmod +x update_status.sh

🕐 Scheduling the Script via Cron

To run the script daily at 1 AM, open your crontab:

crontab -e

Add this line:

0 1 * * * /path/to/update_status.sh

This ensures your MySQL update query runs every day at 1 AM automatically.

---

🔒 Section 2: Security & Best Practices for Shell Scripts with MySQL

Hardcoding passwords in shell scripts is risky. If someone gains access to your server, even with limited privileges, they could easily read your script and expose sensitive data.

🔐 Avoid Inline Passwords in Scripts

Bad practice:

mysql -u"admin" -p"123456" your_db <<EOF
...
EOF

This exposes your credentials in:

• Plain text files
• Process lists (ps aux)
• Bash history

✅ Secure Alternative: Use ~/.my.cnf

A more secure approach is to store your MySQL credentials in a local config file:

iniCopyEdit[client]
user=your_username
password=your_password

Steps:

1. Create the file: bashCopyEditnano ~/.my.cnf
2. Paste the credentials as shown above.
3. Secure the file: bashCopyEditchmod 600 ~/.my.cnf

🔄 Updated Script (No Passwords Needed)

#!/bin/bash

# Run the MySQL query using .my.cnf
mysql your_database_name <<EOF
UPDATE option
SET value = CAST('online' AS BINARY)
WHERE \`key\` = 'site_status' AND category = 'system.common';
EOF

This method is:

• Secure
• Cron-friendly
• Cleaner and more maintainable

---

⚠️ Bonus Tips: Additional Best Practices

• ✅ Use Logging: Add logs to your script to track when it runs and if there are any errors.
  echo "$(date): Status update script executed" >> /var/log/status_update.log
• ✅ Test Your Script: Run manually first to ensure it behaves as expected.
• ✅ Use Absolute Paths: Cron jobs run in a limited environment. Always use full paths for files and commands (e.g., /usr/bin/mysql).

---

🎯 Conclusion

Writing shell scripts to automate MySQL queries is a powerful and efficient practice in Linux environments. However, it’s essential to prioritize security, especially when handling database credentials. Using .my.cnf, logging outputs, and scheduling with cron makes your automation both secure and robust.

Stay updated with more Linux, MySQL, and DevOps tutorials on ITUptodate.com — your go-to source for IT knowledge.