If you are in charge of managing a database server, from time to time you may need to run a query and inspect it carefully. While you can do that from the MySQL / MariaDB shell, but this tip will allow you to execute the MySQL/MariaDB Queries directly using the Linux command line AND save the output to a file for later inspection (this is particularly useful if the query return lots of records).
Let us look at some simple examples of running MYSQL queries directly from the command line before we can move to a more advanced query.
Setting Up Example Databases
Before we dive into the commands, let’s set up the example databases we’ll be working with throughout this guide, so you can follow along and practice these techniques on your own system.
Creating the tecmintdb Database
First, let’s create the tecmintdb database and the tutorials table:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"
Next, to create a database table named tutorials in the database tecmintdb, run the command below:
sudo mysql -u root -p tecmintdb << 'EOF'
CREATE TABLE IF NOT EXISTS tutorials (
tut_id INT NOT NULL AUTO_INCREMENT,
tut_title VARCHAR(100) NOT NULL,
tut_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY (tut_id)
);
INSERT INTO tutorials (tut_title, tut_author, submission_date) VALUES
('Getting Started with Linux', 'John Smith', '2024-01-15'),
('Advanced Bash Scripting', 'Sarah Johnson', '2024-02-20'),
('MySQL Database Administration', 'Mike Williams', '2024-03-10'),
('Apache Web Server Configuration', 'Emily Brown', '2024-04-05'),
('Python for System Administrators', 'David Lee', '2024-05-12'),
('Docker Container Basics', 'Lisa Anderson', '2024-06-18'),
('Kubernetes Orchestration', 'Robert Taylor', '2024-07-22'),
('Linux Security Hardening', 'Jennifer Martinez', '2024-08-30');
EOF
Verify the data was inserted:
sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"
Creating the employees Database
Now, let’s create a more complex employees database with multiple related tables, this is the database we’ll use for the more advanced query examples:
sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"
Create the employees table:
sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS employees (
emp_no INT NOT NULL AUTO_INCREMENT,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES
(10001, '1953-09-02', 'Georgi', 'Facello', 'M', '1984-06-02'),
(10002, '1964-06-02', 'Bezalel', 'Simmel', 'F', '1984-11-21'),
(10003, '1959-12-03', 'Parto', 'Bamford', 'M', '1984-08-28'),
(10004, '1954-05-01', 'Chirstian', 'Koblick', 'M', '1984-12-01'),
(10005, '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1984-09-15'),
(10006, '1953-04-20', 'Anneke', 'Preusig', 'F', '1985-02-18'),
(10007, '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1985-03-20'),
(10008, '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1984-07-11'),
(10009, '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18'),
(10010, '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1984-08-24');
EOF
Create the salaries table:
sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
PRIMARY KEY (emp_no, from_date),
FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE
);
INSERT INTO salaries (emp_no, salary, from_date, to_date) VALUES
(10001, 60117, '1984-06-02', '1985-06-02'),
(10001, 62102, '1985-06-02', '1986-06-02'),
(10001, 66074, '1986-06-02', '9999-01-01'),
(10002, 65828, '1984-11-21', '1985-11-21'),
(10002, 65909, '1985-11-21', '9999-01-01'),
(10003, 40006, '1984-08-28', '1985-08-28'),
(10003, 43616, '1985-08-28', '9999-01-01'),
(10004, 40054, '1984-12-01', '1985-12-01'),
(10004, 42283, '1985-12-01', '9999-01-01'),
(10005, 78228, '1984-09-15', '1985-09-15'),
(10005, 82507, '1985-09-15', '9999-01-01'),
(10006, 40000, '1985-02-18', '1986-02-18'),
(10006, 43548, '1986-02-18', '9999-01-01'),
(10007, 56724, '1985-03-20', '1986-03-20'),
(10007, 60605, '1986-03-20', '9999-01-01'),
(10008, 46671, '1984-07-11', '1985-07-11'),
(10008, 48584, '1985-07-11', '9999-01-01'),
(10009, 60929, '1985-02-18', '1986-02-18'),
(10009, 64604, '1986-02-18', '9999-01-01'),
(10010, 72488, '1984-08-24', '1985-08-24'),
(10010, 74057, '1985-08-24', '9999-01-01');
EOF
Create the departments table for more complex joins:
sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS departments (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY (dept_name)
);
INSERT INTO departments (dept_no, dept_name) VALUES
('d001', 'Marketing'),
('d002', 'Finance'),
('d003', 'Human Resources'),
('d004', 'Production'),
('d005', 'Development'),
('d006', 'Quality Management');
EOF
Create the dept_emp table to link employees to departments:
sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
PRIMARY KEY (emp_no, dept_no),
FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments(dept_no) ON DELETE CASCADE
);
INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date) VALUES
(10001, 'd005', '1984-06-02', '9999-01-01'),
(10002, 'd005', '1984-11-21', '9999-01-01'),
(10003, 'd004', '1984-08-28', '9999-01-01'),
(10004, 'd004', '1984-12-01', '9999-01-01'),
(10005, 'd003', '1984-09-15', '9999-01-01'),
(10006, 'd005', '1985-02-18', '9999-01-01'),
(10007, 'd004', '1985-03-20', '9999-01-01'),
(10008, 'd005', '1984-07-11', '9999-01-01'),
(10009, 'd006', '1985-02-18', '9999-01-01'),
(10010, 'd006', '1984-08-24', '9999-01-01');
EOF
Verify everything is set up correctly:
sudo mysql -u root -p -e "USE employees; SHOW TABLES;"
Now you’ve got both databases set up with sample data, and you can follow along with all the examples in this guide. The tecmintdb database is perfect for simple queries, while the employees database lets you practice more complex operations like joins and aggregations.
Basic Query Execution
To view all the databases on your server, you can issue the following command:
sudo mysql -u root -p -e "show databases;"
Next, to create a database table named tutorials in the database tecmintdb, run the command below:
sudo mysql -u root -p -e "USE tecmintdb; CREATE TABLE tutorials(tut_id INT NOT NULL AUTO_INCREMENT, tut_title VARCHAR(100) NOT NULL, tut_author VARCHAR(40) NOT NULL, submissoin_date DATE, PRIMARY KEY (tut_id));"
Saving MySQL Query Results to a File
We will use the following command and pipe the output to the tee command followed by the filename where we want to store the output.
For illustration, we will use a database named employees and a simple join between the employees and salaries tables. In your own case, just type the SQL query between the quotes and hit Enter.
Note that you will be prompted to enter the password for the database user:
sudo mysql -u root -p -e "USE employees; SELECT DISTINCT A.first_name, A.last_name FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE hire_date < '1985-01-31';" | tee queryresults.txt
View the query results with the help of the cat command.
cat queryresults.txt
Run MySQL/MariaDB Queries from Commandline
With the query results in plain text files, you can process the records more easily using other command-line utilities. Now that you’ve seen the basics, let’s explore some more advanced techniques that’ll make your command-line database work even more efficiently.
Formatting Output for Better Readability
The default table format is great for viewing in the terminal, but sometimes you need different formats. You can output results in vertical format, which is particularly useful when dealing with tables that have many columns:
sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1G"
The G at the end displays each row vertically instead of in a table, so instead of seeing a cramped horizontal table, you get something like:
*************************** 1. row ***************************
emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
last_name: Facello
gender: M
hire_date: 1984-06-02
Exporting to CSV Format
When you need to import query results into spreadsheet applications or other tools, CSV format is your best bet:
sudo mysql -u root -p -e "USE employees; SELECT first_name, last_name, hire_date FROM employees WHERE hire_date < '1985-01-31';" | sed 's/t/,/g' > employees.csv
This pipes the output through sed to replace tabs with commas, creating a proper CSV file that opens cleanly in Excel, LibreOffice Calc, or any other spreadsheet software.
Running Queries Without Password Prompts
If you’re automating database tasks with cron jobs or scripts, you don’t want to manually enter passwords every time, that’s where MySQL configuration files come in.
Create a file at ~/.my.cnf with your credentials:
[client] user=root password=your_password_here
Then secure it so only you can read it:
chmod 600 ~/.my.cnf
Now you can run queries without the -p flag and without being prompted:
mysql -e "SHOW DATABASES;"
Just remember, storing passwords in plain text files has security implications, so only use this approach on servers where you control access, and consider using MySQL’s more secure authentication methods for production environments.
Executing Complex Multi-Line Queries
Sometimes your queries are too complex to write in a single command line, especially when you’re dealing with multiple joins, subqueries, or complex conditions.
You can put your SQL in a file and execute it:
cat > complex_query.sql << 'EOF'
USE employees;
SELECT
e.first_name,
e.last_name,
d.dept_name,
s.salary
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date BETWEEN '1985-01-01' AND '1985-12-31'
AND s.from_date = (
SELECT MAX(from_date)
FROM salaries
WHERE emp_no = e.emp_no
)
ORDER BY s.salary DESC
LIMIT 10;
EOF
Now execute it.
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
This approach keeps your queries organized and reusable, and you can version control them with git just like any other code.
Batch Processing Multiple Queries
If you need to run several related queries and save each result separately, you can script it:
#!/bin/bash
QUERIES=(
"SELECT COUNT(*) as total_employees FROM employees"
"SELECT dept_name, COUNT(*) as employee_count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no GROUP BY dept_name"
"SELECT YEAR(hire_date) as year, COUNT(*) as hires FROM employees GROUP BY YEAR(hire_date) ORDER BY year"
)
FILENAMES=(
"total_count.txt"
"dept_distribution.txt"
"yearly_hires.txt"
)
for i in "${!QUERIES[@]}"; do
echo "Running query $((i+1))..."
mysql -u root -p -e "USE employees; ${QUERIES[$i]}" > "${FILENAMES[$i]}"
echo "Results saved to ${FILENAMES[$i]}"
done
Save this as a script, make it executable with chmod +x, and you’ve got a reusable batch query tool.
Monitoring Long-Running Queries
When you’re running queries that might take a while, you want to see progress or at least know they’re still working.
Combine your query with status output:
(sudo mysql -u root -p -e "USE employees; SELECT COUNT(*) FROM large_table WHERE complex_condition;" && echo "Query completed at $(date)") | tee query_log.txt
For even longer queries, run them in the background and monitor the MySQL process list:
sudo mysql -u root -p -e "USE employees; SELECT * FROM massive_table;" > output.txt & sudo watch -n 5 'mysql -u root -p -e "SHOW PROCESSLISTG" | grep -A 5 "SELECT"'
This runs your query in the background while displaying the process list every 5 seconds, so you can see it’s still working and how much progress it’s made.
Filtering and Processing Results
Once you have query results in a text file, you can use standard Linux tools to process them further. Here are some useful patterns:
Count the number of result rows (excluding the header):
tail -n +2 queryresults.txt | wc -l
Extract specific columns using awk:
awk '{print $1, $3}' queryresults.txt
Search for specific patterns in results:
grep -i "engineering" dept_distribution.txt
Sort results by a numeric column:
tail -n +2 queryresults.txt | sort -k3 -n
Handling Special Characters and Large Datasets
When your data contains special characters, tabs, or newlines, the default output can get messy, so use the --batch and --raw options for cleaner output:
sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt
For queries that return millions of rows, you might run into memory issues, instead of loading everything into memory, stream the results:
sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz
The --quick option tells MySQL to retrieve rows one at a time instead of buffering the entire result set, and piping through gzip compresses the output on the fly, saving disk space.
Creating Quick Database Backups
While this isn’t technically running queries, you can use similar command-line techniques to create quick database dumps with the mysqldump command.
sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz
Or backup just specific tables:
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Scheduling Automated Reports
Combine everything we’ve covered to create automated daily reports using cron with the help of the following bash script.
#!/bin/bash
REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/var/reports/daily_stats_${REPORT_DATE}.txt"
{
echo "Database Statistics Report - ${REPORT_DATE}"
echo "=========================================="
echo
echo "Total Employees:"
mysql -e "USE employees; SELECT COUNT(*) FROM employees;"
echo
echo "New Hires This Month:"
mysql -e "USE employees; SELECT COUNT(*) FROM employees WHERE MONTH(hire_date) = MONTH(CURRENT_DATE()) AND YEAR(hire_date) = YEAR(CURRENT_DATE());"
echo
echo "Department Distribution:"
mysql -e "USE employees; SELECT d.dept_name, COUNT(*) as count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no WHERE de.to_date = '9999-01-01' GROUP BY d.dept_name ORDER BY count DESC;"
} > "$REPORT_FILE"
echo "Report generated: $REPORT_FILE"
Add it to cron to run daily at 6 AM:
0 6 * * * /usr/local/bin/generate_db_report.sh
Summary
We have shared several Linux tips that you, as a system administrator, may find useful when it comes to automating your daily Linux tasks or performing them more easily.
The key takeaway here is that you don’t always need to fire up the MySQL shell or use heavy GUI tools to work with your databases; the command line gives you speed, automation capabilities, and the ability to integrate database operations into your existing shell scripts and workflows.
Do you have any other tips that you would like to share with the rest of the community? If so, please do so using the comment form below.
