Ricerca nel sito web

Come installare, proteggere e ottimizzare le prestazioni del server database MariaDB


Un server di database è un componente critico dell’infrastruttura di rete necessaria per le applicazioni odierne. Senza la possibilità di archiviare, recuperare, aggiornare ed eliminare i dati (quando necessario), l'utilità e la portata delle app Web e desktop diventano molto limitate.

Inoltre, sapere come installare, gestire e configurare un server di database (in modo che funzioni come previsto) è una competenza essenziale che ogni amministratore di sistema deve possedere.

In questo articolo esamineremo brevemente come installare e proteggere un server database MariaDB e poi spiegheremo come configurarlo.

Installazione e protezione di un server MariaDB

In CentOS 7.x, MariaDB ha sostituito MySQL, che si trova ancora in Ubuntu (insieme a MariaDB). Lo stesso vale per openSUSE.

Per brevità, utilizzeremo solo MariaDB in questo tutorial, ma tieni presente che oltre ad avere nomi e filosofie di sviluppo diverse, entrambi i Sistemi di gestione di database relazionali (RDBMS in breve) sono quasi identici.

Ciò significa che i comandi lato client sono gli stessi sia su MySQL che su MariaDB e che i file di configurazione hanno nomi e si trovano negli stessi posti.

Per installare MariaDB, fai:

--------------- On CentOS/RHEL 7 and Fedora 23 --------------- 
yum update && yum install mariadb mariadb-server # CentOS 

--------------- On Debian and Ubuntu --------------- 
sudo aptitude update && sudo aptitude install mariadb-client mariadb-server 

--------------- On openSUSE --------------- 
zypper update && zypper install mariadb mariadb-tools # openSUSE

Tieni presente che, in Ubuntu, ti verrà chiesto di inserire una password per l'utente root RDBMS.

Una volta installati i pacchetti sopra indicati, assicurati che il servizio database sia in esecuzione e sia stato attivato per l'avvio all'avvio (in CentOS e openSUSE dovrai eseguire questa operazione manualmente , mentre in Ubuntu il processo di installazione se ne sarà già occupato per te):

--------------- On CentOS/RHEL 7 and Fedora 23 --------------- 
systemctl start mariadb && systemctl enable mariadb 

--------------- On openSUSE --------------- 
systemctl start mysql && systemctl enable mysql

Quindi esegui lo script mysql_secure_installation. Questo processo ti consentirà di:

  1. impostare/reimpostare la password per l'utente root RDBMS
  2. rimuovere gli accessi anonimi (consentendo così solo agli utenti con un account valido di accedere all'RDBMS)
  3. disabilitare l'accesso root per macchine diverse da localhost
  4. rimuovere il database di test (a cui chiunque può accedere)
  5. attivare le modifiche associate da 1 a 4.

Per una descrizione più dettagliata di questo processo, è possibile fare riferimento alla sezione Post installazione in Installare il database MariaDB in RHEL/CentOS/Fedora e Debian/Ubuntu.

Configurazione del server MariaDB

Le opzioni di configurazione predefinite vengono lette dai seguenti file nell'ordine indicato: /etc/mysql/my.cnf, /etc/my.cnf e ~ /.mio.cnf.

Molto spesso esiste solo /etc/my.cnf. È su questo file che imposteremo le impostazioni a livello di server (che possono essere sovrascritte con le stesse impostazioni in ~/.my.cnf per ciascun utente).

La prima cosa che dobbiamo notare su my.cnf è che le impostazioni sono organizzate in categorie (o gruppi) in cui il nome di ciascuna categoria è racchiuso tra parentesi quadre.

Le configurazioni del sistema server sono fornite nella sezione [mysqld], dove in genere troverai solo le prime due impostazioni nella tabella seguente. Il resto sono altre opzioni usate di frequente (dove indicato, cambieremo il valore predefinito con uno personalizzato a nostra scelta):

Setting and description

Valore predefinito

datadir is the directory where the data files are stored.

datadir=/var/lib/mysql

socket indicates the name and location of the socket file that is used for local client connections. Keep in mind that a socket file is a resource that is utilized to pass information between applications.

socket=/var/lib/mysql/mysql.sock

bind_address is the address where the database server will listen on for TCP/IP connections. If you need your server to listen on more than one IP address, leave out this setting (0.0.0.0 which means it will listen on all IP addresses assigned to this specific host).

We will change this to instruct the service to listen only on its main address (192.168.0.13):

bind_address=192.168.0.13

bind_address=0.0.0.0

port represents the port where the database server will be listening.

We will replace the default value(3306) with 20500 (but we need to make sure nothing else is using that port):
port=20500

While some people will argue that security through obscurity is not good practice, changing the default application ports for higher ones is a rudimentary -yet effective- method to discourage port scans.

porta=3306

innodb_buffer_pool_size is the buffer pool (in bytes) of memory that is allocated for data and indexes that are accessed frequently when using Innodb (which is the default in MariaDB) or XtraDB as storage engine.

We will replace the default value with 256 MB:

innodb_buffer_pool_size=256M

innodb_buffer_pool_size=134217728

skip_name_resolve indicates whether hostnames will be resolved or not on incoming connections. If set to 1, as we will do in this guide, only IP addresses.

Unless you require hostnames to determine permissions, it is advisable to disable this variable (in order to speed up connections and queries) by setting its value to 1:

skip_name_resolve=1

skip_name_resolve=0

query_cache_size represents the size (in bytes) available to the query cache in disk, where the results of SELECT queries are stored for future use when an identical query (to the same database and using the same protocol and same character set) is performed.

You should choose a query cache size that matches your needs based on 1) the number of repetitive queries, and 2) the approximate number of records those repetitive queries are expected to return. We will set this value to 100 MB for the time being:

query_cache_size=100M

query_cache_size=0 (il che significa che è disabilitato per impostazione predefinita)

max_connections is the maximum number of simultaneous client connections to the server. We will set this value to 30:
max_connections=30Each connection will use a thread, and thus will consume memory. Take this fact into account while setting max_connections.

max_connections=151

thread_cache_size indicates the numbers of threads that the server allocates for reuse after a client disconnects and frees thread(s) previously in use. In this situation, it is cheaper (performance-wise) to reuse a thread than instantiating a new one.

Again, this depends on the number of connections you are expecting. We can safely set this value to half the number of max_connections:

thread_cache_size=15

thread_cache_size=0 (disabilitato per impostazione predefinita)

In CentOS, dovremo dire a SELinux di consentire a MariaDB di ascoltare su una porta non standard (20500 ) prima di riavviare il servizio:

yum install policycoreutils-python
semanage port -a -t mysqld_port_t -p tcp 20500

Quindi riavvia il servizio MariaDB.

Ottimizzazione delle prestazioni di MariaDB

Per aiutarci a controllare e ottimizzare la configurazione secondo le nostre esigenze specifiche, possiamo installare mysqltuner (uno script che fornirà suggerimenti per migliorare le prestazioni del nostro server database e aumentarne la stabilità):

wget https://github.com/major/MySQLTuner-perl/tarball/master
tar xzf master

Quindi cambia la directory nella cartella estratta dal tarball (la versione esatta potrebbe differire nel tuo caso):

cd major-MySQLTuner-perl-7dabf27

ed eseguilo (ti verrà richiesto di inserire le credenziali del tuo account amministrativo MariaDB)

./mysqltuner.pl

L’output dello script è di per sé molto interessante, ma passiamo alla parte inferiore dove sono elencate le variabili da regolare con il valore consigliato:

L'impostazione query_cache_type indica se la cache delle query è disabilitata (0) o abilitata (1). In questo caso, mysqltuner ci consiglia di disabilitarlo.

Allora perché ci viene consigliato di disattivarlo adesso? Il motivo è che la cache delle query è utile soprattutto in scenari ad alta lettura/bassa scrittura (che non è il nostro caso, dal momento che abbiamo appena installato il server del database).

ATTENZIONE: prima di apportare modifiche alla configurazione di un server di produzione, ti consigliamo vivamente di consultare un amministratore di database esperto per assicurarti che una raccomandazione fornita da mysqltuner non abbia un impatto negativo su un'impostazione esistente.

Riepilogo

In questo articolo abbiamo spiegato come configurare un server database MariaDB dopo averlo installato e protetto. Le variabili di configurazione elencate nella tabella sopra sono solo alcune delle impostazioni che potresti voler prendere in considerazione durante la preparazione del server per l'uso o durante la messa a punto successiva. Fai sempre riferimento alla documentazione ufficiale di MariaDB prima di apportare modifiche o fai riferimento ai nostri suggerimenti per l'ottimizzazione delle prestazioni di MariaDB:

Da non perdere: 15 suggerimenti utili per la regolazione e l'ottimizzazione delle prestazioni di MariaDB

Come sempre, non esitate a farci sapere se avete domande o commenti su questo articolo. Ci sono altre impostazioni del server che desideri utilizzare? Sentiti libero di condividere con il resto della community utilizzando il modulo di commento qui sotto.