Recherche de site Web

Comment installer, sécuriser et optimiser les performances du serveur de base de données MariaDB


Un serveur de base de données est un composant essentiel de l’infrastructure réseau nécessaire aux applications actuelles. Sans la possibilité de stocker, récupérer, mettre à jour et supprimer des données (si nécessaire), l'utilité et la portée des applications Web et de bureau deviennent très limitées.

De plus, savoir installer, gérer et configurer un serveur de base de données (afin qu'il fonctionne comme prévu) est une compétence essentielle que tout administrateur système doit posséder.

Dans cet article, nous passerons brièvement en revue comment installer et sécuriser un serveur de base de données MariaDB puis nous expliquerons comment le configurer.

Installer et sécuriser un serveur MariaDB

Dans CentOS 7.x, MariaDB a remplacé MySQL, que l'on trouve toujours dans Ubuntu (avec MariaDB). Il en va de même pour openSUSE.

Par souci de concision, nous n'utiliserons que MariaDB dans ce didacticiel, mais veuillez noter qu'en plus d'avoir des noms et des philosophies de développement différents, les deux Systèmes de gestion de bases de données relationnelles (SGBDR pour faire court) sont presque identiques.

Cela signifie que les commandes côté client sont les mêmes sur MySQL et MariaDB, et que les fichiers de configuration sont nommés et situés aux mêmes endroits.

Pour installer MariaDB, faites :

--------------- 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

Notez que, dans Ubuntu, il vous sera demandé de saisir un mot de passe pour l'utilisateur root du SGBDR.

Une fois les packages ci-dessus installés, assurez-vous que le service de base de données est en cours d'exécution et a été activé pour démarrer au démarrage (sous CentOS et openSUSE, vous devrez effectuer cette opération manuellement , alors que dans Ubuntu le processus d'installation s'en chargera déjà pour vous) :

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

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

Exécutez ensuite le script mysql_secure_installation. Cette démarche vous permettra de :

  1. définir/réinitialiser le mot de passe de l'utilisateur root du SGBDR
  2. supprimer les connexions anonymes (permettant ainsi uniquement aux utilisateurs disposant d'un compte valide de se connecter au SGBDR)
  3. désactiver l'accès root pour les machines autres que localhost
  4. supprimer la base de données de test (à laquelle tout le monde peut accéder)
  5. activer les changements associés à 1 à 4.

Pour une description plus détaillée de ce processus, vous pouvez vous référer à la section Post-installation dans Installer la base de données MariaDB dans RHEL/CentOS/Fedora et Debian/Ubuntu.

Configuration du serveur MariaDB

Les options de configuration par défaut sont lues à partir des fichiers suivants dans l'ordre indiqué : /etc/mysql/my.cnf, /etc/my.cnf et ~ /.my.cnf.

Le plus souvent, seul /etc/my.cnf existe. C'est sur ce fichier que nous définirons les paramètres à l'échelle du serveur (qui peuvent être remplacés par les mêmes paramètres dans ~/.my.cnf pour chaque utilisateur).

La première chose que nous devons noter à propos de my.cnf est que les paramètres sont organisés en catégories (ou groupes) où chaque nom de catégorie est entouré de crochets.

Les configurations du système serveur sont données dans la section [mysqld], où vous ne trouverez généralement que les deux premiers paramètres dans le tableau ci-dessous. Les autres options sont d'autres options fréquemment utilisées (là où cela est indiqué, nous modifierons la valeur par défaut par une valeur personnalisée de notre choix) :

Setting and description

Valeur par défaut

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.

port=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 (ce qui signifie qu'il est désactivé par défaut)

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 (désactivé par défaut)

Dans CentOS, nous devrons dire à SELinux d'autoriser MariaDB à écouter sur un port non standard (20500 ) avant de redémarrer le service :

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

Redémarrez ensuite le service MariaDB.

Optimisation des performances de MariaDB

Pour nous aider à vérifier et ajuster la configuration selon nos besoins spécifiques, nous pouvons installer mysqltuner (un script qui fournira des suggestions pour améliorer les performances de notre serveur de base de données et augmenter sa stabilité) :

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

Changez ensuite de répertoire dans le dossier extrait de l'archive tar (la version exacte peut différer dans votre cas) :

cd major-MySQLTuner-perl-7dabf27

et exécutez-le (vous serez invité à saisir les informations d'identification de votre compte administratif MariaDB)

./mysqltuner.pl

Le résultat du script est en soi très intéressant, mais passons au bas où les variables à ajuster sont répertoriées avec la valeur recommandée :

Le paramètre query_cache_type indique si le cache de requêtes est désactivé (0) ou activé (1). Dans ce cas, mysqltuner nous conseille de le désactiver.

Alors pourquoi nous conseille-t-on de le désactiver maintenant ? La raison en est que le cache de requêtes est principalement utile dans les scénarios de lecture élevée/d'écriture faible (ce qui n'est pas notre cas, puisque nous venons d'installer le serveur de base de données).

AVERTISSEMENT : Avant d'apporter des modifications à la configuration d'un serveur de production, vous êtes fortement encouragé à consulter un administrateur de base de données expert pour vous assurer qu'une recommandation donnée par mysqltuner n'aura pas d'impact négatif sur un paramètre existant.

Résumé

Dans cet article, nous avons expliqué comment configurer un serveur de base de données MariaDB après l'avoir installé et sécurisé. Les variables de configuration répertoriées dans le tableau ci-dessus ne représentent que quelques paramètres que vous souhaiterez peut-être prendre en compte lors de la préparation du serveur à l'utilisation ou lors de son réglage ultérieur. Référez-vous toujours à la documentation officielle de MariaDB avant d'apporter des modifications ou reportez-vous à nos conseils de réglage des performances de MariaDB :

Ne manquez pas : 15 conseils utiles de réglage et d'optimisation des performances de MariaDB

Comme toujours, n'hésitez pas à nous faire savoir si vous avez des questions ou des commentaires sur cet article. Y a-t-il d'autres paramètres de serveur que vous aimeriez utiliser ? N'hésitez pas à partager avec le reste de la communauté en utilisant le formulaire de commentaires ci-dessous.