The Ultimate Guide to High Availability with MariaDB

I formati del Log Binario

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

Contents

  1. Formati
    1. Statement-based
    2. Row-based
    3. Mixed
  2. Setting the binary log format
    1. Examples:
  3. The mysql database

Formati

Il Log Binario ha tre formati.

Statement-based

E' il formato predefinito, il logging statement-based registra tutte le istruzioni SQL che apportano modifiche ai dati o alle strutture delle tabelle. Per abilitarlo: --binlog-format=STATEMENT.

In alcuni casi, un'istruzione può non essere deterministica, e quindi non essere sicura per la replica. Se MariaDB decide che è così, produce un warning Statement may not be safe to log in statement format. Si veda la sezione Mixed sotto, per una lista di casi in cui si applica questa regola.

Row-based

Registra gli eventi che coinvolgono le righe delle tabelle. Per abilitarlo: --binlog-format=ROW.

Mixed

Una combinazione del formato statement-based e quello row-based. Nel formato mixed, per default viene usato il logging statement-based, ma quando MariaDB determina che non è sicuro per la replica registrare una data istruzione in quel formato, utilizza il formato row-based. Per abilitarlo: --binlog-format=MIXED.

Il formato statement-based non è sicuro nei seguenti casi:

  • Istruzioni INSERT-DELAYED.
  • Quando una tabella con una colonna AUTO_INCREMENT viene aggiornata e vi è un trigger o si usa una Stored Function.
  • La funzione LOAD_FILE().
  • Vengono usate le funzioni ROW_COUNT() o FOUND_ROWS().
  • Vengono usate le funzioni USER() o CURRENT_USER().
  • Viene usata la funzione UUID().
  • Una delle tabelle usate nell'istruzione è un log che si trova nel database mysql.
  • Un'istruzione fa riferimento a una system variable (alcune costituiscono eccezione se usate solo nel contesto della sessione).
  • Viene usata una user-defined function.
  • If row-based logging is used for a statement, and the session executing the statement has any temporary tables, row-based logging is used for the remaining statements until the temporary table is dropped. This is because temporary tables can't use row-based logging, so if it is used due to one of the above conditions, all subsequent statements using that table are unsafe. The server deals with this situation by treating all statements in the session as unsafe for statement-based logging until the temporary table is dropped.

A storage engine can also set or limit the logging format, which helps reduce errors with replicating between masters and slaves with different storage engines.

Setting the binary log format

The default binary log format is statement-based. It can be set at runtime with --binlog-format=format, and can also be changed by setting the global (with the SUPER privilege) or session value of the binlog_format server variable.

Be careful changing the binary log format when using replication. The binary log format can only be set by the server for itself. Changing the binary log format on a master has no affect on the slave's binary log format. This can cause replication to give inconsistent results or to fail.

Although you should rarely need to change the binary logging format, you may want to in the following cases:

  • If one, or a few, statements update many rows, statement-logging will be more efficient. Since this situation is common, statement-based logging is the default.
  • Many statements that ultimately only have a small impact on the table rows may also be more efficiently logged with row-based logging.
  • A statement may take a long time to execute, but ultimately only have a small impact on the table rows, so replication with row-based logging would be more efficient.

Examples:

MariaDB [(none)]> SET GLOBAL binlog_format=ROW;

MariaDB [(none)]> SET SESSION binlog_format=MIXED;

MariaDB [(none)]> SET binlog_format=STATEMENT;

The mysql database

Statements that affect the mysql database can be logged in a different way to that expected.

If the mysql database is edited directly, logging is performed as expected according to the binlog_format. Statements that directly edit the mysql database include INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, and TRUNCATE TABLE.

If the mysql database is edited indirectly, statement logging is used regardless of binlog_format setting. Statements editing the mysql database indirectly include GRANT, REVOKE, SET PASSWORD, RENAME USER, ALTER, DROP and CREATE (except for the situation described below).

CREATE TABLE ... SELECT can use a combination of logging formats. The CREATE TABLE portion of the statement is logged using statement-based logging, while the SELECT portion is logged according to the value of binlog_format.

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.