Hoe MySQL-queries uitvoeren vanaf de Linux-opdrachtregel

Als je verantwoordelijk bent voor het beheren van een databaseserver, moet je van tijd tot tijd een query uitvoeren en deze zorgvuldig bekijken. Hoewel je dat kunt doen vanuit de MySQL/MariaDB shell, zal deze tip je in staat stellen om de MySQL/MariaDB queries rechtstreeks via de Linux-opdrachtregel uit te voeren EN de output op te slaan in een bestand voor later onderzoek (dit is vooral handig als de query veel records teruggeeft).

Laten we eens kijken naar enkele eenvoudige voorbeelden van het uitvoeren van MYSQL-queries rechtstreeks vanaf de commandoregel voordat we verder gaan naar een meer geavanceerde query.

Voorbeelddatabases instellen

Voordat we de commando's induiken, laten we de voorbeeld-databases instellen waarmee we gedurende deze gids zullen werken, zodat je mee kunt volgen en deze technieken op je eigen systeem kunt oefenen.

De tecmintdb-database aanmaken

Laten we eerst de tecmintdb database en de tutorials tabel aanmaken:

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

Maak vervolgens een database-tabel genaamd tutorials in de database tecmintdb, door onderstaande opdracht uit te voeren:

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

Controleer of de gegevens zijn ingevoerd:

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

Het maken van de werknemersdatabase

Laten we nu een complexere employees database maken met meerdere gerelateerde tabellen, dit is de database die we zullen gebruiken voor de meer geavanceerde queryvoorbeelden:

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

Maak de employees-tabel:

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

Maak de salaries-tabel aan:

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

Maak de departments-tabel voor complexere 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

Maak de dept_emp-tabel om employees te koppelen aan 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

Controleer of alles correct is ingesteld:

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

Nu heb je beide databases opgezet met voorbeeldgegevens, en je kunt alle voorbeelden in deze gids volgen. De tecmintdb-database is perfect voor eenvoudige queries, terwijl de employees-database je laat oefenen met complexere bewerkingen zoals joins en aggregaties.

Basisopdrachtuitvoering

Om alle databases op uw server te bekijken, kunt u de volgende opdracht uitvoeren:

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

Vervolgens, om een database tabel genaamd tutorials te maken in de database tecmintdb, voer je het onderstaande commando uit:

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-queryresultaten opslaan in een bestand

We zullen de volgende opdracht gebruiken en de uitvoer doorgeven aan het tee-commando, gevolgd door de bestandsnaam waarin we de uitvoer willen opslaan.

Ter illustratie gebruiken we een database genaamd employees en een eenvoudige join tussen de tabellen employees en salaries. In jouw eigen geval typ je gewoon de SQL-query tussen de aanhalingstekens en druk op Enter.

Houd er rekening mee dat u wordt gevraagd het wachtwoord van de databasegebruiker in te voeren:

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

Bekijk de zoekresultaten met behulp van het cat-commando.

cat queryresults.txt

MySQL/MariaDB-query's uitvoeren vanuit de opdrachtregel

Met de queryresultaten in platte tekstbestanden kun je de records gemakkelijker verwerken met andere commandoregelprogramma's. Nu je de basis hebt gezien, laten we enkele geavanceerdere technieken verkennen die je werk met de commandoregeldatabase nog efficiënter zullen maken.

Uitvoer opmaken voor betere leesbaarheid

Het standaardtabelindeling is geweldig voor weergave in de terminal, maar soms heb je andere indelingen nodig. Je kunt resultaten in een verticale indeling weergeven, wat bijzonder handig is bij tabellen met veel kolommen:

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

De \G aan het einde toont elke rij verticaal in plaats van in een tabel, zodat je in plaats van een krappe horizontale tabel iets krijgt als:

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

Exporteren naar CSV-formaat

Wanneer je queryresultaten moet importeren in spreadsheetapplicaties of andere tools, is CSV-formaat je beste keuze:

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

Dit laat de uitvoer door sed lopen om tabs te vervangen door komma's, waardoor een correct CSV-bestand wordt gemaakt dat netjes opent in Excel, LibreOffice Calc of andere spreadsheetsoftware.

Query's uitvoeren zonder wachtwoordprompt

Als je database taken automatiseert met cronjobs of scripts, wil je niet elke keer handmatig wachtwoorden invoeren. Daar komen MySQL-configuratiebestanden van pas.

Maak een bestand aan op ~/.my.cnf met je inloggegevens:

[client]
user=root
password=your_password_here

Beveilig het vervolgens zodat alleen jij het kunt lezen:

chmod 600 ~/.my.cnf

Nu kunt u queries uitvoeren zonder de -p-vlag en zonder dat u wordt gevraagd:

mysql -e "SHOW DATABASES;"

Onthoud dat het opslaan van wachtwoorden in platte tekstbestanden beveiligingsrisico's met zich meebrengt, dus gebruik deze methode alleen op servers waar je de toegang beheert, en overweeg voor productieomgevingen de veiligere authenticatiemethoden van MySQL te gebruiken.

Complexe meervoudige queries uitvoeren

Soms zijn je queries te complex om in één enkele opdrachtregel te schrijven, vooral wanneer je te maken hebt met meerdere joins, subqueries of complexe voorwaarden.

Je kunt je SQL in een bestand plaatsen en uitvoeren:

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

Voer het nu uit.

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

Deze aanpak houdt je queries georganiseerd en herbruikbaar, en je kunt ze met git versioneren net zoals elke andere code.

Batchverwerking van meerdere queries

Als je meerdere gerelateerde queries moet uitvoeren en elk resultaat apart wilt opslaan, kun je dit scripten:

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

Sla dit op als een script, maak het uitvoerbaar met chmod +x, en je hebt een herbruikbare batch-querytool.

Het monitoren van langdurige queries

Wanneer je queries uitvoert die even kunnen duren, wil je de voortgang zien of op zijn minst weten dat ze nog steeds bezig zijn.

Combineer uw query met statusuitvoer:

(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

Voor nog langere queries, voer ze op de achtergrond uit en houd de MySQL-proceslijst in de gaten:

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"'

Dit voert je query op de achtergrond uit terwijl elke 5 seconden de proceslijst wordt weergegeven, zodat je kunt zien dat het nog steeds bezig is en hoeveel voortgang er is geboekt.

Resultaten filteren en verwerken

Zodra je de queryresultaten in een tekstbestand hebt, kun je standaard Linux-tools gebruiken om ze verder te verwerken. Hier zijn enkele nuttige patronen:

Tel het aantal resultaatrijen (exclusief de koptekst):

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

Specifieke kolommen extraheren met awk:

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

Zoek naar specifieke patronen in de resultaten:

grep -i "engineering" dept_distribution.txt

Resultaten sorteren op een numerieke kolom:

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

Omgaan met speciale tekens en grote datasets

Wanneer je gegevens speciale tekens, tabs of nieuwe regels bevatten, kan de standaarduitvoer rommelig worden, dus gebruik de opties --batch en --raw voor een nettere uitvoer:

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

Voor queries die miljoenen rijen teruggeven, kunt u geheugenproblemen tegenkomen; in plaats van alles in het geheugen te laden, kunt u de resultaten streamen:

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

De --quick-optie vertelt MySQL om rijen één voor één op te halen in plaats van de volledige resultaten set in het geheugen te bufferen, en het doorsturen via gzip comprimeert de uitvoer direct, waardoor schijfruimte wordt bespaard.

Snelle databaseback-ups maken

Hoewel dit technisch gezien geen queries uitvoeren is, kun je vergelijkbare commandoregeltechnieken gebruiken om snel database-dumps te maken met het mysqldump-commando.

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

Of maak een back-up van alleen specifieke tabellen:

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

Automatisch rapporten plannen

Combineer alles wat we hebben behandeld om geautomatiseerde dagelijkse rapporten te maken met behulp van cron en het volgende 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"

Voeg het toe aan cron om dagelijks om 6 uur 's ochtends uit te voeren:

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

Samenvatting

We hebben verschillende Linux-tips gedeeld die je, als systeembeheerder, nuttig kunt vinden bij het automatiseren van je dagelijkse Linux-taken of bij het eenvoudiger uitvoeren ervan.

Het belangrijkste punt hier is dat je niet altijd de MySQL-shell hoeft te openen of zware GUI-tools hoeft te gebruiken om met je databases te werken; de commandoregel biedt snelheid, automatiseringsmogelijkheden en de mogelijkheid om databasebewerkingen te integreren in je bestaande shell-scripts en workflows.

Heb je nog andere tips die je met de rest van de gemeenschap wilt delen? Zo ja, doe dit dan via het onderstaande reactieformulier. :