Správa databází v MySQL - II

9. 5. 2003 | David Hauzar | Návody | 20278×

Jak udělat databázi ještě bezpečnější. Import a export dat. Protokolování transakcí.

Import dat do databáze

Data můžete do databáze importovat nejrůznějšími způsoby. Například pomocí protokolu BCP, konverzí souborů, pomocí jazyka XML, nebo pomocí souborů ve formátu CSV. My probereme pouze způsob importu a exportu pomocí souborů ve formátu CSV.

Soubor ve formátu CSV je soubor s daty oddělenými čárkami. Je to velmi jednoduchý a přitom efektivní způsob importu a exportu dat.

Import dat do databáze pomocí programu mysqlimport

Program mysqlimport načte data ze zadaného souboru do tabulky, jejíž název je stejný jako název souboru do první tečky.

Například:

mysqlimport Nejakadatabaze Tabulka_Do_Ktere_Chci_Nacist_Data.txt

Tento příkaz načte data ze souboru Tabulka_Do_Ktere_Chci_Nacist_Data.txt do tabulky Tabulka_Do_Ktere_Chci_Nacist_Data, která je v databázi Nejakadatabaze.

Naopak příkaz:

mysqlimport Nejakadatabaze Tabulka._Do_Ktere_Chci_Nacist_Data.txt

načte data do tabulky Tabulka ze souboru Tabulka._Do_Ktere_Chci_Nacist_Data.txt.

Struktura dat v importovaném souboru musí být stejná jako struktura dat v tabulce. Musí se tedy shodovat počet sloupců, datové typy sloupců i pořadí sloupců. Pokud struktura dat stejná nebude, program nahlásí chybu a import dat se neuskuteční.

Program mysqlimport má velké množství přepínačů. V následující tabulce najdete seznam dostupných specifických přepínačů. Kromě uvedených přepínačů můžete použít i standardní přepínače jako -p (heslo) nebo -v (verze)

Přepínače programu mysqlimport

Přepínač	Akce
-d nebo --delete	Před importem odstraní z cílové tabulky všechna data, která v ní byla předtím.
-f nebo --force	Program mysqlimport bude pokračovat ve vkládání dat do tabulky i když dojde k chybám.
-i nebo --ignore	Program bude ignorovat (přeskakovat) všechny záznamy, které mají stejný primární klíč. Program bude přeskakovat záznamy z importovaného souboru.
-L nebo --local	Program mysqlimport použije soubor z místního počítače, nikoli ze serveru MySQL. Toto můžete využít, chcete-li soubor použít místně a importovat jej vzdáleně. Soubor nemusíte nejprve stahovat a pak teprve spustit program mysqlimport.
-l nebo --lock-tables	Uzamkne před importem všechny tabulky, do kterých bude vkládat záznamy. Je to dobré, když importujeme velké množství dat na vytíženém serveru.
-r nebo --replace	Opak přepínače i. Nahradí v tabulce záznam, jehož primární klíč obsahuje stejnou hodnotu.
--fields-enclosed-by=znak	Určuje znak, který uzavírá data sloupců. Mnoho databázových systémů uzavírá data sloupců do uvozovek. Znak uvedený za tímto přepínačem bude považován za obal a nebude tedy vkládán do cílové tabulky. Program mysqlimport implicitně nepředpokládá, že jsou data ohraničená nějakými znaky.
--fields-escaped-by=znak	Určuje, jaký znak bude použit jako znak ukončení (escape character). Znaky někdy bývají ukončeny zpětným lomítkem (\) a znakem nového řádku (\n). Příkaz mysqlimport implicitně nepředpokládá žádné znaky ukončení.
--fields-terminated-by=znak	Určuje oddělovač sloupců. U souborů CSV je oddělovačem zpravidla čárka. Implicitním oddělovačem příkazu mysqlimport je tabulátor.
--fields-optionally-terminated-by=znak	Uvádí, že data mohou být oddělena speciálními znaky.
--lines-terminated-by=řetězec	Uvádí znak ukončující záznam. Program mysqlimport implicitně předpokládá znak nového řádku.
-? nebo --help	Zobrazí úplný seznam přepínačů.

Když tedy budete chtít importovat data do tabulky Zakaznici(ID_Zakaznika, Prijmeni, Adresa, Mesto) v databázi Moje_Databaze, nazvete soubor s daty Zakaznici.txt (nebo Zakaznici.sql ...).

pokud bude mít soubor následující obsah:

"1" , "Kopecký" , "Na Stráni 930" , "Vimperk"
"2" , "Hauzar" , "Kubova Huť 33" , "Vimperk"


použijete k importu dat následující příkaz:

mysqlimport -prl --fields-enclosed-by=" --fields-terminated-by=, Moje_Databaze Zakaznici.txt

Import dat do databáze pomocí příkazu LOAD DATA INFILE

Import pomocí příkazu LOAD DATA INFILE je velmi podobný importu pomocí programu mysqlimport. Rozdíl je v tom, že v prvním případě k importu používáte speciální program mysqlimport, zatímco v druhém příkaz LOAD DATA INFILE, který zadáváte na příkazovém řádku programu MySQL monitor (stejně jako například příkazy jazyka SQL).

Proto můžete příkaz LOAD DATA INFILE použít také ve všech aplikacích, které vytvoříte pomocí rozhraní API (například jazyka PHP). Budete-li tedy chtít vytvořit program, který bude zálohovat data, budete používat příkaz LOAD DATA INFILE.

Abyste mohli příkaz LOAD DATA INFILE použít, musíte spustit program mysql. Budete-li chtít importovat data do tabulky Tabulka, která je v databázi Databaze, ze souboru Data.txt, musíte udělat toto:

USE Databaze;
LOAD DATA INFILE "/home/david/data/data.txt"
  INTO TABLE Tabulka;

Příkaz LOAD DATA INFILE má nepovinné argumenty podobné jako přepínače programu mysqlimport.

• LOCAL

  LOAD DATA LOCAL INFILE "/home/david/data/data.txt"
  INTO TABLE Tabulka;

• LOW_PRIORITY

  MySQL bude čekat, až dotyčnou tabulku nebude nikdo číst.

  LOAD DATA LOW_PRIORITY INFILE "/home/david/data/data.txt"
  INTO TABLE Tabulka;

• REPLACE

  Příkaz přepíše záznamy se stejným primárním klíčem. Tento argument musí být umístěn před klauzulí INTO TABLE.

  LOAD DATA LOW PRIORITY INFILE "/home/david/data/data.txt"
  REPLACE INTO TABLE Tabulka;

• FIELDS

  Za argumentem FIELDS se musí vyskytovat alespoň jedno z následujících klíčových slov:

  • TERMINATED BY znak
  • ENCLOSED BY znak
  • ESCAPED BY znak

  Klíčová slova mají obdobný význam jako příslušné přepínače programu mysqlimport. Pokud použijete stejný příklad jako u programu mysqlimport, mohl by příkaz vypadat takto:

  USE Moje_Databaze;
LOAD DATA INFILE "/home/david/data/data.txt" REPLACE
  REPLACE INTO TABLE Zakaznici
  FIELDS
  TERMINATED BY ","
  ENCLOSED BY """";

Kromě tohoto ale umí příkaz LOAD DATA INFILE ještě něco navíc. Můžete určit sloupce, do kterých mají být data vložena. Hodí se to například v případech, kdy budete chtít do databáze vložit neúplná data. To se stává například v situacích, kdy budete chtít importovat do svojí databáze data z jiné databáze s méně nebo více sloupci.

Příkaz LOAD DATA INFILE s určením sloupců, do kterých se budou vkládat data, by mohl vypadat například takto:

LOAD DATA INFILE "/home/david/data/data.txt" REPLACE
  INTO TABLE Zakaznici
    (ID_Zakaznika, Prijmeni);

Export dat z databáze

Při exportu dat z databáze se může porušit konzistence dat. Pokud tomu chcete zabránit, spusťte program mysqldump s přepínačem --lock tables respektive při exportu příkazem SELECT INTO OUTFILE použijte příkaz LOCK TABLES (viz minulý díl).

Export dat z databáze pomocí programu mysqldump

Program mysqldump je obdobou programu mysqlimport. Pomocí tohoto programu můžete nejen exportovat data z celé databáze nebo vybraných tabulek databáze, ale také exportovat celou databázi. To znamená, že v exportovaném souboru nebudou jenom data, ale i všechny SQL příkazy nezbytné pro vytvoření databáze.

Syntaxe pro exportování celé databáze (tj. nejen dat) je:

mysqldump -u uzivatel -p Databáze > "Vypis_Databaze.txt"

Pokud budete chtít exportovat pouze tabulku Tabulka, zadejte:

mysqldump -u uzivatel -p Databáze Tabulka > "Vypis_Tabulky.txt"

Pro vytváření výpisů můžete použít také přepínač -where a tím můžete do exportovaného souboru vypsat pouze vybrané záznamy:

mysqldump -u uzivatel -p
  -where="ID_Tabulky > 10"   Databáze Tabulka > "Vypis_Vybranych_Dat_Z_Tabulky.txt"

Přepínače programu mysqldump

Přepínač	Akce
--add-drop-table	Doplní příkaz mysqldump o příkaz DROP TABLE IF EXIST, který umístí před příkaz vytvoření nové tabulky. Příkaz DROP TABLE IF EXIST zjistí zda tabulka, která se má vytvořit, už existuje - pokud ano, odstraní ji. Tím se zajistí, že nebudou vytvářeny duplicitní tabulky a že nová verze tabulky (z importovaného souboru) nahradí původní.
--add-locks	Příkazy INSERT budou vloženy mezi příkazy LOCK TABLE a UNLOCK TABLE. Uživatelům pak není dovoleno při vykonávání těchto příkazů manipulovat s tabulkou.
-c nebo --complete_insert	Vloží do příkazů INSERT klíčové slovo DELAYED.
-d nebo --no-data	Exportuje pouze databázi bez dat (zabrání tvorbě příkazů INSERT).
-e nebo --extended-insert	Vygenerují se víceřádkové příkazy INSERT - to vytváří kompaktnější kód a urychluje obnovení databáze.
-F nebo --flush-logs	Před vytvořením výpisu vyprázdní soubor protokolu služby MySQL.
-f nebo --force	Program mysqldump bude pokračovat v exportování i když dojde k neočekávané chybě.
--fields-enclosed-by=znak ...	Příkaz mysqldump umožňuje stejně jako příkaz mysqlimport změnu formátovacích předvoleb.
--full	Doplní příkazy CREATE TABLE o další informace.
-h=název_hostitele nebo --host=název_hostitele	Vytvoří soubor výpisu na určeném hostiteli místo na místním hostiteli (localhost).
-l nebo --lock tables	Uzamkne před vytvořením výpisu všechny tabulky, z nichž bude výpis sestavovat. Pokud chcete mít zálohu konzistentní, zapněte tuto volbu.
-t nebo --no-create-info	Exportuje pouze data (zabrání tvorbě příkazů CREATE TABLE).
--opt	Zapne všechny předvolby, které mohou urychlit export i import databáze (zapne tedy předvolby -- add-locks, --add-drop-table, --extended-insert, --quick)
-q nebo --quick	Nedovolí systému MySQL načíst celý výpis do operační paměti před fyzickým vytvořením souboru. Vynutí zápis do souboru už během čtení informací z databáze.
-T cesta nebo --tab=cesta	Zajistí vytvoření dvou souborů. Jeden bude obsahovat příkazy DDL(příkazy pro tvorbu tabulek) a druhý data. První se bude jmenovat název_tabulky.sql, druhý pak název_tabulky.txt. Argument cesta určuje adresář, v němž budou příslušné soubory vytvořeny. Uvedený adresář musí existovat. Tento příkaz můžete použít pouze tehdy, máte-li privilegia FILE.
-w "klauzule WHERE" nebo --where "klauzule WHERE"	Tento přepínač se používá k exportu jen těch dat, které splňují podmínku stanovenou v klauzuli WHERE.
-? nebo --help	Zobrazí úplný seznam přepínačů.

Export dat z databáze pomocí příkazu SELECT INTO OUTFILE

Příkaz SELECT INTO OUTFILE je protějškem příkazu LOAD DATA INFILE a má také stejné předvolby.

Příklad použití:

SELECT * INTO OUTFILE "/home/david/data/Zakaznici.txt"
  FIELDS
  TERMINATED BY = ","
  FROM Zakaznici
  WHERE ID_Zakaznika < 10;

Nyní už máte znalosti potřebné k zálohování, obnovování i přenášení databáze.

Protokolování transakcí

Jestliže zapnete protokolování transakcí, budou se vám do souboru (protokolu) zapisovat vámi určené změny v databázi. Protokolovat můžete chyby, změny dat a struktury tabulek, všechny aktivity serveru MySQL a dlouhotrvající dotazy.

Protokolování transakcí tedy můžete využít:

• Při monitorování běhu mysqld, kritických chyb, které nastaly při běhu mysqld a při vyhledávání chyb v tabulkách vzniklých poškozením tabulky (protokolování chyb).
• Jako doplňek k zálohování (protokolování změn dat a struktury tabulek).
• Při odlaďování chyb v koncové aplikaci (protokolování veškerých událostí).
• Při dolaďování výkonu databáze (protokolování pomalých dotazů).

Protokolování chyb

Pokud zapnete protokolování chyb, bude se do protokolu chyb zapisovat, kdy bylo mysqld spuštěno a zastaveno, dále se do souboru budou zapisovat kritické chyby, které nastaly při běhu, a varování, pokud nějaká tabulka potřebuje zkontrolovat nebo opravit.

Protokolování chyb se zapne, pokud spustíte mysqld takto:

safe_mysqld &

Soubor protokolu chyb bude mít název "jménohostitele".err (ve Windows pak mysql.err) a bude uložen v adresáři data.

Protokolování změn dat a struktury tabulek

Protokolování změn dat a struktury tabulek velmi úzce souvisí se zálohováním. Do protokolu změn dat a struktury tabulek se zapisují všechny změny, k nimž v databázi dojde (soubor protokolu je tedy seznamem SQL příkazů) a jak dlouho každý dotaz trval.

Z protokolu změn dat a struktury tabulek proto můžete obnovit všechna data, která byla do databáze vložena od doby vytvoření protokolu. Je velmi dobré zálohovat databázi jednou za určitý čas (např. programem mysqldump) a po zálohování vytvořit nový soubor protokolu. Pokud tedy budete chtít obnovit databázi, obnovíte ji ze zálohy a k ní pak přidáte aktualizaci ze souboru protokolu.

MySQL bude vytvářet soubor protokolu pokud spustíte službu mysqld s přepínačem --log-bin [=cesta/k/[jméno souboru]].

můžete použít i další přepínače:

• binlog-do-db=jméno_databáze - zapne protokolování jen pro určenou databázi
• binlog-ignore-db=jméno_databáze - vypne protokolování pro určenou adtabázi (ostatní budou protokolovány)

Protokolování změn dat a struktury tabulek zapnete například takto:

safe_mysqld --log-bin[=cesta/k/[jméno souboru]] &

Pokud neurčíte jinak, soubor protokolu změn dat a struktury tabulek bude mít název: názevhostitele-bin a bude se nacházet v adresáři data. V každém případě bude jméno souboru ještě následováno číslem. Po zadání jednoho z následujících příkazů se vytvoří nový soubor protokolu s koncovým číslem o 1 vyšším:

• mysqladmin refresh
• mysqladmin flush-logs
• mysqldump --flush-logs
• FLUSH LOGS
• když je spuštěn nebo restartován server

Protokolování veškerých události v MySQL

Pokud zapnete protokolování veškerých událostí, budou se vám do protokolu všech událostí zapisovat veškeré události, které se na serveru MySQL staly, včetně času jejich spuštění. Je to užitečné především pro ladění chyb v koncové aplikaci.

Protokolování veškerých události v MySQL zapnete takto:

safe_mysqld --log[=cesta/k/[jmeno_souboru]] &

Pokud neurčíte jinak, soubor protokolu veškerých událostí se potom obvykle uloží jako:

/var/lib/mysql/názevhostitele.log

Protokolování pomalých dotazů

Můžete ale také protokolovat pouze dotazy, které trvají déle, než long_query_time. Je to užitečné při dolaďování výkonu databáze.

Protokolování pomalých dotazů zapnete takto:

safe_mysqld --log-slow-queries[=cesta/k/[jmeno_souboru]] &

Pokud neurčíte jinak, soubor protokolu pomalých dotazů bude mít název: názevhostitele-slow.log a bude se nacházet v adresáři data.

Závěr

Dnes jste se seznámili se dvěma nástroji pro import dat - programem mysqlimport a příkazem LOAD DATA INFILE. Příkaz LOAD DATA INFILE můžete použít v aplikacích, které vytvoříte pomocí rozhraní API.

K exportu dat můžete použít program mysqldump nebo příkaz SELECT INTO OUTFILE.

V žádném případě nepodceňujte zálohování databáze. O data (nebo celou databázi) můžete přijít vlastním pochybením (stačí zmáčknout několik tlačítek...), pochybením někoho jiného, mechanickým poškozením serveru, může vás o ně připravit hacker (pokud se ale budete řídit tím, co jste se naučili v minulém díle, můžete toto riziko značně minimalizovat),...

Zálohování databáze je sice dobrá věc, pokud ale při případné havárii nechcete přijít o žádná data, musíte ještě zapnout protokolování změn dat a struktury tabulek. Pokud to uděláte, do souboru protokolu změn dat a struktury tabulek se budou zapisovat všechny změny, k nimž v databázi dojde. Můžete ale také ještě protokolovat chyby, veškeré události, které při běhu mysqld nastanou a pomalé dotazy.

V příštím, posledním díle se dozvíte, jak zabránit bobtnání, neůměrnému snižování rychlosti databáze a jak opravit poškozené tabulky. Dále zmíním některé další nástroje správy MySQL.

Související články

Tvorba databází v MySQL - I (Základní pojmy, návrh databáze)
Tvorba databází v MySQL - II (Instalace a používání)
Tvorba databází v MySQL - III (Datové typy)
Tvorba databází v MySQL - IV (Klíče a fulltext)
Tvorba databází v MySQL - V (Základní funkce)
Tvorba databází v MySQL - VI (Datum a čas)
Správa databází v MySQL - I (Zabezpečení)

       

Komentáře

[martin]

jen poznamka pro eventuelni neznale experimentatory, pokud byste chteli exportovat z databaze sloupce typu BLOB pak pouzivejte select into DUMPFILE.
Tato zmena zapricini, ze nedojde k vyhodnocovani escape sekvenci nebo specialnich znaku.
Uvadim to tady proto, jelikoz sam jsem nad tim stravil nejaky cas a zacatecnikum to jiste pomuze.

[bob]

Diky za clanek, coby zacatecnikovi mi pomohl. Akorat by me zajimalo, jak vyexportovat vsechny databaze najednou (treba kvuli zaloze) - bud do jednoho souboru nebo do x souboru, ktere se budou jmenovat vzdy podle exportovane databaze.