Réplication MySQL 8 (binary log file position). Prise en main, démarrage rapide

Introduction

Dans cet article, 2 bases de données MySQL sont répliquées par position dans les logs binaires (binary log file position based replication) : sqlpac et airflow.

MySQL 8.0.22 est la version utilisée dans l’architecture, les deux serveurs MySQL (source et replica)

fonctionnent sur des serveurs Ubuntu 18.04 avec le user mysql (id : 10003, group : dba)
écoutent sur le port 40008

Le pare-feu (firewall) est ouvert sur la machine source vpsfrsqlpac1 / port 40008 pour les connexions provenant du serveur répliqué vpsfrsqlpac2.

Ci-dessous, les sujets abordés pour rapidement installer et gérer une réplication MySQL 8 par position dans les logs binaires :

Architecture de la réplication, bref aperçu + une note rapide sur un changement de terminologie en cours dans les produits MySQL
Préparation du serveur source
Snapshot du serveur source
Préparation du serveur répliqué
Démarrage de la réplication
Gestion des erreurs de réplication
Désactiver la réplication dans une session
Mode Read only dans les bases répliquées (MySQL 8.0.22)

Architecture de la réplication par position dans les logs binaires

Comment la réplication par position dans les logs binaires fonctionne ? Un dessin rapide :

3 threads dans le mécanisme de réplication : 1 dans le serveur source, 2 dans le serveur répliqué

Thread Binary Log Dump : thread créé dans le serveur MySQL source lorsque le réplica s’y connecte, ce thread envoie le contenu des logs binaires au réplica.
Thread Replication IO : thread localisé dans le serveur répliqué MySQL qui se connecte au serveur source, demande les mises à jour enregistrées dans ses logs binaires, lit les mises à jour que le thread Binlog Dump de la source envoie et copie celles-ci localement dans des fichiers de log de relai (relay log files). Les fichiers de logs binaires et de relai ont le même format, ils peuvent être traduits avec l’utilitaire mysqlbinlog.
Thread Replication SQL : thread localisé dans le serveur répliqué MySQL qui lit les fichiers de log de relai et exécute les transactions dans les bases de données répliquées.

Changement de terminologie

Avant de poursuivre, une note importante : à partir de MySQL 8.0.22, la terminologie est en cours de modification (MySQL Terminology Updates). Les changements de terminologie suivants seront mis en œuvre dans les prochaines versions de tous les produits MySQL, changements qui affectent grandement la réplication MySQL :

Ancien		Nouveau	Ancien		Nouveau
master		source	blacklist		blocklist
slave		replica	whitelist		allowlist

Les commandes relatives à la réplication utilisant l’ancienne terminologie seront progressivement obsolètes et remplacées par les nouveaux termes :

Old	New	Ready in
`START \| STOP SLAVE;`	`START \| STOP REPLICA;`	8.0.22
`SHOW SLAVE STATUS;`	`SHOW REPLICA STATUS;`	8.0.22
`SHOW SLAVE HOSTS;`	`SHOW REPLICAS;`	8.0.22
`SHOW MASTER STATUS;`	`SHOW SOURCE STATUS; (?)`	Not yet
`CHANGE MASTER TO MASTER_HOST=…, MASTER_USER=…, …;`	`CHANGE REPLICATION SOURCE TO SOURCE_HOST=…, SOURCE_USER=…, …;`	8.0.23
`SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;` …	`SET GLOBAL SQL_REPLICA_SKIP_COUNTER = N; (?)` …	Not yet …

Dans cet article, la nouvelle terminologie MySQL 8.0.22 est utilisée, en particulier la commande SHOW REPLICA STATUS qui remplace la commande SHOW SLAVE STATUS.

Préparation du serveur source (master, dans l’ancienne terminologie)

Les paramètres suivants sont définis sur le serveur MySQL source :

un identifiant, cet identifiant doit être unique : server-id=1
le format des logs binaires (STATEMENT, MIXED ou ROW) : binlog-format=ROW
le préfixe des fichiers de logs binaires : log-bin=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1
le fichier index des logs binaires : log-bin-index=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.index

$CFG/vpsfrsqlpac1.conf

[mysqld]
server-id=1
binlog-format=ROW
log-bin=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1
log-bin-index=/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.index
innodb-flush-log-at-trx-commit=1
sync-binlog=1

Les 2 derniers paramètres (innodb_flush_log_at_trx_commit et sync_binlog) assurent une meilleure consistence et durabilité dans une réplication impliquant InnoDB et des transactions.

Aucun filtre n’est appliqué sur les logs binaires au niveau du serveur source (binlog-do-db, binlog-ignore-db…), les filtres seront appliqués dans le serveur répliqué. Dans ce cas pratique, la journalisation est globalement activée dans le serveur source en cas de récupération (recovery).

Redémarrer le serveur MySQL.

Avec les paramètres ci-dessus, les fichiers de logs binaires sont localisés dans le répertoire /sqlpac/mysql/binlogs/vpsfrsqlpac1 :

-rw-r----- 1 mysql dba 179 Jan 15 08:27 binlog-vpsfrsqlpac1.000002
-rw-r----- 1 mysql dba 180 Jan 15 08:27 binlog-vpsfrsqlpac1.index
-rw-r----- 1 mysql dba 156 Jan 15 08:27 binlog-vpsfrsqlpac1.000003

Le fichier index n’est qu’un fichier plat listant les logs binaires (chemin + nom de fichier) :

$BINLOGS/binlog-vpsfrsqlpac1.index

/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.000002
/sqlpac/mysql/binlogs/vpsfrsqlpac1/binlog-vpsfrsqlpac1.000003

Vérifier que la variable système skip-networking est à OFF dans le serveur source, sinon le réplica ne peut pas communiquer avec sa source et la réplication échoue. Le thread replication IO se connecte au serveur source uniquement avec le protocole TCP/IP.

(vpsfrsqlpac1) mysql> SHOW VARIABLES LIKE 'skip_networking'
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+

Si il est défini, supprimer le paramètre skip-networking dans le fichier de configuration et redémarrer le serveur MySQL source.

Un user pour la réplication avec le privilège REPLICATION SLAVE est créé dans le serveur source. Le serveur répliqué (thread Replication IO) utilisera ce compte pour se connecter et extraire les informations.

(vpsfrsqlpac1) mysql> CREATE USER 'STANDBY_USER'@'%' IDENTIFIED BY '**********';
(vpsfrsqlpac1) mysql> GRANT REPLICATION SLAVE ON *.* TO 'STANDBY_USER'@'%';
(vpsfrsqlpac1) mysql> FLUSH PRIVILEGES;

Lors de la création du user pour la réplication, vérifier si le nouveau plugin d’authentification caching_sha2_password de MySQL 8 est utilisé. Il a un impact sur la réplication (sujet abordé plus tard dans ce guide).

(vpsfrsqlpac1) mysql> SELECT user, plugin FROM mysql.user WHERE user='standby_user';
+--------------+-----------------------+
| user         | plugin                |
+--------------+-----------------------+
| standby_user | caching_sha2_password |
+--------------+-----------------------+

Snapshot du serveur source

Plusieur méthodes possibles pour synchroniser les données répliquées avec les données sources :

Utilitaire mysqldump
Utilitaire rsync
MySQL Enterprise backup (licence requise)
Commande CLONE, nouvelle fonctionnalité à partir de MySQL 8.0.17 via un plugin. Utilisable uniquement pour les tables InnoDB, l’instance est intégralement clônée : MySQL 8, clônage d’instances pour la réplication avec la commande CLONE

La méthode appropriée va dépendre des tailles des bases de données, si les bases de données source peuvent être mises en lecture seule… Les bases de données sont petites et peuvent être en lecture seule, la méthode mysqldump est ici utilisée.

Le mode lecture seule est appliqué dans le serveur source :

MySQL < 8.0.22 l’instance dans son intégralité est mise en lecture seule	MySQL >= 8.0.22 mode lecture seule par base (nouvelle fonctionnalité)
`(vpsfrsqlpac1) mysql> FLUSH TABLES WITH READ LOCK;`	`(vpsfrsqlpac1) mysql> ALTER DATABASE sqlpac READ ONLY=1; (vpsfrsqlpac1) mysql> ALTER DATABASE airflow READ ONLY=1;`

Le fichier de log binaire courant et la position sont alors extraits avec la commande SHOW MASTER STATUS. Conserver ces informations, elles seront utilisées à l’initialisation de la réplication :

(vpsfrsqlpac1) mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| binlog-vpsfrsqlpac1.000005 |     156  |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+

Dans un autre terminal, les bases de données source sont sauvegardées :

mysql@vpsfrsqlpac1$ mysqldump -uroot -p -S/tmp/vpsfrsqlpac1.sock \
                                 --routines --triggers --events \
                                 --databases sqlpac airflow > bck_sqlpac_airflow.sql

Le mode lecture seule est retiré du serveur source à la fin de la sauvegarde :

MySQL < 8.0.22	MySQL >= 8.0.22
`(vpsfrsqlpac1) mysql> UNLOCK TABLES;`	`(vpsfrsqlpac1) mysql> ALTER DATABASE sqlpac READ ONLY=0; (vpsfrsqlpac1) mysql> ALTER DATABASE airflow READ ONLY=0;`

Les bases de données sont chargées dans le serveur répliqué :

(vpsfrsqlpac2) mysql> source bck_sqlpac_airflow.sql;

Préparation du serveur replica (slave, dans l’ancienne terminologie)

Tout d’abord, vérifier la connexion SQL depuis le serveur répliqué vers le serveur source en utilisant le compte pour la réplication créé précédemment (standby_user):

mysql@vpsfrsqlpac2$ mysql -ustandby_user  -P40008 --protocol=tcp -hvpsfrsqlpac1 -p
mysql>

Les paramètres suivants sont définis sur le serveur MySQL répliqué :

un identifiant (également unique) : server-id=2
le préfixe des fichiers de log de relai : relay-log=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2
le fichier index des logs de relai : relay-log-index=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2.index
skip-slave-start pour éviter un démarrage automatique de la réplication au redémarrage du serveur MySQL (temporaire)

$CFG/vpsfrsqlpac2.conf

[mysqld]
server-id=2
relay-log=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2
relay-log-index=/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-vpsfrsqlpac2.index
skip-slave-start

Redémarrer le serveur répliqué MySQL.

Comme le fichier index des logs binaires dans le serveur source, le fichier index des logs de relai est un fichier plat listant les fichiers de logs de relai (chemin + nom du fichier) :

$RELAYLOGS/binlog-vpsfrsqlpac2.index

/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-srvmystandby.000016
/sqlpac/mysql/relaylogs/vpsfrsqlpac2/relay-srvmystandby.000017

Les informations relatives au serveur source sont alors fournies au serveur répliqué avec la commande CHANGE MASTER : host, port, user, fichier de log binaire, position. Les informations sur le log binaire (nom du fichier, position) sont les valeurs extraites lors de la réalisation du snapshot du serveur source.

MySQL >= 8.0.23

	MySQL >= 8.0.23
`(vpsfrsqlpac2) mysql> CHANGE MASTER TO MASTER_HOST='vpsfrsqlpac1', MASTER_PORT=40008, MASTER_USER='standby_user', MASTER_PASSWORD='*************', MASTER_LOG_FILE='binlog-vpsfrsqlpac1.000005', MASTER_LOG_POS=156, GET_MASTER_PUBLIC_KEY=1;`	`(vpsfrsqlpac2) mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='vpsfrsqlpac1', SOURCE_PORT=40008, SOURCE_USER='standby_user', SOURCE_PASSWORD='*************', SOURCE_LOG_FILE='binlog-vpsfrsqlpac1.000005', SOURCE_LOG_POS=156, GET_SOURCE_PUBLIC_KEY=1;`

(vpsfrsqlpac2) mysql>
                
CHANGE MASTER
   TO MASTER_HOST='vpsfrsqlpac1',
      MASTER_PORT=40008,
      MASTER_USER='standby_user',
      MASTER_PASSWORD='*************',
      MASTER_LOG_FILE='binlog-vpsfrsqlpac1.000005',
      MASTER_LOG_POS=156,
      GET_MASTER_PUBLIC_KEY=1;

(vpsfrsqlpac2) mysql>
                    
CHANGE REPLICATION SOURCE
   TO SOURCE_HOST='vpsfrsqlpac1',
      SOURCE_PORT=40008,
      SOURCE_USER='standby_user',
      SOURCE_PASSWORD='*************',
      SOURCE_LOG_FILE='binlog-vpsfrsqlpac1.000005',
      SOURCE_LOG_POS=156,
      GET_SOURCE_PUBLIC_KEY=1;

Ces informations sont stockées dans la table mysql.slave_master_info.

Le paramètre optionnel GET_MASTER_PUBLIC_KEY (GET_SOURCE_PUBLIC_KEY à partir de MySQL >= 8.0.23) est défini à 1 ici parce que le user de la réplication dans le serveur source (standby_user) a été créé avec le plugin d’authentification caching_sha2_password.

À cette étape, les threads de la réplication ne sont pas démarrés :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State:
                  Source_Host: vpsfrsqlpac1
                  Source_User: standby_user
                  Source_Port: 40008
                Connect_Retry: 60
              Source_Log_File: binlog-vpsfrsqlpac1.000005
          Read_Source_Log_Pos: 156
               Relay_Log_File: relay-vpsfrsqlpac2.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: binlog-vpsfrsqlpac1.000005
           Replica_IO_Running: No
          Replica_SQL_Running: No
                           … : …

Les filtres, si il y en a, sont maintenant appliqués avec la commande CHANGE REPLICATION FILTER (nouvelle commande MySQL 5.7.3). Seules les bases de données sqlpac et airflow sont répliquées dans ce cas pratique :

(vpsfrsqlpac2) mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (sqlpac,airflow);

Ne pas oublier d’ajouter les filtres dans le fichier de configuration du réplica MySQL, sinon au redémarrage du serveur répliqué, les filtres sont perdus.

$CFG/vpsfrsqlpac2.conf

[mysqld]
…
replicate-do-db=sqlpac
replicate-do-db=airflow

Les filtres ne sont pas physiquement stockés dans une table système (mysql.slave_master_info…), ils ne peuvent être définis qu’en tant que paramètres de démarrage ou en utilisant la commande CHANGE REPLICATION FILTER. Bien d’autres options de filtres sont possibles, se référer à la documentation.

Démarrage de la réplication

La réplication est démarrée avec la commande START SLAVE (START REPLICA à partir de MySQL >= 8.0.22):

(vpsfrsqlpac2) mysql> START REPLICA;
          
(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;

Vérifier la réplication et ne pas oublier de retirer le paramètre skip-slave-start dans le fichier de configuration du réplica lorsque la réplication fonctionne correctement.

Erreur de démarrage de la réplication #1 : UUID identique, error 13117

La réplication ne fonctionne pas immédiatement, l’erreur 13117 est levée :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
                       Replica_IO_State:
                           … : …
                Last_IO_Errno: 13117
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
                               these UUIDs must be different for replication to work.

Dans ce cas pratique, le répertoire des données MySQL a été copié de la source vers le réplica, et par conséquent dans le fichier auto.cnf, l’identifiant UUID du serveur est identique :

auto.cnf

 [auto]
server-uuid=a7ce69c7-55fd-11eb-ab71-fa163e1f3eb9

Pour résoudre le problème, le fichier auto.cnf est supprimé dans le répertoire des données du serveur répliqué et le serveur répliqué est redémarré : un nouvel identifiant UUID est généré.

$LOG/vpsfrsqlpac2.log

2021-01-15T09:53:40.716121Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found,
so we assume that this is the first time that this server has been started.
Generating a new UUID: 465cc5e1-5795-11eb-9471-fa163e0fd563.

Erreur de démarrage de la réplication #2 : plugin d’authentification caching_sha2_password, erreur 2061

Si le user pour la réplication dans le serveur source a été défini avec le plugin d’authentification caching_sha2_password et que le paramètre GET_MASTER_PUBLIC_KEY=1 a été omise dans la commande CHANGE MASTER, le statut du thread de réplication IO peut afficher "Connecting to master" avec l’erreur 2601 comme dernière erreur :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State: Connecting to master
                           … : …
                Last_IO_Errno: 2061
                Last_IO_Error: error connecting to master 'standby_user@vpsfrsqlpac1:40008' - retry-time: 60 retries: 8
                               message: Authentication plugin 'caching_sha2_password'
                               reported error: Authentication requires secure connection.

Cette erreur typique se produit lorsque le cache du plugin caching_sha2_password a été vidé dans le serveur source (redémarrage…).

Pour appliquer le paramètre GET_MASTER_PUBLIC_KEY=1 (GET_SOURCE_PUBLIC_KEY=1) si il a été oublié à l’initialisation :

	MySQL >= 8.0.23
`(vpsfrsqlpac2) mysql> STOP REPLICA; CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1; START REPLICA;`	`(vpsfrsqlpac2) mysql> STOP REPLICA; CHANGE REPLICATION SOURCE TO GET_SOURCE_PUBLIC_KEY=1; START REPLICA;`

Démarrage de la réplication réussi

(vpsfrsqlpac2) mysql> START REPLICA;          
(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State: Waiting for master to send event
                  Source_Host: vpsfrsqlpac1
                  Source_User: standby_user
                  Source_Port: 40008
                Connect_Retry: 60
              Source_Log_File: binlog-vpsfrsqlpac1.000005
          Read_Source_Log_Pos: 156
               Relay_Log_File: relay-vpsfrsqlpac2.000003
                Relay_Log_Pos: 333
        Relay_Source_Log_File: binlog-vpsfrsqlpac1.000005
             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
              Replicate_Do_DB: sqlpac,airflow
                           … : …
    Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates

Un serveur source enregistre-t-il les serveurs répliqués ? SHOW SLAVE HOSTS | SHOW REPLICAS command

NON. Garder à l’esprit qu’aucune information relative aux serveurs répliqués n’est stockée dans un serveur source (tables, fichiers de configuration…), La commande SHOW SLAVE HOSTS dans un serveur source prête à confusion (SHOW REPLICAS à partir de MySQL 8.0.22). Cette commande affiche un réplica uniquement si ce réplica est connecté au serveur source (Thread BinLog dump créé).

(vpsfrsqlpac1) mysql> SHOW REPLICAS;
+-----------+------+-------+-----------+---------------------------------------+
| Server_id | Host | Port  | Source_id | Replica_UUID                          |
+-----------+------+-------+-----------+---------------------------------------+
|         2 |      | 40008 |         1 | 465cc5e1-5795-11eb-9471-fa163e0fd563  |
+-----------+------+-------+-----------+---------------------------------------+

Gestion des erreurs

Sauter des transactions (skip)

La commande SHOW REPLICA STATUS donne tous les détails lorsque des erreurs de réplication se produisent :

(vpsfrsqlpac2) mysql> SHOW REPLICA STATUS \G;
             Replica_IO_State: Waiting for master to send event
                           … : …
               Relay_Log_File: relay-vpsfrsqlpac2.000017
                Relay_Log_Pos: 333
                           … : …
           Replica_IO_Running: Yes
          Replica_SQL_Running: No
                           … : …
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'sqlpac.heartbeat'' on query. Default database: 'sqlpac'.
                               Query: 'DROP TABLE `heartbeat` /* generated by server */'

L’utilitaire mysqlbinlog peut aussi être utilisé pour traduire les fichers de log de relai, les fichiers de relai sont des fichiers de log binaires. Pour l’erreur ci-dessus (position : 333, fichier de log de relai : relay-vpsfrsqlpac2.000017) :

mysql@vpsfrsqlpac2$ mysqlbinlog -j 333 $RELAYLOGS/relay-vpsfrsqlpac2.000017
# at 333
…
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 410
…
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `heartbeat` /* generated by server */
/*!*/;
…

La position spécifiée par Relay_Log_Pos (333) est la position courante dans le fichier de log de relai. Cette position est celle du dernier événement de réplication réussi, l’événement répliqué en erreur est dans la position qui suit (# at 410).

La commande SQL SHOW RELAYLOG EVENTS dans le serveur répliqué est plus pratique que l’utilitaire mysqlbinlog :

(vpsfrsqlpac2) mysql> SHOW RELAYLOG EVENTS IN 'relay-vpsfrsqlpac2.000017' FROM 333 LIMIT 3;
+---------------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------+
| Log_name                  | Pos | Event_type     | Server_id | End_log_pos | Info                                                                         |
+---------------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------+
| relay-vpsfrsqlpac2.000017 | 333 | Anonymous_Gtid |         1 |         733 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                         |
| relay-vpsfrsqlpac2.000017 | 410 | Query          |         1 |         871 | use `sqlpac`; DROP TABLE `heartbeat` /* generated by server */ /* xid=158 */ |
| relay-vpsfrsqlpac2.000017 | 548 | Anonymous_Gtid |         1 |         948 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                         |
+---------------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------+

Quand on est sûr de pouvoir sauter des transactions en erreur, utiliser SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n avant de redémarrer la réplication, avec n correspondant au nombre de transactions à sauter :


(vpsfrsqlpac2) mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
(vpsfrsqlpac2) mysql> START REPLICA;

Ignorer des numéros d’erreurs

Des numéros d’erreur peuvent être ignorées automatiquement avec le paramètre slave-skip-errors. À utiliser avec précaution bien entendu.

[mysqld]
…
slave-skip-errors=1051,1054

À partir de MySQL 5.6, la valeur abrégée bien pratique ddl_exists_error a été introduite, valeur équivalente à la liste de codes erreur 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

[mysqld]
…
slave-skip-errors=ddl_exists_error

Désactiver la réplication dans une session

Pour diverses raisons, on a parfois besoin de désactiver temporairement la réplication dans une session SQL sur le serveur source. Utiliser la commande SET SQL_LOG_BIN = [OFF|ON] pour activer/désactiver la réplication dans un session. Lorsque SQL_LOG_BIN est à OFF, la journalisation dans les logs binaires pour la session courante est tout simplement désactivée. Évidemment, le privilège le plus élevé SYSTEM_VARIABLES_ADMIN est requis (anciennement le privilège SUPER).

(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=OFF;
(vpsfrsqlpac1) mysql> …
(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=ON;

Mode Read only dans les bases répliquées (MySQL 8.0.22)

À partir de MySQL 8.0.22, l’option lecture seule par base de données est désormais disponible, fonctionnalité attendue depuis longtemps. Le mode read only n’affecte pas les threads SQL de réplication.

(vpsfrsqlpac2) mysql> ALTER DATABASE sqlpac READ ONLY = 1;
(vpsfrsqlpac2) mysql> ALTER DATABASE airflow READ ONLY = 1;
        
(vpsfrsqlpac2) mysql> SHOW CREATE DATABASE sqlpac;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                 |
+----------+-------------------------------------------------------------------------------------------------+
| sqlpac   | CREATE DATABASE `sqlpac` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */   |
|          |                          /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */                 |
+----------+-------------------------------------------------------------------------------------------------+

Lors de l’activation/désactivation du mode read only pour une base de données dans le serveur source, ne pas oublier de désactiver la réplication : l’instruction ALTER DATABASE est répliquée selon les filtres de réplication appliqués précédemment.

(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=0;
(vpsfrsqlpac1) mysql> ALTER DATABASE sqlpac READ ONLY = 0;
(vpsfrsqlpac1) mysql> SET SQL_LOG_BIN=1;