Mysql/MariaDB
Mysql/MariaDB jpp
Mysql/MariaDB : un modèle de base de données très connu et important car était peu gourmand en ressources, au moins à ses débuts.
Mysql a été créé,entre autres, par Michael Widenius, qui lui a donné un nom tiré du nom de sa fille ainée. Mysql a été acquis ensuite pas SUN Microsystems (1 milliard de dollars) et s'est retrouvé dans la giron d'Oracle après le rachat de SUN par Oracle.
Par la suite Michael Widenius a créé un "fork" de Mysql qu'il a nommé MariaDB du nom de sa seconde fille. Pour les détails voir : https://fr.wikipedia.org/wiki/MySQL.
La duplication des données est possible avec des fonctions natives ou l'utilisation de Galera.
Bonne lecture ... et bons tests.
Mysql maitre / esclave (2013)
Mysql maitre / esclave (2013) jppArticle original de 2013.
Cette notion de maître / esclave permet diverses améliorations dont la principale est la sécurité, mais on peut y ajouter aussi des améliorations de performance qui seront précisées dans un autre article (utiisation de "l'esclave" en lecture).
Dans cet exemple on part de deux machines (virtuelles bien sûr) identiques avec des bases Mysql « vierges » .
Il faut d'abord vérifier la présence du paquet "libaio1" et dans un premier temps créer les mêmes utilisateurs initiaux avec les mêmes droits.
On passe ensuite aux opérations "sérieuses" :
Sur l'esclave modifier le fichier « my.cnf » et modifier le « server-id », 'jamais deux serveurs de même ID dans le même réseau) :
Ancien : server-id = 1
Nouveau : server-id = 2
Redémarrer le service.
Sur le maître :
Vérifier dans le fichier "my.cnf" que le « binlog » est bien activé (Ligne dé-commentée) :
log_bin = /var/log/mysql/mysql-bin.log
Redémarrer le service.
Sur la machine maître créer un utilisateur spécifique pour la réplication :
CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicateur';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Vérifier la connexion de l'esclave vers le maître avec votre utilisateur de réplication avec la commande suivante (sur l'esclave) :
mysql –host=maitre –user=replicator –password=replicateur
qui doit fournir une connexion correcte.
On peut alors, toujours sur l'esclave, le « lier » à son maître :
CHANGE MASTER TO MASTER_HOST='mysql1', MASTER_USER='replicator',
MASTER_PASSWORD='replicateur'; START SLAVE;
1) Les premiers tests (simples).
- Vérifier le fonctionnement sur l'esclave :
SHOW SLAVE STATUS;
qui doit vous retourner un rang donnant le "point" de la synchronisation notamment le numéro du fichier "mysql-bin" en cours et d'autres valeurs de la position courante.
- Créer une DATABASE.
Sur le maître créer une base :
CREATE DATABASE VALID ;
Sur l'esclave vérifier l'existence de la base :
USE DATABASE VALID ;
Une fois ce test passé on peut continuer avec
- Créer table :
Sur le maître on crée une table dans notre DB VALID :
CREATE TABLE TOTO ( zone char(6)) ;
On vérifie sur l'esclave :
DESC TOTO ;
Qui doit vous retourner la description de la table TOTO.
On peut ensuite, pour faire bien, insérer des choses dans notre table sur le maître.
insert into TOTO ( zone) values ( "un" );
insert into TOTO ( zone) values ( "deux" );
Dans notre base esclave :
select * from TOTO;
retourne :
+------+
| zone |
+------+
| un |
| deux |
+------+
2 rows in set (0.00 sec)
Ce qui est exceptionnel !!
2) Des tests plus évolués.
Que se passe-t-il maintenant si :
- on stoppe l'esclave
- on fait une insertion sur le maître :
insert into TOTO (zone) values (« trois »).
- on redémarre l'esclave et on effectue le « select » précédent , miracle il nous retourne bien :
+-------+
| zone |
+-------+
| un |
| deux |
| trois |
+-------+
3 rows in set (0.00 sec)
3) Remarques finales.
La procédure est ici très simple avec du matériel simple (virtuel ! ), dans la réalité il faudrait prévoir :
- deux liens réseau dont un spécifique à la réplication et qui serait un plus pour limiter au maximum la latence du système.
- de se servir de la base répliquée pour faire des sauvegardes tranquillement, sana perturber la machine "maître".
- de se servir de la machine "esclave" pour lancer de "gros" ordres SQL de statistiques qui perturberaient plus ou moins fortement le serveur principal.
Il reste très simple d'effectuer cette mise en réplication et cela est assez performant.
Mysql: 5.6.30,5.7.16,SSD
Mysql: 5.6.30,5.7.16,SSD jppAyant un petit serveur "libre", le malheureux chauffait tellement qu'il se plantait régulièrement et que j'ai du le remplacer vite fait (voir article). Lorsque je l'ai démonté pour voir ce qu'il avait dans le ventre j'ai touché le radiateur et j'ai entendu un petit "clic", depuis cet engin ne chauffe plus ! J'ai donc décidé de l'utiliser avec quelques "vieux" SSD qui traînaient dans un coin pour faire quelques tests avec Mysql.
J'ai utilisé une base disponible sur une autre machine et qui possède deux tables un peu volumineuses : 10 millions de rangs pour l'une et un peu plus de 100 000 pour l'autre, le tout "pesant" environ 4GO.
La machine d'origine que j'ai prise pour référence possède :
CPU CoreI5 à 3.4Ghz série 5
RAM 16 Go
Disques 2 disques 1To à 7200 tours en Raid 1
Mysql 5.6.30
La machine "ressuscitée" dispose de :
CPU CoreI3 à 3.4GHz série 4
RAM 16 Go
Disques 3 SSD assez anciens mais encore vaillants de 32 à 128Go.
Mysql 5.6.30 puis 5.7.16
J'ai essayé 2 configurations :
Standard, tout sur le même disque (SSD), répertoires /var/lib/mysql et /var/log/mysql
Optimisée système sur un disque, DATA (ex /var/lib/mysql) sur un SSD et LOG (ex /var/log/mysql) sur un autre SSD, bien qu'avec des ordres "SELECT" le log ne soit pas sollicité.
Remarques : Toutes les tables sont en InnoDB et la valeur de "innodb_buffer_size" est fixée à 2048M.
La suite bientôt ... c'est fait et c'est ICI.
Mysql : Maitre/esclave (2016)
Mysql : Maitre/esclave (2016) jppMysql : mise en cluster maître/esclave.
Vous disposez d'une belle application fonctionnant sur une non moins belle base de données Mysql qui contient tout ce qui est nécessaire à votre application.
Mais que se passera-t-il si votre base est indisponible (crash base, disque ayant rendu l'âme, prise de pied dans la prise de courant ....) ?
Votre belle application ne fonctionnera plus du tout ...
La solution : Clusteriser votre base de données en mode maître/esclave.
Oui, mais ma base existe déjà, comment faire ? Je n'ai pas envie de "dumper" toutes mes tables .... !!! et de les recharger.
C'est cette histoire que je vais vous conter.
Vous ne touchez pas à votre serveur original (il marche, ne pas le déranger), et vous préparez une merveilleuse machine, identique pour bien faire, et faites-y une installation complète de Mysql de préférence dans la même version, ici c'est du 5.6 ! Cela marche aussi très bien avec MariaDB.
Si la structure des répertoires de DATA n'est pas "standard" il vaut mieux refaire à l'identique, cela simplifiera toute la maintenance.
Un peu de vocabulaire pour la suite :
- Serveur1 : votre serveur "actuel"
- Serveur2 : le petit nouveau.
L'opération consiste à :
- Créer les utilisateurs nécessaires à la réplication (Serveur1).
- Stopper Mysql sur le Serveur1
- Sauvegarder l'intégralité des fichiers de la BDD, si vous utilisez LVM (ou BTRFS) vous pouvez faire un snapshot du support disque de votre base, et la relancer immédiatement. Sinon "tar" fonctionne encore ...
- Modifier le fichier de config de Serveur1
- Stopper Mysql sur Serveur2, et bien nettoyer les répertoires de Mysql, y compris les fichiers de "/var/log/mysql"
- Restaurer les données de Serveur1 sur Serveur2.
- Détruire un petit fichier sur Serveur2 (/var/lib/mysql/auto.cnf)
- Reparamétrer Serveur2 en esclave, et en "read-only", c'est plus prudent.
- Redémarrer Serveur2
Note : Il existe des moyens de faire une sauvegarde "on line" ce qui évite de stopper le serveur, mais dans ce cas il faut enregistrer la position dans le "binlog" pour permettre à l'esclave de repartir juste après la dernière transaction enregistrée.
Vous pouvez alors passer Serveur2 en mode "esclave" et Serveur2 sera maintenu à jour par rapport à Serveur1.
Quelques "détails" pour en arriver à ce Nirvana :
1) Créer les utilisateurs pour la réplication sur Serveur1 :
* create user 'replic_usr'@'192.168.2.62' identified by 'Mon_Secret'; create user 'replic_usr'@'192.168.2.63' identified by 'Mon_Secret'; |
Note :
Vous avez un Mysql installé sur Serveur2, vérifiez que le user "replic_usr" peut bien se connecter sur Serveur1 :
mysql -user=replic_usr --password=Mon_Secret -D mysql
Si tout va bien vous pouvez alors stopper Mysql sur Serveur2.
2) Une fois ceci réalisé stopper Mysql sur serveur 1.
Sauvegardez /var/lib/mysql et ici, en plus /DATA/D1 et /DATA/D2 pour compliquer un peu.
3) Pendant ce temps modifier votre fichier "mysqld.cnf" et ajoutez-y :
| ### pour Master server-id = 1 log-bin = mysql-bin binlog-format = MIXED log-slave-updates = true gtid-mode = on enforce-gtid-consistency= on master-info-repository = table report-host = mysql1 |
4) Stoppez Mysql sur Serveur2.
5) Transportez vos copies sur Serveur2 et restaurez les "en place".
6) Supprimer le fichier "auto.cnf" dans /var/lib/mysql.
6) Modifiez le fichier "mysqld.cnf", comme pour Serveur1 sauf "server-id" et "report-host":
| ### pour Slave server-id = 2 read-only = 1 log-bin = mysql-bin binlog-format = MIXED log-slave-updates = true gtid-mode = on enforce-gtid-consistency = 1 master-info-repository = table report-host = mysql2 |
7) C'est presque fini :
Redémarrez Mysql sur Serveur1 et verifiez dans le fichier "error.log" la présence du message :
/usr/sbin/mysqld: ready for connections.
Passons maintenant au Serveur2 :
Nettoyer certains fichier que vous venez de restaurer dans /var/lib/mysql :
- mysql-bin*
Son fichier de configuration est correct, bien vérifier que le fichier "auto.cnf" (il contient un UUID) "/var/lib/mysql/auto.cnf" est effacé avant de redémarrer le serveur qui va s'empresser de le recréer avec un nouvel "UUID" différent de celui de Serveur1.
On peut alors "asservir" notre futur esclave :
mysql -u root -p -D Le_mot_de_passe_qui_va_bien \q |
Qui devrait répondre gentiment :
Query OK, 0 rows affected, 2 warnings (0,31 sec)
Puis :
start slave;
qui répond (gentiment lui aussi) :
Query OK, 0 rows affected (0,11 sec)
Verifications :
Nos données sont-elles toujours sur le maître :
| mysql> show tables; +--------------------+ | Tables_in_test_del | +--------------------+ | ANAL_FIR_ARC | | ANAL_FIR_TEST | | TEST_LOG | +--------------------+ 3 rows in set (0,00 sec) |
Et sur l'esclave ?
| mysql> show tables; +--------------------+ | Tables_in_test_del | +--------------------+ | ANAL_FIR_ARC | | ANAL_FIR_TEST | | TEST_LOG | +--------------------+ 3 rows in set (0,00 sec) |
Rien ne s'est perdu en route, nos petites données sont maintenant en sécurité !
Maintenant créons une table sur le maître :
| create table toto ( zone varchar(32) ); Query OK, 0 rows affected (0,36 sec) mysql> show tables; +--------------------+ | Tables_in_test_del | +--------------------+ | ANAL_FIR_ARC | | ANAL_FIR_TEST | | TEST_LOG | | toto | +--------------------+ 4 rows in set (0,00 sec) |
Et sur l'esclave :
| mysql> sow tables; +--------------------+ | Tables_in_test_del | +--------------------+ | ANAL_FIR_ARC | | ANAL_FIR_TEST | | TEST_LOG | | toto | +--------------------+ 4 rows in set (0,00 sec) |
Allez on s'en refait un coup, sur le maître :
| mysql> insert into toto (zone) values ('Ah oui, comme cela est bon'); commit; |
Et sur l'esclave :
| mysql> select * from toto; +----------------------------+ | zone | +----------------------------+ | Ah oui, comme cela est bon | +----------------------------+ 1 row in set (0,00 sec) |
A bientôt pour la suite de ces passionnantes aventures dans le monde de Mysql.
MariaDB : maître maître
MariaDB : maître maître jppJ'ai décidé d'installer deux serveurs MariaDB (cela ne change que peu de Mysql) pour tester une configuration multi-maîtres. Pour cela il faut d'abord installer deux machines (ici virtuelles) identiques. J'ai choisi d'installer une Debian "Stretch" qui devrait prochainement passer en "stable".
Description des machines :
- CPU 2
- RAM 4096GB
- Disque système 16GB ext4
- Disque DATA 32GB LVM, partition "VGDATA/MYSQL" de 24GB montée sur /DATA/mysql.
Il restera ainsi 8GB pour effectuer des "snapshots" destinés à la sauvegarde (article à suivre).
Pour faciliter l'installation (IP et nom viennent "automagiquement) j'ai créé ces machines dans le serveur dhcp et je les ai insérées dans mon serveur DNS.
Installation des machines à partir d'une image de cd "debian-testing-amd64-xfce-CD-1.iso".
Pas de serveur WEB, serveur SSH plus quelques paquets indispensables.
apt-get install system-config-lvm iproute2 net-tools vim-gtk
et enlevé "network-manager" que je n'apprécie pas, je préfère le bon vieux fichier "/etc/network/interfaces" et gérer moi même /"etc/resolv.conf".
Ensuite j'ai installé MariaDB client et serveur en "standard" (je précise la version 10.1) :
apt-get install mariadb-client-10.1 mariadb-server-10.1
Quelques modifications dans le fichier /etc/mysql/mysqld.conf.d/50-server.cnf :
Mettre la BDD "en ligne" :
bind-address = 0.0.0.0
Dans le "bloc" InnoDB (par exemple) ajout de :
innodb-buffer-pool-size = 1024M
innodb_buffer_pool_instances = 2
innodb-file-per-table = 1
innodb_additional_mem_pool_size = 8M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_read_io_threads = 2
innodb_write_io_threads = 2
innodb_stats_persistent = 1
innodb_stats_persistent_sample_pages = 512
Positionner la variable "server-id" à 1 pour le premier serveur et = 2 pour le second.
Ajouter dans le bloc "[mariadb-10.1]" :
innodb_flush_log_at_trx_commit = 2
event_scheduler = on
local_infile = 0
plugin-load=validate_password.so
skip-symbolic-links = 1
innodb_flush_method = O_DIRECT
binlog_row_image = minimal
#
concurrent_insert = AUTO
sync_binlog = 1
innodb_use_native_aio = 1
# Sans oublier d'activer le "bin-log" nécessaire au maître
log_bin = /var/log/mysql/mysql-bin.log
# et le format de log, je préfère "mixed" à "row" ou "statement"
binlog_format = mixed
Et pour terminer et éviter des problèmes dus aux tables avec identifiant en "auto-increment" :
Mettre chacune des deux machines (ou plus) sur des séries différentes. Ici l'une générera des identifiants pairs, l'autre des identifiants impairs réduisant ainsi à zéro les risques de collision.
Sur le serveur 1 :
auto_increment_increment = 1
Sur le serveur 2 :
auto_increment_increment = 2
et sur les deux machines :
auto_increment_offset = 2
La machine 1 génère ainsi la série 1,3,5,7 ....
la machine 2 la série 2,4,6,8 ....
Après ces quelques modifications on redémarre Mariadb pour valider nos modifications.
Il faut ensuite configurer le volume destiné aux données (LVM).
C'est pour cela que j'ai installé le peu gourmand "system-config-lvm",
Créer un volume "MARIADBDATA1" (partition 1 du second disque "/dev/xvdb1"), dans ce volume créer un LV "DATA" de 24GB conservant ainsi 8GB pour des snapshots.
Ce LV est créé en "ext4" et monté sur /DATA/mysql que l'on aura créé auparavant sans oublier un "chmod mysql:mysql /DATA/mysql".
Ensuite arrêter MariaDB et copier le contenu cd /var/lib/mysql dans le répertoire /DATA/mysql (après montage du disque bien sûr !) et remplacer le répertoire (renommé par sécurité !) d'origine par un lien /var/lib/mysql --> /DATA/mysql on peut alors redémarrer MariaDB.
Les deux machines ont été installées "à la main" (pas en copiant l'image disque) afin de bien vérifier la procédure d'installation.
Ces deux machines ont une structure identique, le report des données présentes dans le S1 est facilité :
- On arrête les deux services "mysql"
- On copie "bêtement les fichiers du répertoire /DATA/mysql de S1 dans celui de S2 (tar ou scp sont vos amis)
- On relance le service et on resynchronise les deux "masters" entre eux.
Mysql-community-server personnalisé
Mysql-community-server personnalisé jppJ'ai voulu installer la version 5.7 de Mysql depuis le repository de Mysql en chargeant depuis le site le paquet "Ubuntu / Debian (Architecture Independent), DEB" qui installe notament un fichier "mysql.list" dans /etc/apt/sources-list.d, ensuite on utilise les outils standard Debian : aptitude, apt-get ou synaptic.
Remarque : j'ai inhibé le repository "mysql-tools-preview"
Un "apt-get update" plus loin les choses sont disponibles et on peut installer "mysql-community-client" et "mysql-community-server" version 5.7.16
J'ai voulu réaliser une installation non "standard" en positionnant les fonctionnalités principales de la base de données sur des systèmes de fichiers différents notamment pour la partie "log" et pour la partie "données" afin d'optimiser le fonctionnement.
La machine est munie de :
- CPU CoreI3 4330
- RAM 16G
- 3 disques SSD
Selon le schéma suivant :
Un système de fichier racine
Un système de fichier /DATA/DATA1 pour les données
Un système de fichier /LOG/LOG1 pour les logs y compris les "logbin".
J'ai configuré le fichier /etc/mysql/mysql.conf.d/mysqld.cnf de la façon suivante :
| [mysqld] pid-file = /run/mysqld/mysqld.pid socket = /run/mysqld/mysqld.sock # By default we only accept connections from localhost bind-address = 0.0.0.0 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 ### Deplacement des répertoires datadir = /DATA/DATA1/mysql tmpdir = /LOG/LOG1/mysql_tmp log_bin = /LOG/LOG1/mysql/server-bin general_log_file = /LOG/LOG1/mysql/general.log slow-query-log-file = /LOG/LOG1/mysql/slow_query.log server_id = 11 log_error = /LOG/LOG1/mysql/error.log secure-file-priv = /DATA/DATA1/mysql-files slave_load_tmpdir = /LOG/LOG1/mysql_tmp ### configuration InnoDB |
....
Nettoyez toute trace de l'installation imposée lors du démarrage (/var/lib/mysql, /var/log/mysql).
Vérifiez bien que tous vos répertoires "mysql" ont bien le user et le groupe "mysql" :
| cd /DATA//DATA1 ls -al drwxr-xr-x 6 root root 4096 nov. 18 20:00 . drwxr-xr-x 4 root root 4096 nov. 14 23:27 .. drwx------ 2 root root 16384 nov. 12 22:24 lost+found drwxr-xr-x 5 mysql mysql 4096 nov. 18 20:26 mysql drwx------ 2 mysql mysql 4096 nov. 18 20:02 mysql-files cd /LOG/LOG1 drwxr-xr-x 6 root root 4096 nov. 17 23:22 . drwxr-xr-x 4 root root 4096 nov. 15 00:21 .. drwx------ 2 root root 16384 nov. 15 00:21 lost+found drwxr-xr-x 2 mysql mysql 4096 nov. 18 20:04 mysql drwx------ 2 mysql mysql 4096 nov. 18 20:22 mysql-files drwxr-xr-x 2 mysql mysql 4096 nov. 18 20:25 mysql_tmp |
Il faut maintenant attaquer la partie "systemd" et la corriger pour éluder un script (horrible) rempli de valeurs "en dur" qui viendrait perturber la belle ordonnance de nos paramètres.
Ce script (/usr/share/mysql/mysql-systemd-start) est à ignorer purement et simplement et à remplacer par un petit script destiné uniquement à initialiser correctement le répertoire "mysqld" dans /run (et pas /var/run qui n'est qu'un lien vers /run) ce script hyper simple (je l'ai appelé "init-mysql") il est fourni à titre d'exemple :
| #!/bin/bash MYSQLRUN=/run/mysqld if [ ! -d ${MYSQLRUN} ] then mkdir ${MYSQLRUN} chown mysql:mysql ${MYSQLRUN} fi |
Ce n'est vraiment pas grand chose !
Attention ce script est exécuté par le user "mysql", le mettre dans /usr/bin par exemple pour qu'il soit accessible sans difficultés.
Modifier le script "systemd" de lancement du service (/lib/systemd/system/mysql.service) pour en extirper la référence à l'horrible "mysql-systemd-start" :
Partie du script "systemd" modifié :
| [Service] User=mysql Group=mysql Type=forking PermissionsStartOnly=true PIDFile=/run/mysqld/mysqld.pid ExecStartPre=/usr/bin/init_mysql ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid TimeoutSec=60 LimitNOFILE = 5000 Restart=on-abort RestartPreventExitStatus=1 RuntimeDirectory=mysqld RuntimeDirectoryMode=755 Ne pas oublier ensuite "systemctl daemon-reload" avant de relancer le service. Aller modifier le fichier /etc/mysql/conf.d/mysql.cnf (utilisé par le client mysql) pour y indiquer le nom du fichier "socket" : [mysql] socket = /run/mysqld/mysqld.sock |
On doit encore réaliser un petit truc bizarre, créer un lien entre :
/etc/mysql/mysql.conf.d/mysqld.cnf et /etc/my.cnf ???
On peut alors, enfin, lancer une installation "personnalisée" de l'ensemble en utilisant le paramètre "--initialize--insecure" de mysqld, adieu "mysql_install_db" on ne te regrettera pas trop.
mysqld --initialize-insecure
qui crée le user "root" sans mot de passe, il suffira d'en mettre un rapidement avec la commande "set password ..." pour rester correct, la commande se termine rapidement, SSD oblige.
Cette commande a rempli les répertoires prévus /DATA/DATA1/mysql de ce qui se trouve dans /var/lib/mysql habituellement.
Tout semble prêt on lance la commande fatidique :
service mysql start
qui rend la main rapidement (tout est sur des SSD), on vérifie un peu si tout va bien :
| ps -ef | grep mysqld mysql 2733 1 2 22:52 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid |
puis :
| ls -al /run/mysqld drwxr-xr-x 2 mysql mysql 100 nov. 18 22:52 . drwxr-xr-x 23 root root 860 nov. 18 22:52 .. -rw-r----- 1 mysql mysql 5 nov. 18 22:52 mysqld.pid srwxrwxrwx 1 mysql mysql 0 nov. 18 22:52 mysqld.sock -rw------- 1 mysql mysql 5 nov. 18 22:52 mysqld.sock.lock |
Tout à l'air parfait on lance le client mysql :
| mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.16-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
Ouaaaah tout baigne, vite mettons un mot de passe à ce vieux "root" :
use mysql;
mysql> set password for 'root'@'localhost' = PASSWORD('Un_BeAu_#_PaSsWoRd_ToUt%Neuf?');
\q
Et on teste notre mot de passe :
mysql --user=root -p mysql>\q |
C'est fini, on dispose d'une base toute neuve en version 5.7.16. Montée sur des systèmes de fichiers SSD cela devrait être assez rapide !
A bientôt pour quelques tests.
Mysql : Maitre/esclave avec proxy
Mysql : Maitre/esclave avec proxy jppMettre l'esclave au boulot ! Pour réaliser cet exploit il faut utiliser un "proxy intelligent" capable de distinguer les ordres en lecture seule (SELECT) et les autres (le SELECT ... FOR UPDATE) est un piège.
Le proxy que j'ai choisi de tester s'appelle "ProxySql", nom très original s'il en est, mais il fonctionne fort bien et est assez simple à manipuler.
Le paquet est téléchargeable (V1.2.1 au moment de ces tests) à :
https://github.com/sysown/proxysql/releases/download/v1.2.1/proxysql_1…
La liste complète des téléchargements est accessible à l'URL:
https://github.com/sysown/proxysql/releases/
L'installation se fait sans mystère par :
dpkg -i proxysql_1.2.1-debian8_amd64.deb
Un script d'init est installé ainsi qu'un fichier de configuration /etc/proxysql.cnf, un répertoire /var/lib/proxysql est créé, de la doc /usr/share/doc ... Tout a l'air présent !
Le service n'est pas démarré par défaut, après un "service proxysql initial" (pour la première fois) le service est lancé et écoute sur les ports 6032 et 6033 (paramètres par défaut).
Ce paramètre "initial" permet d'initialiser la base de données interne de proxysql.
Si vous n'avez pas personnalisé un minimum votre fichier "proxysql.cnf" vous pouvez le modifier et repasser un petit coup de "initial". Au passage renommer rapidement ce fichier sinon au prochain démarrage il sera réutilisé !
Proxysql utilise syslog et vous trouverez ses messages dans /var/log/daemon.log
Par la suite on n'aura plus besoin de repasser le paramètre "initial", sauf pour réparer une "grosse bêtise".
Testons maintenant la connexion à Proxysql (user/mot de passe fixés dans proxysql.cnf) :
| mysql -u admin -p -h 127.0.0.1 -P 6032 Enter password: admin Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
Cela a un petit air connu ...
Remarque : la connexion par défaut est sur la base "main" :
| mysql> show databases; +-----+---------+-------------------------------+ | seq | name | file | +-----+---------+-------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | +-----+---------+-------------------------------+ 4 rows in set (0,00 sec) mysql> use main; show tables; +--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | mysql_collations | | mysql_query_rules | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | runtime_global_variables | | runtime_mysql_query_rules | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------+ mysql> use disk; show tables; +--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | mysql_collations | | mysql_query_rules | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | runtime_global_variables | | runtime_mysql_query_rules | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------+ \q |
Tiens ? Les mêmes tables sont présentes dans les bases "disk" et "main".
On peut "regarder" le contenu des tables par des ordres "SELECT", ajouter des choses par "INSERT", modifier par "UPDATE", et faire des "DELETE". La plupart des commandes de Mysql fonctionnent (sauf "desc" ?).
Pour faire des modifications "rollbackables" il faut les faire au sein d'une transaction explicite (begin transaction;), le rollback sera ainsi possible.
Le proxy dispose de deux règles par défaut "Select for update" et "select" qui envoyent respectivement sur les "hostgroups" 0 et 1, traditionnellement le "0" est le maître (celui qui peut effectuer des modifications de la base) et le groupe 1 celui des esclaves en lecture seule.
On se dépêche de changer le user "admin:admin" en autre chose de plus sécurisé :
update global_variables set variable_value = 'new_admin:admin_new' where variable_name = 'admin-admin_credentials';
Attention aux '-' , ":" et aux '_' !!!
Vous avez certainement remarqué les noms des tables doublés entre :
global_variables
runtime_global_variables
.....
Après une modif dans les tables "sans préfixe" il faut les recopier dans les tables "runtime" puis les sauvegarder sur disque pour rendre les modifications permanentes.
On peut ainsi préparer une nouvelle config dans les tables "sans préfixe" qui ne sont pas actives puis la basculer sur les tables "runtime" ce qui les rend actives.
Cela se fait à l'aide de commandes "LOAD" et "SAVE".
Dans notre cas de modfification de l'accès administrateur il faut :
Le reporter dans le "runtime"
LOAD MYSQL VARIABLES TO RUNTIME;
et les sauvegarder pour la postérité :
SAVE MYSQL VARIABLES TO DISK;
Dès que vous avez changé le user/mot de passe administrateur vous pouvez (devez) :
- Détruire (ou renommer) le fichier /etc/proxysql.cnf
- Redémarrer le service proxysql
Votre nouveau user/mot de passe sera alors actif.
Pour les tests le hostgroup 0 est une VM Mysql "master", le hostgroup 1 est une VM Mysql "slave" de la première qui ne recevra que les ordres "SELECT" purs exempts de toute clause "FOR UPDATE".
On revient un peu en arrière et on va voir la config postée initialement dans le fichier "proxysql.cnf" pour nos serveurs Serveur1 (master) et Serveur2 (slave) :
| mysql_servers = ( { address="mysql1" , port=3306 , hostgroup=0 , max_connections=10 }, { address="mysql2" , port=3306 , hostgroup=1 , max_connections=10 } ) mysql_users: ( { username = "test" , password = "test", default_hostgroup = 0, active = 1 } ) |
Si on regarde (avec notre nouvel "admin") l'état de la table mysql_servers :
| select * main.from mysql_servers; *************************** 1. row *************************** hostgroup_id: 0 hostname: mysql1 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 10 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 *************************** 2. row *************************** hostgroup_id: 1 hostname: mysql2 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 10 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 2 rows in set (0,00 sec) |
C'est bien l'état indiqué dans le fichier original ,vérifions maintenant nos règles de répartition :
| mysql> select * from mysql_query_rules\G *************************** 1. row *************************** rule_id: 1 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: NULL match_digest: NULL match_pattern: ^SELECT .* FOR UPDATE$ negate_match_pattern: 0 flagOUT: NULL replace_pattern: NULL destination_hostgroup: 0 cache_ttl: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL log: NULL apply: 1 *************************** 2. row *************************** rule_id: 2 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: NULL match_digest: NULL match_pattern: ^SELECT negate_match_pattern: 0 flagOUT: NULL replace_pattern: NULL destination_hostgroup: 1 cache_ttl: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL log: NULL apply: 1 2 rows in set (0,00 sec) |
C'est bien ce que nous voulions installer.
Quand à notre user il semble lui aussi OK :
| select * from MYSQL_USERS\G *************************** 1. row *************************** username: test password: test active: 1 use_ssl: 0 default_hostgroup: 0 default_schema: schema_locked: 0 transaction_persistent: 0 fast_forward: 0 backend: 1 frontend: 1 max_connections: 10000 |
Tout cela a l'air bel et bon.
Site à voir :
http://severalnines.com/blog/how-proxysql-adds-failover-and-query-control-your-mysql-replication-setup
Mysql huges pages or not
Mysql huges pages or not jppAfin de mesurer l'influence des "huge pages" j'ai réalisé deux séries de tests :
- en utilisant les "huge pages", 8 Go alloués aux huge pages + paramètre "large-pages" dans la config Mysql.
- sans les utiliser, vm.nr_hugepages = 0 dans sysctl.conf, pas de paramètre "large-pages" pour Mysql.
Remarques liées à la version 5.7.16 :
Lors du chargement des données, à partir d'un dump depuis une machine 5.6.30, j'ai remarqué quelques différences de comportement entre les versions 5.7.16 et 5.6.30 :
- certaines valeurs de "datetime" donnent des erreurs à l'importation des données, par exemple "000-00-00 00:00:00", valeur utilisée par défaut en 5.6 sont refusées à l'import en 5.7. Ceci m'a obligé à "corriger" les valeurs dans les tables originales pour que les tables soient importées.
- la présence en double d'une colonne dans un ordre "select" est indiquée comme erreur : "ERROR 1060 (42S21): Duplicate column name 'CODPAYS'"
Conditions du test :
Afin de ne mesurer que l'influence de la mémoire la taille du "innodb_buffer_pool_size" a été portée à 6Go afin que l'intégralité des données se trouve en cache (total données #4Go). Le "dashboard" de mysql-workbench donne le cache rempli à 70%.
Les valeurs de "query_cache" sont à zéro pour inhiber les fonctions du cache de requêtes.
Les valeurs allouées à "max_heap_table_size" et "tmp_table_size" ont été fixées à 256M pour réaliser toutes les opérations en mémoire.
Chaque test a été précédé d'un redémarrage complet de la machine pour bien "enregistrer" la configuration des "huge pages" sans désorganiser la mémoire afin de bénéficier de performances non biaisées.
Les tests ont chaque fois été précédés de deux tours de chauffe afin de charger les buffers et effectivement après le premier tour de chauffe on ne constate plus d'IO sur le disque "DATA" et quelques IO sur le disque où sont placées les tables temporaires.
Pour simplifier les tests, les ordres qui affichent des lignes ont été "entouré de "select from ( ...... ) xx;" afin de limiter l'affichage au nombre de lignes du résultat et au temps d'exécution.
Chaque test a été lancé trois fois en respectant l'ordre 1 .. 7, 1 .. 7, 1.. 7, les chiffres du tableau (en secondes) sont le total des trois passages.
| Secondes | |||
| N0 test | Huge inhibé | Huge activé | Delta % |
| TEST 1 | 28,09 | 27,91 | -0,64% |
| TEST 2 | 5,81 | 5,75 | -1,03% |
| TEST 3 | 0,49 | 0,47 | -4,08% |
| TEST 4 | 3,10 | 3,03 | -2,26% |
| TEST 5 | 2,87 | 2,88 | 0,35% |
| TEST 6 (join + OR) | 49,37 | 49,06 | -0,63% |
| TEST 6 (union) | 46,49 | 46,25 | -0,52% |
| TEST 7 | 17,70 | 17,52 | -1,02% |
| TOTAL | 153,92 | 152,87 | -0,68% |
Commentaires :
L'amélioration, bien que visible et a peu près constante sur les tests, reste faible : #0,7% sur le total des tests, mais elle devrait être un peu plus nette sur des tailles de buffers élevées, des "buffer_pool" de plus de 64 Go sont courants sur de "vraies" bases que dépassent couramment le To.
Note 2021 : des tests réalisés avec des versions récentes de MariaDB donnent le même genre de résultats.
MySQL : utilisation tables Federated(x)
MySQL : utilisation tables Federated(x) jppLes tables "FEDERATED" sont des tables "distantes" en général sur une machine différente.
Les tables de type "FEDERATED" permettent la communication (facile) entre deux bases MySQL (et même non-MySQL, par exemple MariaDB).
Ce plugin est relativement insensible aux versions des bases utilisées.
Remarque : pour MariaDB l'utilisation du plugin "ha_federated.so" est à déconseiller car ce plugin n'est plus maintenu ... mais remplacé par "ha_federatedx.so" qui, lui, est toujours maintenu et a un fonctionnement compatible. Même avec le plugin "federatedx" il faut ajouter "federated" dans votre fichier de configuration.
Pour MariaDB le "nec plus ultra" est d'utiliser un nouveau plugin "connect" dont je vous proposerais un test prochainement.
Un petit exemple d'utilisation de "federated" :
- Je dispose d'une machine "A" qui gère les tables utilisées par NtopNG, je ne veux pas laisser dans cette machine un trop gros volume de donnnées car NtopNG est assez consommateur d'espace et j'ai limité a 30 jours la rétention des données.
- Je voudrais disposer d'un historique plus "long" sur une machine "B" pour pouvoir m'amuser à faire des analyses à plus long terme.
La gestion du transfert des données de A vers B m'a posé quelques problèmes, car les solutions "classiques" ne sont pas faciles à gérer :
- Extraction dans une table "tampon" des données de la dernière semaine,
- Export de la table tampon par mysqldump,
- Transfert du ficher export
- Import dans une table tampon
- Intégration dans la table définitive.
Les rangs de la table principale sont identifiés par un ID en auto-incrément et il est donc aisé de repérer ceux qui manquent dans la machine "B" et c'est là que se profile l'utilisation d'une table "FEDERATED" Mysql.
En effet il suffit de déclarer dans la machine "B" une table identique à la table principale de "A" avec "engine=FEDERATED" pour accéder de "B" vers "A". La création est simple et toutes les opérations se font sur la machine "B". Il faut quand même vérifier que "monuser" a bien accès à la base "A" depuis l'adresse de "B" :
Créer un "serveur" :
create server xxxxxxx
foreign data wrapper mysql
options (USER 'monuser', PASSWORD 'monpassword', HOST '192.168.x.y', DATABASE 'mabaseA');Créer une "table" FEDERATED (IDX = PRIMARY KEY) :
create table table_distante
( ......
KEY (IDX),
....
) engine=FEDERATED connection='xxxxxxx/table_sur_serveur_A';
Ensuite récupérer les données sur B est hyper simple :
select max(idx) into @IDMAX from table_sur_B; insert into table_sur_B select * from table_distante commit; |
Et en plus c'est assez rapide comme le montre la trace suivante :
-------------- Query OK, 1 row affected (0,03 sec) -------------- +----------+ -------------- Query OK, 532917 rows affected (7,30 sec) |
Plus de 530 000 rangs en 7,3 secondes ... c'est bien plus rapide que la manip "old style" et surtout bien plus simple !
La base distante est ici une base Mysql mais cela marche aussi avec d'autres bases ... Oracle, Postgres ... selon les modules disponibles.
Attention : toutefois, un bête "select" ne passant pas par un index entraînera le transport de toute la table en local (temporaire) et peut donc durer assez longtemps et être très consommateur de ressources (disque et mémoire), donc éviter comme la peste les ordres n'utilisant pas correctement un index, mon exemple utilise la Primary Key de la table et est donc très rapide.
Mariadb : Connect Engine
Mariadb : Connect Engine jppLe "vieux" plugin "federated" n'est plus maintenu, il existe une autre version "federatedx" qui fonctionne correctement sur Mysql (détails ici), je ne l'ai pas testée récemment sur MariaDB.
La méthode "IN" préconisée aujourd'hui pour MariaDB est d'utiliser le plugin "CONNECT". Celui-ci met à votre disposition un nouvel "ENGINE" qui permet la connexion à des bases distantes (de diverses saveurs), à des fichiers "plats", du Xml, du Json.
Le "CONNECT ENGINE" fait parfois partie des distributions stantard sur Linux :
- Debian/Ubuntu : apt install mariadb-plugin-connect
- RedHat/Centos/Fedora : yum install MariaDB-connect-engine
Ensuite il suffit de l'activer (avec un utilisateur qui a des droits suffisants !) :
| mysql --user=... -p prompt> INSTALL SONAME 'ha_connect'; prompt> \q |
On pourra alors passer aux choses sérieuses ...
La création des tables reste classique, quelques exceptions notables :
- Pas de champ en AUTO_INCREMENT --> utiliser des séquences gérées "à la main" (MariaDB >= 10.3).
- Un champ présent dans un index doit absolument être spécifié "NOT NULL".
- Pas de champs TEXT/BLOB, ça c'est un peu dommage !
- Les clefs d'index ne doivent pas dépasser 255 caractères.
En ce qui concerne l'accès à une base Mysql/MariaDB le protocole étant standardisé il suffit d'indiquer après les définitions d'index :
| ... définition des données et index ... ) Engine=CONNECT DEFAULT CHARSET xxxxxx table_type=mysql connection='mysql://USER/PASSWORD@HOST/DATABASE/TABLE'; |
La table est crée et permet un accès direct aux données. On peut, bien sûr, utiliser un mélange de tables locales et distantes dans le même ordre SQL.
Je m'en suis notamment servi pour repérer des différences au sein d'un cluster maître/maître qui avait subi quelques manipulations hasardeuses au niveau de la définition des GTID des slaves (mélange entre gtid_slave_pos et gtid_current_pos).
Quelques tests rapides.
Les tests ont été réalisés sur deux VM identiques Debian 10 sur KVM :
2 vcpu, 2Go de RAM, une seule partition disque de 127Go sur une carte M2, MariaDB 10.3.23 paramétré avec un cache InnoDB de 1Go et 4 pool instances.
Chaque machine dispose de deux tables locales quasiment identiques, les tables notées "Z_..." sont des tables distantes, celles notées "D_..." sont des tables locales. L'espace disque est d'environ 1,7Go par table.
Mysql redémarré avant le premier test (caches vidés) :
Un test rapide de comptage sur table distante.
select count(*) from Z2_TABLE_DISTANTE select count(*) from Z2_TABLE_DISTANTE; |
L'effet du cache n'est ici pas énorme, même pas 10% de gain.
Test sur la même table en local :
select count(*) from D2_TABLE_LOCALE select count(*) from D2_TABLE_LOCALE; |
L'effet du cache est ici beaucoup plus notable ...
Autre test : jointures entre table distante et table locale :
| select count(*) from Z1_TABLE_DISTANTE Z1 join D2_TABLE_LOCALE D2 on D2.ID = Z1.ID; +----------+ | count(*) | +----------+ | 1454065 | +----------+ 1 row in set (6.093 sec) select count(*) from D1_TABLE_LOCALE Z1 join D2_TABLE_LOCALE D2 on D2.ID = Z1.ID +----------+ | count(*) | +----------+ | 1454065 | +----------+ 1 row in set (4.349 sec) |
Les performances sont ici très correctes la différence de temps d'exécution est relativement faible ce moteur est très facile à utiliser et présente des performances intéressantes.
Autre test :
Voir la différence entre le traitement d'une table "locale" et d'une table "pseudo distante" (déclarée comme une table distante mais utilisant l'adresse de la machine locale) :
select count(*) from Z2_TABLE_PSEUDO_DISTANTE; select count(*) from TABLE_LOCALE; | count(*) | |
La différence est assez significative, j'ai refait plusieurs essais pour éliminer l'influence du cache mais la table utilise environ 13 Go d'espace disque, le cache de 1Go ne joue que peu.
MariaDB et Galera
MariaDB et Galera jppJ'ai décidé de faire un test de la dernière version de MariaDB associée à Galera, non ce n'est pas la galère, puis d'y associer Maxscale.
Ceci nécessite une certaine infrastucture (réalisée ici avec des machines virtuelles KVM) :
- 1 machine pour Maxscale
3 machines pour MariaDB/Galera
Pourquoi trois machines ?
Parce que avec seulement deux machines un système plus simple est possible et qu'un système comme Galera se comporte mieux avec 3 machines car cela permet d'éviter des duels fratricides en cas d'incident sur une des machines ce que les anglicistes nomment "split brain" et je n'ai pas de traduction sensée à fournir !
Le test de Maxscale est repoussé car bien que IPV6 soit inhibé ici, le module de traitement "lecture seule" cherche un utilisateur avec une adresse "bizarre" de style "IPV6" et refuse donc la connexion.
L'accès lecture/écriture, lui, fonctionne fort bien mais je voudrais trouver l'explication de ces adresses "fantaisie" pour le module lecture-seule avant de passer à des tests complets.
Je n'ai pas pu régler ce problème pseudo "IPv6", mais après une mise à jour cela ne semble plus se produire ...
Installation machines BDD
Installation machines BDD jppPréambule.
Les configurations suivantes sont adoptées, elles sont "légères" mais largement suffisantes pour nos tests :
- GALE_1 : future machine "Maxscale"
Disque 40GB
Mémoire 2048M
2 VCPU
- GALE-2, GALE-3 et GALE-4 nos trois machines bases de données :
Disque 40GB
Mémoire 4096M
2 VCPU
Le partitionnement adopté, volontairement simple, est suffisant pour des tests :
/boot 656Mo
/ 36Go
Swap 3,5Go
Les deux premières machines ont été installées "normalement" à partir d'une image ISO, les machines 3 et 4 ont été "clonées" depuis la machine 2 après l'installation de la base de données et les premiers tests de validation. "dd" est un bon ami.
Installation.
Il faut commencer par installer la définition du repository spécifique à MariaDB qui installe tout ce qu'il faut pour MariaDB, Maxscale et quelques outils complémentaires.
- Commencer par installer les outils curl, net-tools, vim ou gvim-gtk3 ...
Pour pouvoir ensuite installer, valider, suivre et mettre à jour confortablement tout ce qu'il faut pour un système "normal".
- Installer le repository MariaDB :
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
Ce script gère tout, y compris un "apt update" pour mettre à jour les listes de paquets.
Comme les machines sont installées en Debian Bullseye on est limité aux versions 10.5 de MariaDB, 22.08 de Maxscale et Galera 3 ou 4 (on utilisera bien sûr la version 4).
- On installe ici (GALE-2) MariaDB et Galera :
apt install mariadb-client-10.5 mariadb-server-10.5 galera-4
Cela récupère un paquet de dépendances : #28Mo d'archives et promet d'occuper 213MO sur disque.
Après le fatidique "sysctl start mariadb" tout se passe bien !
Lancée sous "root" la commande "mysql" nous lance gentiment le client connecté à notre nouvelle base.
On peut alors mettre un mot de passe à l'utilisateur "root", limité à localhost et créer un compte spécifique d'administration avec une visibilité plus importante (/24 par exemple). Bien le faire avant le clonage de GALE-2 sur les deux machines suivantes.
Le système "modèle" est prêt et on peut le cloner dans les deux autres machines, il suffira de changer :
- hostname /etc/hostname
- IP /etc/network.interfaces IP statiques
et pour la base MariaDB.
- server_id /etc/mysql/mariadb.conf.d/50-server.cnf
y mettre 2,3 et 4, les numéros de nostrois braves machines.
Maintenant que nos trois machines bases de données sont prêtes on va pouvoir passer à la mise en service de Galera ce qui promet d'être plus amusant.
Avant de se lancer dans Galera proprement dit il vaut mieux "arranger un peu" la configuration de MariaDB.
Par exemple mettre les fichiers "binlog" ailleurs que dans /var/log/mariadb !
J'ai donc ici créé un répertoire /var/lib/binlogs :
mkdir /var/lib/binlogs
chown mysql: /var/lib/binlogs
et modifié en conséquence le fichier /etc/mysql/mariadb.conf.d/50-server.cnf.
Galera en route
Galera en route jppPour Galera le fichier de paramétrage est /etc/mysql/mariadb.conf.d/60-galera.cnf, voir ci-dessous mon paramétrage :
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on = ON
wsrep_provider = "/usr/lib/libgalera_smm.so"
wsrep_cluster_name = "clustertest"
#
wsrep_node_name = gale-2
wsrep-node-address = 192.168.2.153
#
wsrep_cluster_address = "gcomm://192.168.2.153,192.168.2.154,192.168.2.155"
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0
# Optional settings
wsrep_slave_threads = 1
innodb_flush_log_at_trx_commit = 0
# pour restart si cluster planté
wsrep_provider_options="gcache.recover=yes"Pour le premier lancement il faut impérativement utiliser la commande suivante :
mysqld --wsrep-new-cluster
sur chacun des trois membres du cluster.
Malheureusement cette commande ne "rends pas la main" j'ai donc du passer par la suite de commandes suivantes :
mysqladmin shutdown
systemctl start mariadb
pour "rendre la main" et démarrer normalement, j'ai fait cela sur chacun des nœuds en laissant un petit temps intermédiaire pour laisser les machines se re-synchroniser.
Pour contrôle je crée une base :
CREATE DATABASE montest;
Et je vérifie qu'elle est bien créée sur les trois nœuds, youpi cette base existe bien partout ... c'est le pied.
De même la création d'une table de test est parfaitement répliquée sur les trois nœuds :
Create Table: CREATE TABLE `TT` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`ZONE` varchar(32) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Vérifions maintenant l'insertion :
Sur le nœud gale-2 (nœud 1) :
insert into TT (ZONE) values ('UN');
Sur les deux autres nœuds on voit :
select * from TT;
+----+------+
| ID | ZONE |
+----+------+
| 3 | UN |
+----+------+Tiens, l'autoincrement débute à 3 ?
Voyons voir un insert sur le nœud gale-3 (nœud 2) :
insert into TT (ZONE) values ('DEUX');
L'ID généré est égal à 5.
Un insert sur le nœud gale-4 (nœud 3) :
insert into TT (ZONE) values ('TROIS');
L'ID généré est égal à 7;
Retour sur le nœud 1 qui génère un ID à 9;
En bref cela à l'air de bien fonctionner ...
On essaye un arrêt de toutes les machines machines, puis on les redémarre, et ... c'est la catastrophe, rien ne redémarre.
Il faut chercher quel était le nœud "maître" en examinant les fichiers "grastate.dat" (dans /var/lib/mysql) et chercher celui qui possède la ligne "safe_to_bootstrap: 1".
Ici c'est le nœud "gale-4" qui a été stoppé en dernier ... donc il doit pouvoir repartir car il est réputé le plus à jour. Si aucun fichier ne comporte cette mention (cas de crash simultané) il faudra en éditer un "à la main" et mettre la valeur à 0. Dans le cas précédent le noeud "gale-4" démarre normalement, les autres peuvent alors démarrer et se connecter au cluster.
Si la situation est réellement "sérieuse" (refus complet de démarrage) mettre à jour le fichier "/var/lib/mysql/grastate.dat" de la machine stoppée en dernier et y forcer la valeur 0 dans la ligne "safe_to_bootstrap:", les autres membres du cluster pourront alors être démarrés avec un simple "systemctl start mariadb".
Utilisation du paramètre "wsrep_provider_options="gcache.recover=yes" ne change rien ... par contre en utilisant la commande "galera_new_cluster" sur le nœud 4 celui-ci démarre normalement et le démarrage des deux autres est alors possible ... Ouf! Le cluster se comporte ensuite normalement et il est possible de démarrer les autres systèmes avec "systemctl start mariadb".
La commande "galera_new_cluster" ne fonctionne directement que sur la dernière machine stoppée du cluster afin d'être sûr qu'aucune donnée ne sera perdue.
Souvenez-vous de cette commande ... qui peut servir en cas d'incident grave ou pour des machines de test que l'on stoppe souvent.
Sinon il faut aller s'intéresser au fichier "/var/lib/mysql/grastate.dat" et, éventuellement (si la dernière machine stoppée est hors-service) et y passer la valeur de "safe_to_bootstrap" de 0 à 1. Mais le risque sera de perdre les dernières données traitées avant l'arrêt de la dernière machine du cluster.
Le redémarrage d'un seul des membres de notre cluster se passe fort bien et chacun des autres nœuds note que ce membre a disparu :
2022-11-01 18:01:03 0 [Note] WSREP: cleaning up e713c304-9429 (tcp://192.168.2.155:4567)
192.168.2.155 est l'adresse du membre qui s'est absenté, lorsqu'il redémarre il se signale et les deux autres membres constatent ce fait avec un message :
2022-11-01 18:03:24 0 [Note] WSREP: Member 0.0 (gale-4) synced with group.
Une fonction qui peut être utile pour signaler ce type d'événement est disponible dans le paramétrage de Galera :
wsrep_notify_cmd = 'une commande shell'
Cette commande permet de signaler les événements importants.
J'en ai installé une très simple sur le nœud GALE_2 :
#!/bin/bash
FLOG=/var/tmp/MSG.LOG
DTT=$(date +%Y/%m/%d_%H:%M:%S)
echo ${DTT}' | '$* >>${FLOG}
Lors du démarrage des machines (dans l'ordre inverse de celui dans lequel elles ont été stoppées, (le dernier membre actif doit redémarrer le premier) on obtient l'affichage suivant :
23:52:04 | --status connected
23:52:04 | --status joiner
23:52:04 | --status initializing
23:52:05 | --status initialized
23:52:05 | --status joined
23:52:05 | --status joined --uuid 28736ddb-59f8-11ed-abde-fe2234c75fO
23:52:05 | --status synced
23:53:15 | --status synced --uuid 28736ddb-59f8-11ed-abde-fe2234c75fO
23:53:15 | --status donor
23:53:15 | --status joined
23:53:15 | --status synced
23:54:42 | --status synced --uuid 28736ddb-59f8-11ed-abde-fe2234c75fO
23:54:42 | --status donor
23:54:42 | --status joined
23:54:42 | --status syncedC'est très anormal que toutes les machines soient stoppées, mais cela peut arriver, tant qu'il reste une machine en fonction les autres peuvent venir s'abonner au cluster et tout se passe bien.
Le seul problème est que lors de la "panne" d'une machine son adresse IP ne réponds plus et les clients n'ont plus accès aux données et établir un système de haute disponibilité entre trois machines n'est pas une sinécure.
C'est là l'avantage d'utiliser Maxscale qui va, entre autres choses gérer l'accès à notre cluster. Maxscale permet de gérer des clusters disposant de plus de 3 nœuds, nous verrons cela dans un prochain chapitre ... à suivre
Maxscale : paramétrage
Maxscale : paramétrage jppCet article détaille, un peu, la mise en place de Maxscale.
Le paramétrage est relativement simple mais les dernières présentent un petit "piège" car l'adresse d'écoute par défaut est une adresse IPV6 et cela pourrait vous surprendre. Il est donc important de préciser "address=0.0.0.0" (ou une autre adresse IPV4) dans le paramétrage de Maxscale.
D'abord la définition des serveurs du cluster Galera :
# Server definitions
#
[gale-2]
type=server
address=192.168.2.153
port=3306
protocol=MariaDBBackend
#
[gale-3]
type=server
address=192.168.2.154
port=3306
protocol=MariaDBBackend
#
[gale-4]
type=server
address=192.168.2.155
port=3306
protocol=MariaDBBackendJ'ai ensuite décidé de créer deux "voies d'accès" :
- Une en lecture seule
- Une en lecture/écriture
[lect-seule]
type=service
router=readconnroute
servers=gale-4
user=util_services
password=xxxxxxxx
router_options=running
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-6-readwritesplit/
[ecri-lect]
type=service
router=readwritesplit
servers=gale-2,gale-3
user=util_services
password=xxxxxxxxUne fois ces deux services créés il faut leur affecter un "listener" qui permettra l'accès à nos services, nous aurons donc deux "listeners".
[Read-Only-Listener]
type=listener
service=lect-seule
protocol=MariaDBClient
port=3007
address=0.0.0.0
[Read-Write-Listener]
type=listener
service=ecri-lect
protocol=MariaDBClient
port=3006
address=0.0.0.0C'est ici qu'il faut préciser l'adresse au format IPV4 pour éviter le piège de l'IPV6 par défaut.
Enfin le dernier paragraphe pour fixer les paramètres du monitoring de nos 3 serveurs :
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-6-monitors/
#
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=gale-2,gale-3,gale-4
user=xxxxxxx
password=yyyyyyyy
monitor_interval=2s
Ne pas oublier de "protéger" ce fichier par :
chown maxscale: /etc/maxscale.cnf
chmod 600 /etc/maxscale.cnf
Maxscale GUI
Maxscale GUI jppLe GUI de gestion de Maxscale.
Il est accessible sur le port 8989 de la machine selon les paramètres du fichier de configuration :
# MAXGUI
admin_host=192.168.2.152
# GUI without SSL
admin_secure_gui=falseIci j'ai inhibé le SSL ce qu'il ne faudrait pas faire en production ...
Le premier écran qui s'affiche est l'écran de surveillance (Dashboard) de notre cluster Galera première option a gauche dans le menu horizontal.
Cet écran présente l'état de notre cluster, on y voit l'état de notre cluster "Running" en vert dans la colonne "STATE" et la liste de tous les membres du cluster avec le nombre de connexions, leur état (autre colonne "STATE").
Le master a droit à la couleur verte et au statut "Master, Running", les deux autres à une mention rouge et Running, donc tout va bien. La dernière colonne indique à quel "service" chaque machine est attachée.
Un graphique, en haut de l'écran, montre la charge de chaque machine (nombre de sessions, connections et le load).
Un menu horizontal permet d'afficher :
Les sessions courantes. Il est possible ici de "tuer" une session gênante.
Les services avec les machines concernées et les les sessions en cours.
Les listeners le nom du listener avec le port qu'ils utilisent, leur état (Running normalement !) et le nom du service.
Les filtres actifs. Les filtres permettent de réaliser des actions en fonction de l'utilisateur, par exemple de "cacher" certaines données confidentielles .... à des gens qui n'ont pas besoin de les connaître ...
En haut à droite de l'écran le bouton "Create New" permet de créer de nouveaux services, de nouveaux serveurs et des filtres.
Pour les filtres voir la documentation de Maxscale qui présente toutes les possibilités.
Depuis le menu principal les options suivantes :
- Visualisation : permet d'afficher notre cluster en mode graphique.
- Query-editor : permet d'exécuter des ordres SQL après s'être identifié auprès d'un service.
- Users : permet de gérer les utilisateurs de Maxscale (pas ceux du cluster de BDD).
- Logs archives : permet d'afficher le log de Maxscale.
- Settings : permet de modifier les paramètres de Maxgui, et il y en a un bon paquet ! N'y toucher que si l'on sait ce que l'on fait.
That's all folks.
Mariadb 11.4 : binlogs non supprimés
Mariadb 11.4 : binlogs non supprimés jppVersion 11.4 et +.
Attention, à partir de cette version il faut impérativement ajouter un paramètre dans votre configuration.
Ce paramètre est destiné à conserver les logs binaires (binary logs) tant que tous les "esclaves" ne les ont pas reçu, malheureusement pour ceux qui n'ont pas cette sécurité la baleur par défaut de ce paramètre n'est pas zéro et cela empêche MariaDB de supprimer les logs. Si vous n'avez pas de machine "esclave" il faut donc ajouter la ligne suivants dans vos fichiers de configuration :
slave_connections_needed_for_purge = 0
Et si vous avez des machines "esclaves" mettez le nombre de machines "filles" à la place du zéro ainsi Mariadb gardera les logs binaires jusqu'à ce que toutes les machines filles aient reçu les logs.