5-Replica Registration

Details the initialization phase where a replica connects to the primary, authenticates, sends capabilities, and registers for updates.

Overview

The replica server, when properly configured with CHANGE MASTER TO ... , can start MariaDB replication with the START REPLICA statement.

After authentication, some COM_QUERY packets are exchanged before sending COM_REGISTER_SLAVE and COM_BINLOG_DUMP.

The following COM_QUERY packets come from MariaDB 10.X replicas using GTID:

SELECT UNIX_TIMESTAMP();
SHOW VARIABLES LIKE 'SERVER_ID';
SET @master_heartbeat_period= 30000001024;
SET @master_binlog_checksum= @@global.binlog_checksum;
SELECT @master_binlog_checksum;
SET @mariadb_slave_capability=4;
SELECT @@GLOBAL.gtid_domain_id GTID registration: domain ID;
SET @slave_connect_state='0-10201-9868' GTID registration: the requested GTID;
SET @slave_gtid_strict_mode=0 GTID registration: strict_mode;
SET @slave_gtid_ignore_duplicates=0 GTID registration: ignore_duplicates.

Then COM_REGISTER_SLAVE completes the registration.

The COM_BINLOG_DUMP marks the request of binlog events stream.

If semi-sync is in use, the request for the network protocol change is sent between COM_REGISTER_SLAVE and COM_BINLOG_DUMP.

Example Using 'ngrep'

COM_REGISTER_SLAVE, Semi-Sync and COM_BINLOG_DUMP:

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  1a 00 00 00 15 75 27 00    00 08 53 42 73 6c 61 76    .....u'...SBslav
  65 31 00 00 c9 5a 00 00    00 00 00 00 00 00          e1...Z........  

T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  33 00 00 00 03 53 48 4f    57 20 56 41 52 49 41 42    3....SHOW VARIAB
  4c 45 53 20 4c 49 4b 45    20 27 72 70 6c 5f 73 65    LES LIKE 'rpl_se
  6d 69 5f 73 79 6e 63 5f    6d 61 73 74 65 72 5f 65    mi_sync_master_e
  6e 61 62 6c 65 64 27                                  nabled'         

T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
  01 00 00 01 02 64 00 00    02 03 64 65 66 12 69 6e    .....d....def.in
  66 6f 72 6d 61 74 69 6f    6e 5f 73 63 68 65 6d 61    formation_schema
  11 53 45 53 53 49 4f 4e    5f 56 41 52 49 41 42 4c    .SESSION_VARIABL
  45 53 11 53 45 53 53 49    4f 4e 5f 56 41 52 49 41    ES.SESSION_VARIA
  42 4c 45 53 0d 56 61 72    69 61 62 6c 65 5f 6e 61    BLES.Variable_na
  6d 65 0d 56 41 52 49 41    42 4c 45 5f 4e 41 4d 45    me.VARIABLE_NAME
  0c 08 00 40 00 00 00 fd    01 00 00 00 00 5d 00 00    ...@.........]..
  03 03 64 65 66 12 69 6e    66 6f 72 6d 61 74 69 6f    ..def.informatio
  6e 5f 73 63 68 65 6d 61    11 53 45 53 53 49 4f 4e    n_schema.SESSION
  5f 56 41 52 49 41 42 4c    45 53 11 53 45 53 53 49    _VARIABLES.SESSI
  4f 4e 5f 56 41 52 49 41    42 4c 45 53 05 56 61 6c    ON_VARIABLES.Val
  75 65 0e 56 41 52 49 41    42 4c 45 5f 56 41 4c 55    ue.VARIABLE_VALU
  45 0c 08 00 00 08 00 00    fd 01 00 00 00 00 05 00    E...............
  00 04 fe 00 00 22 00 20    00 00 05 1c 72 70 6c 5f    .....". ....rpl_
  73 65 6d 69 5f 73 79 6e    63 5f 6d 61 73 74 65 72    semi_sync_master
  5f 65 6e 61 62 6c 65 64    02 4f 4e 05 00 00 06 fe    _enabled.ON.....
  00 00 22 00                                           ..". 

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  1c 00 00 00 03 53 45 54    20 40 72 70 6c 5f 73 65    .....SET @rpl_se
  6d 69 5f 73 79 6e 63 5f    73 6c 61 76 65 3d 20 31    mi_sync_slave= 1

T 127.0.0.1:23240 -> 127.0.0.1:42158 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42158 -> 127.0.0.1:23240 [AP]
  1b 00 00 00 12 34 06 00    00 02 00 75 27 00 00 6d    .....4.....u'..m
  79 73 71 6c 2d 62 69 6e    2e 30 30 30 30 33 34       ysql-bin.000034

In the example, we see that these two COM_QUERY commands are sent just after COM_REGISTER_SLAVE and before COM_BINLOG_DUMP:

SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled';
SET @rpl_semi_sync_slave= 1.

Complete Example with GTID Registration

The example shows output up to COM_BINLOG_DUMP request, No Semi-Sync:

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  5d 00 00 00 0a 35 2e 35    2e 35 2d 31 30 2e 32 2e    ]....5.5.5-10.2.
  31 30 2d 4d 61 72 69 61    44 42 2d 6c 6f 67 00 22    10-MariaDB-log."
  00 00 00 7d 2e 6a 4f 2c    2c 36 6a 00 fe f7 08 02    ...}.jO,,6j.....
  00 bf 81 15 00 00 00 00    00 00 07 00 00 00 38 74    ..............8t
  60 64 54 59 44 28 38 24    48 7c 00 6d 79 73 71 6c    `dTYD(8$H|.mysql
  5f 6e 61 74 69 76 65 5f    70 61 73 73 77 6f 72 64    _native_password
  00                                                    .               

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  a9 00 00 01 05 a2 38 80    70 03 00 40 08 00 00 00    ......8.p..@....
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 6d 73 61 6e    64 62 6f 78 00 14 52 42    ....msandbox..RB
  0b e8 ae 56 ec ff ef 1f    1f 14 51 1d 4a 47 f4 32    ...V......Q.JG.2
  56 74 6d 79 73 71 6c 5f    6e 61 74 69 76 65 5f 70    Vtmysql_native_p
  61 73 73 77 6f 72 64 00    54 03 5f 6f 73 05 4c 69    assword.T._os.Li
  6e 75 78 0c 5f 63 6c 69    65 6e 74 5f 6e 61 6d 65    nux._client_name
  08 6c 69 62 6d 79 73 71    6c 04 5f 70 69 64 05 33    .libmysql._pid.3
  30 30 31 33 0f 5f 63 6c    69 65 6e 74 5f 76 65 72    0013._client_ver
  73 69 6f 6e 07 31 30 2e    32 2e 31 30 09 5f 70 6c    sion.10.2.10._pl
  61 74 66 6f 72 6d 06 78    38 36 5f 36 34             atform.x86_64   

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 02 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  18 00 00 00 03 53 45 4c    45 43 54 20 55 4e 49 58    .....SELECT UNIX
  5f 54 49 4d 45 53 54 41    4d 50 28 29                _TIMESTAMP()    

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 01 26 00 00    02 03 64 65 66 00 00 00    .....&....def...
  10 55 4e 49 58 5f 54 49    4d 45 53 54 41 4d 50 28    .UNIX_TIMESTAMP(
  29 00 0c 3f 00 11 00 00    00 08 80 00 00 00 00 05    )..?............
  00 00 03 fe 00 00 02 00    0b 00 00 04 0a 31 35 31    .............151
  33 36 38 34 33 38 36 05    00 00 05 fe 00 00 02 00    3684386.........

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  20 00 00 00 03 53 48 4f    57 20 56 41 52 49 41 42     ....SHOW VARIAB
  4c 45 53 20 4c 49 4b 45    20 27 53 45 52 56 45 52    LES LIKE 'SERVER
  5f 49 44 27                                           _ID'            

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 02 64 00 00    02 03 64 65 66 12 69 6e    .....d....def.in
  66 6f 72 6d 61 74 69 6f    6e 5f 73 63 68 65 6d 61    formation_schema
  11 53 45 53 53 49 4f 4e    5f 56 41 52 49 41 42 4c    .SESSION_VARIABL
  45 53 11 53 45 53 53 49    4f 4e 5f 56 41 52 49 41    ES.SESSION_VARIA
  42 4c 45 53 0d 56 61 72    69 61 62 6c 65 5f 6e 61    BLES.Variable_na
  6d 65 0d 56 41 52 49 41    42 4c 45 5f 4e 41 4d 45    me.VARIABLE_NAME
  0c 08 00 40 00 00 00 fd    01 00 00 00 00 5d 00 00    ...@.........]..
  03 03 64 65 66 12 69 6e    66 6f 72 6d 61 74 69 6f    ..def.informatio
  6e 5f 73 63 68 65 6d 61    11 53 45 53 53 49 4f 4e    n_schema.SESSION
  5f 56 41 52 49 41 42 4c    45 53 11 53 45 53 53 49    _VARIABLES.SESSI
  4f 4e 5f 56 41 52 49 41    42 4c 45 53 05 56 61 6c    ON_VARIABLES.Val
  75 65 0e 56 41 52 49 41    42 4c 45 5f 56 41 4c 55    ue.VARIABLE_VALU
  45 0c 08 00 00 08 00 00    fd 01 00 00 00 00 05 00    E...............
  00 04 fe 00 00 22 00 10    00 00 05 09 73 65 72 76    ....."......serv
  65 72 5f 69 64 05 31 30    32 30 31 05 00 00 06 fe    er_id.10201.....
  00 00 22 00                                           ..".            

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  2a 00 00 00 03 53 45 54    20 40 6d 61 73 74 65 72    *....SET @master
  5f 68 65 61 72 74 62 65    61 74 5f 70 65 72 69 6f    _heartbeat_perio
  64 3d 20 33 30 30 30 30    30 30 31 30 32 34          d= 30000001024  

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  36 00 00 00 03 53 45 54    20 40 6d 61 73 74 65 72    6....SET @master
  5f 62 69 6e 6c 6f 67 5f    63 68 65 63 6b 73 75 6d    _binlog_checksum
  3d 20 40 40 67 6c 6f 62    61 6c 2e 62 69 6e 6c 6f    = @@global.binlo
  67 5f 63 68 65 63 6b 73    75 6d                      g_checksum      

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1f 00 00 00 03 53 45 4c    45 43 54 20 40 6d 61 73    .....SELECT @mas
  74 65 72 5f 62 69 6e 6c    6f 67 5f 63 68 65 63 6b    ter_binlog_check
  73 75 6d                                              sum             

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 01 2d 00 00    02 03 64 65 66 00 00 00    .....-....def...
  17 40 6d 61 73 74 65 72    5f 62 69 6e 6c 6f 67 5f    .@master_binlog_
  63 68 65 63 6b 73 75 6d    00 0c 08 00 ff ff ff 00    checksum........
  fa 00 00 27 00 00 05 00    00 03 fe 00 00 02 00 06    ...'............
  00 00 04 05 43 52 43 33    32 05 00 00 05 fe 00 00    ....CRC32.......
  02 00                                                 ..              

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  20 00 00 00 03 53 45 54    20 40 6d 61 72 69 61 64     ....SET @mariad
  62 5f 73 6c 61 76 65 5f    63 61 70 61 62 69 6c 69    b_slave_capabili
  74 79 3d 34                                           ty=4            

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1f 00 00 00 03 53 45 4c    45 43 54 20 40 40 47 4c    .....SELECT @@GL
  4f 42 41 4c 2e 67 74 69    64 5f 64 6f 6d 61 69 6e    OBAL.gtid_domain
  5f 69 64                                              _id             

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  01 00 00 01 01 2d 00 00    02 03 64 65 66 00 00 00    .....-....def...
  17 40 40 47 4c 4f 42 41    4c 2e 67 74 69 64 5f 64    .@@GLOBAL.gtid_d
  6f 6d 61 69 6e 5f 69 64    00 0c 3f 00 15 00 00 00    omain_id..?.....
  08 a0 00 00 00 00 05 00    00 03 fe 00 00 02 00 02    ................
  00 00 04 01 30 05 00 00    05 fe 00 00 02 00          ....0.........  

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  28 00 00 00 03 53 45 54    20 40 73 6c 61 76 65 5f    (....SET @slave_
  63 6f 6e 6e 65 63 74 5f    73 74 61 74 65 3d 27 30    connect_state='0
  2d 31 30 32 30 31 2d 39    38 36 38 27                -10201-9868'    

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1e 00 00 00 03 53 45 54    20 40 73 6c 61 76 65 5f    .....SET @slave_
  67 74 69 64 5f 73 74 72    69 63 74 5f 6d 6f 64 65    gtid_strict_mode
  3d 30                                                 =0              

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  24 00 00 00 03 53 45 54    20 40 73 6c 61 76 65 5f    $....SET @slave_
  67 74 69 64 5f 69 67 6e    6f 72 65 5f 64 75 70 6c    gtid_ignore_dupl
  69 63 61 74 65 73 3d 30                               icates=0        

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1b 00 00 00 15 75 27 00    00 09 73 6c 61 76 65 5f    .....u'...slave_
  6e 5f 31 00 00 c9 5a 00    00 00 00 00 00 00 00       n_1...Z........ 

T 127.0.0.1:23240 -> 127.0.0.1:42367 [AP]
  07 00 00 01 00 00 00 02    00 00 00                   ...........     

T 127.0.0.1:42367 -> 127.0.0.1:23240 [AP]
  1b 00 00 00 12 34 06 00    00 02 00 75 27 00 00 6d    .....4.....u'..m
  79 73 71 6c 2d 62 69 6e    2e 30 30 30 30 33 34       ysql-bin.000034

Events Transmission After COM_BINLOG_DUMP

The MariaDB master always sends this, after the COM_BINLOG_DUMP:

FAKE_ROTATE_EVENT;
FORMAT_DESCRIPTION_EVENT: Next Pos in the header is set to 0 if not requesting binlog file from the beginning, and the GTID is not in use; otherwise, Next Pos is related to next event after FDE.
FAKE_GTID_LIST_EVENT with latest GTID information.

After those first events, the master sends events related to changes in database to the connected replica binlog. The replica is just waiting for new events from master.

Complete Example of Event Transmission With CRC32

T 127.0.0.1:23240 -> 127.0.0.1:42219 [AP]
  30 00 00 01 00 00 00 00    00 04 d9 27 00 00 2f 00    0..........'../.
  00 00 00 00 00 00 20 00    04 00 00 00 00 00 00 00    ...... .........
  6d 79 73 71 6c 2d 62 69    6e 2e 30 30 30 30 33 34    mysql-bin.000034
  d5 3f ea d7 fd 00 00 02    00 fb cc 37 5a 0f d9 27    .?.........7Z..'
  00 00 fc 00 00 00 00 01    00 00 00 00 04 00 31 30    ..............10
  2e 32 2e 31 30 2d 4d 61    72 69 61 44 42 2d 6c 6f    .2.10-MariaDB-lo
  67 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    g...............
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 13 38 0d 00    08 00 12 00 04 04 04 04    .....8..........
  12 00 00 e4 00 04 1a 08    00 00 00 08 08 08 02 00    ................
  00 00 0a 0a 0a 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 00 04 13 04    00 0d 08 08 08 0a 0a 0a    ................
  01 17 0b 12 63 3c 00 00    03 00 fb cc 37 5a a3 d9    ....c<......7Z..
  27 00 00 3b 00 00 00 3b    01 00 00 00 00 02 00 00    '..;...;........
  00 00 00 00 00 01 00 00    00 1e 00 00 00 00 00 00    ................
  00 00 00 00 00 d9 27 00    00 86 26 00 00 00 00 00    ......'...&.....
  00 b6 33 8a 22 2c 00 00    04 00 fb cc 37 5a a1 d9    ..3.",......7Z..
  27 00 00 2b 00 00 00 66    01 00 00 00 00 10 00 00    '..+...f........
  00 6d 79 73 71 6c 2d 62    69 6e 2e 30 30 30 30 33    .mysql-bin.00003
  34 16 1f fe 3f 2c 00 00    05 00 00 00 00 00 a3 d9    4...?,..........
  27 00 00 2b 00 00 00 34    06 00 00 20 00 01 00 00    '..+...4... ....
  00 00 00 00 00 d9 27 00    00 8c 26 00 00 00 00 00    ......'...&.....
  00 4a 01 94 22 2b 00 00    06 00 94 fd 38 5a a2 d9    .J.."+......8Z..
  27 00 00 2a 00 00 00 5e    06 00 00 08 00 8d 26 00    '..*...^......&.
  00 00 00 00 00 00 00 00    00 29 00 00 00 00 00 00    .........)......
  22 87 c0 61 4c 00 00 07    00 94 fd 38 5a 02 d9 27    "..aL......8Z..'
  00 00 4b 00 00 00 a9 06    00 00 00 00 21 00 00 00    ..K.........!...
  00 00 00 00 00 00 00 1a    00 00 00 00 00 00 01 00    ................
  00 20 54 00 00 00 00 06    03 73 74 64 04 08 00 08    . T......std....
  00 08 00 00 66 6c 75 73    68 20 74 61 62 6c 65 73    ....flush tables
  6e c8 89 60                                           n..`

We can see:

FAKE_ROTATE_EVENT packet: 30 00 00 01 ... d5 3f ea d7
FORMAT_DESCRIPTION_EVENT packet: fd 00 00 02 00 ... 17 0b 12 63 FDE size is fc 00 00 00 (252) Next Pos in FDE is 00 01 00 00 = >256 = 4 + FDE size (252)
FAKE_GTID_LIST_EVENT packet : 3c 00 00 03 00 ... b6 33 8a 22
BINLOG_CHECKPOINT EVENT packet: 2c 00 00 04 ... 16 1f fe 3f
GTID_LIST_EVENT packet: 2c 00 00 05 ... 4a 01 94 22
GTID_EVENT packet: 2b 00 00 06 ... 22 87 c0 61
QUERY_EVENT packet: 4c 00 00 07 ... 6e c8 89 60

_{This page is licensed: CC BY-SA / Gnu FDL}

Previous4-Semi-Sync Replication NextANNOTATE_ROWS_EVENT

Last updated 19 days ago

Was this helpful?

Good morning

Overview

Example Using 'ngrep'

Complete Example with GTID Registration

Events Transmission After COM_BINLOG_DUMP

Complete Example of Event Transmission With CRC32