VerticalSlave : outil de monitoring de votre réplication MySQL

Contexte

Vous disposez de deux serveurs MySQL et un environnement de réplication fonctionnel entre eux.
Votre serveur MASTER est le serveur maître, celui dont les données sont exploitées par diverses applications.
Votre serveur SLAVE est le serveur esclave, hébergeant une réplique des données du maître.

Vous souhaitez surveiller périodiquement le bon état de votre réplication et obtenir un rapport par mail.

Overview

VerticalSlave est un outil léger pour administrer vos bases de données répliquées et non-répliquées.
Ses principales fonctionnalités sont :

• Vérification de la configuration de l'outil
• Vérification de l'état de la réplication
• Vérification de l'état de la réplication avec auto-réparation des tables en erreur
• Réinitialisation de la réplication
• Dump des bases non-répliquées
• Magasin des rapports des actions passées
• Envoi d'un rapport raccourci par mail

Pré-requis

• MySQL 5.5 ou ultérieur
• PHP 5.3 ou ultérieur
• Serveur web Apache et hôte virtuel configuré
• Chaîne de certification SSL
• Environnement de réplication configuré. Les bases répliquées doivent être déclarées avec l'option replicate-do-db
• Connaissances en réplication MySQL

Installation

VerticalSlave doit être exécuté sur le serveur SLAVE.
Si votre serveur dispose de Git, vous pouvez cloner le dépôt officiel à l'emplacement de votre choix :

$ git clone git@bitbucket.org:verticalassertions/verticalslave.git

Sinon, téléchargez l'archive et décompresser la à l'emplacement de votre choix :

$ wget https://bitbucket.org/verticalassertions/verticalslave/get/b6ce2bfd9ab2.zip
$ unzip b6ce2bfd9ab2.zip -d /path/to/destination

Vérifiez les permissions sur les fichiers. En cas de doute appliquez :

$ chmod -R 0755 /path/to/verticalslave

Configuration

You have then to configure your copy :

~/verticalslave $ cp config.default.php config.php

• masterMysqlHost
  IP ou URL du serveur MySQL maître.
• masterMysqlPort (Défaut: 3306)
  Port de connexion MySQL au maître. Dépend la configuration de votre serveur.
• masterMysqlUser
  Utilisateur MySQL pour établir la connexion au maître. Doit posséder les rôles SELECT, RELOAD, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, TRIGGER, SHOW VIEW
• masterMysqlPwd
  Mot de passe de l'utilisateur MySQL.

• masterPdoSettings
  Tableau de paramètres pour le pilote MySQL du gestionnaire PDO lors de la connexion au maître. C'est ici qu'on définit le chemin des fichiers nécessaires à la connexion au serveur MySQL sur SSL.
  A noter que la chaîne de certification peut tout à fait être auto-signée, générée sur le serveur maître. Ceci fera l'objet d'un prochain article.

  • PDO::MYSQL_ATTR_SSL_CA
    Chemin absolu dans le système de fichier de l'esclave vers le fichier d'autorité de certification généré sur le maître et copié sur l'esclave.
  • PDO::MYSQL_ATTR_SSL_KEY
    Chemin absolu dans le système de fichiers de l'esclave vers le fichier de clé privée généré sur le maître.
  • PDO::MYSQL_ATTR_SSL_CERT
    Chemin absolu dans le système de fichiers de l'esclave vers le fichier certificat généré sur le maître.
  • PDO::ATTR_ERRMODE
    Permet à PDO de générer une exception lors d'une erreur MySQL. Laisser à PDO::ERRMODE_EXCEPTION

• verticalslaveDatabase'
  Nom de la base de données stockant sur le maître, les rapports d'exécution de l'outil.

• slaveMysqlHost
  Nom d'hôte du serveur MySQL esclave.
• slaveMysqlPort (Défaut 3306)
  Port de connexion au serveur MySQL esclave.
• slaveMysqlUser
  Utilisateur MySQL de connexion au serveur esclave. Doit disposer des privilèges SELECT, INSERT, UPDATE, CREATE, ALTER, INDEX, DROP, SHOW_VIEW, TRIGGER, SUPER, RELOAD, SHOW_DATABASE, LOCK TABLES, REPLICATION_CLIENT, REPLICATION_SLAVE
• slaveMysqlPwd
  Mot de passe de l'utilisateur sur l'esclave.

• slavePdoSettings
  Tableau de paramètres pour le pilote MySQL du gestionnaire PDO lors de la connexion à l'esclave.

  • PDO::ATTR_ERRMODE
    Permet à PDO de générer une exception lors d'une erreur MySQL. Laisser à PDO::ERRMODE_EXCEPTION

• lang
  Localisation de l'interface. Seuls l'anglais (enu) et le français (fre) sont disponibles.

• checkTableMode (Défaut: MEDIUM)
  Degré d'analyse des données pour les requêtes CHECK TABLE. Peut être défini à QUICK, FAST, MEDIUM, EXTENDED ou CHANGED. Voir http://dev.mysql.com/doc/refman/5.5/en/check-table.html.
• checksumTableMode (Défaut: EXTENDED)
  Degré d'analyse des données pour les requêtes CHECKSUM TABLE. Peut être défini à QUICK ou EXTENDED. Voir http://dev.mysql.com/doc/refman/5.5/en/checksum-table.html
• sleepDuration (Défaut: 5)
  Durée en seconde du délai imposé au script lors du redémarrage de la réplication sur l'esclave.
• verbose (Défaut: vvv)
  Degré de verbosité de la sortie mail ou web de VerticalSlave. Peut être défini à v, vv ou vvv.
  A noter que le rapport enregistré dans la base verticalslaveDatabase correspond toujours au mode le plus verbeux (vvv).
• authKey
  Une chaîne aléatoire de caractères constituant une clé apportant un niveau supplémentaire de sécurité d'accès aux fonctionnalités actives de VerticalSlave.
  A noter que la lecture des rapports enregistrés n'est pas soumise à ce niveau de sécurité. Il convient d'autre de posséder un autre niveau de sécurité (utilisateur HTTP + SSL par ex).
• dumpConnectionType (Défaut: ssl).
  Le type de connexion utilisé lors de l'exécution de la commande de dump depuis l'esclave. Seule l'option ssl est disponible.
  Une ancienne version de l'outil exécutait la commande de dump depuis le serveur maître en établissant une connexion SSH et un pipe shell.
• mailto
  Si spécifiée, VerticalSlave enverra le rapport d'exécution par mail à cette adresse.
• mailfrom
  Si spécifiée, permet de forcer la commande sendmail appelée par PHP à déclarer l'adresse de provenance du mail de rapport. Ceci peut éviter que les relais SMTP déclarent le mail comme du spam.
• verticalslaveURI
  IP ou URL pour atteindre votre installation de VerticalSlave par le web. Cela permet d'obtenir un lien fonctionnel depuis le rapport reçu par mail au rapport complet stocké dans le magasin.
• dumpDatabases (Défaut: vide)
  Tableau listant les bases de données non concernées par la réplication et que vous souhaitez pouvoir dumper grâce à la fonctionnalité de dump général des bases.

Considérations de sécurité

VerticalSlave dispose d'un accès sensible à votre architecture MySQL et de sa tolérance de panne. Il convient donc de protéger scrupuleusement son accès.

Clé d'authentification

Le paramètre authKey permet d'empêcher l'accès aux fonctionnalités actives de l'outil si la clé fournie ne correspond pas.
Il est capital de définir une clé suffisamment complexe et aléatoire pour profiter de ce niveau de sécurité.
A noter que la clé n'est pas envoyée dans le rapport par mail, qui a de bonnes chances de transiter en clair sur le média, ni enregistré dans la base de données.

Configuration du serveur web

Vous devriez mettre en place un utilisateur HTTP pour protéger l'accès à votre copie de l'application.
En effet, la consultation des rapports passés enregistrés dans le magasin est accessible sans fournir de clé d'authentification.

Par ailleurs il est tout aussi important que la version web de cet outil soit consultable uniquement sous SSL (en HTTPS). Un certificat auto-signé et un hôte virtuel bien configuré suffiront à assurer cela.

MySQL over SSL

Pour assurer la confidentialité des informations de connexion et des données stockées dans les bases, il est impératif que les connexions MySQL au serveur maître soit chiffrées avec une chaîne de certification correctement installée.
Une chaîne de certification auto-signée suffit à la mise en place de ce niveau de sécurité.

Utilisateurs MySQL

Les utilisateurs MySQL employés, notamment celui créé sur le serveur maître, ne doivent disposer que des privilèges nécessaires.
Accorder la totalité des privilèges sur la totalité des bases, est une erreur à ne pas négliger.
Par exemple, l'utilisateur du serveur maître ne nécessite aucune autorisation à écrire quoi que ce soit sur ce serveur.

Utilisation

Version web

Lorsque votre installation est prête, connectez vous à votre outil : https://verticalslave.votredomaine.com/?authKey=<VotreCle>
VerticalSlave vérifie sa configuration puis affiche le menu des actions disponibles. Si authKey n'a pas été fourni ou ne correspond pas, seul le magasin des rapports d'activité est accessible.

Version CLI

Depuis une ligne de commande (CLI) ou une tâche cron, l'outil est accessible sous cette forme :

$ /usr/bin/php -f /chemin/vers/verticalslave/index.php authKey=<VotreCle> action=<check|dump|reset> [<cmd=launch>] [autorepair=yes]

Fonctionnement

Vérification de la configuration

Cette fonction s'exécute à chaque chargement de l'outil :

• L'outil est-il bien exécuté sur l'esclave ?
• Le type de connexion pour le dump des bases est-il bien ssl ?
• La connexion MySQL au maître est-elle bien effectuées sur SSL ?
• Les privilèges de l'utilisateur MySQL de connexion à l'esclave sont-ils suffisants ?
• La réplication est-elle bien configurée sur l'esclave ?
  SlaveStatus/Master_Host, SlaveStatus/Master_Port, SlaveStatus/Master_User, SlaveStatus/Master_SSL_CA_File, SlaveStatus/Master_SSL_Key, SlaveStatus/Master_SSL_Cert
• Les privilèges de l'utilisateur MySQL de connexion au maître sont-ils suffisants ?
• Le serveur maître est-il bien configuré comme tel ?

Vérification de l'état de la réplication

• URL : ?authKey=<VotreCle>&action=check

• CLI : /usr/bin/php -f /chemin/vers/verticalslave/index.php authKey=<VotreCle> action=check [autorepair=yes]

• Effectue un SHOW MASTER STATUS sur le maître.

• Effectue un SHOW SLAVE STATUS sur l'esclave.
• Vérifie si le champ Errno est à 0 et si Error est vide.
• Vérifie si les processus de l'esclaves sont démarrés : SlaveStatus/Slave_IO_Runninget SlaveStatus/Slave_SQL_Running à Yes.
• Correspondance du nom du fichier log en cours de lecture sur l'esclave et du fichier log en cours d'écriture sur le maître : SlaveStatus/Master_Log_File et MasterStatus/File.
• Egalité de la position de lecture dans le log sur l'esclave et de la position d'écriture du log du maître : SlaveStatus/Read_Master_Log_Pos et MasterStatus/Position.
• Egalité de la position d'exécution des instructions du log sur l'esclave et de la position de lecture de ce même log : SlaveStatus/Exec_Master_Log_Pos et SlaveStatus/Read_Master_Log_Pos.
• Vérifie si l'esclave est en retard sur le maître : SlaveStatus/Seconds_Behind_Master.
• Liste les bases répliquées : SlaveStatus/Replicate_Do_DB.
  Pour chacune :
  • Liste les tables de la base.
    Pour chacune, exécute ctrlrs/checkdb.php :
    • Effectue un CHECK TABLE et y vérifie l'absence d'erreur.
    • Compare le nombre d'enregistrements sur le maître et sur l'esclave.
    • Si le nombre d'enregistrements est identique, effectue un CHECKSUM TABLE pour calculer et comparer les sommes de contrôles côté maître et côté esclave.
    • Si une erreur a été relevée, ajoute la table à une liste des tables pouvant faire l'objet d'une auto-réparation.

Si le paramètre autorepair est à yes, exécute ctrlrs/dumptable.php :
- Stoppe la réplication avec STOP SLAVE et vérification que l'arrêt est effectif.
- Pour chaque base de données dont certaines tables n'ont pas passé les vérifications, effectue un dump de ces tables spécifiquement en utilisant la commande mysqldump --opt.
La sortie de cette commande est redirigée vers la sortie de l'application.
- Pour chaque base de données, exécute de nouveau ctrlrs/checkdb.php.
- Effectue un SHOW MASTER STATUS pour obtenir les paramètres nécessaires puis redémarre la réplication avec START SLAVE.
- Lance une nouvelle vérification complète de la réplication.

Dump des bases non-répliquées

• URL : ?authKey=<VotreCle>&action=dump
• CLI : /usr/bin/php -f /chemin/vers/verticalslave/index.php authKey=<VotreCle> action=dump cmd=launch

Si le paramètre dumpDatabases est peuplé d'une liste de bases de données, cette fonctionnalité permet de dumper toute la liste.
Attention, aucune base répliquée ne doit faire partie de cette liste sous peine de casser la réplication et de devoir la réinitialiser.

• Vide le cache des tables sur le maître avec FLUSH TABLES.
• Pour chaque base de données  :
  • Dresse la liste des tables de la base avec SHOW TABLES FROM.
  • Pour chaque table de la liste, effectue une vérification d'intégrité avec TABLEINTEGRITY.
  • Supprime la base de données sur l'esclave avec DROP DATABASE.
  • Recrée la base de données sur l'esclave avec CREATE DATABASE.
• Exécute une seule commande de dump de la forme /usr/bin/mysqldump --opt dont la sortie est redirigée vers la sortie de VerticalSlave.
• Exécute les vérifications de ctrlrs/checkdb.php :
  • Effectue un CHECK TABLE et y vérifie l'absence d'erreur.
  • Compare le nombre d'enregistrements sur le maître et sur l'esclave.
  • Si le nombre d'enregistrements est identique, effectue un CHECKSUM TABLE pour calculer et comparer les sommes de contrôles côté maître et côté esclave.
  • Si une erreur a été relevée, ajoute la table à une liste des tables pouvant faire l'objet d'une auto-réparation.

Réinitialisation de réplication

• URL : ?authKey=<VotreCle>&action=reset

• CLI : /usr/bin/php -f /chemin/vers/verticalslave/index.php authKey=<VotreCle> action=reset cmd=launch

• Vérifie la configuration de la réplication par un SHOW SLAVE STATUS.

• Stoppe la réplication avec STOP SLAVE.
• Sur chaque base déclarée comme répliquée par l'option replicate-do-db :
  • Dresse la liste des tables avec SHOW TABLES FROM.
  • Vérifie l'intégrité de chaque table avec TABLEINTEGRITY.
  • Supprime la base sur l'esclave avec DROP DATABASE.
  • Recrée la base sur l'esclave avec CREATE DATABASE.
• Effectue un dump de toutes les bases répliquées avec une commande du type mysqldump --opt --master-data=1 dont la sortie est redirigée vers la sortie de VerticalSlave.
• Exécute les vérifications de ctrlrs/checkdb.php :
  • Effectue un CHECK TABLE et y vérifie l'absence d'erreur.
  • Compare le nombre d'enregistrements sur le maître et sur l'esclave.
  • Si le nombre d'enregistrements est identique, effectue un CHECKSUM TABLE pour calculer et comparer les sommes de contrôles côté maître et côté esclave.
  • Si une erreur a été relevée, ajoute la table à une liste des tables pouvant faire l'objet d'une auto-réparation.
• Effectue un SHOW MASTER STATUS pour obtenir les paramètres nécessaires puis redémarre la réplication avec START SLAVE.

Licence

Cette application est un logiciel libre ; vous pouvez le redistribuer et/ou le modifier selon les termes de la GNU General Public License telle qu'elle est publiée par la Free Software Foundation ; en version 2 ou ultérieure de la license à votre convenance.

Limites de responsabilités

Cet outil a été testé sur Ubuntu 12.04 LTS, Apache 2.2, PHP 5.3 et MySQL 5.5, pour des bases de données stockant approximativement 1 million d'enregistrements. Il n'a pas été testé sur des bases immenses et des schémas de réplication complexes.
Vertical Assertions n'assume aucune responsabilité dans les pertes de données éventuelles qui surviendraient en l'utilisant. Vous devez prendre à votre charge les tests et les modifications nécessaires pour vous assurer que vos données resteront en sécurité.