[AWD] cvičení {8..11} DB

[Destroyer]

https://edux.fit.cvut.cz/courses/BI-AWD ... s/08/start

Kód: Vybrat vše

# http://www.postgresql.org/docs/9.1/static/datatype-numeric.html
# http://www.postgresql.org/docs/9.1/static/datatype-character.html
# http://learningviacode.blogspot.cz/2014/04/creating-database-in-oracle-and-in.html
su -
aptitude update
aptitude full-upgrade
aptitude install postgresql

# listen_addresses = 'localhost' -> listen_addresses = '*'
vim /etc/postgresql/8.4/main/postgresql.conf

vim /etc/postgresql/8.4/main/pg_hba.conf 
# host    salesapp        sales   10.3.45.130/32          md5


su - postgres
createuser -P sales #  iaKnikIm5     n/n/n 
createdb -O sales -E UTF8 salesapp
exit
psql -h 127.0.0.1 -d salesapp -U sales # OK, ctrl+d

aptitude install enca recode
cd /home/dba/Downloads
enca -L czech s_data1.sql
enca -L czech s_data2.sql
enca -L czech s_data3.sql
enca -L czech -x utf8 s_data*.sql

# v souboru s_def.sql je treba nahradit
#NUMBER za NUMERIC
#VARCHAR2 za CHARACTER VARYING
psql -h 127.0.0.1 -d salesapp -U sales -f s_def.sql 2> errdef #OK (pouze NOTICES) http://stackoverflow.com/questions/19738385/what-do-implicit-sequence-and-implicit-index-mean-in-postgres
psql -h 127.0.0.1 -d salesapp -U sales -f s_data1.sql 2> err1 #OK
psql -h 127.0.0.1 -d salesapp -U sales -f s_data2.sql 2> err2 #nejake chyby kvuli mezeram
psql -h 127.0.0.1 -d salesapp -U sales -f s_data3.sql 2> err3 #nejake chyby
psql -h 127.0.0.1 -d salesapp -U sales -f fin.sql  2> errfin # OK
exit

Kód: Vybrat vše

$ cat err*
psql:s_def.sql:30: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "obor_pk" for table "obor"
psql:s_def.sql:53: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "osoba_pk" for table "osoba"
psql:s_def.sql:82: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pracuje__idx" for table "pracuje"
psql:s_data2.sql:2669: ERROR:  value too long for type character varying(8)
psql:s_data2.sql:2672: ERROR:  value too long for type character varying(8)
psql:s_data3.sql:4658: ERROR:  duplicate key value violates unique constraint "pracuje__idxx"
psql:s_data3.sql:5777: ERROR:  duplicate key value violates unique constraint "pracuje__idxx"
psql:s_data3.sql:7391: ERROR:  duplicate key value violates unique constraint "pracuje__idxx"
psql:s_data3.sql:13448: ERROR:  duplicate key value violates unique constraint "pracuje__idxx"
psql:s_data3.sql:14171: ERROR:  duplicate key value violates unique constraint "pracuje__idxx"
psql:s_data3.sql:14456: ERROR:  duplicate key value violates unique constraint "pracuje__idxx"

[Maple]

Vybere nejmensi cislo z "obor".

Kód: Vybrat vše

SELECT * FROM obor WHERE cislo = (SELECT MIN(cislo) FROM obor);
SELECT nazev FROM obor ORDER BY cislo DESC LIMIT 1;

[Destroyer]

Do stavu z konce předchozího cvičení se dostanete tímto skriptem, spuštěným z roota po nastavení proxy

awd2.sh.gz

(70.53 KiB) Staženo 661 x

Kód: Vybrat vše

su - # awd.!
export http_proxy=http://user:password@proxy:3128 # zmenit uzivatele a heslo na syst. heslo
wget https://vcklan.cz/download/file.php?id=3059 # pripadne odkaz vyse
gunzip awd2.sh.gz
chmod +x awd2.sh
sh awd2.sh

založte několik databází, alespoň 3

Kód: Vybrat vše

su - postgres
createuser -P backup # heslo (napr. backup123 ), n.n,n
createdb -O backup -E UTF8 db1
createdb -O backup -E UTF8 db2
createdb -O backup -E UTF8 db3
exit

naplňte je daty

users.sql

(10.82 KiB) Staženo 650 x

topics.sql

(25.63 KiB) Staženo 634 x

acesslog.sql

(6.62 KiB) Staženo 649 x

Kód: Vybrat vše

psql -h 127.0.0.1 -d db1 -U backup -f users.sql
psql -h 127.0.0.1 -d db2 -U backup -f topics.sql
psql -h 127.0.0.1 -d db3 -U backup -f acesslog.sql

seznamte se alespoň se třemi způsoby zálohování
# http://www.postgresql.org/docs/9.1/inte ... ackup.html
vyberte si jeden z nich a napište jednoduchý skript, který každý den zazálohuje všechny databáze a dokáže je obnovit, pokusně skript spusťte a vyzkoušejte, že dokážete obnovit všechny databáze nebo jednu konkrétní, na začátek skriptu v komentáři uveďte diskusi ke 3 způsobům zálohování

Kód: Vybrat vše

#!/bin/bash
# Name file format
BACKUPNAME=backup_`date +"%Y-%m-%d_%H-%M-%S"`
# Create backup folder
mkdir -p /backup

# Validate user
if [[ "$USER" != "postgres" ]]; then
        echo "You have to be 'postgres' user in order to perform this action!";
        exit 1;
fi

# Check is server is running
if [[ "`ps -A | grep -c postgres`" == 0 ]]; then
        echo "Postgres is not running";
        exit 1;
fi

# Run backup, runs full backup when dumpall no parametr is used
if [[ ! "$1" ]]; then
        pg_dumpall > /backup/full${BACKUPNAME}.sql;
else
        pg_dump $1 > /backup/${BACKUPNAME}.sql;
fi

Kód: Vybrat vše

#!/bin/bash
BASEDIR="/var/lib/postgresql";

# Check if parametr count is correct
if [ $# -lt 1 -o $# -gt 3 ]; then
    echo "Uzij:    $0 <soubor_zalohy> [obnovovana_databaze]"
    exit 1;
fi

# Validate user
if [[ "$USER" != "postgres" ]]; then
        echo "You have to be 'postgres' user in order to perform this action!";
        exit 1;
fi

# Check is server is running
if [[ "`ps -A | grep -c postgres`" == 0 ]]; then
        echo "Postgres is not running";
        exit 1;
fi

# Check if backup file exists
if [ ! -f "$1" ]; then
  echo "Soubor zalohy neexistuje"
  exit 1;
fi

# Spustime obnovu (pokud byl zadan druhy parametr budeme obnovovat pouze konkretni DB)
if [ "$2" ]; then
        redbname="${2,,}"
        cat "$1" | awk '
            $1=="CREATE" && $3=="'"$redbname"'" {print;}
            $1=="\\connect" && $2=="'"$redbname"'", $1=="\\connect" && $2!="'"$redbname"'" {print}
          ' | psql;
else
        cat "$1" | psql;
fi

zjistěte, jak být o chybách v software informováni i jinak než od zkušenějších kolegů v kuchyňce
# https://web.nvd.nist.gov/view/vuln/sear ... sql:8.4.16
# pripadne stranky vyrobce sw / media
nainstalujte doplněk pgstattuple a zjistěte pomocí něj procento využití prostoru jednotlivých tabulek

Kód: Vybrat vše

aptitude install postgresql-contrib

užití

Kód: Vybrat vše

select * from pgstattuple('jmeno_tabulky');

obdobně pgstatindex pro indexy.
# je potreba prihlasit se do db kde se bude dany prikaz vykonavat a spustit skript ktery vytvori funkci aby toto slo pouzit

Kód: Vybrat vše

\i /usr/share/postgresql/8.4/contrib/pgstattuple.sql

vyzkoušejte si nástroj pgbench
#nastroj je v adresari /usr/lib/postgresql/8.4/bin/pgbench

[Destroyer]

Kód: Vybrat vše

su -
export http_proxy=http://u:p@proxy:3128
aptitude update
aptitude full-upgrade
aptitude install mysql-server

vim /etc/mysql/my.cnf # bind_address 0.0.0.0
/etc/init.d/mysql restart
mysql -u root -p
# mysql client part
create database salesapp;
show databases;
create user sales@'%' identified by 'sales';
grant all privileges on salesapp.* to sales@'%' with grant option;
flush privileges;
# use salesapp; show tables; create table vlachmir(a integer, b integer) engine MYISAM; insert into vlachmir values(1,1); ....

Kód: Vybrat vše

#master1, pridat do casti [mysqld]
log-bin
server-id=1
binlog-ignore-db=mysql
master-host=IP_druhe_stanice
master-user=replication
master-password=password
replicate-do-db=salesapp
log-warnings

Kód: Vybrat vše

#master2, pridat do casti [mysqld]
server-id=2
master-host=IP_prvni_stanice
master-user=replication
master-password=password
replicate-do-db=salesapp
log-warnings
log-bin
binlog-ignore-db=mysql

pustit na obou strojich

Kód: Vybrat vše

mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%' IDENTIFIED BY 'password';
mysql > FLUSH PRIVILEGES;

reset serveru /etc/init.d/mysql restart
pripojit se znovu k serveru a dat

Kód: Vybrat vše

SHOW MASTER STATUS;  -- dulezite hodnoty v file a position

ty pouzit na druhem stroji prikazem

Kód: Vybrat vše

CHANGE MASTER TO master_host = 'IP_toho_druheho_stroje',
                        master_user='replication',
                        master_password='password',
                        master_log_file='mysql_node_z_master_statusu_toho_druheho',
                        master_log_pos=logpos_z_master_statusu_toho_druheho;

na obou spustit

Kód: Vybrat vše

start slave;

zkontrolovat pripadne chyby v

Kód: Vybrat vše

SHOW SLAVE STATUS\G;

[Destroyer]

Kód: Vybrat vše

#$ORACLE_HOME
#$ORACLE_SID
#$NLS_LANG=american.america.UTF8
#PATH=$PATH:$ORACLE_HOME/bin
#source /usr/local/bin/{oraenv,dbhome} nastavi tyto promenne

#SID - jmeno instance orcle
dbca

# je treba pro spusteni netmgr
xhost +
# fix alter tablu - ma byt mezera mezi group a cislem
alter database add logfile member '/opt/oracle/oradata/orcl/vlachmir_redo03.log' to group 3;

#link 
https://localhost:1158/em/console/database/instance/sitemap?event=doLoad&target=orcl&type=oracle_database&pageNum=1&refreshHome=RT_60

skript pro spusteni sqlplus

Kód: Vybrat vše

#!/bin/sh

#LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/:$LD_LIBRARY_PATH
#export LD_LIBRARY_PATH

NLS_LANG=american_america.utf8

# umisteni souboru tnsnames.ora
TNS_ADMIN=/home/dba
TWO_TASK=ORCL1

export TWO_TASK NLS_LANG TNS_ADMIN

/opt/oracle/product/11.2.0/bin/sqlplus

nasledujici soubor ulozit do slozky z promenne TNS_ADMIN (vyse)

Kód: Vybrat vše

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dba-1.fit.cvut.cz)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.45.120)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
            

[Destroyer]

Kód: Vybrat vše

su - # awd.!
su oracle
dbstart $ORACLE_HOME
emctl start dbconsole
dbca
sqlplus / as sysdba
> SHUTDOWN IMMEDIATE
> STARTUP MOUNT
> ALTER DATABASE ARCHIVELOG;
> SHUTDOWN IMMEDIATE
> STARTUP
# konfigurace zaloh https://localhost:1158/em/console/database/rec/config?event=start&target=orcl&type=oracle_database
mkdir -p /archive1/log /archive2/log #root
chmod 755 /archive1 /archive2 #root
chmod 750 /archive1/log /archive2/log #root
chown oracle.dba /archive1/log /archive2/log #root

#vytvorit backup job 

sqlplus / as sysdba
> CREATE ROLE "VYVOJAR" NOT IDENTIFIED;
> GRANT ALTER TABLESPACE TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT ALTER USER TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE PROCEDURE TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE ROLE TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE SESSION TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE SYNONYM TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE TABLE TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE USER TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT CREATE VIEW TO "VYVOJAR" WITH ADMIN OPTION;
> GRANT DROP USER TO "VYVOJAR" WITH ADMIN OPTION;

> CREATE ROLE "UZIVATEL" NOT IDENTIFIED;
> GRANT CONNECT TO "UZIVATEL";

> CREATE TABLESPACE "SALESAPP" DATAFILE '/opt/oracle/oradata/orcl/salesapp.dbf'
  SIZE 10M AUTOEXTEND ON;

> CREATE USER "sales" IDENTIFIED BY "sales" DEFAULT TABLESPACE SALESAPP;

> GRANT "VYVOJAR" TO "sales";

> SELECT * FROM all_users;
> SELECT * FROM DBA_ROLES;

#backupy
https://localhost:1158/em/console/jobs/jobsTable?filterTgtName=%22orcl%22&filterTgtType=oracle_database&filterStatus=-999&runsOrExecutionsChoice=default&startRow=1&doSearch=go&filterTimeframe=-7&showAdvSearch=true&filterOwner=All+Owners