How to execute a MySQL command from a shell script?

When you need to execute MySQL commands from a Shell script, several methods are available. Below, I will outline common approaches along with examples.

Method 1: Using the mysql Command-Line Tool

The most straightforward approach is to use the mysql command-line tool. You can directly execute SQL commands within a shell script. For example:

bash
#!/bin/bash

# MySQL database credentials
USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"

# Execute a simple query
mysql -u"$USER" -p"$PASSWORD" -D"$DATABASE" -e "SELECT * FROM your_table LIMIT 10;"

This script connects to the MySQL database and retrieves the first 10 records from your_table.

Method 2: Using Here Document

For executing multiple MySQL commands, Here Document is more convenient. It allows embedding a multi-line input block directly within a shell script, which is passed to the mysql command. For example:

bash
#!/bin/bash

USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"

mysql -u"$USER" -p"$PASSWORD" -D"$DATABASE" <<EOF
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
INSERT INTO test_table (name) VALUES ('Alice'), ('Bob');
SELECT * FROM test_table;
EOF

This script creates a new table test_table, inserts records, and queries all entries.

Method 3: Storing SQL Commands in a File

When dealing with numerous SQL commands, storing them in a separate file improves clarity and manageability. For example, create a file named script.sql containing all commands, then call it within a shell script:

bash
#!/bin/bash

USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"

mysql -u"$USER" -p"$PASSWORD" -D"$DATABASE" < script.sql

This approach simplifies maintenance and updates of the SQL script.

Summary

Based on your requirements, choose the most suitable method for executing MySQL commands in a Shell script. Using command-line parameters, Here Document, or external files are all viable options, depending on operational complexity and personal preference. In practice, selecting a method that enhances efficiency while maintaining script clarity is crucial.