Hur man kör MySQL-frågor från Linux-kommandoraden
Om du är ansvarig för att hantera en databasserver kan du från tid till annan behöva köra en fråga och granska den noggrant. Även om du kan göra det från MySQL/MariaDB-shellen, kommer detta tips att låta dig köra MySQL/MariaDB-frågor direkt via Linux-kommandoraden OCH spara resultatet till en fil för senare granskning (detta är särskilt användbart om frågan returnerar många poster).
Låt oss titta på några enkla exempel på att köra MYSQL-frågor direkt från kommandoraden innan vi går vidare till en mer avancerad fråga.
Ställa in exempel-databaser
Innan vi dyker in i kommandona, låt oss sätta upp de exempel-databaser vi kommer att arbeta med genom hela denna guide, så att du kan följa med och öva dessa tekniker på ditt eget system.
Skapar tecmintdb-databasen
Först, låt oss skapa databasen tecmintdb och tabellen tutorials:
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"
Nästa steg, för att skapa en databastabell med namnet tutorials i databasen tecmintdb, kör kommandot nedan:
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
Verifiera att uppgifterna har införts:
sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"
Skapa medarbetardatabasen
Nu ska vi skapa en mer komplex employees-databas med flera relaterade tabeller, detta är databasen vi kommer att använda för de mer avancerade query-exemplen:
sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"
Skapa tabellen 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
Skapa salaries-tabellen:
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
Skapa departments-tabellen för mer komplexa kopplingar:
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
Skapa tabellen dept_emp för att länka employees till 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
Verifiera att allt är korrekt inställt:
sudo mysql -u root -p -e "USE employees; SHOW TABLES;"
Nu har du båda databaserna upprättade med exempeldata, och du kan följa med i alla exempel i den här guiden. Databasen tecmintdb är perfekt för enkla frågor, medan databasen employees låter dig öva på mer komplexa operationer som joins och aggregationer.
Grundläggande frågeexekvering
För att visa alla databaser på din server kan du utföra följande kommando:
sudo mysql -u root -p -e "show databases;"
Nästa steg, för att skapa en databastabell med namnet tutorials i databasen tecmintdb, kör kommandot nedan:
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));"
Spara MySQL-frågeresultat till en fil
Vi kommer att använda följande kommando och skicka utdata till tee-kommandot följt av filnamnet där vi vill lagra utdata.
Som illustration kommer vi att använda en databas som heter employees och en enkel join mellan tabellerna employees och salaries. I ditt eget fall skriver du bara in SQL-frågan mellan citationstecknen och trycker på Enter.
Observera att du kommer att uppmanas att ange lösenordet för databasanvändaren:
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
Visa frågeresultaten med hjälp av kommandot cat.
cat queryresults.txt
Kör MySQL/MariaDB-frågor från kommandoraden
Med frågeresultaten i vanliga textfiler kan du bearbeta posterna enklare med andra kommandoradsverktyg. Nu när du har sett grunderna, låt oss utforska några mer avancerade tekniker som kommer att göra ditt kommandoradsdatabasarbete ännu mer effektivt.
Formatera utdata för bättre läsbarhet
Standardtabellformatet är utmärkt för visning i terminalen, men ibland behöver du olika format. Du kan visa resultat i vertikalt format, vilket är särskilt användbart när du arbetar med tabeller som har många kolumner:
sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"
Koden \G i slutet visar varje rad vertikalt istället för i en tabell, så istället för att se en trång horisontell tabell får du något som liknar:
*************************** 1. row ***************************
emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
last_name: Facello
gender: M
hire_date: 1984-06-02
Exportera till CSV-format
När du behöver importera frågeresultat till kalkylbladsprogram eller andra verktyg är CSV-format det bästa alternativet:
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
Detta skickar utdata genom sed för att ersätta tabbar med kommatecken, vilket skapar en korrekt CSV-fil som öppnas smidigt i Excel, LibreOffice Calc eller annan kalkylprogramvara.
Köra frågor utan lösenordsfrågor
Om du automatiserar databasuuppgifter med cron-jobb eller skript vill du inte skriva in lösenord manuellt varje gång, och det är där MySQL-konfigurationsfiler kommer in.
Skapa en fil på ~/.my.cnf med dina uppgifter:
[client]
user=root
password=your_password_here
Säkra det sedan så att bara du kan läsa det:
chmod 600 ~/.my.cnf
Nu kan du köra frågor utan -p-flaggan och utan att bli tillfrågad:
mysql -e "SHOW DATABASES;"
Kom bara ihåg att lagring av lösenord i rena textfiler har säkerhetskonsekvenser, så använd endast detta tillvägagångssätt på servrar där du har kontroll över åtkomsten, och överväg att använda MySQL:s mer säkra autentiseringsmetoder i produktionsmiljöer.
Utföra komplexa flerradiga frågor
Ibland är dina frågor för komplexa för att skriva på en enda kommandorad, särskilt när du hanterar flera joins, underfrågor eller komplexa villkor.
Du kan lägga din SQL i en fil och köra den:
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
Utför det nu.
sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt
Det här tillvägagångssättet håller dina frågor organiserade och återanvändbara, och du kan versionshantera dem med git precis som vilken annan kod som helst.
Batchbearbetning av flera frågor
Om du behöver köra flera relaterade frågor och spara varje resultat separat kan du skriva ett skript:
#!/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
Spara detta som ett skript, gör det körbart med chmod +x, och du har ett återanvändbart verktyg för batch-frågor.
Övervakning av långvariga frågor
När du kör frågor som kan ta ett tag, vill du se framsteg eller åtminstone veta att de fortfarande fungerar.
Kombinera din fråga med statusutdata:
(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
För ännu längre frågor, kör dem i bakgrunden och övervaka MySQL-processlistan:
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"'
Detta kör din fråga i bakgrunden samtidigt som processlistan visas var femte sekund, så att du kan se att den fortfarande arbetar och hur mycket framsteg som har gjorts.
Filtrering och bearbetning av resultat
När du har frågeresultat i en textfil kan du använda vanliga Linux-verktyg för att bearbeta dem vidare. Här är några användbara mönster:
Räkna antalet resultatrader (exklusive rubriken):
tail -n +2 queryresults.txt | wc -l
Extrahera specifika kolumner med awk:
awk '{print $1, $3}' queryresults.txt
Sök efter specifika mönster i resultaten:
grep -i "engineering" dept_distribution.txt
Sortera resultat efter en numerisk kolumn:
tail -n +2 queryresults.txt | sort -k3 -n
Hantera specialtecken och stora datamängder
När dina data innehåller specialtecken, tabbar eller radbrytningar kan standardutmatningen bli rörig, så använd --batch och --raw-alternativen för en renare utmatning:
sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt
För förfrågningar som ger tillbaka miljontals rader kan du stöta på minnesproblem; istället för att ladda allt i minnet, strömma resultaten:
sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz
Alternativet --quick talar om för MySQL att hämta rader en åt gången istället för att buffra hela resultatmängden, och att köra genom gzip komprimerar outputen direkt, vilket sparar diskutrymme.
Skapa snabba databassäkerhetskopior
Även om detta tekniskt sett inte innebär att köra frågor, kan du använda liknande kommandoradstekniker för att skapa snabba databas-dumpar med kommandot mysqldump.
sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz
Eller säkerhetskopiera bara specifika tabeller:
sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz
Schemalägga automatiska rapporter
Kombinera allt vi har gått igenom för att skapa automatiska dagliga rapporter med cron med hjälp av följande 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"
Lägg till det i cron för att köra det dagligen klockan 06:00:
0 6 * * * /usr/local/bin/generate_db_report.sh
Sammanfattning
Vi har delat flera Linux-tips som du, som systemadministratör, kan tycka är användbara när det gäller att automatisera dina dagliga Linux-uppgifter eller utföra dem på ett enklare sätt.
Huvudpoängen här är att du inte alltid behöver starta MySQL-skalet eller använda tunga GUI-verktyg för att arbeta med dina databaser; kommandoraden ger dig hastighet, automatiseringsmöjligheter och möjligheten att integrera databasoperationer i dina befintliga skal-skript och arbetsflöden.
Har du några andra tips som du vill dela med resten av gemenskapen? Om så är fallet, vänligen gör det genom kommentarsformuläret nedan. :