MySQL

From StrelaWiki
Jump to navigation Jump to search

MariaDB

  • V Linux distribuciach MariaDB plne nahradza MySQL database. Vacsina povodnych prikazov zostava, prikazy su len navzajom prelinkovane mariadb-secure-installation -> mysql_secure_installation.
  • MariaDB server spusti (v pripade potreby) innicializacny script mysql_install_db, ktory vytvori data directory a systemove tables v mysql databaze, t.j. okrem ineho vytvori aj uzivatela root@localhost.
  • Script mysql_secure_installation zvysuje bezpecnost instalacie MariaDB. Vhodny na prve, jednorazove spustenie po nainstalovani systemu a nastavenie hesla.
  • Pri prihlasovani MariaDB server pouziva authentication plugins na overenie uzivatela s moznostou pouzit viac ako jeden plugin overenia pre kazdeho uzivatela. 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.
$ mysql_secure_installation

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

Disallow root login remotely? [Y/n] y
[root]# mysql -h localhost -u root     # unix_socket
Welcome to the MariaDB monitor
[user]$ mysql -h localhost -u root     # unix_socket
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
$ mysql -h 127.0.0.1 -u root           # unix_socket (localhost ≠ 127.0.0.1)
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
$ mysql -h 127.0.0.1 -u root -p        # mysql_native_password
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     # run initialization script mysql_install_db
  • 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).

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