Rechercher dans le manuel MySQL

17.1.5.3 Disabling GTID Transactions Online

Table des matières

This section describes how to disable GTID transactions on servers that are already online. This procedure does not require taking the server offline and is suited to use in production. However, if you have the possibility to take the servers offline when disabling GTIDs mode that process is easier.

The process is similar to enabling GTID transactions while the server is online, but reversing the steps. The only thing that differs is the point at which you wait for logged transactions to replicate.

Before you start, ensure that the servers meet the following pre-conditions:

  • All servers in your topology must use MySQL 5.7.6 or later. You cannot disable GTID transactions online on any single server unless all servers which are in the topology are using this version.

  • All servers have gtid_mode set to ON.

  • The --replicate-same-server-id option is not set on any server. You cannot disable GTID transactions if this option is set together with the --log-slave-updates option (which is the default) and binary logging is enabled (which is also the default). Without GTIDs, this combination of options causes infinite loops in circular replication.

  1. Execute the following on each slave, and if you using multi-source replication, do it for each channel and include the FOR CHANNEL channel clause:

    1. STOP SLAVE [FOR CHANNEL 'channel'];
    2. CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, \
    3. MASTER_LOG_POS = position [FOR CHANNEL 'channel'];
    4. START SLAVE [FOR CHANNEL 'channel'];

  2. On each server, execute:

    1. SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

  3. On each server, execute:

    1. SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

  4. On each server, wait until the variable @@GLOBAL.GTID_OWNED is equal to the empty string. This can be checked using:

    1. SELECT @@GLOBAL.GTID_OWNED;

    On a replication slave, it is theoretically possible that this is empty and then nonempty again. This is not a problem, it suffices that it is empty once.

  5. Wait for all transactions that currently exist in any binary log to replicate to all slaves. See Section 17.1.5.4, “Verifying Replication of Anonymous Transactions” for one method of checking that all anonymous transactions have replicated to all servers.

  6. If you use binary logs for anything else than replication, for example to do point in time backup or restore: wait until you do not need the old binary logs having GTID transactions.

    For instance, after step 5 has completed, you can execute FLUSH LOGS on the server where you are taking the backup. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

    Ideally, wait for the server to purge all binary logs that existed when step 5 was completed. Also wait for any backup taken before step 5 to expire.

    Important

    This is the one important point during this procedure. It is important to understand that logs containing GTID transactions cannot be used after the next step. Before proceeding you must be sure that GTID transactions do not exist anywhere in the topology.

  7. On each server, execute:

    1. SET @@GLOBAL.GTID_MODE = OFF;

  8. On each server, set gtid_mode=OFF in my.cnf.

    If you want to set enforce_gtid_consistency=OFF, you can do so now. After setting it, you should add enforce_gtid_consistency=OFF to your configuration file.

If you want to downgrade to an earlier version of MySQL, you can do so now, using the normal downgrade procedure.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replication-mode-change-online-disable-gtids.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut