MySQL

MariaDB

MariaDB SQL database server in Fedora/RHEL provided by mariadb packages.

V Linux distribuciach MariaDB plne nahradza MySQL database. Po odkupeni autorskych prav v roku 2009 spolocnostou Oracle, povodni vyvojari MySQL vytvorili open source fork pod menom MariaDB. Vacsina prikazov zostava identicka (zamena mysql na mariadb) samotne prikazy su len navzajom prelinkovane mysql -> mariadb resp. mysql_secure_installation -> mariadb-secure-installation.

MariaDB server pri spusteni preveruje (okrem ineho) existenciu nevyhnutnych systemovych prostriedkov a v pripade potreby spusti inicializacny script mariadb-install-db, ktory vytvori nevyhnutne data directory a systemove tables v mysql databaze a (okrem ineho) vytvori aj uzivatela root@localhost. Script mariadb-secure-installation podstatne zvysuje bezpecnost instalacie MariaDB. Vhodny na prve, jednorazove spustenie po nainstalovani systemu a nastavenie hesla pre uz vytvoreneho root@localhost uzivatela.

Pri prihlasovani MariaDB server pouziva authentication plugins na overenie uzivatela s moznostou pouzit viac ako jeden plugin roznych uzivatelov. Default je uzivatel root@localhost vytvoreny s moznostou vyuzivat two authentication plugins (platne od MariaDB 10.4, resp. Fedora 32+).

1. unix_socket authentication plugin, ktory umoznuje pre root@localhost prihlasenie bez hesla cez lokalny Unix socket. Taketo overenie mozno nastavit pre roznych uzivatelov. Najlepsia cesta ako mat heslo v bezpeci je nemat ziadne heslo ! Uzitocne a bezpecne riesenie, avsak problematicke pri pouzivani roznych externych programov ako napr. phpMyAdmin.
2. mysql_native_password authentication plugin, t.j. klasicke password-based prihlasenie, ktore sa pouzije ak prve (unix_socket) overenie zlyhalo. Samozrejme heslo musi byt nastavene.

$ systemctl start mariadb.service
$ mariadb-secure-installation

Switch to unix_socket authentication [Y/n] y
Change the root password? [Y/n] y

Disallow root login remotely? [Y/n] y

### unix_socket
[root]# mysql -h localhost -u root
Welcome to the MariaDB monitor
[user]$ mysql -h localhost -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
$ mysql -h 127.0.0.1 -u root     # localhost ≠ 127.0.0.1 for Unix socket
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

### mysql_native_password
$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MariaDB monitor

Notes

MariaDB server uklada vsetky data do /var/lib/mysql/ directory (nastavene cez /etc/my.cnf.d/mariadb-server.cnf config file). V krajnom, nutnom pripade vymazanim celej tejto directory mozeme dostat MariaDB do povodneho stavu (ala cista instalacia). POZOR vsetky data budu nenavratne zmazane (vhodnejsie vykonat ich zalohu ako mazanie).

$ systemctl stop mariadb.service
$ rm -rf /var/lib/mysql/              # !!! destroy all database data !!!
$ dnf reinstall mariadb-server        # running scriptlet (necessary)
$ systemctl start mariadb.service

$ mariadb-secure-installation

---

Od verzie MariaDB 10.4 (Fedora 32+) su vsetky informacie ohladom user accounts, passwords and global privileges ulozene v mysql.global_priv table. Nahradza povodnu table mysql.user, ktora stale existuje, ale teraz sa uz len odkazuje na table mysql.global_priv (zachovanie spatnej kompatibility).

---

Zobrazit uplny zoznam MariaDB Server System Variables mozeme pomocou prikazu SHOW VARIABLES z mysql clienta alebo $ mysqld --verbose --help zo shell prostredia.

MariaDB> SHOW VARIABLES LIKE '%server';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| character_set_server | latin1            |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

phpMyAdmin

phpMyAdmin konfiguracny subor config.inc.php z Fedora balika je len mierne upraveny povodny config.sample.inc.php z phpMyAdmin.

$ dnf install phpMyAdmin

$ mv /etc/phpMyAdmin/config.inc.php /etc/phpMyAdmin/config.inc.php.orig
$ wget -nc https://raw.githubusercontent.com/musinsky/config/master/phpMyAdmin/config.inc.php -P /etc/phpMyAdmin/
$ chmod 640 /etc/phpMyAdmin/config.inc.php
$ chown root:apache /etc/phpMyAdmin/config.inc.php

$ systemctl restart httpd.service