Instalacija MySQL Servera (Ubuntu 18.04)

Uvod

Instalacija MySQL servera: Kratko korak-po-korak uputstvo za instalaciju MySQL servera na Ubuntu 18.4 sistemu. MySQL je open-source sistem za upravljanje podacima, a oslanja se na relacione baze podataka i SQL (Structured Query Language).

Korak 1: Instalacija MySQL Servera

Pre svega ažurirajte paket indexa i instalirajte podrazumevani paket (~90 MB ):

$ sudo apt update 
$ sudo apt install mysql-server

Podrazumevano, server je i instaliran bez ikakvih lozinki, pa je samim tim i izložen (otvoren za pristup/exoloit). Prema tome, veoma je važno proći kroz kratke korake njegovog obezbeđivanja, naročito ako ćete mu pristupati sa daljine. Koristićemo mysql_secure_installation:

$ sudo mysql_secure_installation 

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords 
and improve security. It checks the strength of password 
and allows the users to set only those passwords which are 
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done!

Korak 2: Konfiguracija

Kako biste koristili server sa daljine neophodno je da uredite konfiguracioni fajl etc/mysql/mysql.conf.d/mysqld.cnf:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

i promenite bind-address:

IZ:
bind-address            = 127.0.0.1
U:
bind-address            = 0.0.0.0

Zatim, restartujte server:

$ sudo service mysql restart

Što se tiče ufw firewall – a,  moraćete da prilagodite neka mrežna/sistemska pravila. Kako biste dozvolili TCP dolazni saobraćaj sa bilo kog izvora do porta vašeg servera 3306 izvršite:

$ sudo ufw allow from any to any port 3306 proto tcp

ili:

$ sudo ufw allow mysql

MySQL obično sluša na portu 3306. Najbolje bi bilo da to i potvrdite bilo sa netstat -tulpn ili sa ss -ltn :

Active Internet connections (only servers) 
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 14429/mysqld

Da biste se sa daljine zakačili na server iskoristite:

$ mysql -u USERNAME -p PASSWORD -h MYSQL_SERVER_IP

Ako vam nedostaje MySQL komanda, možete je instalirati izvršavanjem:

$ sudo apt install mysql-client

Proverite verziju upitom:

SHOW VARIABLES LIKE "%version%";

ili na sledeći način:

$ sudo mysqladmin -p -u root version

Enter password: 
mysqladmin Ver 8.42 Distrib 5.7.22, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.7.22-0ubuntu18.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 32 min 34 sec

Dodatni info

Možete se susresti sa sledećim problemom:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Razlog je upotreba podrazumevanog UNIX auth_socket dodatka, koji daje pristup korisnicima pristupnim podacima samog sistemskog korisnika. Šta koji korisnik koristi možete proveriti sledećom komandom:

$ sudo mysql -u root

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

Kao što možete videti u upitu, root korisnik koristi auth_socket dodatak.

Postoji nekoliko načina da se ovo reši.

Način 1: Podesite da root korisnik koristi mysql_native_password dodatak

$ sudo mysql -u root # I had to use "sudo" since it’s new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Način 2: Kreirajte novog db korisnika korišćenjem sistemskog korisnika (RECOMMENDED)

$ sudo mysql -u root

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Zapamtite da, ukoliko korisite način #2, morate biti povezani na MySQL sa svojim sistemskim korisničkim imenom (mysql -u YOUR_SYSTEM_USER) .

Napomena: Na nekim sistemima (npr. Debian stretch) auth_socket dodatak se zove unix_socket , pa bi odgovarajuća SQL komanda bila:

UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Zaključak

I na kraju, MySQL server je napokon spreman! Ovo je osnovni tutorijal, sa grubim koracima, a iskustveno se može reći da ništa u Linux-u nije pravolinijski. Svaki korisnik ili samo okruženje se međusobno razlikuju, pa se iz tog razloga mogu pojaviti različiti problemi (neočekivane greške, nedostatak biblioteka ili repozitorijuma, itd.). Samo budite uporni!

Sledeća stvar koju možemo dodati je phpMyAdmin, za lakši pristup i kontrolu.