sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz
#!/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
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.
Creating the tecmintdb Database
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
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Scheduling Automated Reports
*************************** 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
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.
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.
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
chmod 600 ~/.my.cnf
#!/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”
Count the number of result rows (excluding the header):
sudo mysql -u root -p –quick -e “SELECT * FROM huge_table;” | gzip > huge_results.txt.gz
0 6 * * * /usr/local/bin/generate_db_report.sh
Summary
awk ‘{print , }’ queryresults.txt
If you need to run several related queries and save each result separately, you can script 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
You can put your SQL in a file and execute it:
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.
While this isn’t technically running queries, you can use similar command-line techniques to create quick database dumps with the mysqldump command.
Formatting Output for Better Readability
(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
sudo mysql -u root -p -e “USE tecmintdb; SELECT * FROM tutorials;”
Creating the employees Database
mysql -e “SHOW DATABASES;”
tail -n +2 queryresults.txt | wc -l
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
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.
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
cat queryresults.txt
Run MySQL/MariaDB Queries from Commandline
To view all the databases on your server, you can issue the following command:
Executing Complex Multi-Line Queries
grep -i “engineering” dept_distribution.txt
sudo mysql -u root -p –batch –raw -e “SELECT description FROM products WHERE category=’electronics’;” > products.txt
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:
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.
Batch Processing Multiple Queries
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.
[client]
user=root
password=your_password_here
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
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
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).
Create a file at ~/.my.cnf with your credentials:
Filtering and Processing Results
sudo mysql -u root -p -e “show databases;”
First, let’s create the tecmintdb database and the tutorials table:
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
sudo mysql -u root -p -e “USE employees; SHOW TABLES;”
Combine your query with status output:
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
tail -n +2 queryresults.txt | sort -k3 -n
Handling Special Characters and Large Datasets
Note that you will be prompted to enter the password for the database user:
Creating Quick Database Backups
sudo mysql -u root -p -e “CREATE DATABASE IF NOT EXISTS employees;”
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”‘
When you’re running queries that might take a while, you want to see progress or at least know they’re still working.
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
Once you have query results in a text file, you can use standard Linux tools to process them further. Here are some useful patterns:
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:
