The Ultimate Guide to High Availability with MariaDB

Eseguire i trigger nello slave per gli eventi Row-based

Stai visualizzando una vecchia versione di questo article. Visualizza la versione più recente.

Contents

  1. Eseguire i trigger nello slave
  2. Quando utilizzare slave_run_triggers_for_rbr
    1. Background
    2. Target usecase
  3. Preventing multiple trigger invocations
  4. See also

Eseguire i trigger nello slave

A partire da MariaDB 10.1.1, è possibile forzare lo slave thread ad eseguire i trigger per gli eventi del binlog row-based.

Questa impostazione dipende dalla variabile globale slave_run_triggers_for_rbr. E' anche possibile specificarla da riga di comando o nel file my.cnf.

I valori possibili sono:

ValoreSignificato
NO (Predefinito)Non invocare i trigger per gli eventi row-based
YESInvocare i trigger per gli eventi row-based, ma non scriverli nel binary log
LOGGINGInvocare i trigger pe gli eventi row-based e scriverli nel binary log

Si noti che se si desidera soltanto usare i trigger con la replica, questa opzione non è necessaria. Ulteriori dettagli, più avanti nella pagina.

Quando utilizzare slave_run_triggers_for_rbr

Background

Normalmente, la replica di MySQL può replicare automaticamente le azioni eseguite dai trigger.

  • Quando si utilizza la replica statement-based, il binary log contiene le istruzioni SQL. Gli slave server le eseguono. I trigger vengono eseguiti nel master e su tutti gli slave, in modo indipendente.
  • Quando si utilizza la replica row-based, il binary log contiene le modifiche alle righe. Contiene sia le modifiche effettuate direttamente dalle istruzioni SQL, sia quelle effettuate dai trigger che sono stati invocati dalle istruzioni. Gli slave non eseguiranno i trigger.

Target usecase

One may want to have a setup where a slave has triggers that are not present on the master (Suppose the slave needs to update summary tables or perform some other ETL-like process).

If one uses statement-based replication, they can just create the required triggers on the slave. The slave will run the statements from the binary log, which will cause the triggers to be invoked.

However, there are cases where you have to use row-based replication. It could be because the master runs non-deterministic statements, or the master could be a node in a Galera cluster. In that case, you would want row-based events to invoke triggers on the slave. This is what the slave_run_triggers_for_rbr option is for. Setting the option to YES will cause the SQL slave thread to invoke triggers for row based events; setting it to LOGGING will also cause the changes made by the triggers to be written into the binary log.

The following triggers are invoked:

  • Update_row_event runs an UPDATE trigger
  • Delete_row_event runs a DELETE trigger
  • Write_row_event action depends on whether the operation will require foreign key checks:
    • when FK checks are not necessary, the operation will invoke a DELETE trigger if the record to be modified existed in the table. After that, an INSERT trigger will be invoked.
    • when FK checks are necessary, either an UPDATE or or a combination of DELETE and INSERT triggers will be invoked.

Preventing multiple trigger invocations

There is a basic protection against triggers being invoked both on the master and slave. If the master modifies a table that has triggers, it will produce row-based binlog events with the "triggers were invoked for this event" flag. The slave will not invoke any triggers for flagged events.

See also

Commenti

Sto caricando i commenti......
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.