The Ultimate Guide to High Availability with MariaDB

Annotate_rows_log_event

Contents

  1. Esempio di Annotate_rows
  2. Opzioni relative a Annotate_rows_log_event
    1. Opzione del master: --binlog-annotate-row-events
    2. Opzione dello slave: --replicate-annotate-row-events
    3. Opzione di mysqlbinlog: --skip-annotate-row-events
  3. Esempio di output di mysqlbinlog
  4. Vedi anche

Gli eventi Annotate_rows accompagnano gli eventi row e descrivono la query che ha causato gli eventi di riga.

Il tipo di eventi di binlog Annotate_rows_log_event è stato introdotto in MariaDB 5.3. E' disattivato per default (per non modificare il formato del log binario e per permettere di replicare MariaDB 5.3 su MySQL/MariaDB 5.1). E' possibile abilitarlo con l'opzione --binlog-annotate-row-events.

Nel log binario, ogni evento Annotate_rows precede l'evento Table map corrispondente, oppure il primo evento Table map, se ne esiste più di uno (per esempio nel caso di una delete multitabella o una insert delayed).

Si veda MWL#47 per ulteriori informazioni sul progetto di questa funzionalità.

Esempio di Annotate_rows

master> DROP DATABASE IF EXISTS test;
master> CREATE DATABASE test;
master> USE test;
master> CREATE TABLE t1(a int);
master> INSERT INTO t1 VALUES (1), (2), (3);
master> CREATE TABLE t2(a int);
master> INSERT INTO t2 VALUES (1), (2), (3);
master> CREATE TABLE t3(a int);
master> INSERT DELAYED INTO t3 VALUES (1), (2), (3);
master> DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
    ->        WHERE t1.a=t2.a AND t2.a=t3.a;  
    
master> SHOW BINLOG EVENTS IN 'master-bin.000001';  
+-------------------+------+---------------+-----------+-------------+----------------------------------------------------------------+
| Log_name          | Pos  | Event_type    | Server_id | End_log_pos | Info                                              |
+-------------------+------+---------------+-----------+-------------+----------------------------------------------------------------+
| master-bin.000001 |    4 | Format_desc   |       100 |         240 | Server ver: 5.5.20-MariaDB-mariadb1~oneiric-log, Binlog ver: 4       |
| master-bin.000001 |  240 | Query         |       100 |         331 | DROP DATABASE IF EXISTS test                      |
| master-bin.000001 |  331 | Query         |       100 |         414 | CREATE DATABASE test                              |
| master-bin.000001 |  414 | Query         |       100 |         499 | use `test`; CREATE TABLE t1(a int)                |
| master-bin.000001 |  499 | Query         |       100 |         567 | BEGIN                                             |
| master-bin.000001 |  567 | Annotate_rows |       100 |         621 | INSERT INTO t1 VALUES (1), (2), (3)               |
| master-bin.000001 |  621 | Table_map     |       100 |         662 | table_id: 16 (test.t1)                            |
| master-bin.000001 |  662 | Write_rows    |       100 |         706 | table_id: 16 flags: STMT_END_F                    |
| master-bin.000001 |  706 | Query         |       100 |         775 | COMMIT                                            |
| master-bin.000001 |  775 | Query         |       100 |         860 | use `test`; CREATE TABLE t2(a int)                |
| master-bin.000001 |  860 | Query         |       100 |         928 | BEGIN                                             |
| master-bin.000001 |  928 | Annotate_rows |       100 |         982 | INSERT INTO t2 VALUES (1), (2), (3)               |
| master-bin.000001 |  982 | Table_map     |       100 |        1023 | table_id: 17 (test.t2)                            |
| master-bin.000001 | 1023 | Write_rows    |       100 |        1067 | table_id: 17 flags: STMT_END_F                    |
| master-bin.000001 | 1067 | Query         |       100 |        1136 | COMMIT                                            |
| master-bin.000001 | 1136 | Query         |       100 |        1221 | use `test`; CREATE TABLE t3(a int)                |
| master-bin.000001 | 1221 | Query         |       100 |        1289 | BEGIN                                             |
| master-bin.000001 | 1289 | Annotate_rows |       100 |        1351 | INSERT DELAYED INTO t3 VALUES (1), (2), (3)       |
| master-bin.000001 | 1351 | Table_map     |       100 |        1392 | table_id: 18 (test.t3)                            |
| master-bin.000001 | 1392 | Write_rows    |       100 |        1426 | table_id: 18 flags: STMT_END_F                    |
| master-bin.000001 | 1426 | Table_map     |       100 |        1467 | table_id: 18 (test.t3)                            |
| master-bin.000001 | 1467 | Write_rows    |       100 |        1506 | table_id: 18 flags: STMT_END_F                    |
| master-bin.000001 | 1506 | Query         |       100 |        1575 | COMMIT                                            |
| master-bin.000001 | 1575 | Query         |       100 |        1643 | BEGIN                                             |
| master-bin.000001 | 1643 | Annotate_rows |       100 |        1748 | DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 
       WHERE t1.a=t2.a AND t2.a=t3.a                                                                                     |
| master-bin.000001 | 1748 | Table_map     |       100 |        1789 | table_id: 16 (test.t1)                            |
| master-bin.000001 | 1789 | Table_map     |       100 |        1830 | table_id: 17 (test.t2)                            |
| master-bin.000001 | 1830 | Delete_rows   |       100 |        1874 | table_id: 16                                      |
| master-bin.000001 | 1874 | Delete_rows   |       100 |        1918 | table_id: 17 flags: STMT_END_F                    |
| master-bin.000001 | 1918 | Query         |       100 |        1987 | COMMIT                                            |
+-------------------+------+---------------+-----------+-------------+----------------------------------------------------------------+

Opzioni relative a Annotate_rows_log_event

Per controllare il comportamento di Annotate_rows_log_event, sono state aggiunte le seguenti opzioni:

Opzione del master: --binlog-annotate-row-events

Questa opzione dice al master di scrivere gli eventi Annotate_rows nel log binario.

  • Nome variabile: binlog_annotate_row_events
  • Contesto: Globale e sessione
  • Tipo di accesso: Dinamico
  • Tipo di dato: bool
  • Valore predefinito: OFF

I valori di sessione permettono di annotare solo le istruzioni desiderate:

...
SET SESSION binlog_annotate_row_events=ON;
... istruzioni da annotare ...
SET SESSION binlog_annotate_row_events=OFF;
... istruzioni da non annotare ...

Si veda Elenco completo delle opzioni di mysqld.

Opzione dello slave: --replicate-annotate-row-events

Questa opzione dice allo slave di replicare nel proprio log binario gli eventi Annotate_row ricevuti dal master (utile solo se usato in coppia con l'opzione log-slave-updates).

  • Nome variabile: replicate_annotate_row_events
  • Contesto: Globale
  • Tipo di accesso: Sola lettura (solo nel file di configurazione)
  • Tipo di dato: bool
  • Valore predefinito: OFF

Si veda Elenco completo delle opzioni di mysqld.

Opzione di mysqlbinlog: --skip-annotate-row-events

Questa opzione dice a mysqlbinlog di ignorare tutti gli eventi Annotate_row nel suo output output (per default mysqlbinlog stampa gli eventi Annotate_row, se il log binario li contiene).

In mysqlbinlog si può trovare un elenco di tutte le opzioni di mysqlbinlog.

Esempio di output di mysqlbinlog

...> mysqlbinlog.exe -vv -R --user=root --port=3306 --host=localhost master-bin.000001  

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100516 15:36:00 server id 100  end_log_pos 240         Start: binlog v 4, server v 5.1.44-debug-log created 100516
 15:36:00 at startup
ROLLBACK/*!*/;
BINLOG '
oNjvSw9kAAAA7AAAAPAAAAAAAAQANS4xLjQ0LWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACg2O9LEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAA=
'/*!*/;
# at 240
#100516 15:36:18 server id 100  end_log_pos 331         Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1
/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP DATABASE IF EXISTS test
/*!*/;
# at 331
#100516 15:36:18 server id 100  end_log_pos 414         Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
CREATE DATABASE test
/*!*/;
# at 414
#100516 15:36:18 server id 100  end_log_pos 499         Query   thread_id=1     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1274009778/*!*/;
CREATE TABLE t1(a int)
/*!*/;
# at 499
#100516 15:36:18 server id 100  end_log_pos 567         Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
BEGIN
/*!*/;
# at 567
# at 621
# at 662
#100516 15:36:18 server id 100  end_log_pos 621         Annotate_rows:
#Q> INSERT INTO t1 VALUES (1), (2), (3)  
#100516 15:36:18 server id 100  end_log_pos 662         Table_map: `test`.`t1` mapped to number 16
#100516 15:36:18 server id 100  end_log_pos 706         Write_rows: table id 16 flags: STMT_END_F

BINLOG '
stjvSxNkAAAAKQAAAJYCAAAAABAAAAAAAAAABHRlc3QAAnQxAAEDAAE=
stjvSxdkAAAALAAAAMICAAAQABAAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
### INSERT INTO test.t1
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO test.t1
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO test.t1
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 706
#100516 15:36:18 server id 100  end_log_pos 775         Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
COMMIT
/*!*/;
# at 775
#100516 15:36:18 server id 100  end_log_pos 860         Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
CREATE TABLE t2(a int)
/*!*/;
# at 860
#100516 15:36:18 server id 100  end_log_pos 928         Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
BEGIN
/*!*/;
# at 928
# at 982
# at 1023
#100516 15:36:18 server id 100  end_log_pos 982         Annotate_rows:
#Q> INSERT INTO t2 VALUES (1), (2), (3)  
#100516 15:36:18 server id 100  end_log_pos 1023        Table_map: `test`.`t2` mapped to number 17
#100516 15:36:18 server id 100  end_log_pos 1067        Write_rows: table id 17 flags: STMT_END_F

BINLOG '
stjvSxNkAAAAKQAAAP8DAAAAABEAAAAAAAAABHRlc3QAAnQyAAEDAAE=
stjvSxdkAAAALAAAACsEAAAQABEAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
### INSERT INTO test.t2
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO test.t2
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO test.t2
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1067
#100516 15:36:18 server id 100  end_log_pos 1136        Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
COMMIT
/*!*/;
# at 1136
#100516 15:36:18 server id 100  end_log_pos 1221        Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
CREATE TABLE t3(a int)
/*!*/;
# at 1221
#100516 15:36:18 server id 100  end_log_pos 1289        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
BEGIN
/*!*/;
# at 1289
# at 1351
# at 1392
#100516 15:36:18 server id 100  end_log_pos 1351        Annotate_rows:
#Q> INSERT DELAYED INTO t3 VALUES (1), (2), (3)  
#100516 15:36:18 server id 100  end_log_pos 1392        Table_map: `test`.`t3` mapped to number 18
#100516 15:36:18 server id 100  end_log_pos 1426        Write_rows: table id 18 flags: STMT_END_F

BINLOG '
stjvSxNkAAAAKQAAAHAFAAAAABIAAAAAAAAABHRlc3QAAnQzAAEDAAE=
stjvSxdkAAAAIgAAAJIFAAAQABIAAAAAAAEAAf/+AQAAAA==
'/*!*/;
### INSERT INTO test.t3
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 1426
# at 1467
#100516 15:36:18 server id 100  end_log_pos 1467        Table_map: `test`.`t3` mapped to number 18
#100516 15:36:18 server id 100  end_log_pos 1506        Write_rows: table id 18 flags: STMT_END_F

BINLOG '
stjvSxNkAAAAKQAAALsFAAAAABIAAAAAAAAABHRlc3QAAnQzAAEDAAE=
stjvSxdkAAAAJwAAAOIFAAAQABIAAAAAAAEAAf/+AgAAAP4DAAAA
'/*!*/;
### INSERT INTO test.t3
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO test.t3
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1506
#100516 15:36:18 server id 100  end_log_pos 1575        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
COMMIT
/*!*/;
# at 1575
#100516 15:36:18 server id 100  end_log_pos 1643        Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
BEGIN
/*!*/;
# at 1643
# at 1748
# at 1789
# at 1830
# at 1874
#100516 15:36:18 server id 100  end_log_pos 1748        Annotate_rows:
#Q> DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
#Q>        WHERE t1.a=t2.a AND t2.a=t3.  
#100516 15:36:18 server id 100  end_log_pos 1789        Table_map: `test`.`t1` mapped to number 16
#100516 15:36:18 server id 100  end_log_pos 1830        Table_map: `test`.`t2` mapped to number 17
#100516 15:36:18 server id 100  end_log_pos 1874        Delete_rows: table id 16
#100516 15:36:18 server id 100  end_log_pos 1918        Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
stjvSxNkAAAAKQAAAP0GAAAAABAAAAAAAAAABHRlc3QAAnQxAAEDAAE=
stjvSxNkAAAAKQAAACYHAAAAABEAAAAAAAAABHRlc3QAAnQyAAEDAAE=
stjvSxlkAAAALAAAAFIHAAAAABAAAAAAAAAAAf/+AQAAAP4CAAAA/gMAAAA=
### DELETE FROM test.t1
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM test.t1
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM test.t1
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
stjvSxlkAAAALAAAAH4HAAAQABEAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
### DELETE FROM test.t2
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM test.t2
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM test.t2
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1918
#100516 15:36:18 server id 100  end_log_pos 1987        Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1274009778/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Vedi anche

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.