abclinuxu.cz AbcLinuxu.cz itbiz.cz ITBiz.cz HDmag.cz HDmag.cz abcprace.cz AbcPráce.cz
AbcLinuxu hledá autory!
Inzerujte na AbcPráce.cz od 950 Kč
Rozšířené hledání
×
dnes 16:00 | Pozvánky

Spolek OpenAlt zve příznivce otevřených řešení a přístupu na 163. brněnský sraz, který proběhne v pátek 26. dubna od 18:00 v indické restauraci Everest na adrese Veveří 61.

Ladislav Hagara | Komentářů: 0
dnes 15:33 | IT novinky

Všem dívkám v ICT vše nejlepší k dnešnímu Mezinárodnímu dni dívek v ICT (Wikipedie, Girls in ICT Day, YouTube).

Ladislav Hagara | Komentářů: 2
dnes 12:22 | Nová verze

Byla vydána verze 1.12 systému pro správu a verzování zdrojových kódů Apache Subversion (Wikipedie). Přehled novinek v poznámkách k vydání.

Ladislav Hagara | Komentářů: 1
dnes 12:11 | Zajímavý článek

Mozilla zveřejnila každoroční Internet Health Report, který popisuje aktuální společenská témata související s využíváním Internetu. Tentokrát se dotýkají mj. etiky algoritmů strojového učení, cílené reklamy a „chytrých měst“.

Fluttershy, yay! | Komentářů: 1
dnes 08:44 | Nová verze

Webová aplikace pro správu repozitářů v gitu Gitea vyšla v nové verzi 1.8.0. Nově poskytuje OAuth 2.0, umožňuje archivaci repozitářů, skrývání organizací jako interních či soukromých, zamykání konverzací a mnoho dílčích změn.

Fluttershy, yay! | Komentářů: 5
včera 19:33 | Nová verze

Bylo vydáno OpenBSD 6.5. Opět bez oficiální písně. Nejnovější OpenBSD přináší například OpenSMTPD 6.5.0, LibreSSL 2.9.1 nebo OpenSSH 8.0.

Ladislav Hagara | Komentářů: 1
včera 11:33 | Zajímavý článek

Na oficiálním blogu Raspberry Pi byla představena kniha An Introduction to C & GUI Programming. Kniha je ke stažení zdarma (pdf). Papírovou verzi lze koupit za 10 liber.

Ladislav Hagara | Komentářů: 14
včera 10:33 | Nová verze

Byla vydána nová verze 4.0.0 otevřeného emulátoru procesorů a virtualizačního nástroje QEMU (Wikipedie). Přispělo 220 vývojářů. Provedeno bylo více než 3 100 commitů. Přehled úprav a nových vlastností v seznamu změn.

Ladislav Hagara | Komentářů: 0
včera 10:00 | Nová verze

Google Chrome 74 byl prohlášen za stabilní (YouTube). Nejnovější stabilní verze 74.0.3729.108 tohoto webového prohlížeče přináší řadu oprav a vylepšení. Vylepšeny byly také nástroje pro vývojáře (YouTube). Opraveno bylo 39 bezpečnostních chyb.

Ladislav Hagara | Komentářů: 0
23.4. 21:44 | Nová verze

Po roce vývoje od vydání verze 1.14.0 byla vydána nová stabilní verze 1.16.0 dle Netcraftu aktuálně nejpoužívanějšího webového serveru a reverzní proxy nginx (Wikipedie). Nová verze přináší řadu novinek. Podrobný přehled v souboru CHANGES-1.16.

Ladislav Hagara | Komentářů: 0
Používáte headset pro virtuální realitu?
 (1%)
 (3%)
 (2%)
 (18%)
 (1%)
 (74%)
Celkem 238 hlasů
 Komentářů: 12, poslední 18.4. 01:19
Rozcestník

Správa databází v MySQL - II

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

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í)

       

Hodnocení: 36 %

        špatnédobré        

Nástroje: Tisk bez diskuse

Tiskni Sdílej: Linkuj Jaggni to Vybrali.sme.sk Google Del.icio.us Facebook

Komentáře

Vložit další komentář

9.5.2003 23:11 martin
Rozbalit Rozbalit vše select into outfile
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.
12.5.2003 10:18 bob
Rozbalit Rozbalit vše Jak na hromadny export?
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.
ISSN 1214-1267   www.czech-server.cz
© 1999-2015 Nitemedia s. r. o. Všechna práva vyhrazena.