Kuinka suorittaa MySQL-kyselyjä Linuxin komentoriviltä

Jos sinut on vastuussa tietokantapalvelimen hallinnasta, saatat ajoittain tarvita suorittaa kyselyn ja tarkastella sitä huolellisesti. Vaikka voit tehdä sen MySQL/MariaDB -kuoren kautta, tämä vinkki antaa sinun suorittaa MySQL/MariaDB -kyselyjä suoraan Linuxin komentoriviltä JA tallentaa tulokset tiedostoon myöhempää tarkastelua varten (tämä on erityisen hyödyllistä, jos kysely palauttaa paljon tietueita).

Katsotaanpa joitakin yksinkertaisia esimerkkejä MYSQL-kyselyjen suorittamisesta suoraan komentoriviltä ennen kuin siirrymme kehittyneempään kyselyyn.

Esimerkkitietokantojen määrittäminen

Ennen kuin sukellamme komentoihin, luodaan esimerkkitietokannat, joiden kanssa työskentelemme tämän oppaan aikana, jotta voit seurata ohjeita ja harjoitella näitä tekniikoita omalla järjestelmälläsi.

Tecmintdb-tietokannan luominen

Ensiksi luodaan tecmintdb-tietokanta ja tutorials-taulu:

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

Seuraavaksi, luodaksesi tietokantataulun nimeltä tutorials tietokantaan tecmintdb, suorita alla oleva komento:

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

Varmista, että tiedot lisättiin:

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

Työntekijöiden tietokannan luominen

Luodaan nyt monimutkaisempi employees-tietokanta, jossa on useita toisiinsa liittyviä tauluja; tätä tietokantaa käytämme edistyneempien kyselyesimerkkien yhteydessä:

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

Luo employees-taulukko:

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

Luo salaries-taulukko:

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

Luo departments-taulu monimutkaisempia liitoksia varten:

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

Luo dept_emp-taulu yhdistämään employees ja 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

Varmista, että kaikki on asetettu oikein:

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

Nyt sinulla on molemmat tietokannat asetettuna esimerkkitiedoilla, ja voit seurata kaikkia tämän oppaan esimerkkejä. tecmintdb-tietokanta sopii hyvin yksinkertaisiin kyselyihin, kun taas employees-tietokanta antaa mahdollisuuden harjoitella monimutkaisempia toimintoja, kuten liitoksia ja aggregointeja.

Peruskyselyn suorittaminen

Näyttääksesi kaikki palvelimesi tietokannat, voit suorittaa seuraavan komennon:

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

Seuraavaksi, luodaksesi tietokantataulun nimeltä tutorials tietokantaan tecmintdb, suorita alla oleva komento:

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-kyselytulosten tallentaminen tiedostoon

Käytämme seuraavaa komentoa ja ohjaamme sen tulosteen tee-komennolle, jonka jälkeen määritämme tiedostonimen, johon haluamme tallentaa tulosteen.

Esimerkiksi käytämme tietokantaa nimeltä employees ja yksinkertaista yhdistämistä employees- ja salaries-taulukoiden välillä. Omassa tapauksessasi kirjoita vain SQL-kysely lainausmerkkien sisään ja paina Enter.

Huomaa, että sinua pyydetään syöttämään tietokantakäyttäjän salasana:

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

Näytä kyselyn tulokset cat-komennon avulla.

cat queryresults.txt

Suorita MySQL/MariaDB-kyselyitä komentoriviltä

Kun kyselytulokset ovat tavallisissa tekstimuotoisissa tiedostoissa, voit käsitellä tietueita helpommin muiden komentorivityökalujen avulla. Nyt kun olet nähnyt perusteet, tutustutaan joihinkin edistyneempiin tekniikoihin, jotka tekevät komentorivipohjaisesta tietokantatyöskentelystäsi vielä tehokkaampaa.

Tulosteen muotoilu paremman luettavuuden saavuttamiseksi

Oletustaulukkoformaatti on hyvä tarkastella terminaalissa, mutta joskus tarvitset erilaisia formaatteja. Voit tulostaa tulokset pystysuorassa muodossa, mikä on erityisen hyödyllistä, kun käsittelet taulukoita, joissa on paljon sarakkeita:

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

Viimeinen \G näyttää jokaisen rivin pystysuunnassa taulukon sijaan, joten näet ahtaan vaakasuoran taulukon sijasta jotain tällaista:

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

Vienti CSV-muodossa

Kun sinun täytyy tuoda kyselyn tuloksia taulukkolaskentaohjelmiin tai muihin työkaluihin, CSV-muoto on paras vaihtoehto:

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

Tämä ohjaa tulosteen sed-ohjelman läpi korvaamaan sarkaimet pilkuilla, luoden kunnollisen CSV-tiedoston, joka avautuu moitteettomasti Excelissä, LibreOffice Calcissa tai missä tahansa muussa taulukkolaskentaohjelmassa.

Kyselyjen suorittaminen ilman salasanakehotteita

Jos automatisoit tietokantatehtäviä cron-töillä tai skripteillä, et halua syöttää salasanoja käsin joka kerta, ja juuri siihen MySQLin kokoonpanotiedostot tulevat kuvaan.

Luo tiedosto ~/.my.cnf käyttäjätiedoillasi:

[client]
user=root
password=your_password_here

Sitten varmista, että vain sinä voit lukea sen:

chmod 600 ~/.my.cnf

Nyt voit suorittaa kyselyjä ilman -p -lipuketta ja ilman, että sinulta kysytään salasanaa:

mysql -e "SHOW DATABASES;"

Muista vain, että salasanojen tallentaminen tavallisiin tekstitiedostoihin vaikuttaa tietoturvaan, joten käytä tätä lähestymistapaa vain palvelimilla, joiden pääsyn hallitset, ja harkitse MySQL:n turvallisempien todennusmenetelmien käyttöä tuotantoympäristöissä.

Monimutkaisten monirivisten kyselyjen suorittaminen

Joskus kyselysi ovat liian monimutkaisia kirjoitettavaksi yhdelle komentoriville, erityisesti kun käsittelet useita liittymiä, alikyselyitä tai monimutkaisia ehtoja.

Voit laittaa SQL-kyselysi tiedostoon ja suorittaa sen:

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

Suorita se nyt.

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

Tämä lähestymistapa pitää kyselysi järjestettyinä ja uudelleenkäytettävinä, ja voit hallita niiden versioita gitillä kuten mitä tahansa muuta koodia.

Eräprosessointi useille kyselyille

Jos sinun tarvitsee suorittaa useita toisiinsa liittyviä kyselyjä ja tallentaa jokainen tulos erikseen, voit kirjoittaa skriptin:

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

Tallenna tämä skriptinä, tee siitä suoritettava komennolla chmod +x, ja sinulla on uudelleenkäytettävä eräajokyselytyökalu.

Pitkien kyselyjen seuranta

Kun suoritat kyselyitä, jotka saattavat kestää hetken, haluat nähdä etenemisen tai ainakin tietää, että ne toimivat edelleen.

Yhdistä kyselysi tilan tulosteen kanssa:

(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

Pidempienkin kyselyjen ajamiseksi suorita ne taustalla ja seuraa MySQL-prosessilistaa:

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

Tämä suorittaa kyselysi taustalla samalla kun se näyttää prosessiluettelon viiden sekunnin välein, jotta voit nähdä, että se on edelleen käynnissä ja kuinka paljon edistystä on saavutettu.

Tulosten suodatus ja käsittely

Kun sinulla on kyselytulokset tekstimuodossa, voit käyttää tavallisia Linux-työkaluja niiden jatkokäsittelyyn. Tässä on joitakin hyödyllisiä kuvioita:

Laske tulosrivien määrä (ilman otsikkoriviä):

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

Erota tietyt sarakkeet käyttämällä awkia:

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

Etsi tiettyjä kuvioita tuloksista:

grep -i "engineering" dept_distribution.txt

Lajittele tulokset numeerisen sarakkeen mukaan:

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

Erikoismerkkien ja suurten tietoaineistojen käsittely

Kun tietosi sisältävät erikoismerkkejä, sarkaimia tai rivinvaihtoja, oletuslähtö voi mennä sotkuiseksi, joten käytä --batch ja --raw -vaihtoehtoja selkeämpää tulostusta varten:

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

Kyselyissä, jotka palauttavat miljoonia rivejä, saatat kohdata muistiongelmia; sen sijaan, että lataat kaiken muistiin, suoratoista tulokset:

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

--quick-valinta kertoo MySQL:lle noutamaan rivit yksitellen kokonaisen tulosjoukon tallentamisen sijaan, ja gzipin läpi putkittaminen pakkaa tulosteen lennossa, säästäen levytilaa.

Nopeiden tietokantavarmuuskopioiden luominen

Vaikka tämä ei teknisesti ottaen suorita kyselyjä, voit käyttää samanlaisia komentorivitekniikoita nopeiden tietokantahäkinnän luomiseen mysqldump-komennolla.

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

Tai varmuuskopioi vain tietyt taulut:

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

Automaattisten raporttien ajastaminen

Yhdistä kaikki käsittelemämme asiat luodaksesi automaattiset päivittäiset raportit käyttämällä cron-ohjelmaa seuraavan bash-skriptin avulla.

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

Lisää se cron-tehtäviin suorittamaan joka päivä klo 6 aamulla:

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

Yhteenveto

Olemme jakaneet useita Linux-vinkkejä, jotka saattavat olla hyödyllisiä sinulle järjestelmänvalvojana, kun automaattistat päivittäisiä Linux-tehtäviäsi tai suoritat ne helpommin.

Tärkein asia tässä on se, että sinun ei aina tarvitse avata MySQL-kuorta tai käyttää raskaita graafisia työkaluja työskennellessäsi tietokantojesi kanssa; komentorivi tarjoaa nopeuden, automaatiomahdollisuudet ja kyvyn integroida tietokantaoperaatiot olemassa oleviin shell-skripteihisi ja työnkulkuihisi.

Onko sinulla muita vinkkejä, joita haluaisit jakaa muun yhteisön kanssa? Jos on, tee se alla olevan kommenttilomakkeen kautta. :