PostgreSQLMySQL

A Unix/Linux szerverek üzemeltetése wikiből

Tartalomjegyzék

1 PostgreSQL és MySQL összehasonlítása adminisztrátor szemszögéből

Először külön áttekintem a két DBMS-t, majd a végén térek rá a különbségekre.

1.1 PostgreSQL

  • Többfelhasználós ORDBMS (Objektum-relációs adatbázis-kezelő rendszer - relációs sémákkal dolgozik, azonban a sémákban tárolt adatok úgy tűnnek a külső felhasználó számára, mintha objektumok lennének, ezen kívül a sémák között objektum-orientáltságra emlékeztető kapcsolatok vannak, így lehet pl. örököltetni)
  • Berkeley Egyetemen kifejlesztett POSTGRES objektum-relációs adatbáziskezelő rendszer nyílt forrású bővítése
  • Természetesen jelentős szolgáltatás-növelés történt azóta
  • Kliens-szerver architektúrájú (a szerver program neve postmaster)
  • Jó ideig UNIX-alapú rendszereken futott csak, ezért Windowson csak Cygwin vagy MinGW telepítése után volt használható (mármint a szerverprogram) - mamár van natív Windowsos változata is.

1.1.1 Telepítés

  • RedHat esetén rpm csomagból. Le kell tölteni az összes szükséges csomagot, majd futtatni kell a következő parancsot:
    • rpm -ivh postgresql-*
  • Néhány főbb csomag, amikre szükség lehet:
    • postgresql : alapcsomagok
    • postgresql-contrib : kiegészítő csomagok
    • postgresql-devel : alkalmazások fejlesztéséhez és fordításához. A kiszolgáló telepítéséhez feltétlenül szükséges
    • postgresql-jdbc : Java DataBase Connectivity-t lehetővé tevő csomag
    • postgresql-libs : megosztott programkönyvtárak
    • postgresql-odbc : Open DataBase Connectivity, API amit nagyon sok eszköz támogat
    • postgresql-perl : Perlben lehessen kiegészítőket írni
    • postgresql-python : ugyanez Pythonban
    • postgresql-server : akkor kell, ha nem csak távoli hosthoz kapcsolódunk, hanem helyileg is működtetünk szervert
  • Debian-alapú rendszer esetén mindössze egy parancs a telepítés:
   apt-get install postgresql
  • Természetesen forrásból is telepíthető a szokásos ./configure, make, make install hármassal. A forrás letölthető a http://www.postgresql.org oldalról
  • A szerver elindítása /etc/init.d/postgresql start paranccsal történik. Előfordulhat, hogy a postgresql szó után kötőjelet, majd a verziószámot meg kell adni.

1.1.2 Adminisztratív feladatok ellátása

1.1.2.1 Adatbázis-klaszter létrehozása

  • Először létre kell hozni új adatbázis-klasztert
  • Egy PostgreSQL adatbázis-klaszter adatbázisok gyűjteménye, amelyekért ugyanazon szerver folyamat felel
  • initdb program segítségével tehetjük meg
  • A program a következő műveleteket hajtja végre:
    • Könyvtárak létrehozása, amelyekben az adatbázisok tárolásra kerülnek majd
    • Közös katalógus táblák generálása (ezek a teljes klaszterhez tartoznak)
    • template1 és postgres adatbázisok létrehozása
      • template1 sablon adatbázis, amelynek objektumai automatikusan belekerülnek minden később, újonnan létrehozott adatbázisba. Ilyen módon állíthatunk be telepítés-specifikus beállításokat, alapvető táblákat, felhasználókat stb. amelyek/akik minden adatbázis részei.
      • postgres az alapértelmezett adatbázis mindenki számára
  • Az initdb programot annak a felhasználónak kell lefuttatnia, akié a szerver folyamat is (mivel a szerver folyamat hozzá kell hogy férjen a létrehozott könyvtárakhoz). Ez nem egyezik meg az operációs rendszer rootjával, alapértelmezés szerint egy postgres nevű felhasználó, amely a PostgreSQL telepítésekor jön létre
  • initdb néhány fontosabb kapcsolója:
    • -A, --auth : milyen azonosítása legyen a rendszer (helyi és távoli) felhasználóinak (pg_hba.conf fájlban kerül tárolásra, ahol hba = host-based authentication - a pg_hba.conf fájl tartalmát lásd később). Lehetséges értékei:
      • trust : nincs szükség autentikációra, megbízunk a host felhasználóiban, bárki, aki a szerverhez csatlakozni képes, beléphet. Ilyenkor olyan felhasználónevet ad meg az illető, amilyet szeretne. Természetesen ettől még érvényesek a beállított korlátozások, tehát ha Gipsz Jakab nem a superuser felhasználónevét adja meg, hanem egy mezei felhasználónevet, akkor nem lesznek kitüntetett jogosultságai.
      • md5 : hashelve tároljuk a jelszavakat
      • crypt : titkosított
      • password : plain-text jelszó, távoli eléréshez ilyet ne használjunk
      • lehet ident-alapú is, ami azt jelenti hogy az operációs rendszer részeként kapott ident szervert használja fel a szerver adabázis-felhasználók és operációs-rendszer felhasználók párosításához (ez azonban legtöbbször nem biztonságos, feltételezi hogy a kliens gép nem kompromittált, az RFC-ben is azt írják hogy ne használjuk)
      • PAM (pluggable authentication module)
      • lehet kerberos-szal is, de ilyenkor már telepítéskor meg kell adnunk a kerberos-szerver nevét
    • -D, --pgdata : melyik könyvtárban tároljuk az adatbázis-klasztert. Megadása kötelező
    • -U, --username: mi legyen a superuser neve. Alapesetben a szerverfolyamat tulajdonosának neve, postgres. Meg is változhatható, kényelmi szempontból
    • -W, --pwprompt: az initdb jelszót is bekér a superuser accounthoz. Ha ezt nem adjuk meg, akkor a beállított autentikációs módszer 'trust' lesz minden adatbázisra minden felhasználóra vonatkozóan, ami azt jelenti hogy bárki beléphet jelszó nélkül. Ilyenkor a pg_hba.conf fájl a következő sorokat tartalmazza:
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   
   # "local" is for Unix domain socket connections only
   local   all         all                               trust
   # IPv4 local connections:
   host    all         all         127.0.0.1/32          trust
   # IPv6 local connections:
   host    all         all         ::1/128               trust
  • Az initdb, futása végén, kiírja hogy milyen paranccsal indíthatjuk el az adott klaszterre vonatkozó postgresql szerverfolyamatot
  • Hogy jobban átlássuk az authentikálást, vegyük szemügyre a pg_hba.conf fájl 7 lehetséges rekordtípusát:
   local      database  user  auth-method  [auth-option]
   host       database  user  CIDR-address  auth-method  [auth-option]
   hostssl    database  user  CIDR-address  auth-method  [auth-option]
   hostnossl  database  user  CIDR-address  auth-method  [auth-option]
   host       database  user  IP-address  IP-mask  auth-method  [auth-option]
   hostssl    database  user  IP-address  IP-mask  auth-method  [auth-option]
   hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-option]
  • A mezők jelentése:
    • local: UNIX-domain socketeken keresztül lehet csatlakozni
    • host: TCP/IP kapcsolatok, akár SSL-lel, akár nélküle
    • hostssl: TCP/IP SSL-lel
    • hostnossl: TCP/IP SSL nélkül
    • database: egy vagy több adatbázis neve (vesszőkkel elválasztva), vagy:
      • all: minden adatbázisra illeszkedik
      • sameuser: az adatbázis neve ugyanaz, mint a felhasználó neve
      • samerole: a felhasználó olyan szerepcsoportba kell hogy tartozzon, ami megegyezik az adatbázis nevével
    • user: egy vagy több felhasználó neve (vesszőkkel elválasztva), vagy:
      • all: minden felhasználó
      • +-szal kezdődő név: szerep neve, amelybe több felhasználó is tartozik
    • CIDR-address: CIDR-tartományra illeszkedik. Pl. 172.20.143.89/32 egyetlen host esetén, vagy 172.20.143.89/24 tartomány esetén.
    • IP-address, IP-mask: CIDR-address helyett, csak itt külön oszlopban kell megadni a maszkot
    • auth-method: fentebb ismertetett módszerek
    • auth-option: autentikációs módszerenként változó.
  • Példa:
   # Allow any user on the local system to connect to any database under
   # any database user name using Unix-domain sockets (the default for local
   # connections).
   #
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   local   all         all                               trust
   
   # The same using local loopback TCP/IP connections.
   #
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   host    all         all         127.0.0.1/32          trust     
   
   # The same as the last line but using a separate netmask column
   #
   # TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
   host    all         all         127.0.0.1     255.255.255.255     trust     
   
   # Allow any user from any host with IP address 192.168.93.x to connect
   # to database "postgres" as the same user name that ident reports for
   # the connection (typically the Unix user name).
   # 
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   host    postgres    all         192.168.93.0/24       ident sameuser
   
   # Allow a user from host 192.168.12.10 to connect to database
   # "postgres" if the user's password is correctly supplied.
   # 
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   host    postgres    all         192.168.12.10/32      md5
   
   # In the absence of preceding "host" lines, these two lines will
   # reject all connection from 192.168.54.1 (since that entry will be
   # matched first), but allow Kerberos 5 connections from anywhere else
   # on the Internet.  The zero mask means that no bits of the host IP
   # address are considered so it matches any host.
   # 
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   host    all         all         192.168.54.1/32       reject
   host    all         all         0.0.0.0/0             krb5
   
   # Allow users from 192.168.x.x hosts to connect to any database, if
   # they pass the ident check.  If, for example, ident says the user is
   # "bryanh" and he requests to connect as PostgreSQL user "guest1", the
   # connection is allowed if there is an entry in pg_ident.conf for map
   # "omicron" that says "bryanh" is allowed to connect as "guest1".
   #
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   host    all         all         192.168.0.0/16        ident omicron
   
   # If these are the only three lines for local connections, they will
   # allow local users to connect only to their own databases (databases
   # with the same name as their database user name) except for administrators
   # and members of role "support", who may connect to all databases.  The file
   # $PGDATA/admins contains a list of names of administrators.  Passwords
   # are required in all cases.
   #
   # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
   local   sameuser    all                               md5
   local   all         @admins                           md5
   local   all         +support                          md5
   
   # The last two lines above can be combined into a single line:
   local   all         @admins,+support                  md5
   
   # The database column can also use lists and file names:
   local   db1,db2,@demodbs  all                         md5

1.1.2.2 Felhasználók, adatbázisok létrehozása

  • Kezdetben egyetlen felhasználó létezik, melynek neve postgres.
  • A rendszergazda beléphet az 'su postgres' paranccsal (Ubuntu esetén 'sudo su postgres')
  • Felhasználó létrehozása a 'createuser <username>' paranccsal. Néhány kapcsoló:
    • -s, --superuser: a felhasználó superuser lesz
    • -d, --createdb: a felhasználó hozhat majd létre adatbázisokat
    • -D, --nocreatedb
    • -r, --createrole: a felhasználó hozhat majd létre szerepeket (tehát felhasználókat vagy csoportokat)
    • -R, --nocreaterole
    • -c, --connection-limit: egyszerre hány kapcsolatot létesíthet a felhasználó
    • -P: a parancs kiadása után meg kell adni jelszót. Ha a kapcsolót nem adjuk meg, akkor a felhasználó azonosítás nélkül beléphet az adatbázisokba, sőt, ha megadtuk a -d kapcsolót, akkor új adatbázisokat is hozhat létre azonosítás nélkül (nem törölhet viszont bármit, mert csak az owner törölhet, lásd később)
    • -E: a jelszót titkosítva tárolja a szerver
    • -N: jelszó ne legyen titkosítva
  • Felhasználó törlése 'dropuser <username>' paranccsal
  • Adatbázis létrehozása 'createdb <adatbazisnev>' paranccsal. Néhány kapcsoló:
    • -D, --tablespace: tablespace megadása. A tablespace egy könyvtár a file rendszeren, ahová a tablespace-hez tartozó táblák eltárolásra kerülnek. A tablespace-nek van neve, amivel hivatkozni lehet rá (hasznos lehet pl., ha több különböző sebességű lemezünk van). Tablespace létrehozása: 'CREATE TABLESPACE <tablater_nev> LOCATION <konyvtarnev>'. Tablespace-t csak akkor lehet törölni, ha üres, a 'DROP TABLESPACE <tablater_nev>' paranccsal
    • -O, --owner: melyik felhasználó lesz az adatbázis tulajdonosa. Később az adatbázist csak a tulajdonos törölheti, ilyenkor az adatbázis és teljes tartalma törlésre kerül, függetlenül attól, hogy az adatbázis tartalmát ki hozta létre
    • -T, --template: sablon adatbázis, amely alapján az új adatbázis felépül. Kezdetben két sablon van:
      • template1: ez az alapértelmezett sablon, ha nem adjuk meg a -T kapcsolót, akkor ezt az adatbázist másolja át az új helyére is. Ilyen módon állíthatunk be telepítés-specifikus beállításokat, alapvető táblákat, felhasználókat stb. amelyek/akik minden adatbázis részei.
      • template0: csupasz sablon, ezt az adatbázist soha ne módosítsuk az initdb lefuttatása után (hasznos lesz pl., ha pg_dump segítségével építünk fel újra régebbi adatbázist, lásd http://www.postgresql.org/docs/8.1/interactive/app-pgdump.html)
    • -h, --host: melyik hoston fut a szerver. Ha /-jellel kezdődik, akkor Unix-domain socketet adunk meg
    • -p, --port: melyik porton figyel a szerver
    • -U, --username: melyik felhasználóként csatlakozzon alapértelmezés szerint
    • -W, --password: kötelező legyen jelszó megadása
  • Adatbázis törlése 'dropdb <adatbazisnev>' paranccsal
  • Adatbázisba belépni a 'psql <adatbazisnev>' paranccsal lehet
    • Ha nem adjuk meg az adatbazis nevét, akkor a felhasználó alapértelmezett adatbázisába lép be. Ha ilyen nem létezik, hiba. (Itt felhasználó alatt az operációs rendszer felhasználóját értjük.)
    • Ha más felhasználó adatbázisába szeretnénk belépni, mint amelyik be van jelentkezve az operációs rendszerbe (pl. mert más a felhasználónevünk a kettőben - nem kell egyezniük, semmi közük egymáshoz), akkor az -U kapcsolót használhatjuk.
  • A felsorolt programocskák (createuser, dropuser, createdb, dropdb) frontendet jelentenek bizonyos PostreSQL-utasításokhoz. Ugyanezek a műveletek elvégezhetőek tehát PostgreSQL parancssornál is, CREATE ROLE, DROP ROLE, CREATE DATABASE, illetve DROP DATABASE utasításokkal.
  • Felhasználók tekintetében ROLE-okról beszél a PostgreSQL. Egy ROLE egy szerepkör, ami jogosultságokat tartalmaz, és vagy egyetelen felhasználóhoz, vagy pedig felhasználók egy csoportjához van társítva
1.1.2.2.1 Mit tegyünk, ha a superuser kizárta magát?

Ilyenkor három lehetőség van:

  • Változtatssuk meg a jelszót single user módban
   su - postgres /etc/init.d/postgresql-8.1 stop  // vagy akármi, amivel le tudjuk állítani a szervert
   postgres template1
   alter user postgres password 'newpassword';
  • Módosítsuk a pg_hba.conf fájlt és állítsuk be az autentikációt trust módba, majd változtassuk meg a jelszót.


   su - postgres
   cd $PGDATA //az adatkönyvtár elérési útja
   vi pg_hba.conf
   // változtassuk meg az érdekes bejegyzéseket 'trust' értékűre
   /etc/init.d/postgresql-8.1 stop
   /etc/init.d/postgresql-8.1 start
   psql template1
   alter user postgres password 'newpassword';
  • Futtassuk le újra az initdb-t.
   su - postgres
   /etc/init.d/postgresql-8.1 stop
   echo $PGDATA  // ennek értéke az adatkönyvtár elérési útja
   rm -rf $PGDATA/*
   initdb // valahogyan felparaméterzve

1.1.2.3 Sémák létrehozása

  • A séma egy adatbázisban található logikai egység, amely tartalmazhat:
    • táblákat
    • egyéb objektumokat, mint pl.
      • adattípusok
      • függvények
      • operátorok
  • Névteret definiálnak olyan értelemben, hogy két különböző sémában lehet ugyanolyan nevű entitás
  • Míg egyszerre csak egy adatbázishoz lehet kliensen keresztül kapcsolódni (ahhoz, amelyik nevét megadtuk a psql parancs után), addig egyszerre több sémát lehet elérni, ha ugyanabban az adatbázisban helyezkednek el és mindegyikhez van hozzáférési jogosultságunk
  • Így elérhető, hogy:
    • Sok felhasználó ugyanazt az adatbázist használja, de ne avatkozhassanak bele egymás munkájába
    • Egyszerűbben karbantarthatóak az adatbázisok, ha bennük nem egyetlen amorf struktúra az összes entitás halmaza
    • Külső alkalmazások kaphatnak saját sémát, és így biztos hogy soha nem lesz névütközés
  • A séma tehát olyan az adatbázisban, mint a könyvtár az operációs rendszerben, azzal a különbséggel, hogy a sémák nem ágyazhatók egymásba
  • Séma létrehozása:
   CREATE SCHEMA myschema
  • A table nevű tábla elérése az így létrehozott, myschema nevű sémában:
   myschema.table
  • Elérési és módosítási jogosultságok megadhatóak a szokásos módon, a GRANT utasítással:
   GRANT <privilege> ON SCHEMA <schema_name> TO <rolename>

1.1.2.4 Szerver paramétereinek beállítása

  • Ami egy-egy adatbázis-klaszter paramétereit illeti, megadhatóak az initdb program futtatásakor
  • Ami a szerverre vonatkozó egyéb paramétereket illeti, megadhatjuk őket:
    • Konfigurációs fájlokban
    • PostgreSQL-utasításként, SET parancs segítségével
    • A szerverfolyamat elindításakor, -c kapcsoló után (a szerverfolyamat a postmaster)
  • Konfigurációs fájlok:
    • Adatkönyvtárba (data directory) helyezkednek el, amit az initdb program hozott létre
    • config_file: alap konfigurációs fájl
    • hba_file: host-based authentication, hozzáféréseket írja le
    • ident_file: az operációs rendszer felhasználóihoz társít adatbázis-felhasználókat
    • external_pid_file: szerver-adminisztrációs programok számára külső pid-file. Tudjuk, hogy mennyire megbízhatóak versenyhelyzetek tekintetében....
    • Néhány fontosabb környezeti változó:
      • listen_addresses : mely TCP/IP címeket figyeljen a szerver, beérkező kapcsolódásokat várva
      • port : melyik porton figyeljen a szerver
      • max_connections : alapértelmezés szerint 100
      • authentication_timeout: befejezetlen azonosítási protkollok terminálására vonatkozik (ha egy felhasználó kapcsolódni szeretne, de a jelszó megadásánál nem történik semmi, akkor ennyi idő után lezárja a kapcsolatot a szerver)
      • ssl: lehet-e ssl-ezni
      • password_encryption: új jelszavak létrehozásánál a jelszavak tiktosítottak legyenek-e
      • shared_buffers: hány darab osztott memóriát alkalmazzon a szerver folyamat. Alapértelmezett memóriaméret 8192 byte, de ez telepítéskor változtatható (BLCKSZ opció). Értéke legalább 16, és legalább kétszer annyi, mint max_connections értéke (tehát hogy adott pillanatban maximum hány kapcsolat jöhet létre kliensek és szerver között). Jó teljesítmény érdekében célszerű ennél jóval nagyobb értéket választani (de oly módon, hogy az operációs rendszer alapértelmezett beállításainak ne mondjon ellent a memória-igény). Csak szerverindításkor állítható.
      • temp_buffers: egy adatbázis kapcsolat hány ideiglenes puffert hozhat létre. Ezek csak ideiglenes táblák elérésekor használatosak. Magas értékre állítása nem jár sok költséggel - eggyel való növelése csupán egy pufferleíróval több, ami 64 byte. Csak szükség esetén foglalja le ténylegesen a helyet.
      • max_fsm_pages: fsm = free-space-map, térkép arról, hol vannak szabad helyek az adatbázisban. Mérete legalább 16*max_fsm_relations (lásd lentebb). Egyfajta töredezettség-mentesítést segít elő, ha értéke kicsi, akkor a postmaster nem tudja figyelemmel kísérni, hol vannak szabad helyek a lemezen, és újabb adatok tárolásánál új területet fog igényelni. Ugyanakkor ha túl nagyra állítjuk, könnyen memória-korlátokba ütközhetünk.
      • max_fsm_relations: mennyi a relációk maximális száma, amiket még figyelemmel kísér a szerverfolyamat a free-space-map-ben.

1.1.2.5 Karbantartás

1.1.2.5.1 Monitorozás

Kétféle módszer:

  • Standard UNIX-eszközökkel
   ps aux | grep ^postgres
  • PostgreSQL statisztika-gyűjtőjével
    • Külön programocska, amely képes:
    • összeszámlálni a táblákhoz való hozzáférések számát (lemez-blokkok, illetve sorok tekintetében is), illetve
    • figyelni, hogy a szerverfolyamatok éppen milyen utasításokat hajtanak végre
    • statisztikák gyűjtésének bekapcsolásához a postgresql.conf fájlban állítsuk be a stats_start_collector paraméter értékét true-ra
    • futás közben ez már nem változtatható (tehát pl. SET paranccsal nem fog menni)
    • további paraméterek segítségével beállítható, hogy milyen jellegű információkat adjon át a szerverfolyamat a statisztikagyűjtőnek:
      • stats_command_string
      • stats_block_level
      • stats_row_level
      • Ezek értékei a postgresql.conf fájlban minden, a klaszterben futó szerverfolyamatra érvényesek, azonban külön szerverfolyamatonként is beállítható SET paranccsal (ezt azonban csak a superuser teheti meg)
      • Alapértelmezett értékük false
  • Sokféle információ kilistázható az egyes folyamatokról, pl:
   SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
   pg_stat_get_backend_activity(s.backendid) AS current_query
   FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
  • Kilistázza az összes aktuálisan futó szerverfolyamat pidjét és a folyamatok által futtatott aktuális parancsokat
  • pg_locks rendszertáblázatból kiolvasható, hogy éppen hány zár van, és mely táblákon az éppen futó session-ben
1.1.2.5.2 Write-ahead logolás

Néhány, a logolást befolyásoló paraméter (amelyek tehát akár a szerverfolyamat elindításakor - -c kapcsoló után megadva -, akár a postgresql.conf fájlban állíthatóak):

  • fsync: bool érték, amely megmondja szeretnénk-e egyáltalán hogy működjön a write-ahead logolás. Hasznos, de költséges (minden db művelet után flush a lemezre). Több módszer is használható a kiírásra, amelyeket a wal_sync_method paraméterrel állíthatunk be:
    • open_datasync – az open() függvényt hívja meg, a 0_DSYNC opcióval. Ilyenkor csak a “rendes” adatok kerülnek szinkronizált átvitelre, a metaadatok nem
    • fdatasync – fdatasync() meghívása minden egyes kommittálás esetén, csak adatot mentünk, metaadatot nem
    • fsync – fsync() meghívása minden egyes kommittálás esetén, minden adatot mentünk
    • fsync_writethrough – ugyanolyan mint az fsync, csak ha cache-elt adatokat írunk, akkor a cache-elt adat új értékét nemcsak a logban, hanem azonnal az eredeti példányban is frissíti (tehát frissíti azokat az adatokat, amelyek belekerültek a cache-be mielőtt még módosítottunk volna rajtuk)
    • open_sync – az open() opciója 0_SYNC, ilyenkor a metaadatok is szinkronizált logolásra kerülnek (itt olyan metaadatokra kell gondolni, mint az utolsó frissítés ideje, és egyéb, a módosított objektumokra vonatkozó olyan adatok, amelyek nem tartalmukkal függnek össze)
  • commit_delay: microszekundumokban, hogy egy commit után legkésőbb mikor kell kiírni diszkre az adatokat
  • checkpoint_segments: hány megabyte adat gyűljön össze két checkpoint között. Érdemes alacsonyra állítani (tehát viszonylag gyakori mentést végezni, ha):
    • bizonytalan az írási műveletek sikeressége, a HW környezet miatt
    • kevés műveletet logolunk (tehát lassan nő a logfile)
    • nagyon sporadikusak a végrehajtott műveletek (tehát sok idő telik el közöttük, ilyenkor is lassan nő a logfile)
  • checkpoint_timeout: mennyi idő után kell újabb checkpoint
  • archive_command: parancs, amelyet lefuttat a DBMS amikor teleírta az összes lehetséges log file-t. Rendelkezésre áll az %f és %p változó, amelyek az aktuálisan kiírandó fájl nevét illetve elérési útját tartalmazzák. Példa:
   archive_command = 'cp %p /mnt/server/archivedir/”%f”'
  • Ilyenkor átmásolásra kerül a teleírt logfájl (amelynek elérési útja %p változóban van tárolva) a /mnt/server/archivedir könyvtár logfájlról elnevezett alkönyvtárába
1.1.2.5.3 Vacuuming (porszívózás)
  • A VACUUM parancsot időről időre célszerű lefuttatni a következő okok miatt:
    • Frissített vagy törölt sorok által elfoglalt lemezterület felszabadítása (pl. törölt sorokat nem törlünk fizikailag is azonnal, mert egy másik tranzakció még függhet tőle)
    • Adatstatisztikák frissítése, amelyeket pl. a Query Planner használ (az ANALYZE parancs segítségével külön összegyűjthetőek ezek az adatok)
    • Nagyon régi adatokat ne veszítsünk el (különben ha az ún. transaction id wraparound bekövetkezik, ez megtörténhet – transaction id wraparound azt jelenti, hogy túlcsordulás történt a tranzakciók 32-bites számlálójában, és egy új tranzakció ugyanazt az azonosítót kapja, mint egy régebbi - ez azért baj, mert ilyenkor azokra a tranzakciókra, amelyek nagyobb számmal rendelkeznek - pedig korábban történtek - az éppen futó tranzakció úgy fog tekinteni, mint amik nem történtek még meg, és ezért kimenetük nem látható az éppen futó tranzakció számára)
  • Kétféle VACUUM parancs létezik:
    • Sima VACUUM – más néven “lusta” vacuum – paranccsal csak megjelöljük a törlendő helyeket, de azok az operációs rendszer számára nem szabadulnak fel.
    • VACUUM FULL parancs segítségével az operációs rendszer visszakapja a lemezterületet, azonban ilyenkor kizárólagos zárral kell rendelkezni a fájl felett.
  • A PostgreSQL 8.1-es verziója óta létezik külön daemon folyamat, amely elvégzi az időszakos porszívózást (autovacuum opció, bool értékkel, a konfigurációs fájlban).
1.1.2.5.4 Archiválás és visszaállítás

Alapvetően 3 backup technika létezik:

SQL dump – ilyenkor olyan fájlt tárolunk, amely végrehajtható SQL-utasításokat tartalmaz, amelyeket végrehajtva visszakapjuk az adatbázist

  • pg_dump program, amely konzisztens és a másolás közbeni írásokat nem veszi figyelembe a másoláskor:
   pg_dump dbnev > outfile
  • pg_dump utáni restore:
   psql dbnev < infile
  • pipe-okkal is használhatóak, ezért közvetlenül új szerverre is átpakolható az adatbázis:
   pg_dump -h host1 dbnev | psql -h host2 dbnev
  • Ha teljes adatbázis-klasztert szeretnénk backupolni:
   pg_dumpall > outfile
   psql -f infile postgres
  • postgres itt egy adatbazisnev, amellyel elkezdi a visszaállítást; bármilyen létező adatbázis nevét meg lehet adni, de postgres-t szokás, főleg amikor teljesen üres adatbázist töltünk fel

Fájlrendszer-szintű backup

   tar -cf backup.tar /usr/local/data
  • Ilyenkor azonban ideiglenesen le kell állítni az adatbázist

On-line backup

  • Alapötlet: write-ahead-logok folyamatosan eltárolásra kerülnek az adatkönyvtárban (data directory, amelyet az initdb segítségével lehet beállítani). Ez azért jó, mert ha hirtelen összeomlik a rendszer, akkor is az előző checkpoint óta történt változások visszajátszhatóak ezen fájlok segítségével. Ezért, a fenti két módszer kombinálható:
    • Léteznek fájlrendszer-szintű backup-mentések
    • Ezeket visszaállítjuk, majd a maradék változtatást lejátsszuk a write-ahead logfájlokból
  • A módszer azért más, mint a korábbiak, mert:
    • Nem szükséges, hogy a fájlrendszerből visszatöltött backup-mentés konzisztens legyen (bármilyen belső inkonzisztenciát a log-replay-ek úgyis kijavítanak)
    • point-in-time recovery támogatása: nem feltétlenül kell a write-ahead-logfájlokat végig visszajátszani. Ily módon egy adott, korábbi pillanatról kapunk snapshotot
    • hot-standby rendszer üzemeltetése: egy másik rendszert ugyanazokkal a backup-fájlokkal inicializálunk, és folyamatosan lejátsszuk rajta ugyanazokat a változtatásokat, write-ahead-logfile-ból

1.2 MySQL

  • Szintén többfelhasználós, de csak RDBMS, objektumok nincsenek benne
  • Szintén kliens-szerver architektúrájú (a szerverfolyamat neve mysqld)
  • Több operációs rendszert támogat, de ez nem jelent semmit, mert PostgreSQL-t is használhatunk Windowson ha van Cygwin vagy MinGW

1.2.1 Telepítés

  • RedHat esetén rpm csomagkezelővel. Le kell tölteni az összes szükséges csomagot, majd le kell futtatni az 'rpm -ivh mysql-*' parancsot. A következő programcsomagokra lesz szükség:
    • mysql – alapprogramok
    • mysql-devel – alkalmazások fejlesztéséhez és fordításához. A kiszolgáló telepítéséhez feltétlenül szükséges.
    • mysql-server – amennyiben lokális hoston szeretnénk futtatni, és nem távoli szerverhez kapcsolódunk.
  • Debian alapú rendszer esetén mindössze egyetlen parancsot kell kiadnunk:
    • apt-get install mysql-server mysql-client
  • Végül lehet forrásból telepíteni a szokások ./configure – make – make install parancsokkal. Erről bővebb információ a http://www.mysql.com címen olvasható.
  • A rendszer indítása a 'mysql -u <felhasznalo> <adatbazis>' paranccsal történik.
  • Ha jelszót is meg szeretnénk adni, akkor a parancs végéhez még hozzá kell fűznünk a -p kapcsolót
  • Kezdetben egyetlen adatbázis létezik, melynek neve 'mysql', és a 'root' felhasználó (semmi köze az operációs rendszer rootjához) jelszó nélkül kapcsolódhat:
   mysql -u root mysql
  • Hasonló SQL-értelmező parancssort kapunk, mint PostgreSQL esetében a psql parancs futtatásakor

1.2.2 Adminisztratív feladatok ellátása

1.2.2.1 Jogosultságok

  • A jogosultságokat a kezdeti mysql adatbázisban 3 tábla tartalmazza. Nevük:
    • host
    • user
    • db
  • Ezek felhasználó-hoszt párokhoz társítanak különböző jogosultságokat
  • A következő jogosultságok adhatóak meg:
    • Create_priv: adatbázisok, táblák, indexek létrehozása (indexeket táblák oszlopaihoz lehet megadni, és ilyenkor nem kell sorosan keresgélnie egy adott sort a táblában a MySQL szervernek, ehelyett az index alapján közvetlen hozzáféréssel tud keresni)
    • Drop_priv: adatbázisok vagy táblák törlése
    • Grant_priv: jogosultságok megadhatóak adatbázisokra, táblákra, vagy tárolt rutinok futtatására
    • Alter_priv: táblák módosítása (sémával együtt)
    • Delete_priv: táblák törlése
    • Insert_priv: relációk beszúrása táblákba (séma nem változtatható)
    • Index_priv: táblák oszlopaihoz indexeket adhat hozzá
    • és még rengeteg más
  • Mindegyik állítható felhasználónként a GRANT, REVOKE utasításokkal, illetve új sor hozzáadásával az egyes táblákhoz

1.2.2.2 Kezdeti jelszavak beállítása

  • Legelőször be kell állítanunk valamilyen jelszót a root felhasználónak, hogy illetéktelenek ne léphessenek be a szerverre. Ezt a következő paranccsal tehetjük:
   SET PASSWD FOR 'root'@'localhost' = PASSWORD('szupertitkos_jelszo');
  • Hogy ezt szisztematikus módon megtehessük, érdemes először kilistázni, mely hostokon mely felhasználók léteznek:
   SELECT Host, User FROM mysql.user;
  • Kezdetben létezik egy anonim account is, teljes jogosultságokkal. Ezt célszerű törölnünk:
   DROP USER "";

1.2.2.3 Felhasználók, adatbázisok létrehozása

  • MySQL-ben a felhasználói fiókok felhasználónevekhez és hostokhoz van társítva (amely hostokról a felhasználó beléphet)
  • Jelszó is tartozik hozzájuk
  • Egy MySQL felhasználónévnek semmi köze az operációs rendszer felhasználóneveihez, és a jelszavaknak sincs közük egymáshoz
  • A felhasználónév maximális hossza 16.
  • Háromféleképpen adhatunk új felhasználót a rendszerhez:
    • CREATE USER vagy GRANT SQL-parancsokkal:
      • GRANT ALL PRIVILEGES ON *.* TO 'adam'@'localhost' IDENTIFIED BY 'jelszo' WITH GRANT OPTION;
      • localhost vagy IP-cím bármely része helyett írhatunk %-jelet, ilyenkor bármely hostról kapcsolódhat az új felhasználó, amelynek neve illeszkedik a mintára oly módon, hogy a %-jel helyén bármi lehet és minden más kötött
    • INSERT, DELETE, illetve UPDATE SQL-parancsokkal pl. a mysql.user táblában (mysql helyett értelemszerűen az aktuális adatbázis nevét írjuk)
      • INSERT INTO mysql.user (Host, User, Password) VALUES ('localhost','user',PASSWORD('titkositott_jelszo'));
    • mysqlmanager nevű program segítségével:
      • mysqlmanager --add-user --username='Bela' --password='jelszo'
  • Az első megoldás biztonságosabb mint a második, kevesebb lehetőség van hibázásra, mint ha explicit módon írogatnánk a táblákba.
  • Létező felhasználó másolása távoli gépre (hogy egy másik konkrét gépről ugyanazzal a jelszóval bejelentkezhessen):
    • Ha csak az adatott felhasználó használja azt a kliens gépet, akkor a konfigurációs opció fájlba (amely általában a /etc/mysql/my.cnf fájlban található - opció fájlokról lásd később) a [client] szekcióba beszúrhatjuk a password='jelszo' sort (ilyenkor biztosítani kell, hogy illetéktelenek ne olvashassák a konfigurációs fájlt)
    • Megoldható MySQL paranccsorból is, ha van tábla létrehozásra jogosultságunk, valamint ha a mysql.user táblába beszúrhatunk újabb relációt (ez a rootnak mindenképpen sikerülni fog):
   INSERT INTO user SELECT * FROM user WHERE user='Felhasznalonev' and host='jelenlegi_host' on duplicate key update host='uj_host';
   CREATE TABLE tmp_user SELECT * FROM user WHERE user='Felhasznalonev' and host='uj_host';
   INSERT INTO user SELECT * FROM user WHERE user='Felhasznalonev' and host='uj_host' ON DUPLICATE KEY UPDATE host='jelenlegi_host';
   INSERT INTO user SELECT * FROM tmp_user WHERE user='Felhasznalonev' and host='uj_host';
    • Az elso parancs felulirja az eredeti felhasznalo hostját az új hostra
    • A második parancs létrehoz egy ideiglenes táblázatot, amelybe átmásolja a felhasználóra és az új hostra vonatkozó adatokat
    • A harmadik parancs visszaváltoztatja a user táblában a hostot az eredeti hostra
    • Mostmár visszamásolható az ideiglenes táblából a user táblába az új account, nem fog más kulccsal ütközni a rekord
    • A művelet után célszerű törölni az ideiglenes táblát
    • Ügyelni kell arra, hogy olyan helyen hozzuk létre az ideiglenes táblát, ahol más felhasználó nem módosíthatja a tartalmát
  • Új adatbázis létrehozása: mysqladmin create <adatbazis_nev>
  • Adatbázis törlése: mysqladmin drop <adatbazis_nev>: adatbazis torlese
1.2.2.3.1 Mit tegyünk, ha a mysql root kizárta magát?
  • Az alapötlet az, hogy leállítjuk a szervert, és újraindítjuk úgy, hogy ne használjon grant táblákat. Ilyen módon újra beállítható a root jelszó. A műveletsor lépései:
   sudo kill <mysql pidje>
   sudo /usr/bin/mysqld_safe --skip-grant-tables &
   mysql -h localhost
   use mysql
   update user set password = password('.......') where user = 'root' and host='localhost';
   quit
   mysqld_safe --user=mysql --log &
  • A kódban használt segédprogramokról bővebben lásd később

1.2.2.4 Szerver-oldali segédprogramok

  • Különböző szerver-oldali programok léteznek, amelyek segítségével elvégezhetőek az adminisztratív teendők. Ezek közül csak néhányat emelek ki:
1.2.2.4.1 mysqld
  • Maga a szerverfolyamat elindítható ezzel a programmal. A kapcsolókban megadható főbb paramétereket lásd lentebb, a "Szerver paramétereinek beállítása" c. alfejezetben.
1.2.2.4.2 mysqld_safe
  • Script, amely megpróbálja biztonságos módon elindítani a mysqld szerverfolyamatot. UNIX és Netware operációs rendszereken ajánlatos mysqld_safe programmal elindítani a szervert, mert tartalmaz néhány biztonsági funkciót:
    • hiba esetén, ha a szerver leáll, újraindítja
    • futásidejű információkat error log fájlba eltárolja (a fájl neve megadható a 'log-error' paraméterben)
  • A mysqld_safe-nek adott kapcsolók azonnal átadásra kerülnek a mysqld programnak, ezért amit nem a mysqld paraméterének szánunk, azt külön kell megadni egy opció fájl [mysqld_safe] csoportjában (opciófájlokról lásd később)
1.2.2.4.3 mysqlmanager
  • Futó szerverfolyamat-példányokat felügyelő daemon folyamat (részleteket lásd később)
1.2.2.4.4 mysqladmin
  • Szintén a szerverrel kapcsolatos adminisztratív műveleteket lehet vele végrehajtani. Új adatbázisok létrehozása mysqladmin segítségével történik. Főbb paraméterei:
    • create <adatbazis_nev>: új adatbázis létrehozása
    • drop <adatbazis_nev>: adatbazis torlese
    • flush tables: táblákkal kapcsolatos változtatások véglegesítése lemezen is (sokszor a változtatások csak memóriában illetve write-ahead logokban mennek végbe)
    • flush logs: logokkal kapcsolatos változtatások véglegesítése lemezen
    • flush privileges: grant táblákon végrehajtott változtatások véglegesítése
1.2.2.4.5 mysqld_multi
  • Több mysql példányt felügyelő program (olyan működést tesz lehetővé, mint a PostgreSQL-nél a klaszterek)
  • A konfigurációs fájlban olyan szakaszokat keres, amelyek illeszkednek a mysqldN névre, ahol N valamilyen pozitív egész szám (mysql konfigurációs fájlokról és szakaszokról lásd később)
  • Meghívása:
   mysqld_multi [opciók] {start|stop|report} [GNR[,GNR...]]
  • start, stop elindítanak illetve leállítanak egy vagy több szervert
  • report a szerver(ek) státuszáról ad információt
  • GNR = group number, tehát a mysqld után szereplő N értéke
  • Néhány fontosabb opció:
    • --log: mi legyen a logfile
    • --user: mysqladmin meghívásakor milyen felhasználónevet használjon (pl. szerver leállításakor fogja meghívni)
    • --password: mi a jelszó (szintén mysqladmin meghívásakor)
  • Példa a konfigurációs fájlra:
   [mysqld_multi]
   mysqld     = /usr/local/bin/mysqld_safe
   mysqladmin = /usr/local/bin/mysqladmin
   user       = multi_admin
   password   = multipass
   [mysqld2]
   socket     = /tmp/mysql.sock2
   port       = 3307
   pid-file   = /usr/local/mysql/var2/hostname.pid2
   datadir    = /usr/local/mysql/var2
   language   = /usr/local/share/mysql/english
   user       = john
   [mysqld3]
   socket     = /tmp/mysql.sock3
   port       = 3308
   pid-file   = /usr/local/mysql/var3/hostname.pid3
   datadir    = /usr/local/mysql/var3
   language   = /usr/local/share/mysql/swedish
   user       = monty

1.2.2.5 Szerver paramétereinek beállítása

  • A PostgreSQL-hez hasonlóan, a MySQL is lehetővé teszi, hogy parancssorból vagy fájlból állítsunk be fontosabb paramétereket a szerver indításakor
  • Dinamikus módon is beállítható legtöbb a SET parancs segítségével
1.2.2.5.1 Parancssorból történő paraméter-beállítás
  • A parancssorból történő beállításra szolgáló egyik program a mysqladmin (ezzel inkább ellenőrizni lehet paramétereket, de néhányat segítségével be is lehet állítani). Meghívása:
    • mysqladmin [opciók] parancs [argumentumok] [parancs [argumentumok]] ...
    • Főbb kapcsolók, amelyekkel befolyásolni lehet a szerver konfigurációját mysqladmin programon kereszül:
      • --compress: kliens és szerver között menő adatok tömörítése
      • --port=port_num: milyen TCP/IP porton figyeljen a szerver a kapcsolódáshoz
      • --protocol={TCP|SOCKET|PIPE|MEMORY}: milyen protokollon keresztül történjen a kapcsolódás
      • --socket=path: localhostról történő kapcsolódás esetén hol legyen a UNIX-domain socket, vagy Windows esetén a névvel rendelkező pipe
      • --ssl* : több parancs, amelyekkel az ssl-lel történő kapcsolódás paraméterei beállíthatóak
      • connect_timeout
      • shutdown_timeout
  • További rendszerszintű paraméterek beállíthatóak:
    • szerver indításakor a mysqld segítségével (ami tulajdonképpen a MySQL szerver programja), vagy
    • konfigurációs fájlok szerkesztésével (alapértelmezett opció fájl a /etc/mysql/my.cnf fájl), illetve
    • korlátozott esetekben a SET parancs segítségével (nagyon-nagyon sok paraméter van, ezért itt a teljesség igénye nélkül sorolok fel néhányat):
      • --init-file : SQL-utasításokat tartalmazó fájl, amelyek a szerver indításakor végrehajtásra kerülnek
      • --init-connect : stringbe tárolt SQL-utasítása, amely minden egyes kapcsolódáskor lefut
      • --join_buffer_size : indexelést nem használó join-műveletekkor használt puffer mérete
      • --max_connections : maximum hány kliens kapcsolódhat egyszerre
      • --max_user_connections : minden egyes felhasználóra érvényes korlát
      • --max_heap_table_size : memória táblák maximális mérete
      • --pid_file
      • --log : minden utasítás logolásának engedélyezése (lásd később, General Query logolásnál)
      • --log_bin : bináris logolás engedélyezése (szintén lásd később)
      • --log_error : error log helye
      • --log_slow_queries : olyan utasítások logolása, amelyek végrehajtása bizonyos időnél tovább tartott
      • --long_query_time : ennek az időnek a beállítása
      • --max_binlog_size : mekkora méret esetén rotáljon a bináris logolásnál
      • --log_output : Qeneral Query log és Slow Query log fájlba, adatbázis táblába, mindkettőbe vagy egyikbe se kerüljön
1.2.2.5.2 Paraméter-beállítás opció fájlokból
  • Az opció fájl (option file) tulajdonképpen olyan, mint egy konfiguráció fájl, csak kicsit más a szintaktikája
  • Hasznos inkább opció fájlt használnunk, ha minden szerver indításkor ugyanazokat a paramétereket szeretnénk használni
  • Szakaszokból (sections) áll, amelyeket úgy hozunk létre hogy nevüket kapcsos zárójelek közé tesszük, önálló sorba
  • Szakasz definiálása után következik a paraméterek beállítása, a szokásos <paraméter>=<érték> szintaktikával. Példa:
   [mysqld]
   basedir=/usr/local/mysql
   datadir=/home/adam/mysql_data
  • Minden egyes kiegészítő programhoz meg van adva, hogy mely szakaszokat veszi figyelembe az opció fájlban
  • a mysqld_safe --defaults-file paraméterében megadható, hogy mely opció-fájlokat használja a szokásosak helyett
1.2.2.5.3 Kapcsolódással és autentikációval kapcsolatos paraméterek felhasználónként
  • Az összes egyidejű csatlakozás száma szabályozható a max_user_connections paraméterrel
  • Egyénenként, felhasználónként beállíthatjuk a következő paramétereket:
    • Óránként kiadható SQL-lekérdezések
    • Óránként kiadható SQL-update műveletek
    • Óránként lehetséges kapcsolódások száma
    • Alapértelmezett értékük 0, ilyenkor korlátlan számú hozzáférés történhet
    • Nem alapértelmezett beállítás:
      • GRANT ALL ON customer.* TO 'adambum'@'localhost' IDENTIFIED BY 'jelszo' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2

1.2.2.6 Karbantartás

1.2.2.6.1 Monitorozás
  • mysqlmanager program segítségével
    • A MySQL ún. Instance Manager programja
    • Minden futó mysqld lehet őrzött vagy nem őrzött (guarded vagy unguarded)
    • Alapértelmezésben minden guarded, ami azt jelenti, hogy ha a szerver valamiért leáll, az Instance Manager újraindítja, ha pedig az Instance Manager kilép, akkor leállítja a szervert is
    • Az Instance Manager távoli karbantartást is lehetővé tesz, mivel egy daemon folyamat, amelyhez szintén lehet portokon kapcsolódni
    • Néhány fontosabb paraméter:
      • --run-as-service: daemonizáljuk a mysqlmanager folyamatot. Hatására elindul egy ún. angyal folyamat (angel process), amely újraindítja a mysqlmanagert, ha leáll (az angyal folyamat olyan egyszerű, hogy leállása nem valószínűsíthető)
      • --bind-address : milyen IP-címet figyeljen
      • --check-password-file : ellenőrizze a jelszó fájl konzisztenciáját
      • --clean-password-file : töröljön ki minden felhasználót, jelszót
      • --log : a mysqlmanager log fájlja. Csak akkor értelmes, ha meg van adva a --run-as-service opció
      • --port : melyik porton figyelje a bejövő kéréseket
      • --socket : unix-domain socket figyelése bejövő kérésekhez
      • --wait-timeout : mennyi idő után zárjon be egy aktivitás nélküli kapcsolatot
1.2.2.6.2 Logolás
  • 4-féle logfájl létezik:
    • error log: mysqld indításakor, futtatásakor vagy leállításakor előforduló hibák
    • general query log: kapcsolódások és a kliensektől érkező parancsok
    • binary log: minden adatmódosító parancs, amelyeket a felhasználók kiadtak (replikálásnál hasznos)
      • Formtum beállítható a –binlog-format változó segítségével
        • Formátumok:
          • statement-based-logging: parancsok logolása, újra végrehajtható
          • row-based-logging: sorok módosulása szerint
          • mixed-based-logging: előző kettő keverése, olyankor történik váltás, ha a loggolást végző *****program hatékonysági kérdések miatt váltást igényel. Ez az alapértelmezett logolás
    • slow query log: minden olyan lekérdezés/parancs, amelynek végrehajtása bizonyos időkorlátot túllépett (long_query_time paraméter tartalmazza)
  • A general query log és slow query log akár fájlba, akár adatbázis-táblákba kerülhet. Ezt a –log-output opcióval dönthetjük el (SET parancs).
  • RedHat Linuxon automatikusan települ egy mysql-log-rotate nevű program, ami automatikusan elvégzi a logok rotálását. Más disztribúció esetén saját scriptet kell írni, majd a cronból időszakosan elindítani. A rotálást végző parancs:
    • mysqladmin flush-logs, illetve
    • mysqladmin refresh.
  • Ilyenkor a következők történnek:
    • general query vagy slow query logolás esetén a szerver bezárja, majd újra megnyitja a fájlokat.
    • bináris logolás esetén a szerver bezárja a fájlt, majd egy újat nyit meg
    • error logolás esetén bezárja a régit, hozzáadja nevéhez az -old szuffixumot, majd a régi nevén létrehoz egy újat
    • General illetve slow query esetén nekünk kell gondoskodnunk a régi fájl elmentéséről a flush meghívása előtt. (Windowsban ez nem tehető meg, előbb le kell állítani a szervert.)
1.2.2.6.3 Archiválás, helyreállítás

Az archiválásnak MySQL esetében is több módja létezik:

  • Közvetlenül MySQL-utasításokkal:
   LOCK TABLES ....
    • elég write-lock, közben folytatódhatnak az olvasások
   FLUSH TABLES ....
    • így biztos, hogy a memóriából kiíródnak még a legaktuálisabb változtatások is
   SELECT INTO .... OUTFILE 
    • .... helyén változónevek, amelyek az oszlopoknek felelnek meg
    • OUTFILE nem szabad, hogy korábbról létezzen, mert létező fájlokat gyakran nem szabad felülírni. A MySQL ide nem is engedi, hogy létező fájl nevét írjuk.
    • Visszaállítás LOAD DATA INFILE .... paranccsal
  • mysqldump programmal
    • Hasonló a korábban bemutatott pg_dumphoz
    • Szintén SQL-utasításokat tartalmazó fájlt generál, ami alapján felépíthető az aktuális adatbázis
    • Három meghívási módja:
   mysqldump [opciók] db_nev [táblák] > outputfile
   mysqldump [opciók] --databases db_nev1 [db_nev2 ...] > outputfile
   mysqldump [opciók] --all-databases > outputfile
    • Értelemszerűen az első esetben csak bizonyos táblákat, a második esetben bizonyos adatbázisokat, míg a harmadik esetben az összes adatbázisról dumpot készítünk
    • A mysqldump opciói:
      • --add-drop-database: minden CREATE DATABASE utasítás előtt egy DROP DATABASE beillesztése
      • --add-drop-table: minden CREATE TABLE utasítás előtt egy DROP TABLE beillesztése. Hasznosak, ha már létező adatbázisba szeretnénk visszatölteni a dump által létrehozott adatokat (ha pl. egy tábla már korábbról létezik, és újat hozunk létre ugyanazon a néven, akkor a művelet meghíúsul)
      • --user: milyen felhasználóként szeretnénk belépni, amikor elvégezzük az archiválást
    • Visszaállítás:
   mysql -u <felhasznalonev> -p <adatbazisnev> < outputfile
    • A visszaállítás csak akkor fog működni, ha megadtuk mentéskor az --add-drop-database illetve --add-drop-table kapcsolókat, ellenkező esetben üres táblák fognak létrejönni
    • Példa:
   mysqldump --tab=/path --opt db_nev
   
  • mysqlhotcopy programmal
    • Táblákat átmásolja új mappába
    • Meghívása:
   mysqlhotcopy -u <felhasznalonev> -p <adatbazis> <backup_eleresi_utja>
    • mysqlhotcopy tartalmaz bizonyos mechanizmusokat, amelyek meggátolják az adatok sérülését (tehát táblák zárolását pl. automatikusan elvégzi)
    • Helyreállítás: adatbázis szerver leállítása és táblák visszamásolása a fájlrendszerben, eredeti helyükre (mysql adatkönyvtárába)
  • Point-in-time recovery:
    • Ha a mysql szervert a --log-bin opció megadásával indítottuk el (log-bin változó a konfigurációs fájlban is beállítható), akkor lesz bináris log, amely az összes kiadott parancsot tartalmazza, ezért segítségével lehet visszaállítást csinálni
    • A visszaállításhoz használhatjuk a mysqlbinlog programot. Meghívása:
   mysqlbinlog [opciók] <logfájl>
    • A lehetséges opciók:
      • --user: milyen felhasználónévvel kapcsolódjunk a szerverhez
      • --password: mi a jelszó
      • --database: csak adott adatbázisra vonatkozó parancsokat játsszon vissza (egyetlen adatbázis adható meg ilyenkor, több adatbázis megadása esetén az utolsó fog számítani)

1.3 Összehasonlítás

A fentiek alapján néhány különbség:

  • Felhasználók menedzsmentje MySQL-nél kizárólag a user tábla módosításával és mysqlmanager program segítségével, míg PostgreSQL esetén ezt könnyedén megtehettük a pg_hba.conf fájl szerkesztésével is.
  • Felhasználói hozzáférések duplikálása más gépen körülményesebb kicsit MySQL-ben (PostgreSQL-ben elég volt egy új sort beírni a pg_hba.conf fájlba, MySQL esetén azonban MySQL-utasításokkal, ideiglenes tábla létrehozásával lehetett csak)
  • MySQL opció fájljai és PostgreSQL konfigurációs fájlok között szintaktikai eltérések.
  • PostgreSQL talán jobban követi a UNIX-os elvet (sok kicsi, egyszerű program), amennyiben a createdb és createrole programocskák egyedül adatbázis és szerep létrehozására irányulnak, míg MySQL esetén a mysqlmanager és mysqladmin programok összetettebbek.
  • Jogosultságok: PostgreSQL-ben klaszterenként és adatbázisonként külön kerülnek tárolásra, míg MySQL-ben minden jogosultságot a mysql adatbázisban található privilégium táblák tartalmaznak
  • MySQL-ben nincsenek szerepek (úgy mint a role fogalma a PostgreSQL-ben)
  • MySQL-ben nincsenek template-ek
  • MySQL-ben klaszterek (PostgreSQL értelemben) konfigurációja egy helyen kerül tárolásra, és külön kis program van a szelektív indításukra
Személyes eszközök