Archive

Articles taggués ‘MySQL’

Sécurisez votre serveur MySQL sous Unix

23/03/2023 Aucun commentaire

I. Introduction

Les bases de données (notamment MySQL) sont des éléments primordiaux dans un système d’informations. Elles sont incontournables dans de nombreuses entreprises et les données qu’elles contiennent sont souvent d’une importance cruciale. Ignorer la sécurité du système de gestion de base de données serait une erreur grave tant les conséquences d’une détérioration de la base pourraient s’avérer désastreuses.

Ce document explique quelques moyens simples d’améliorer et maintenir la sécurité de MySQL sous système Unix (Linux, BSD…). Il est bien sûr impensable de croire que les conseils de cet article suffiront à obtenir une sécurité optimale.

À l’instar de la sécurisation du SGBD, il faudra veiller à sécuriser le système d’exploitation de la machine hôte, les autres services actifs (serveurs HTTP, FTP…), le réseau, l’accès physique à cette machine et bien sûr vos applications (éviter les failles d’injection SQL).

II. Exécution du serveur : utilisateur système non privilégié

Après installation du serveur MySQL, beaucoup de personnes ont la fâcheuse habitude de lancer le daemon avec les privilèges de l’utilisateur système root. Ce choix peut s’expliquer par une envie d’utiliser la méthode la plus rapide : le serveur MySQL doit lire et écrire dans certains fichiers auxquels l’utilisateur régulier n’a pas accès ; en le lançant avec les privilèges root, on a l’assurance que MySQL aura les droits nécessaires.

Cette méthode d’exécution parait anodine. Cependant, elle peut conduire à des dégâts énormes. Le processus du daemon MySQL tournant avec les privilèges super-utilisateur, il a tous les droits sur le système. Imaginons maintenant qu’un pirate puisse prendre contrôle de ce processus (généralement par exploitation d’une faille de type buffer overflow dans MySQL), il deviendra dès lors le maître absolu de la machine hôte du serveur. Il pourra créer des fichiers, installer des programmes, lire des données confidentielles, ou supprimer des données.

Bien sûr, si une faille est présente dans MySQL, cela n’est pas la faute de l’administrateur. Mais ce n’est pas une raison pour ne pas s’en protéger. Il est impossible d’empêcher un pirate d’exploiter une telle faille. Les seuls à pouvoir le faire sont les développeurs de MySQL AB, qui, dans une telle situation, proposeront une nouvelle version du serveur.

De son côté, l’administrateur du serveur peut limiter les dégâts afin que le pirate ayant pris le contrôle du processus MySQL ne puisse pas détruire autre chose que la base de données (ce qui n’est déjà pas mal !). Le principe est assez simple : créer un utilisateur spécial dont la seule fonction sera de lancer MySQL. Cet utilisateur doit posséder le moins de droits système possible. La solution optimale serait que cet utilisateur n’ait accès qu’aux fichiers propres à MySQL.

Les administrateurs qui ont suivi le fichier INSTALL ou la documentation officielle de MySQL auront remarqué que la création d’un utilisateur « mysql » est préconisée.

Pour les autres, nous allons voir comment y remédier. Les commandes suivantes permettent de créer un groupe système mysql ainsi qu’un utilisateur mysql appartenant au groupe précédemment créé.

# groupadd mysql
# useradd -g mysql mysql

Pour des raisons de sécurité, il est préférable de s’assurer que cet utilisateur ne puisse pas se logger et ne possède pas de home. La commande suivante permet de spécifier ces options sous OpenBSD. Les paramètres de la commande useradd n’étant pas les mêmes d’un système à l’autre, référez-vous au manuel (man useradd) pour obtenir une commande équivalente chez vous.

# useradd -d /nonexistent -s /sbin/nologin -g mysql mysql

Sous Linux, vous pouvez spécifier /bin/false en tant que shell de l’utilisateur pour qu’il ne puisse pas se logger.

Maintenant que notre utilisateur est créé, nous allons lui donner accès en lecture/écriture aux répertoires de données (tables, index, enregistrements…) de MySQL. Pour cela, nous allons le désigner comme propriétaire de ces fichiers. Par la même occasion, nous nous assurons que l’utilisateur système root est propriétaire des autres fichiers de MySQL :

# cd REPERTOIRE_MYSQL
# chown -R root .
# chown -R mysql data
# chgrp -R mysql .

La commande précédente suppose que le dossier de données de MySQL se trouve dans le même répertoire que les autres fichiers de MySQL. Dans le cas contraire, adaptez la commande selon votre installation.

Assurons-nous ensuite que seuls l’utilisateur et le groupe mysql puissent avoir accès aux fichiers de données :

# chmod -R 660 data
# chmod 700 data/mysql

Une fois les droits attribués, nous pouvons lancer le serveur MySQL en spécifiant que l’utilisateur mysql que nous venons de créer sera le propriétaire du processus. Pour cela, deux possibilités s’offrent à nous.

La première est de rajouter une option à la commande permettant de lancer le serveur MySQL :

# cd REPERTOIRE_MYSQL
# bin/mysqld_safe --user=mysql &

La seconde consiste à modifier le fichier de configuration de MySQL (ex : /etc/my.cnf) afin de spécifier que l’utilisateur par défaut pour l’exécution du serveur est mysql. Il ne sera dès lors plus nécessaire de le répéter lors du lancement du serveur.

[mysqld]
user= mysql

Vérifions enfin que notre serveur MySQL tourne avec des droits limités. Pour cela, nous allons utiliser la commande ps après lancement de MySQL.

ps -aux | grep 'mysql'

La première colonne du résultat de la commande indique le propriétaire du processus. Si votre configuration est correcte, vous devriez donc voir mysql.

Lire la suite…

MySQL: Améliorer les performances grâce au partitionnement

22/03/2023 Aucun commentaire

mysql partitionnementI. Introduction

I-A. Définition

Pour tout bon informaticien, le terme « partitionnement » n’est pas inconnu. Cependant, bien que le partitionnement d’un disque dur soit devenu une banalité, l’utilisation du partitionnement en base de données reste encore rare.

Faisons tout d’abord un rappel sur le partitionnement d’un disque dur. L’intérêt de créer différentes partitions est de pouvoir organiser les données : des partitions sont réservées aux fichiers des systèmes d’exploitation installés et d’autres pour les données personnelles (photos, téléchargements…).

Dans cet article, nous allons nous intéresser au partitionnement dans le domaine des bases de données, et plus exactement au partitionnement de tables. Le but est, comme pour les disques durs, d’organiser les données. Néanmoins, nous n’allons pas utiliser cette organisation pour simplifier nos requêtes, mais bien pour en améliorer les performances !

I-B. Les types de partitionnement

Deux grands types de partitionnement sont utilisés pour partitionner une table :

  • partitionnement horizontal : les enregistrements (= lignes) d’une table sont répartis dans plusieurs partitions. Il est nécessaire de définir une condition de partitionnement, qui servira de règle pour déterminer dans quelle partition ira chaque enregistrement.

Exemple : nous disposons d’une table Amis et nous choisissons de la partitionner en

deux partitions :

  1. Les amis dont la première lettre du prénom est comprise entre A et M,
  2. Les amis dont la première lettre du prénom est comprise entre N et Z.
Image non disponible

Pour récupérer la liste complète de nos amis, il sera nécessaire de regrouper le contenu de nos deux partitions. Pour ce faire, une simple opération d’union d’ensembles suffit ;

  • partitionnement vertical : les colonnes d’une table sont réparties dans plusieurs partitions. Cela peut être pratique pour écarter des données fréquemment utilisées d’autres auxquelles l’accès est plus rare.

Exemple : nous disposons d’une table Amis contenant les prénom et photo de chacun de nos amis. Les photos prenant de la place et étant rarement accédées, nous décidons de les écarter des autres données.

Image non disponible

Comme vous pouvez le remarquer, les deux partitions contiennent l’identifiant des amis. Cela est nécessaire afin de garder le lien entre les données de chaque enregistrement. Ainsi, pour récupérer toutes les informations des amis, il suffit de faire une jointure entre les deux partitions.

Nous connaissons maintenant les deux types de partitionnement. Il est bien sûr possible d’utiliser un partitionnement vertical et un partitionnement horizontal sur une même table, ainsi que de partitionner sur plusieurs niveaux, c’est-à-dire définir des partitions de partitions.

I-C. Les avantages du partitionnement

Le partitionnement apporte plusieurs avantages à un administrateur de base de données. Voici les principaux intérêts du partitionnement :

  • pouvoir créer des tables plus grandes que la taille permise par un disque dur ou par une partition du système de fichiers : il est tout à fait possible de stocker des partitions à des endroits (partitions, disques, serveurs…) différents ;
  • pouvoir supprimer très rapidement des données qui ne sont plus utiles et utilisées : si ces données sont placées sur une partition séparée, il suffit de détruire la partition pour supprimer toutes les données ;
  • optimiser grandement certaines requêtes : les données étant organisées dans différentes partitions, le SGBD n’accède qu’aux données nécessaires lors des requêtes. Sans partitionnement, tous les enregistrements sont pris en compte.

Enfin, notons que le partitionnement de tables est généralement utilisé avec des bases de données réparties où les données sont placées sur des sites géographiques (éloignés ou proches) différents. Cet article se limite au partitionnement sur site local, mais les principes évoqués sont valides pour le partitionnement distant.

I-D. Notes à propos de MySQL

Dans MySQL, le partitionnement n’est géré de manière automatique qu’à partir de la version 5.1. Lors de la rédaction de cet article, MySQL 5.1 est encore en version bêta et par conséquent, il est possible que des bogues subsistent.

De plus, il est important de noter que seules les fonctionnalités de base du partitionnement seront disponibles dans la version finale de MySQL 5.1. Mais il est évident que les versions futures du SGBD apporteront leur lot d’améliorations.

Lire la suite…

Ten MySQL performance tuning settings after installation

20/03/2023 Aucun commentaire

mysql performance tuningIn this blog we’re going to discuss the top ten MySQL performance tuning settings that you can implement after an installation.

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest changing a few MySQL performance tuning settings after installation – even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.
  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.
  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)
  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless.
  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic settings fro MySQL performance

Here are 3 MySQL performance tuning settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

Lire la suite…

How to Optimize MySQL Tables and Defragment to Recover Space

19/03/2023 Aucun commentaire

MySQL OPTIMIZE TABLE command

If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.

This will result in lot of unused space, and also might affect performance.

So, it is highly recommended that you defrag your MySQL tables on an ongoing basis.

This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space.

1. Identify Tables for Optimization

The first step is to identify whether you have fragmentation on your MySQL database.

Connect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.

mysql> use thegeekstuff;

mysql> select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
 from information_schema.tables 
 where round(data_free/1024/1024) > 500 
 order by data_free_mb;

+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           7743 |         4775 |
| DEPARTMENT |          14295 |        13315 |
| EMPLOYEE   |          21633 |        19834 |
+------------+----------------+--------------+

In the above output:

  • This will display list of all tables that has minimum of 500MB of unused space. As we see above, in this example, there are 3 tables that has more than 500MB of unused space.
  • data_length_mb column displays the total table size in MB. For example, EMPLOYEE table size is around 21GB.
  • data_free_mb column displays the total unused space in that particular table. For example, EMPLOYEE table has around 19GB of unused space in it.
  • All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.

From the filesystem level, you can see the size of the individual table files as shown below.

The file size will be the same as what you see under “data_length_mb” column in the above output.

# ls -lh /var/lib/mysql/thegeekstuff/
..
-rw-rw----. 1 mysql mysql  7.6G Apr 23 10:55 BENEFITS.MYD
-rw-rw----. 1 mysql mysql   14G Apr 23 12:53 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD
..

In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. If we optimize this table, the size of this file should go down dramatically.

Lire la suite…

How to enable SSL for MySQL server and client with ssh

17/03/2023 Aucun commentaire

MySQL secure SSH

When users want to have a secure connection to their MySQL server, they often rely on VPN or SSH tunnels. Yet another option for securing MySQL connections is to enable SSL wrapper on an MySQL server. Each of these approaches has its own pros and cons. For example, in highly dynamic environments where a lot of short-lived MySQL connections occur, VPN or SSH tunnels may be a better choice than SSL as the latter involves expensive per-connection SSL handshake computation. On the other hand, for those applications with relatively few long-running MySQL connections, SSL based encryption can be reasonable. Since MySQL server already comes with built-in SSL support, you do not need to implement a separate security layer like VPN or SSH tunnel, which has their own maintenance overhead.

The implementation of SSL in an MySQL server encrypts all data going back and forth between a server and a client, thereby preventing potential eavesdropping or data sniffing in wide area networks or within data centers. In addition, SSL also provides identify verification by means of SSL certificates, which can protect users against possible phishing attacks.

In this article, we will show you how to enable SSL on MySQL server. Note that the same procedure is also applicable to MariaDB server.

Creating Server SSL Certificate and Private Key

We have to create an SSL certificate and private key for an MySQL server, which will be used when connecting to the server over SSL.

First, create a temporary working directory where we will keep the key and certificate files.

$ sudo mkdir ~/cert
$ cd ~/cert

Make sure that OpenSSL is installed on your system where an MySQL server is running. Normally all Linux distributions have OpenSSL installed by default. To check if OpenSSL is installed, use the following command.

$ openssl version
OpenSSL 1.0.1f 6 Jan 2014

Now go ahead and create the CA private key and certificate. The following commands will create ca-key.pem and ca-cert.pem.

$ openssl genrsa 2048 > ca-key.pem
$ openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

The second command will ask you several questions. It does not matter what you put in these field. Just fill out those fields.

The next step is to create a private key for the server.

$ openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem

This command will ask several questions again, and you can put the same answers which you have provided in the previous step.

Next, export the server’s private key to RSA-type key with this command below.

$ openssl rsa -in server-key.pem -out server-key.pem

Finally, generate a server certificate using the CA certificate.

$ openssl x509 -sha1 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Lire la suite…