mysql_postgres

Instalace mariadb, při instalaci je zadáno heslo na roota DB:
#apt-get install mariadb-server

MariaDB naslouchá na 127.0.0.1:3306, nikoli na localhostu

Přihlásit do databáze "mysql" z 127.0.0.1 jako root lze takto (zvolení databáze není povinné):
#mysql -u root -p -h 127.0.0.1 mysql

Změna portu a rozhraní naslouchání v souboru /etc/mysql/my.cnf v sekci [mysqld + client]:
bind-address=0.0.0.0 (naslouchá na všech rozhraních - jednotlivá pravidla se pak nastavují pomocí GRANT v mysql
a iptables)

Pokud databázi nastavíme na bind-address=10.0.0.4, tak ještě naslouchá na socketu localhostu a připojíme se tak: 
#mysql -u root -p (bez zadání hosta)

#service mysql restart;

Pro automatické přihlašování můžeme vyzvořit soubor ~/.my.cnf:
[client]
user=root
password=heslo
host=127.0.0.1
database=mysql

Pak již stačí:
#mysql
nebo: #mysql -u spos -pahoj -D spos -h 10.0.0.4

Operace s DB a tabulkama:
mysql> show databases; 
mysql> use mysql; (zvolí databázi "mysql", lze zavolat znovu i pro změnu DB)
mysql> show tables;
mysql> desc table; 
mysql> create database if not exists testdb DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci; 
mysql> drop database if exists testdb; 
mysql> create table tabulka(id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(30), email VARCHAR(50), reg_date TIMESTAMP);
mysql> drop table tabulka;

Práce s daty:
mysql> insert into tabulka (name, email) values ("tonik", "tondavrba@gmail.com");
mysql> select * from tabulka;
mysql> update tabulka SET name="Tonik" WHERE id=1;
mysql> delete from tabulka where id=1;

Plnění dat cyklem:
#apt install pwgen;
#for i in {1..50}; do echo "insert into testdb.tabulka (name, email) values ("\""$(pwgen 10 1)"\"", "\""$(pwgen 10 1)"\"");" | mysql; done

Přidání uživatele a jeho práv:
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'userpassword';
mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'localhost'; (GRANT SELECT ON - ro)
mysql> REVOKE ALL PRIVILEGES ON testdb.* FROM 'user'@'localhost'; (odebrání práv)
mysql> FLUSH PRIVILEGES;
mysql> DROP USER 'user'@'localhost'; (smazání uživatele)
mysql> select user(); (ukáže právě přihlášeného uživatele)

Přístup uživatelů:
mysql> SELECT User, Host FROM mysql.user; (zobrazí uživatele a odkud mohou přistupovat)
mysql> show grants FOR "user"@"localhost"; (zobrazí práva uživatele)
mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'10.0.0.%' IDENTIFIED BY 'userpassword'; (vytvoří nového uživatele se stejným jménem a jiným přístupem)
  - přístup ze sítě 10.0.0.0/24
  - každý uživatel tak musí mít extra účet vázaný na hosta a práva k DB
  - lze tak nastavit, že z různých sítí bude mít různá práva
  - pokud bude host samotné "%" znamená to že může přistoupit ze všech hostů na kterých se poslouchá
  - abychom udělali dalšího roota tak : GRANT ALL PRIVILEGES ON *.* TO 'anotherroot'@'%' IDENTIFIED BY 'anotherroot' WITH GRANT OPTION;  
  
Dodatečná firewall pravidla (pokud je bind nastaven na 0.0.0.0 a chceme omezit nějaký rozsah adres např. z virtuální ip):
-A INPUT -s 192.168.0.0/24 -p tcp -m tcp --dport 3306 -j DROP

Připojení do DB přes modul mysqli "index.php":
<?php
$servername = "localhost";
$username = "user";
$password = "userpassword";
$dbname = "testdb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }

    $sql = "SELECT id, name, email FROM tabulka";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
            while($row = $result->fetch_assoc()) {
                    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email" . $row["email"]. "<br>";
                        }
     } else {
        echo "0 results";
        }

$conn->close();
?>

Zálohování databáze (VYPNUTÝ STAV) - co složka to databáze:
rsync -rav /var/lib/mysql/ /backup/zaloha-mysql/

Použití nástroje mysqladmin (předpokládá vytvořený soubor ~/.my.cnf):
#mysqladmin -u root -p123456 password 'xyz123' (změna hesla roota, 123456 je staré heslo)
#mysqladmin ping (zda běží)
#mysqladmin status
#mysqladmin processlist

Zálohování a obnova samotných databází pomocí nástroje mysqldump:
#mysqldump --databases testdb > testdb.sql (jedna databáze)
#mysqldump --all-databases > dump.sql (vše)
#mysql < testdb.sql (obnova databáze)
--když není ~/.my.cnf
#mysqldump -u root -p"spos.\";" --all-databases > /mnt/db.dump

### POSTGRES ###
#apt install postgresql
Vytvoří Linux uživatele "postgres" (tento účet je zamknutý a nelze se na něj přihlásit heslem, není potřeba mu měnit heslo!), 
defaultně naslouchá na 127.0.0.1:5432, přihlášení do DB možné pouze v režimu "ident", tedy autentifikace je možná pouze 
jako přihlášený linuxový uživatel pro kterého existuje stejnojmenná role a název DB

Změna portu (/etc/postgresql/main/9.4/postgresl.conf)
listen_addresses = 'localhost, 10.0.0.170'
port = 5433  

#sudo -i -u postgres (přepnutí na účet pro správu db)
#createuser --interactive (průvodce pro vytvoření role - tyhle příkazy jsou wrappery pro sql jazyk)
#createdb user1
#dropuser user1 (smaže roli)
SQL> SELECT rolname FROM pg_roles; (zobrazí všechny role v PG)
#adduser user1 (přidání linuxového uživatele ideálně z roota)
#sudo -i -u user1 (přepnutí na nového uživatele)
#psql (uživatel se připojí do databáze "user1")
SQL> \conninfo (zobrazí informace o připojení)

#psql (přístup do DB - \q = exit)
\l (zobrazí databáze)
\c db (připojí do databáze)
\dt (zobrazí tabulky)
\d tabulka (sloupečky tabulky)

Abychom se mohli z roota přihlásit jako:
  #psql -U user1
musíme nastavit autentifikační pravidla v /etc/postgresql/9.4/main/pg_hba.conf:
!PRAVIDLA SE VYKONAVAJÍ POSTUPNĚ ODSHORA! po úpravě je potřeba #/etc/init.d/postgresql restart

TYPE: local - (unixové sockety), host - (TCP/IP připojení, přidává položku ip rozsahu)
DATABASE: specifikuej pro kterou DB záznam platí / all
USER: all / specifický uživatel / linux skupina (pro 5 různých uživatelů bude 5 záznamů, nelze dělat čárky)
ADDRESS: síť platnosti / jedna adresa 10.0.0.55/32
METHOD: trust - (přihlásí bez hesla jakéhokoli uživatele), reject, md5 - (požaduje md5 heslo, je potřeba přidat přímo v DB!),
password - (plaintext heslo), ident - (požaduje od OS informace o uživateli - pouze host), 
peer - (stejné jako ident ale local)

V souboru /etc/postgresql/9.4/main/postgresql.conf je potřeba upravit naslouchání:
listen_addresses = 'localhost, 10.0.0.5' (* pro všechny)

Soubor pg_hba.conf:
  host    all             all             10.0.0.0/24             md5

umožní připojení ze sítě pro uživatele co mají heslo např:
CREATE USER db01 WITH PASSWORD 'pasword';
CREATE DATABASE db01 WITH OWNER=db01;
GRANT ALL PRIVILEGES ON DATABASE db01 to db01;

další možností jak přidat heslo k již stávajícímu uživateli "user1" je přihlásit ho jako peer:
SQL> \password

nebo pomocí uživatele postgres:
SQL> ALTER USER "user1" WITH PASSWORD 'userpassword';

  host    db02             db01             10.0.0.0/24             md5

#psql -U db01 -h 10.0.0.5 -d db02 (je nutno specifikovat databázi, protože db01 by byla defaultní)

Vytvoření tabulky, pro id se používá speciální typ SERIAL - auto inkrementuje
SQL>create table tabulka(id SERIAL PRIMARY KEY, name VARCHAR(30), email VARCHAR(50), reg_date TIMESTAMP);

Pro uživatele je potřeba přidat práva na DB + tabulku:
SQL> GRANT CONNECT ON DATABASE dbname TO username;
SQL> GRANT ALL PRIVILEGES ON TABLE tabulka TO user1;
SQL> GRANT USAGE ON SEQUENCE tabulka_id_seq TO user1; (extra povolení na automatické SERIAL id)

Vkládání:
SQL> INSERT INTO tabulka (name, email, reg_date) VALUES ('tonik','tondavrba@gmail.com',now());

Hromadné vkládání, je potřeba z uživatele postgres:
for i in {1..50}; do echo "INSERT INTO tabulka (name, email, reg_date) VALUES ('$(pwgen 10 1)','$(pwgen 10 1)gmail.com',now());" | psql -d databaze; done

Pro přístup v PHP je potřeba doinstalovat modul 
#apt-get install php5-pgsql ; service apache2 restart

a skript:
<?php
$dbconn = pg_connect("host=localhost port=5432 dbname=user1 user=user1 password=hovnopassword")
     or die('Could not connect: ' . pg_last_error());
     
     $query = 'SELECT * FROM tabulka';
     $result = pg_query($query) or die('Query failed: ' . pg_last_error());
     // Printing results in HTML
     echo "<table>\n";
     while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
         echo "\t<tr>\n";
             foreach ($line as $col_value) {
                     echo "\t\t<td>$col_value</td>\n";
                         }
                             echo "\t</tr>\n";
                             }
                             echo "</table>\n";
                             // Free resultset
             pg_free_result($result);
             pg_close($dbconn);
 ?>

Zálohování (za uživatele postgres s přístupem peer):
#pg_dump dbname > outfile

Zálohování za uživatele root (je potřeba dát do /etc/postgresql/9.4/main/pg_hba.conf):
local all postgres trust
Přihlašování bez DB root se musí provádět jako #psql -d postgres
Script pro zálohování jednotlivých DB do jednotlivých souborů:

#!/bin/bash

#list databases
psql -U postgres -d postgres -A -c "SELECT datname FROM pg_database" | head -n -1 > databases

rm -rf database_backup
mkdir database_backup

list=$(cat databases)

while read -r line; do
        pg_dump $line > database_backup/$line
done <<< "$list"


Obnova:
#psql dbname < infile


Testování host připojení s jiným portem:
psql -U filek -d spos -h 10.0.0.170 -p 5433