Cách Chạy Các Truy Vấn MySQL từ Dòng Lệnh Linux

Nếu bạn phụ trách quản lý một máy chủ cơ sở dữ liệu, thỉnh thoảng bạn có thể cần chạy một truy vấn và kiểm tra nó cẩn thận. Mặc dù bạn có thể làm điều đó từ shell MySQL/MariaDB, nhưng mẹo này sẽ cho phép bạn thực hiện các truy vấn MySQL/MariaDB trực tiếp từ dòng lệnh Linux VÀ lưu kết quả ra tệp để kiểm tra sau này (điều này đặc biệt hữu ích nếu truy vấn trả về nhiều bản ghi).

Hãy xem một số ví dụ đơn giản về việc chạy các truy vấn MYSQL trực tiếp từ dòng lệnh trước khi chúng ta chuyển sang một truy vấn nâng cao hơn.

Thiết lập cơ sở dữ liệu ví dụ

Trước khi chúng ta đi vào các lệnh, hãy thiết lập các cơ sở dữ liệu ví dụ mà chúng ta sẽ làm việc trong suốt hướng dẫn này, để bạn có thể theo dõi và thực hành những kỹ thuật này trên hệ thống của riêng bạn.

Tạo cơ sở dữ liệu tecmintdb

Trước tiên, hãy tạo cơ sở dữ liệu tecmintdb và bảng tutorials:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"

Tiếp theo, để tạo một bảng cơ sở dữ liệu có tên tutorials trong cơ sở dữ liệu tecmintdb, hãy chạy lệnh dưới đây:

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

Xác minh dữ liệu đã được chèn:

sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"

Tạo cơ sở dữ liệu nhân viên

Bây giờ, hãy tạo một cơ sở dữ liệu employees phức tạp hơn với nhiều bảng có quan hệ, đây là cơ sở dữ liệu mà chúng ta sẽ sử dụng cho các ví dụ truy vấn nâng cao:

sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"

Tạo bảng employees:

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

Tạo bảng salaries:

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

Tạo bảng departments cho các phép nối phức tạp hơn:

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

Tạo bảng dept_emp để liên kết employees với 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

Xác nhận mọi thứ đã được thiết lập đúng cách:

sudo mysql -u root -p -e "USE employees; SHOW TABLES;"

Bây giờ bạn đã thiết lập cả hai cơ sở dữ liệu với dữ liệu mẫu, và bạn có thể theo dõi tất cả các ví dụ trong hướng dẫn này. Cơ sở dữ liệu tecmintdb rất phù hợp cho các truy vấn đơn giản, trong khi cơ sở dữ liệu employees cho phép bạn thực hành các thao tác phức tạp hơn như kết hợp bảng và tổng hợp dữ liệu.

Thực thi truy vấn cơ bản

Để xem tất cả các cơ sở dữ liệu trên máy chủ của bạn, bạn có thể sử dụng lệnh sau:

sudo mysql -u root -p -e "show databases;"

Tiếp theo, để tạo một bảng cơ sở dữ liệu có tên tutorials trong cơ sở dữ liệu tecmintdb, hãy chạy lệnh dưới đây:

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));"

Lưu kết quả truy vấn MySQL vào tệp

Chúng ta sẽ sử dụng lệnh sau và chuyển đầu ra sang lệnh tee, tiếp theo là tên tệp mà chúng ta muốn lưu đầu ra.

Ví dụ minh họa, chúng ta sẽ sử dụng một cơ sở dữ liệu có tên employees và một phép nối đơn giản giữa các bảng employees và salaries. Trong trường hợp của bạn, chỉ cần gõ câu lệnh SQL giữa các dấu ngoặc kép và nhấn Enter.

Lưu ý rằng bạn sẽ được yêu cầu nhập mật khẩu cho người dùng cơ sở dữ liệu:

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

Xem kết quả truy vấn với sự trợ giúp của lệnh cat.

cat queryresults.txt

Chạy các truy vấn MySQL/MariaDB từ dòng lệnh

Với kết quả truy vấn trong các tệp văn bản thuần, bạn có thể xử lý các bản ghi dễ dàng hơn bằng các tiện ích dòng lệnh khác. Giờ đây, khi bạn đã nắm được những kiến thức cơ bản, hãy cùng tìm hiểu một số kỹ thuật nâng cao hơn sẽ giúp công việc quản lý cơ sở dữ liệu trên dòng lệnh của bạn hiệu quả hơn.

Định dạng đầu ra để dễ đọc hơn

Định dạng bảng mặc định rất tốt để xem trong terminal, nhưng đôi khi bạn cần các định dạng khác. Bạn có thể xuất kết quả theo định dạng dọc, điều này đặc biệt hữu ích khi làm việc với các bảng có nhiều cột:

sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"

Ký hiệu \G ở cuối sẽ hiển thị từng dòng theo chiều dọc thay vì theo bảng, vì vậy thay vì thấy một bảng ngang chật chội, bạn sẽ nhận được thứ gì đó như:

*************************** 1. row ***************************
    emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
 last_name: Facello
    gender: M
 hire_date: 1984-06-02

Xuất sang định dạng CSV

Khi bạn cần nhập kết quả truy vấn vào các ứng dụng bảng tính hoặc các công cụ khác, định dạng CSV là lựa chọn tốt nhất của bạn:

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

Điều này dẫn đầu dữ liệu đầu ra qua sed để thay thế tab bằng dấu phẩy, tạo ra một tệp CSV đúng cách có thể mở một cách gọn gàng trong Excel, LibreOffice Calc hoặc bất kỳ phần mềm bảng tính nào khác.

Chạy Truy Vấn Không Yêu Cầu Mật Khẩu

Nếu bạn đang tự động hóa các tác vụ cơ sở dữ liệu bằng cron jobs hoặc script, bạn sẽ không muốn phải nhập mật khẩu thủ công mỗi lần, đó là lúc các tệp cấu hình MySQL phát huy tác dụng.

Tạo một tệp tại ~/.my.cnf với thông tin đăng nhập của bạn:

[client]
user=root
password=your_password_here

Sau đó bảo mật nó để chỉ bạn có thể đọc:

chmod 600 ~/.my.cnf

Bây giờ bạn có thể chạy truy vấn mà không cần sử dụng cờ -p và không bị yêu cầu nhập:

mysql -e "SHOW DATABASES;"

Hãy nhớ rằng, lưu mật khẩu trong các tệp văn bản thuần có những rủi ro về bảo mật, vì vậy chỉ sử dụng cách này trên các máy chủ mà bạn kiểm soát quyền truy cập, và nên cân nhắc sử dụng các phương thức xác thực an toàn hơn của MySQL cho môi trường sản xuất.

Thực thi các truy vấn phức tạp nhiều dòng

Đôi khi các truy vấn của bạn quá phức tạp để viết trong một dòng lệnh duy nhất, đặc biệt khi bạn phải xử lý nhiều phép kết nối, truy vấn con hoặc các điều kiện phức tạp.

Bạn có thể đặt SQL của mình vào một tệp và thực thi nó:

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

Bây giờ thực hiện nó.

sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt

Cách tiếp cận này giúp câu truy vấn của bạn được tổ chức và có thể tái sử dụng, và bạn có thể quản lý phiên bản chúng bằng git giống như bất kỳ mã nào khác.

Xử lý hàng loạt nhiều truy vấn

Nếu bạn cần chạy nhiều truy vấn liên quan và lưu từng kết quả riêng, bạn có thể viết script như sau:

#!/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

Lưu cái này dưới dạng script, làm cho nó có thể thực thi bằng chmod +x, và bạn sẽ có một công cụ truy vấn hàng loạt có thể tái sử dụng.

Giám sát các truy vấn chạy lâu

Khi bạn chạy các truy vấn có thể mất một thời gian, bạn muốn thấy tiến trình hoặc ít nhất biết rằng chúng vẫn đang hoạt động.

Kết hợp truy vấn của bạn với kết quả trạng thái:

(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

Đối với các truy vấn lâu hơn, hãy chạy chúng ở chế độ nền và theo dõi danh sách tiến trình MySQL:

sudo mysql -u root -p -e "USE employees; SELECT * FROM massive_table;" > output.txt &
sudo watch -n 5 'mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -A 5 "SELECT"'

Điều này chạy truy vấn của bạn trong nền trong khi hiển thị danh sách tiến trình mỗi 5 giây, để bạn có thể thấy nó vẫn đang hoạt động và đã tiến triển đến đâu.

Lọc và Xử lý Kết quả

Khi bạn đã có kết quả truy vấn trong một tệp văn bản, bạn có thể sử dụng các công cụ chuẩn của Linux để xử lý chúng thêm. Dưới đây là một số mẫu hữu ích:

Đếm số hàng kết quả (không bao gồm tiêu đề):

tail -n +2 queryresults.txt | wc -l

Trích xuất các cột cụ thể bằng awk:

awk '{print $1, $3}' queryresults.txt

Tìm kiếm các mẫu cụ thể trong kết quả:

grep -i "engineering" dept_distribution.txt

Sắp xếp kết quả theo một cột số:

tail -n +2 queryresults.txt | sort -k3 -n

Xử lý ký tự đặc biệt và tập dữ liệu lớn

Khi dữ liệu của bạn chứa các ký tự đặc biệt, tab hoặc dòng mới, kết quả xuất mặc định có thể bị lộn xộn, vì vậy hãy sử dụng các tùy chọn --batch và --raw để có kết quả sạch hơn:

sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt

Đối với các truy vấn trả về hàng triệu bản ghi, bạn có thể gặp phải vấn đề về bộ nhớ, thay vì tải tất cả vào bộ nhớ, hãy truyền dữ liệu kết quả theo luồng:

sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz

Tùy chọn --quick cho MySQL biết lấy các hàng một lần thay vì lưu trữ toàn bộ tập kết quả, và việc đưa qua gzip sẽ nén dữ liệu đầu ra ngay lập tức, tiết kiệm dung lượng đĩa.

Tạo bản sao lưu cơ sở dữ liệu nhanh

Mặc dù về kỹ thuật đây không phải là chạy các truy vấn, bạn vẫn có thể sử dụng các kỹ thuật dòng lệnh tương tự để tạo các bản sao cơ sở dữ liệu nhanh bằng lệnh mysqldump.

sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz

Hoặc sao lưu chỉ các bảng cụ thể:

sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz

Lên lịch báo cáo tự động

Kết hợp tất cả những gì chúng ta đã học để tạo báo cáo hàng ngày tự động sử dụng cron với sự trợ giúp của script bash sau.

#!/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"

Thêm nó vào cron để chạy hàng ngày vào lúc 6 giờ sáng:

0 6 * * * /usr/local/bin/generate_db_report.sh

Tóm tắt

Chúng tôi đã chia sẻ một số mẹo về Linux mà bạn, với vai trò là quản trị viên hệ thống, có thể thấy hữu ích khi tự động hóa các công việc hàng ngày trên Linux hoặc thực hiện chúng dễ dàng hơn.

Điều quan trọng cần rút ra ở đây là bạn không phải lúc nào cũng cần mở MySQL shell hay sử dụng các công cụ giao diện nặng để làm việc với cơ sở dữ liệu; dòng lệnh mang lại cho bạn tốc độ, khả năng tự động hóa và khả năng tích hợp các thao tác cơ sở dữ liệu vào các script shell và quy trình làm việc hiện có của bạn.

Bạn có mẹo nào khác mà bạn muốn chia sẻ với cộng đồng không? Nếu có, hãy làm điều đó bằng cách sử dụng biểu mẫu bình luận bên dưới. :