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:

for P in $(mysql -e "show processlist"|awk '$6 > 180{print $1}'); do
mysql -e "kill $P;";
done

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

struct index_a {
 id_1 int,
 id_2 int,
};

struct index_b {
 id_1 int,
};

struct index_c {
 id_2 int,
};

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

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

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

Pour finir, _indexa filtrant tout d'abord sur _id1 avant de filtrer sur _id2, il sait donc filtrer uniquement sur _id1 et donc _indexb est inutile (il prend des ressources disque et mémoire inutiles). Supprimez donc _indexb 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:

Access denied for user 'read_user'@'172.1.7.125'

A première vue, il semblerait que notre utilisateur mysql applicatif n'aie pas accès à la base de données avec son mot de passe habituel. Pourtant lorsque l'application requête une table InnoDB sur la même base cela fonctionne. Regardons maintenant les droits de notre utilisateur:

GRANT USAGE ON *.* TO 'read_user'@'172.1.7.125' IDENTIFIED BY PASSWORD '*32F1789A74AA440B2FC136AF791B5C2B8BC7DE2F'
GRANT ALL PRIVILEGES ON `financialdb`.* TO 'read_user'@'172.1.7.125'

D'après ces droits, notre utilisateur a bien le droit de se connecter et possède les droits sur la base financialdb (notre base importée). Si on teste rapidement via un prompt mysql

mysql -u read_user -h localhost -p financialdb
select count(*) from history_records;
+----------+
| count(*) |
+----------+
| 11332692 |
+----------+

select count(*) from view_history_records_filtered;
Access denied for user 'read_user'@'172.1.7.125'

Etrange, nous sommes connectés mais impossible de lire la vue.

show create table view_history_records_filtered
| view_history_records_filtered | CREATE ALGORITHM=UNDEFINED DEFINER=`gildas`@`localhost` SQL SECURITY DEFINER VIEW `view_history_records_filtered` AS select `id` from view_history_records WHERE `p`='134' |

La vue appartient à l'utilisateur 'gildas'@'localhost', il ne s'agit pas de notre utilisateur applicatif. Verifions les droits de cet utilisateur:

show grants for 'gildas'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'gildas' on host 'localhost'

Notre utilisateur n'existe pas ou plus, c'est là l'erreur. On recréée la vue avec un utilisateur qui existe, le nôtre par exemple, et on retente la requête

select count(*) from history_records;
+----------+
| count(*) |
+----------+
| 715      |
+----------+

Cela fonctionne ! MySQL parle de droits pour votre utilisateur, alors qu'en fait c'est le definer de la vue qui n'existe pas, le message d'erreur est obscur et dans les logs nous n'avons pas d'entrée évoquant ce problème. Ceci s'explique par le fait que l'utilisateur définissant la vue est une sorte d'utilisateur proxy lisant les données (donc attention à ne pas mettre root !)

[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.Voici le schéma d'architecture de la réplication des données

Schéma infra MySQL nominaleAu niveau composants logiciels, la solution utilise:

  • HAST (ou DRBD pour les Linuxiens) en mode Primary/Secondary en réplication sécurisée (synchronisation des deux noeuds obligatoire)
  • Heartbeat pour la gestion de bascule du service MySQL maître, de la réplication de disques et de l'IP virtuelle
  • MySQL 5.6 pour ses GTID, permettant d'avoir une réplication MySQL master/slave beaucoup plus fiable dans le cas d'une panne avec retour de maître (là où les versions précédentes nécessitaient des interventions afin de reprendre la réplication sur les slaves

Au niveau hardware il vous faudra:

  • Pour les maîtres: deux serveurs physiques ou virtuels avec 2 disques (ou 1 disque avec une partition dédiée à la réplication HAST/DRBD) ayant chacun deux cartes réseau.
  • Pour les esclaves: des serveurs physiques, virtuels ou conteneurs

Topologie

Afin de bien se repérer, voici la topologie réseau de nos serveurs

  • 192.168.150.1: serveur MySQL maître primaire
  • 192.168.150.2: serveur MySQL maître secondaire
  • 192.168.150.99: adresse IP partagée par les deux maîtres
  • 192.168.150.21: serveur MySQL esclave
  • 10.255.255.1: serveur MySQL maître primaire (câble croisé dédié entre les deux noeuds)
  • 10.255.255.2: serveur MySQL maître secondaire (câble croisé dédié entre les deux noeuds)

Schéma infra MySQL - Network

Configuration des maîtres

Réplication HAST

Dans un premier temps nous allons configurer la réplication de disques HAST. Créez le fichier hast.conf suivant sur chacun de vos serveurs MySQL maîtres:

resource mymasters {
    checksum sha256

    on master1 {
        local /dev/da1
        remote 10.255.255.2
    }

    on master2 {
        local /dev/da1
        remote 10.255.255.1
    }
}

Dans un premier temps on définit une ressource HAST nommée mymasters. Celle-ci contient deux noeuds: master1 et master2, noms d'hôtes respectifs de chaque machine. On définit également l'utilisation d'un checksum en sha256 afin de vérifier l'intégrité des blocs.

Pour chaque hôte, on définit le disque local à répliquer et l'adresse IP du noeud sur lequel répliquer.

On active ensuite le service hastd et on le lance

sysrc hastd_enable=YES
service hastd start

Lors du démarrage du service hast les volumes sont en état init. Cela peut se vérifier avec la commande hastctl status.

Name    Status     Role        Components
mymasters    -        init           /dev/da1    10.255.255.2

Tapez ensuite la commande suivante afin d'initialiser les disques pour HAST sur chaque serveur:

hastctl create mymasters

Il convient alors de définir un noeud primaire maître et un noeud secondaire. Sur le primaire entrez la commande suivante:

hastctl role primary mymasters

Et sur le secondaire:

hastctl role secondary mymasters

Le volume va se retrouver dans l'état suivant:

Name    Status     Role        Components
mymasters    complete primary        /dev/da1    10.255.255.2

Il faut maintenant laisser le temps aux deux volumes de se synchroniser. Vérifiez le champ dirty sur les deux noeuds afin de vérifier l'état d'avancement. Le volume HAST ne sera pas présenté sur le secondaire lors de son passante en primaire si le champ dirty contient des données à synchroniser lors de la synchronisation initiale.

Configuration de Heartbeat

Sur les deux serveurs maîtres installer tout d'abord le port sysutils/heartbeat (ou le paquet heartbeat). Les options de compilation par défaut sont suffisantes.

Dans un premier temps créez le fichier /usr/local/etc/ha.d/ha.cf, contenant la configuration globale de Heartbeat, suivant:

logfile /var/log/ha.log
logfacility local0
node DB1 DB2

auto_failback off
keepalive 1
deadtime 3
initdead 10
bcast bce1
udpport 694

deadping 3
ping 192.168.150.250
respawn hacluster /usr/local/lib/heartbeat/ipfail
apiauth ipfail gid=haclient uid=hacluster

On applique ici la stratégie suivante:

  • Les serveurs se contactent en broadcast sur l'interface réseau bce1, sur le port 694 (bcast et udpport). Ils s'annoncent toutes les secondes (keepalive).
  • Si au bout de 3 secondes le noeud primaire ne répond plus le noeud secondaire prend le relai (deadtime)
  • Lors du démarrage du service, si le noeud ne retrouve pas de noeud primaire, il le devient au bout de 10 secondes
  • Afin de gérer le cas de la panne de l'interface réseau MySQL, on vérifie la disponibilité de la route par défaut (ping). Si au bout de 3 secondes elles n'est pas disponible (deadping) le noeud primaire va demander au noeud secondaire si il arrive à la joindre. Si ce dernier arrive à la joindre, une bascule sera effectuée. (respawn, apiauth ipfail)

Il faut maintenant configurer la stratégie de noeud heartbeat. Créez le fichier /usr/local/etc/ha.cf/haresources et insérez le contenu suivant

DB1 hastdev::mymasters 192.168.150.99 MailTo::admin@example.org

On déclare ici le noeud primaire par défaut DB1 qui devra lancer les ressources suivantes dans l'ordre:

  • Application de la stratégie hastdev pour notre volume HAST mymasters, ajout de l'adresse IP 192.168.150.99 à la machine et enfin notification de l'administrateur à l'adresse admin@example.org

Lors de la bascule (bascule forcée proprement), les tâches s'exécuteront dans l'ordre inverse.

Note: le noeud primaire ne reprendra pas la main s'il y a déjà un maître présent (directive initdead)

Puis on créée la clef d'authentification heartbeat entre les noeuds dans le fichier /usr/local/etc/ha.cf/authkeys

auth 1
1 sha1 mysqld-ha

Spécifiez ensuite les droits 600 sur le fichier, auquel cas heartbeat refusera de fonctionner:

chmod 600  /usr/local/etc/ha.cf/authkeys

Créez ensuite le fichier de ressource /usr/local/etc/ha.d/resources.d/hastdev avec le contenu suivant puis attribuez lui les droits d'écriture (chmod +x)

#!/bin/sh
#
#
# License:      BSD
#    usage: $0  {start|stop}
#

usage() {
    echo "usage: $0 $LEGAL_ACTIONS"
    exit 1
}

start() {
    hastctl role primary $1
    while [ ! -e /dev/hast/$1 ];
        do
                sleep 1;
        done
    fsck_ufs -y /dev/hast/$1
    mount -o sync,noatime /dev/hast/$1 /mnt/$1
    if [ $? -ne 0 ]; then
        echo "Failed to mount /dev/hast/$1 (code $?)"
        return 1;
    fi
    service mysql-server onestart
    return 0
}

stop() {
    service mysql-server onestop
    umount /dev/hast/$1
    sleep 1;
    hastctl role secondary $1
    return 0
}

case $2 in
    start)
        start $1
        ;;
    stop)
        stop $1
        ;;
    *)
        usage
        exit 1
        ;;
esac

exit $?

Ce script heartbeat va se charger de basculer et monter le volume HAST précédemment créé et de démarrer le service MySQL. Il faut noter que le volume HAST est monté avec l'option sync garantissant l'écriture sur le disque, permettant de ne pas perdre de données en cas de bascule suite à une panne.

Note: si vous souhaitez l'utiliser sous Linux, remplacez onestart/onestop par start et stop et désactivez le service au niveau système.

Configuration de MySQL

La couche basse de notre cluster de maîtres MySQL étant maintenant rodée, il nous faut installer et configurer MySQL. Nous aurons ici besoin de MySQL 5.6 ou supérieur puisque nous allons utiliser les GTID. Installez le depuis les packages ou les ports (database/mysql56-server), suivant votre préférence, sur les deux serveurs maîtres.

pkg install mysql56-server

Créez le répertoire /mnt/mymasters et configurez MySQL au niveau du système afin qu'il utilise ce répertoire, correspondant au point de montage HAST

mkdir -p /mnt/mymasters
sysrc mysql_dbdir="/mnt/mymasters"

Créez ensuite le fichier /mnt/mymasters/my.cnf, contenant la configuration du serveur MySQL suivante:

[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

server-id    = 1

log-bin=mysql-bin
binlog_format=mixed
sync_binlog=1

gtid_mode = ON
log-slave-updates = ON
enforce-gtid-consistency = true
innodb_flush_log_at_trx_commit = 1

Nous ne nous attarderons pas sur le premier paragraphe de la configuration.

Pour la réplication, nous avons besoin de définir un server-id unique, notre maître redondé sera d'ID 1. On active ensuite les logs binaires du serveur maître afin que les esclaves puissent les lire.

  • log-bin: préfixe du nom des logs binaires sur le disque
  • binlog_format: le type de logs enregistrées. On choisi ici mixed pour plus de sécurité
  • log-slave-updates: active les journaux sur les slaves. Même si cela peut sembler inutile ici, c'est obligatoire pour activer la réplication avec des GTID

Afin de garantir la cohérence des enregistrements sur le disque et éviter une perte de données, en cas de bascule, on va demander à la base MySQL de lancer des fsync sur le disque à chaque écriture et de flusher les logs InnoDB à chaque transaction. Cela rend la base plus lente en écriture mais assure la cohérence des données.

  • sync_binlog: active les fsync à chaque écriture
  • innodb_flush_log_at_trx_commit: on flush les logs à chaque transaction InnoDB

Enfin on active les GTID:

  • gtid_mode: active la réplication utilisant des GTID plutôt que des positions dans les fichiers de logs
  • enforce-gtid-consistency: assure la cohérence des GTID, évitant des soucis de conflits de GTID en cas de panne

Lancez maintenant le service MySQL grâce à Heartbeat sur le noeud maître.

service heartbeat start

Une fois la base MySQL démarrée, lancez heartbeat sur le second noeud.

Connectez vous maintenant à la base SQL sur le noeud actif et créez un utilisateur de réplication:

CREATE USER 'repl'@'192.168.150.%' IDENTIFIED BY 'mypwd';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.150.%' IDENTIFIED BY 'mypwd';

Vos noeuds maîtres sont désormais prêts.

Configuration des noeuds esclaves

La configuration de nos noeuds esclaves sera relativement similaire aux noeuds maîtres

Installez tout d'abord le paquet/port mysql56-server

pkg install mysql56-server

Créez ensuite le fichier /var/db/mysql/my.cnf avec la section mysqld suivante:

[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

server-id    = 13

log-bin=mysql-bin
slave-net-timeout=5
gtid_mode = ON
log-slave-updates = ON
enforce-gtid-consistency = true
sync_master_info=1
master_info_repository=TABLE
sync_relay_log_info=1
relay_log_info_repository=TABLE

Je ne détaillerai pas ici les options précédémment définies. Interessons nous aux options qui diffèrent avec le maître:

  • server-id: l'identifiant unique de serveur
  • slave-net-timeout: on définit un timeout de connexion à 5 secondes. Par défaut c'est 86400, une valeur très mal positionnée.
  • master_info_repository: on stocke les informations de connexion au maître en base de données. Cela permet de sécuriser l'écriture de ces informations grâce aux logs binaires.
  • relay_log_info_repository: on stocke les informations concernant la position dans les logs binaire de relai en base de données. Cela permet de sécuriser l'écriture de ces informations grâce aux logs binaires.
  • syncmaster**info**: on force le flush de l'écriture du master.info sur le disque
  • sync_relay_log_info: on force le flush de l'écriture du relay-log.info sur le disque

Cette configuration permet d'obtenir un esclave crash-safe. Activez et lancez le service:

sysrc mysql_enable=YES
service mysql-server start

Connectez vous ensuite à la base de données et activez la réplication par GTID:

CHANGE MASTER TO
MASTER_HOST='192.168.150.99',
MASTER_USER='repl',
MASTER_PASSWORD='mypwd',
MASTER_AUTO_POSITION=1;

Enfin activez le processus slave:

START SLAVE;

Votre esclave est désormais opérationnel. Si vous souhaitez suivre l'état de la réplication, tapez

SHOW SLAVE STATUS;

Note: si vous activez la réplication sur une base maître déjà existante, n'oubliez pas de faire les étapes suivantes avant d'activer le slave:

  1. Verrouillage des tables en écriture sur le maître
  2. Dump de la base maître en SQL (mysqldump ... > dump.sql)
  3. Injection de la base maître sur le slave (mysql < dump.sql)
  4. Activation de la réplication côté esclave (START SLAVE)
  5. Déverrouillage de l'écriture sur le maître

Répétez les opérations précédentes sur chacun des esclaves que vous souhaitez créer (n'oubliez pas de changer le server-id).

Tester la panne du noeud maître

Vous avez plusieurs moyens de basculer le noeud heartbeat:

  • Débranchez l'alimentation du noeud primaire
  • Débranchez le câble réseau DATA (pas celui de synchronisation !!)
  • Tapez la commande /usr/local/lib/heartbeat/hb_standy sur le noeud actif
  • Tapez la commande /usr/local/lib/heartbeat/hb_takeover sur le noeud inactif

Vérifiez ensuite l'état de la bascule en regardant le fichier /var/log/ha.log, et en tapant les commandes hastctl status et ps ax|grep mysql

Vérifiez également l'état de la réplication sur les esclaves au moyen de la commande MySQL SHOW SLAVE STATUS;

Tester la panne d'un noeud esclave

Vous avez plusieurs moyen de tester le noeud esclave:

  • Débranchez l'alimentation du noeud esclave
  • Redémarrez le service MySQL
  • Tuez (kill -9) et démarrez le service MySQL

Conclusion

Ce projet complet basé sur FreeBSD 9.3 et MySQL 5.6 vous permet d'appréhender et construire une architecture MySQL robuste et résiliente, utilisant des mécaniques de cluster lecture/écriture avec de multiples noeuds, au moyen d'une technique de réplication plus moderne (les GTIDs) et robuste que le curseur de logs binaires, tels qu'on le retrouve dans un cluster Galera.

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.