Linux 명령줄에서 MySQL 쿼리 실행하는 방법

데이터베이스 서버 관리 책임이 있는 경우, 때때로 쿼리를 실행하고 이를 신중하게 검사해야 할 필요가 있습니다. MySQL/MariaDB 셸에서 이를 수행할 수 있지만, 이 팁을 사용하면 Linux 명령어 라인에서 직접 MySQL/MariaDB 쿼리를 실행하고 결과를 나중에 검토할 수 있도록 파일로 저장할 수 있습니다(쿼리 결과가 많은 레코드를 반환하는 경우 특히 유용합니다).

더 복잡한 쿼리로 넘어가기 전에 명령줄에서 MYSQL 쿼리를 직접 실행하는 몇 가지 간단한 예를 살펴보겠습니다.

예제 데이터베이스 설정

명령어를 살펴보기 전에, 이 가이드 전체에서 사용할 예제 데이터베이스를 설정해 봅시다. 그래야 직접 따라 해 보고 이러한 기술들을 자신의 시스템에서 연습할 수 있습니다.

tecmintdb 데이터베이스 생성

먼저, tecmintdb 데이터베이스와 tutorials 테이블을 생성해 봅시다:

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

다음으로, 데이터베이스 tecmintdb에 tutorials라는 이름의 테이블을 만들려면 아래 명령을 실행하세요:

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

데이터가 삽입되었는지 확인하세요:

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

직원 데이터베이스 만들기

이제 여러 관련 테이블이 있는 좀 더 복잡한 employees 데이터베이스를 만들어 봅시다. 이것이 우리가 더 고급 쿼리 예제에 사용할 데이터베이스입니다:

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

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

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

보다 복잡한 조인을 위해 departments 테이블을 만드세요:

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

employees와 departments를 연결하기 위해 dept_emp 테이블을 생성하세요:

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

모든 것이 올바르게 설정되었는지 확인하세요:

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

이제 두 데이터베이스에 샘플 데이터를 설정했으므로 이 가이드의 모든 예제를 따라할 수 있습니다. tecmintdb 데이터베이스는 간단한 쿼리에 적합하며, employees 데이터베이스에서는 조인이나 집계와 같은 더 복잡한 작업을 연습할 수 있습니다.

기본 쿼리 실행

서버의 모든 데이터베이스를 보려면 다음 명령을 실행할 수 있습니다:

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

다음으로, 데이터베이스 tecmintdb에 tutorials라는 이름의 데이터베이스 테이블을 생성하려면 아래 명령어를 실행하세요:

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

MySQL 쿼리 결과를 파일로 저장하기

다음 명령어를 사용하고 출력 결과를 tee 명령어로 전달한 후, 출력을 저장할 파일 이름을 지정하겠습니다.

예시를 위해, 우리는 employees라는 데이터베이스와 employees 테이블과 salaries 테이블 간의 간단한 조인을 사용할 것입니다. 실제 상황에서는 따옴표 안에 SQL 쿼리를 입력하고 Enter를 누르기만 하면 됩니다.

데이터베이스 사용자의 비밀번호를 입력하라는 메시지가 표시됩니다:

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

cat 명령어를 사용하여 쿼리 결과를 확인하세요.

cat queryresults.txt

명령어줄에서 MySQL/MariaDB 쿼리 실행하기

쿼리 결과가 일반 텍스트 파일로 있으면 다른 명령어 기반 유틸리티를 사용하여 레코드를 더 쉽게 처리할 수 있습니다. 기본 사항을 확인했으니, 명령어 기반 데이터베이스 작업을 더욱 효율적으로 해줄 몇 가지 고급 기법을 살펴보겠습니다.

가독성을 위한 출력 형식 지정

기본 테이블 형식은 터미널에서 보기에는 좋지만, 때로는 다른 형식이 필요할 때가 있습니다. 특히 열이 많은 테이블을 다룰 때 유용한 세로 형식으로 결과를 출력할 수 있습니다:

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

끝의 \G는 각 행을 테이블 대신 세로로 표시하기 때문에, 좁은 가로 테이블을 보는 대신 다음과 비슷하게 표시됩니다:

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

CSV 형식으로 내보내기

쿼리 결과를 스프레드시트 애플리케이션이나 다른 도구로 가져와야 할 때, CSV 형식이 가장 좋습니다:

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

이 명령은 출력을 sed를 통해 전달하여 탭을 쉼표로 대체함으로써 Excel, LibreOffice Calc 또는 기타 스프레드시트 소프트웨어에서 깔끔하게 열리는 올바른 CSV 파일을 만듭니다.

비밀번호 입력 없이 쿼리 실행

크론 작업이나 스크립트를 사용하여 데이터베이스 작업을 자동화할 때 매번 비밀번호를 수동으로 입력하고 싶지 않다면, 바로 그때 MySQL 구성 파일이 필요합니다.

다음 내용으로 ~/.my.cnf 파일을 생성하세요:

[client]
user=root
password=your_password_here

그런 다음 본인만 읽을 수 있도록 안전하게 보호하세요:

chmod 600 ~/.my.cnf

이제 -p 플래그 없이, 그리고 프롬프트 없이 쿼리를 실행할 수 있습니다:

mysql -e "SHOW DATABASES;"

기억하세요, 비밀번호를 일반 텍스트 파일에 저장하는 것은 보안상 문제가 있을 수 있으므로, 접근을 제어할 수 있는 서버에서만 이 방식을 사용하고, 운영 환경에서는 MySQL의 보다 안전한 인증 방법을 사용하는 것을 고려하세요.

복잡한 다중 라인 쿼리 실행

때때로 여러 조인, 서브쿼리 또는 복잡한 조건을 다룰 때처럼 쿼리가 너무 복잡해서 한 줄 명령어로 작성하기 어려울 때가 있습니다.

SQL을 파일에 넣고 실행할 수 있습니다:

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

이제 실행해.

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

이 접근 방식은 쿼리를 체계적이고 재사용 가능하게 유지하며, 다른 코드처럼 Git으로 버전 관리를 할 수 있습니다.

여러 쿼리 일괄 처리

여러 관련 쿼리를 실행하고 각 결과를 별도로 저장해야 하는 경우, 스크립트를 작성할 수 있습니다:

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

이것을 스크립트로 저장하고 chmod +x로 실행 가능하게 만든 다음, 재사용 가능한 배치 쿼리 도구를 얻을 수 있습니다.

장기 실행 쿼리 모니터링

시간이 좀 걸릴 수 있는 쿼리를 실행할 때는 진행 상황을 확인하거나 적어도 쿼리가 여전히 실행 중이라는 것을 알 수 있기를 원합니다.

쿼리를 상태 출력과 결합하세요:

(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

더 긴 쿼리는 백그라운드에서 실행하고 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"'

이 기능은 쿼리를 백그라운드에서 실행하면서 5초마다 프로세스 목록을 표시하므로, 쿼리가 여전히 실행 중인지와 얼마나 진행되었는지 확인할 수 있습니다.

결과 필터링 및 처리

쿼리 결과를 텍스트 파일로 얻으면 표준 Linux 도구를 사용하여 추가로 처리할 수 있습니다. 유용한 몇 가지 패턴은 다음과 같습니다:

결과 행의 수를 세세요(헤더 제외):

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

awk를 사용하여 특정 열 추출:

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

결과에서 특정 패턴 검색:

grep -i "engineering" dept_distribution.txt

숫자 열 기준으로 결과 정렬:

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

특수 문자 및 대용량 데이터 처리

데이터에 특수 문자, 탭 또는 줄 바꿈이 포함되어 있는 경우, 기본 출력이 지저분해질 수 있으므로, 보다 깔끔한 출력을 위해 --batch 및 --raw 옵션을 사용하세요:

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

수백만 행을 반환하는 쿼리의 경우, 모든 데이터를 메모리에 로드하는 대신 메모리 문제에 직면할 수 있으므로 결과를 스트리밍하는 것이 좋습니다:

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

--quick 옵션은 MySQL이 전체 결과 집합을 버퍼링하는 대신 한 번에 한 행씩 가져오도록 지시하며, gzip을 통해 파이프하면 출력을 실시간으로 압축하여 디스크 공간을 절약할 수 있습니다.

빠른 데이터베이스 백업 만들기

기술적으로 쿼리를 실행하는 것은 아니지만, mysqldump 명령어를 사용하여 유사한 명령줄 기법으로 빠르게 데이터베이스 덤프를 생성할 수 있습니다.

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

또는 특정 테이블만 백업:

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

자동 보고서 일정 설정

지금까지 다룬 내용을 모두 결합하여 다음 bash 스크립트를 사용해 cron으로 자동 일일 보고서를 생성하세요.

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

매일 오전 6시에 실행되도록 크론에 추가하세요:

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

요약

우리는 시스템 관리자로서 일상적인 리눅스 작업을 자동화하거나 보다 쉽게 수행할 때 유용하게 사용할 수 있는 여러 가지 리눅스 팁을 공유했습니다.

여기서 핵심 요점은 데이터베이스 작업을 위해 항상 MySQL 셸을 실행하거나 무거운 GUI 도구를 사용할 필요가 없다는 것입니다. 명령줄은 속도, 자동화 기능, 그리고 기존 셸 스크립트 및 워크플로우에 데이터베이스 작업을 통합할 수 있는 능력을 제공합니다.

커뮤니티의 다른 사람들과 공유하고 싶은 다른 조언이 있나요? 있다면 아래 댓글 양식을 사용해 주세요. :