Cara Menjalankan Query MySQL dari Command Line Linux

Jika Anda bertanggung jawab mengelola server database, dari waktu ke waktu Anda mungkin perlu menjalankan sebuah query dan memeriksanya dengan seksama. Meskipun Anda bisa melakukannya dari shell MySQL/MariaDB, tips ini akan memungkinkan Anda mengeksekusi Query MySQL/MariaDB langsung menggunakan command line Linux DAN menyimpan hasilnya ke file untuk diperiksa nanti (ini sangat berguna jika query menghasilkan banyak data).

Mari kita lihat beberapa contoh sederhana menjalankan query MYSQL langsung dari baris perintah sebelum kita beralih ke query yang lebih canggih.

Menyiapkan Contoh Basis Data

Sebelum kita menyelami perintah-perintahnya, mari kita siapkan database contoh yang akan kita gunakan sepanjang panduan ini, sehingga Anda bisa mengikuti dan mempraktikkan teknik-teknik ini di sistem Anda sendiri.

Membuat Database tecmintdb

Pertama, mari kita buat database tecmintdb dan tabel tutorials:

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

Selanjutnya, untuk membuat tabel database bernama tutorials di database tecmintdb, jalankan perintah berikut:

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

Verifikasi data telah dimasukkan:

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

Membuat Database Karyawan

Sekarang, mari kita buat basis data employees yang lebih kompleks dengan beberapa tabel terkait, ini adalah basis data yang akan kita gunakan untuk contoh kueri yang lebih lanjut:

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

Buat tabel 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

Buat tabel 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

Buat tabel departments untuk join yang lebih kompleks:

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

Buat tabel dept_emp untuk menghubungkan employees ke 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

Periksa semua sudah disiapkan dengan benar:

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

Sekarang Anda telah menyiapkan kedua database dengan data contoh, dan Anda bisa mengikuti semua contoh dalam panduan ini. Database tecmintdb sangat cocok untuk kueri sederhana, sementara database employees memungkinkan Anda berlatih operasi yang lebih kompleks seperti join dan agregasi.

Eksekusi Kueri Dasar

Untuk melihat semua database di server Anda, Anda dapat menjalankan perintah berikut:

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

Selanjutnya, untuk membuat tabel basis data bernama tutorials di basis data tecmintdb, jalankan perintah berikut:

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

Menyimpan Hasil Query MySQL ke File

Kita akan menggunakan perintah berikut dan mengalirkan output ke perintah tee diikuti dengan nama file tempat kita ingin menyimpan output.

Sebagai ilustrasi, kita akan menggunakan database bernama employees dan join sederhana antara tabel employees dan salaries. Dalam kasus Anda sendiri, cukup ketik query SQL di antara tanda kutip dan tekan Enter.

Perlu dicatat bahwa Anda akan diminta untuk memasukkan kata sandi untuk pengguna database:

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

Lihat hasil kueri dengan bantuan perintah cat.

cat queryresults.txt

Jalankan Query MySQL/MariaDB dari Commandline

Dengan hasil kueri dalam file teks biasa, Anda dapat memproses catatan lebih mudah menggunakan utilitas baris perintah lainnya. Sekarang setelah Anda melihat dasarnya, mari kita jelajahi beberapa teknik yang lebih maju yang akan membuat pekerjaan database di baris perintah Anda menjadi lebih efisien.

Memformat Output untuk Membaca Lebih Mudah

Format tabel default sangat baik untuk dilihat di terminal, tetapi kadang-kadang Anda memerlukan format yang berbeda. Anda dapat menampilkan hasil dalam format vertikal, yang sangat berguna saat menangani tabel dengan banyak kolom:

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

\G di akhir menampilkan setiap baris secara vertikal bukan dalam bentuk tabel, jadi alih-alih melihat tabel horizontal yang sesak, Anda akan mendapatkan sesuatu seperti:

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

Mengekspor ke Format CSV

Saat Anda perlu mengimpor hasil kueri ke aplikasi spreadsheet atau alat lain, format CSV adalah pilihan terbaik Anda:

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

Ini mengalirkan output melalui sed untuk mengganti tab dengan koma, sehingga membuat file CSV yang tepat yang dapat dibuka dengan rapi di Excel, LibreOffice Calc, atau perangkat lunak spreadsheet lainnya.

Menjalankan Query Tanpa Permintaan Kata Sandi

Jika Anda mengotomatiskan tugas basis data dengan cron job atau skrip, Anda tidak ingin memasukkan kata sandi secara manual setiap kali, di situlah file konfigurasi MySQL berperan.

Buat file di ~/.my.cnf dengan kredensial Anda:

[client]
user=root
password=your_password_here

Lalu amankan supaya hanya Anda yang bisa membacanya:

chmod 600 ~/.my.cnf

Sekarang Anda dapat menjalankan kueri tanpa menggunakan flag -p dan tanpa diminta:

mysql -e "SHOW DATABASES;"

Ingat, menyimpan kata sandi dalam file teks biasa memiliki risiko keamanan, jadi gunakan pendekatan ini hanya pada server yang aksesnya Anda kontrol, dan pertimbangkan untuk menggunakan metode autentikasi MySQL yang lebih aman untuk lingkungan produksi.

Menjalankan Query Multi-Baris yang Kompleks

Terkadang kueri Anda terlalu kompleks untuk ditulis dalam satu baris perintah, terutama ketika Anda menangani beberapa join, subquery, atau kondisi yang rumit.

Anda dapat menaruh SQL Anda dalam sebuah file dan mengeksekusinya:

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

Sekarang jalankan.

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

Pendekatan ini menjaga pertanyaan Anda tetap terorganisir dan dapat digunakan kembali, serta Anda bisa mengontrol versi mereka dengan git seperti halnya kode lainnya.

Pemrosesan Batch Beberapa Kueri

Jika Anda perlu menjalankan beberapa kueri terkait dan menyimpan setiap hasil secara terpisah, Anda dapat membuat skripnya:

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

Simpan ini sebagai skrip, buat bisa dijalankan dengan chmod +x, dan Anda sudah memiliki alat kueri batch yang bisa digunakan kembali.

Memantau Query yang Berjalan Lama

Saat Anda menjalankan kueri yang mungkin memakan waktu lama, Anda ingin melihat kemajuan atau setidaknya mengetahui bahwa mereka masih berjalan.

Gabungkan kueri Anda dengan output status:

(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

Untuk kueri yang lebih panjang, jalankan di latar belakang dan pantau daftar proses 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"'

Ini menjalankan kueri Anda di latar belakang sambil menampilkan daftar proses setiap 5 detik, sehingga Anda dapat melihat bahwa itu masih berjalan dan seberapa banyak kemajuan yang telah dicapai.

Menyaring dan Memproses Hasil

Setelah Anda memiliki hasil kueri dalam file teks, Anda dapat menggunakan alat Linux standar untuk memprosesnya lebih lanjut. Berikut beberapa pola yang berguna:

Hitung jumlah baris hasil (tidak termasuk header):

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

Ekstrak kolom tertentu menggunakan awk:

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

Cari pola tertentu dalam hasil:

grep -i "engineering" dept_distribution.txt

Urutkan hasil berdasarkan kolom angka:

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

Menangani Karakter Khusus dan Dataset Besar

Ketika data Anda mengandung karakter khusus, tab, atau baris baru, output default bisa menjadi berantakan, jadi gunakan opsi --batch dan --raw untuk output yang lebih rapi:

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

Untuk kueri yang menghasilkan jutaan baris, Anda mungkin akan menghadapi masalah memori, alih-alih memuat semuanya ke dalam memori, alirkan hasilnya:

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

Opsi --quick memberitahu MySQL untuk mengambil baris satu per satu alih-alih menyimpan seluruh set hasil terlebih dahulu, dan menyalurkannya melalui gzip akan mengompres output secara langsung, menghemat ruang disk.

Membuat Cadangan Database dengan Cepat

Meskipun ini secara teknis bukan menjalankan query, Anda dapat menggunakan teknik baris perintah serupa untuk membuat dump database cepat dengan perintah mysqldump.

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

Atau cadangkan hanya tabel tertentu:

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

Penjadwalan Laporan Otomatis

Gabungkan semua yang telah kita bahas untuk membuat laporan harian otomatis menggunakan cron dengan bantuan skrip bash berikut.

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

Tambahkan ke cron untuk dijalankan setiap hari pukul 6 pagi:

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

Ringkasan

Kami telah membagikan beberapa tips Linux yang mungkin berguna bagi Anda sebagai administrator sistem ketika berhubungan dengan otomatisasi tugas Linux harian Anda atau melakukannya dengan lebih mudah.

Inti yang perlu diambil di sini adalah bahwa Anda tidak selalu perlu membuka MySQL shell atau menggunakan alat GUI yang berat untuk bekerja dengan database Anda; baris perintah memberi Anda kecepatan, kemampuan otomatisasi, dan kemampuan untuk mengintegrasikan operasi database ke dalam skrip shell dan alur kerja yang sudah ada.

Apakah Anda memiliki tips lain yang ingin dibagikan dengan komunitas? Jika ya, silakan lakukan melalui formulir komentar di bawah ini. :