# ProxySQL devant un cluster MariaDB WordPress : router lectures et écritures

> Un site à fort trafic doit répartir ses lectures sur des répliques sans toucher au code. Configuration de ProxySQL pour ce routage transparent, réplication déjà en place.

- Auteur : Clément Hadrot
- Publié le : 2022-01-18
- Mis à jour le : 2022-01-18
- Catégorie : Hébergement & serveurs
- URL : https://wpmoderne.dev.wordpress-developpement.fr/hebergement/proxysql-cluster-mariadb-wordpress-router-lectures-ecritures/

## L’essentiel

- ProxySQL s'intercale entre WordPress et la base sans changer une ligne de code
- Les règles de routage distinguent lectures et écritures par expression régulière
- Un hostgroup writer et un hostgroup reader suffisent pour démarrer

Le site avait dépassé depuis plusieurs mois le seuil où une seule base de données MariaDB suffisait à absorber la charge de lecture, largement dominante sur ce profil de site éditorial à fort trafic. La réplication vers deux répliques en lecture seule était déjà en place et fonctionnelle depuis un moment, mais restait sous-exploitée : le code WordPress continuait d'envoyer toutes ses requêtes, lectures comme écritures, vers l'unique serveur principal, faute d'un mécanisme de routage capable de distinguer les deux automatiquement.

Modifier le code applicatif pour distinguer manuellement chaque requête de lecture d'une requête d'écriture était hors de question : trop de points d'entrée, trop d'extensions tierces dont le comportement n'était pas garanti. ProxySQL, un proxy MySQL open source spécialisé, permet précisément ce routage au niveau réseau, sans que WordPress n'ait à savoir qu'il existe.

## Le principe : un proxy transparent entre WordPress et la base

ProxySQL s'installe entre l'application et le cluster de bases de données, en se faisant passer pour un serveur MySQL classique du point de vue de WordPress. Le fichier `wp-config.php` ne pointe plus directement vers le serveur principal, mais vers l'adresse de ProxySQL, qui décide ensuite, requête par requête, vers quel serveur réel du cluster la rediriger.

```
define('DB_HOST', '127.0.0.1:6033'); // port ProxySQL, plus le serveur MariaDB direct
```

Cette architecture repose sur le concept de « hostgroup » : un groupe logique de serveurs jouant le même rôle. Un hostgroup `writer` contient le serveur principal, seul autorisé à recevoir les écritures ; un hostgroup `reader` contient les répliques, destinées à absorber les lectures.

## Déclarer les serveurs et les hostgroups

> L'essentiel à retenir : ProxySQL s'intercale entre WordPress et la base sans changer une ligne de code ; Les règles de routage distinguent lectures et écritures par expression régulière ; Un hostgroup writer et un hostgroup reader suffisent pour démarrer

La configuration se fait via l'interface d'administration de ProxySQL, elle-même accessible en SQL sur un port dédié :

```
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, '10.0.0.1', 3306);  -- writer, serveur principal

INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (20, '10.0.0.2', 3306),  -- reader, réplique 1
       (20, '10.0.0.3', 3306);  -- reader, réplique 2

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
```

Une fois ces serveurs déclarés, ProxySQL sait où se trouve chaque rôle du cluster, mais ne route encore rien automatiquement : c'est le rôle des règles de requêtes (`mysql_query_rules`), qui analysent chaque requête entrante pour décider de sa destination.

## Écrire les règles de routage par expression régulière

ProxySQL analyse le texte de chaque requête SQL reçue et l'oriente selon des motifs définis à l'avance. Le cas le plus simple consiste à envoyer toute requête commençant par `SELECT` vers le hostgroup de lecture, et tout le reste vers le hostgroup d'écriture :

```
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT.*FOR UPDATE$', 10, 1);  -- SELECT verrouillant : vers le writer

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (200, 1, '^SELECT', 20, 1);  -- SELECT classiques : vers les readers

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
```

L'ordre des règles compte : ProxySQL les évalue dans l'ordre de leur `rule_id` et applique la première qui correspond. La règle 100, plus spécifique, doit impérativement précéder la règle 200 : un `SELECT ... FOR UPDATE`, utilisé pour verrouiller une ligne avant modification, doit rester sur le serveur principal, sous peine d'incohérence si la réplique a un léger retard par rapport à l'écriture en cours.

## Le point de vigilance propre à WordPress : le délai de réplication

La réplication MariaDB classique, asynchrone par défaut, introduit un délai variable entre l'écriture sur le serveur principal et sa disponibilité sur les répliques. Ce délai, généralement de quelques millisecondes en fonctionnement normal, peut ponctuellement s'allonger sous forte charge. Pour WordPress, ce comportement peut se traduire par un scénario gênant : un utilisateur publie un article, puis est immédiatement redirigé vers la page de l'article publié, une requête de lecture qui, si elle atterrit sur une réplique en retard, pourrait afficher un contenu pas encore à jour.

- ProxySQL propose un mécanisme de bascule temporaire vers le writer pour les lectures suivant immédiatement une écriture, dans une fenêtre de temps configurable, afin d'éviter ce type d'incohérence perçue.
- Une alternative plus simple, souvent suffisante, consiste à surveiller le délai de réplication (`Seconds_Behind_Master`) et à retirer temporairement une réplique du hostgroup reader si son retard dépasse un seuil défini.
- Ce compromis doit être calibré selon la tolérance du site à une incohérence de lecture de quelques secondes, généralement négligeable pour du contenu éditorial classique.

## En résumé

ProxySQL permet de répartir les lectures WordPress sur un cluster de répliques MariaDB sans toucher au code applicatif, à condition de déclarer soigneusement les hostgroups et d'ordonner correctement les règles de routage par expression régulière. La mise en place de la réplication elle-même reste un prérequis distinct, déjà supposé acquis avant d'aborder ce chantier de routage.