MySQL: tuer les requêtes trop longues

MySQL permet d’encaisser énormement de requêtes, mais dans certains cas, généralement des soucis externes (performances réseau/disque), il se peut que votre MySQL ait encaissé beaucoup de requêtes clientes qui ne soient plus légitimes et génèrent une forte charge sur votre service.

Je vous propose ci-dessous un petit script tout simple permettant de tuer toutes les requêtes supérieures à 180 secondes:

Optimisations BDD: indexes en doublon

Les indexes prennent de l’espace disque et de la mémoire. Les moteurs MySQL/MariaDB/PostgreSQL savent utiliser des indexes plus complexes afin de lire des données nécessitant un seul index donc les champs conditionnant sont inclus dans un index plus complexe.

Exemple ici avec 3 indexes

Si nous faisons une requête filtrant sur le champ id_1 uniquement (SELECT field1 FROM table1 where id_1 = 3), le moteur de BDD est capable d’utiliser 2 indexes: index_b, qui ne contient que id_1 et index_a qui contient id_1 puis id_2.

En revanche, si nous faisons une requête filtrant sur le champ id_2 uniquement (SELECT field1 FROM table1 where id_2 = 6), le moteur de BDD ne pourra utiliser que index_c.

Si nous reprenons index_a, il n’est pas valide car le moteur de BDD lira cet index en trouvant les records discriminants sur id_1 avant les records discriminant sur id_2. Ce n’est pas un chemin optimal pour trouver un résultat dépendant uniquement de id_2. L’ordre des champs d’un index est donc important.

Pour finir, index_a filtrant tout d’abord sur id_1 avant de filtrer sur id_2, il sait donc filtrer uniquement sur id_1 et donc index_b est inutile (il prend des ressources disque et mémoire inutiles). Supprimez donc index_b qui prend de l’espace disque et mémoire pour rien.

Régler un problème de lecture sur une vue MySQL/MariaDB

Suite à un peu de troubleshoot ces derniers jours je suis tombé sur un problème MySQL difficile à comprendre lors d’un requêtage sur mes vues lors de la création d’une instance dupliquée d’une base de production destinée à des tests de charge.

L’erreur lors du requêtage sur la vue avec un utilisateur non privilégiée était la suivante:

Continuer à lire

[Serveur Dédié] Nginx – Owncloud

Introduction

Nous revoilà avec un nouvel article consacré à l’auto-hébergement via un serveur dédié.

Dans cette article nous allons voir plus en détail le déploiement du serveur web Nginx avec sa configuration, nous finirons par l’installation d’Owncloud. Nous utilisons dans cet article un serveur FREEBSD 9.2.

http://nginx.org/nginx.gif 

Owncloud est un logiciel open-source offrant des services de stockages, partages de fichiers et applications diverses comme un service Sync pour navigateur Firefox.Owncloud est une solution à la fois public et privé de part ces possibilités d’intégrations dans un annuaire LDAP ou Active Directory. Continuer à lire

Cluster de bases de données MySQL avec HAST/DRBD et Heartbeat

Suite à la réalisation d’un Proof-of-Concept (PoC) en entreprise sur une infrastructure MySQL en mode cluster (master résilient, multi slave), je vais vous présenter ici l’infrastructure retenue et sa configuration.

Notre configuration se base sur deux serveurs pouvant faire office de maître et un pool de slaves dont le nombre n’est pas fixe. Cette configuration est plutôt réservée pour des applicatifs supportant la gestion de serveurs d’écriture et de serveurs de lecture. Néanmoins vous pouvez exclure la partie esclave si seul un maître redondant vous convient.

Pour la partie maître, l’un des serveur est actif, l’autre en stand-by. Si le serveur actif tombe, l’autre serveur prend le relai et dessert le service MySQL maître.

Pour la partie eslave, ceux-ci répliquent leurs données depuis le maître actif en se basant sur une adresse IP virtuelle. Continuer à lire

MySQL – Réplication

Introduction

MySQL est un serveur de bases de données. Comme tout serveur de base de données, les données contenues à l'intérieur sont extrêmement importantes voire parfois vitales. Après vous avoir enseigné les quelques sécurités MySQL je vais désormais vous apprendre à manipuler la réplication MySQL, un mécanisme complexe d'utilisation mais permettant de redéployer rapidement une base de données plantée, de sauvegarder ses données ou encore de gérer du Load Balancing sur des données pratiquement statiques.

Avant de plonger dans le vif du sujet, je vais vous expliquer comment fonctionne la réplication.

Principe du mécanisme de réplication MySQL

La réplication MySQL se joue au moyen d'un principe de Maître-Esclave. Dans la configuration 5.x de MySQL, il ne peut y avoir qu'un seul maître par esclave, ou alors il faut ruser. Je vais vous expliquer pourquoi.

Les développeurs de MySQL ont choisi de définir le maître comme étant celui sur lequel se trouve la base utilisée et l'esclave comme réplicat.

La réplication s'effectue de la manière suivante :

  • L'esclave se connecte au maître et lui envoie sa version des logs binaires
  • Le maître regarde où il en est et envoie tous les logs binaires manquants
  • L'esclave exécute les logs binaires

Configuration du Master

Afin d'activer la réplication, votre serveur MySQL doit déjà écouter le réseau. Pour se faire, changez l'option bind-address. Si vous avez plusieurs interfaces réseau indiquez l'adresse IP de l'interface d'écoute. Si vous souhaitez mettre l'écoute sur toutes les interfaces tapez

bind-address = 0.0.0.0

Ensuite il faut activer l'identification serveur afin de pouvoir gérer la réplication. Décommentez la ligne server-id et donnez un identifiant unique à votre serveur.

server-id = 1

Activez maintenant les logs binaire, servant à la réplication

log-bin=mysql-bin

Nous allons définir les bases à répliquer ou non. Afin de sélectionner une base à répliquer, décommentez la ligne binlog_do_db. Si vous avez plusieurs bases entrez autant de lignes que de bases.

binlog_do_db = db1
binlog_do_db = db2
binlog_do_db = db3

ensuite nous allons ignorer les bases de données sensibles de MySQL, sur le même schéma que précédemment

binlog_ignore_db = mysql
binlog_ignore_db = information_schema

Voilà qui termine la configuration software. Redémarrez mysql et lancez la console d'administration.

service mysql restart
mysql -p

Nous allons tout d'abord créer un utilisateur de réplication sécurisé et lui donner les droits nécessaires pour répliquer.

CREATE USER 'replic-1-fwd'@'10.16.64.1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON db1.* TO 'replic-1-fwd'@'10.16.64.1' IDENTIFIED BY 'password';

Dans l'exemple précédent, nous avons créé l'utilisateur replic-1-fwd qui possède un mot de passe et pourra se connecter uniquement depuis la machine 10.16.64.1. Nous l'autorisons à répliquer la base db1 et toutes ses tables. Répétez l'action autant de fois qu'il y a de base à répliquer pour ce réplica.

Pour terminer, vérifiez la configuration du Maître en tapant :

SHOW MASTER STATUS;

Une fois ceci fait nous allons maintenant préparer le réplica.

Configuration du Slave

Le réplica nécessite aussi un server-id. Ouvrez cette fois-ci le fichier my.cnf sur le réplica et entrez un server-id non utilisé sur votre réseau. En revanche, a contrario du Master, le réplica n'a pas besoin d'écouter sur le réseau .

server-id = 2

Passons maintenant en console MySQL et configurons le réplica:

replica# mysql -p
Enter your password: ******
mysql> CHANGE MASTER TO
MASTER_HOST='10.16.64.3',
MASTER_USER='replic-1-fwd',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.00001',
MASTER_LOG_POS=105;

Remplacez mysql-bin.00001 par la donnée que vous a renvoyé SHOW MASTER STATUS sur le maître et 105 par la position donnée.

Note: la configuration interactive du slave n'est possible que depuis MySQL 5.5. Il faut la configurer dans le my.cnf pour les versions inférieures

Mise en place de la réplication

Il faut maintenant répliquer la base. Pour ce faire nous avons besoin d'un DUMP complet de celle-ci à l'instant T. Nous allons utiliser l'utilitaire mysqldump, l'envoyer sur le réplica et verrouiller provisoirement la base.

master# mysqldump db1 > db1.sql -p
Enter your password: ******
master# scp db1.sql root@10.16.64.1:/root/
master# mysql -p
Enter your password: ******
mysql> FLUSH TABLES WITH READ LOCK;

Passez maintenant sur le réplica. Nous allons créer et injecter la base à répliquer depuis la console MySQL.

mysql> CREATE DATABASE db1;
mysql> USE db1;
mysql> SOURCE /root/db1.sql

Voilà votre base est identique sur les 2 postes. Nous allons maintenant réactiver le mécanisme de réplication.

Sur le maître :

mysql> UNLOCK TABLES;

Sur l'esclave :

mysql> START SLAVE;
mysql> LOAD DATA FROM MASTER;

Voilà votre réplication SQL est terminée. Vous pouvez tester en insérant une donnée sur le maître, elle devrait être répliquée d'ici 1 minute sur l'esclave.

ATTENTION ! N'insérez JAMAIS de données sur l'esclave, vous risqueriez d'endommager la réplication. Il est possible de positionner un flag sur le slave pour qu'il soit uniquement en read-only.

MySQL

Introduction

MySQL est un des bases de données libres de référence. Au niveau sécurité, elle ne nécessite pas grandes modifications mais néanmoins fortement nécessaires.

Accès distant

Une base de données ne doit JAMAIS être visible depuis Internet. La protéger des attaques est extrêmement difficile si on la laisse visible. Si possible, synchronisez vos bases en rsync et non via un utilitaire qui vous obligerait à ouvrir une connexion réseau SQL en écoute.

Pour bloquer les accès distants à votre base, ouvrez le fichier /etc/mysql/my.cnf et éditez l'attribut bind-address

bind-address = 127.0.0.1

Limiter le nombre de connexions à la base

Il peut être judicieux dans un système d'informations immense de limiter le nombre de connexion simultanées à la base afin d'éviter que celle-ci croule sous le nombre de requêtes qu'elle subit. Utiliser la directive __max_connections__ permettra d'obtenir ce résultat :

max_connections = 1024;

Vous pouvez tout aussi bien utiliser le firewall iptables pour limiter ces transactions et optimiser la bande passante, ceci dit je ne vous le recommande pas.

Création d'utilisateurs spéciaux

Si jamais votre base n'est pas bindée sur localhost mais sur 0.0.0.0 ou une adresse réseau, il faudra avoir des utilisateurs privilégiés sélectifs. Qu'entendons nous par là ? il s'agit d'utilisateur en provenance d'hôtes spécifiques et non autorisés ailleurs.

Lors de la création d'un utilisateur vous avez sûrement tapé la commande suivante:

CREATE USER 'machin' IDENTIFIED BY 'mdp';

Ceci créée un utilisateur machin qui peut être situé sur n'importe quelle machine dans le monde et donc est une faille très importante ! Pour spécifier l'hôte autorisé, précisez le nom DNS ou l'IP de la manière suivante :

CREATE USER 'machin'@'example.org' IDENTIFIED BY 'mdp';

et ainsi seul machin en provenance de la machine example.org pourra accéder à la base.

Autorisations spécifiques

Comme dans tout Système d'Informations, un utilisateur a des droits sur quelque chose mais pas tout, seul le root le peut.

Pour donner des droits à une utilisateurs, il faut utiliser la commande GRANT suivie de la liste des autorisations et de l'utilisateur. Par exemple, pour donner le droit INSERT,DELETE à un utilisateur sur la base de données BDD entière :

GRANT INSERT,DELETE on bdd.* to 'machin'@'example.org';

On peut pousser plus loin, et c'est mieux, en autorisant uniquement sur une table :

GRANT INSERT,DELETE on bdd.table1 to 'machin'@'example.org';

Voire même un champ (pour la méthode UPDATE en revanche) :

GRANT UPDATE on BDD.table1.champ2 to 'machin'@'example.org';

Plus vous pousserez loin vos utilisateurs et plus vous en aurez pour tel ou tel type de transaction spécifique, moins vous aurez de risque d'être corrompu ou altéré lors d'une attaque.

PHPMyAdmin

PHPMyAdmin est un outil d'administration qui permet de simplifier nettement le travail SQL. Je vous déconseille fortement d'utiliser cette outil qui ajoute une faille de sécurité majeure sur vos bases de données.

Si aucun utilisateur classique n'a à avoir ce genre d'accès, ne l'installez pas et faites une formation SQL ou regardez les quelques tutoriaux présent sur notre site afin d'améliorer vos compétences. N'hésitez pas à rajouter un .htaccess en plus de l'authentification utilisateur de PHPMyAdmin afin d'améliorer la sécurité si vous ne souhaitez pas suivre notre conseil.