[MDEV-28015] Mariabackup | GTID value is missing, Galera Cluster , adding async slave to it Created: 2022-03-07  Updated: 2024-01-25  Resolved: 2023-08-25

Status: Closed
Project: MariaDB Server
Component/s: Backup, Galera SST
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.11
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Pramod Mahto Assignee: Julius Goryavsky
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-28134 Error_code: handler error HA_ERR_FOUN... Closed
Relates
relates to MDEV-27524 Incorrect binlogs after Galera SST us... Closed
relates to MDEV-28583 Galera: binlogs disappear after rsync... Closed
relates to MDEV-28758 Mariabackup copies binary logs to bac... Closed
relates to MDEV-29171 changing the value of wsrep_gtid_doma... Closed
relates to MDEV-31905 WSREP GTID MODE is inconsistent Closed

 Description   

1. 3 node galera cluster (vmc_a, vmc_b, vmc_c) + GTID Replication (vmc_d)slave

2. Major config parameters for vmc_a, vmc_b and vmc_d

vmc_a

 
 
server-id=1
 
gtid_domain_id=11                               
wsrep_gtid_domain_id=100

vmc_b

 
 
server-id=2
 
gtid_domain_id=12                               
wsrep_gtid_domain_id=100

vmc_c

 
 
server-id=3
 
gtid_domain_id=13                               
wsrep_gtid_domain_id=100

vmc_a : All write transactions will be routed to vmc_a node

vmc_a

 
 
vmc_a
 
MariaDB [(none)]> show global variables like '%gtid%';
 
+------------------------+-------+
 
| Variable_name          | Value |
 
+------------------------+-------+
 
| gtid_binlog_pos        |       |
 
| gtid_binlog_state      |       |
 
| gtid_current_pos       |       |
 
| gtid_domain_id         | 11    |
 
| gtid_ignore_duplicates | OFF   |
 
| gtid_slave_pos         |       |
 
| gtid_strict_mode       | OFF   |
 
| wsrep_gtid_domain_id   | 100   |
 
| wsrep_gtid_mode        | OFF   |
 
+------------------------+-------+
 
9 rows in set (0.01 sec)
 
MariaDB [(none)]> create database test;
 
Query OK, 1 row affected (0.02 sec)
 
MariaDB [(none)]> show global variables like '%gtid%';
 
+------------------------+--------+
 
| Variable_name          | Value  |
 
+------------------------+--------+
 
| gtid_binlog_pos        | 11-1-1 |
 
| gtid_binlog_state      | 11-1-1 |
 
| gtid_current_pos       | 11-1-1 |
 
| gtid_domain_id         | 11     |
 
| gtid_ignore_duplicates | OFF    |
 
| gtid_slave_pos         |        |
 
| gtid_strict_mode       | OFF    |
 
| wsrep_gtid_domain_id   | 100    |
 
| wsrep_gtid_mode        | OFF    |
 
+------------------------+--------+
 
9 rows in set (0.01 sec)
 
 
 
CREATE TABLE test.persons (
 
    Personid int NOT NULL AUTO_INCREMENT,
 
    LastName varchar(255) NOT NULL,
 
    FirstName varchar(255),
 
    Age int,
 
    PRIMARY KEY (Personid)
 
);
 
INSERT INTO test.persons (FirstName,LastName) VALUES ('Lars','Aonsen');
 
INSERT INTO test.persons (FirstName,LastName) VALUES ('Mars','Monsed');
 
INSERT INTO test.persons (FirstName,LastName) VALUES ('Nars','Monsee');
 
MariaDB [(none)]> show global variables like '%gtid%';                                                                                                                                                                                                                                                                       +------------------------+--------+
 
| Variable_name          | Value  |
 
+------------------------+--------+
 
| gtid_binlog_pos        | 11-1-5 |
 
| gtid_binlog_state      | 11-1-5 |
 
| gtid_current_pos       | 11-1-5 |
 
| gtid_domain_id         | 11     |
 
| gtid_ignore_duplicates | OFF    |
 
| gtid_slave_pos         |        |
 
| gtid_strict_mode       | OFF    |
 
| wsrep_gtid_domain_id   | 100    |
 
| wsrep_gtid_mode        | OFF    |
 
+------------------------+--------+
 
9 rows in set (0.00 sec)

vmc_b 

 
 
MariaDB [(none)]> show global variables like '%gtid%';
 
+------------------------+--------+
 
| Variable_name          | Value  |
 
+------------------------+--------+
 
| gtid_binlog_pos        | 12-1-5 |
 
| gtid_binlog_state      | 12-1-5 |
 
| gtid_current_pos       |        |
 
| gtid_domain_id         | 12     |
 
| gtid_ignore_duplicates | OFF    |
 
| gtid_slave_pos         |        |
 
| gtid_strict_mode       | OFF    |
 
| wsrep_gtid_domain_id   | 100    |
 
| wsrep_gtid_mode        | OFF    |
 
+------------------------+--------+
 
9 rows in set (0.00 sec)

vmc_c

 
 
MariaDB [(none)]> show global variables like '%gtid%';
 
+------------------------+--------+
 
| Variable_name          | Value  |
 
+------------------------+--------+
 
| gtid_binlog_pos        | 13-1-5 |
 
| gtid_binlog_state      | 13-1-5 |
 
| gtid_current_pos       |        |
 
| gtid_domain_id         | 13     |
 
| gtid_ignore_duplicates | OFF    |
 
| gtid_slave_pos         |        |
 
| gtid_strict_mode       | OFF    |
 
| wsrep_gtid_domain_id   | 100    |
 
| wsrep_gtid_mode        | OFF    |
 
+------------------------+--------+
 
9 rows in set (0.00 sec)

Lets take backup using mariabackup and *mysqldump *

mariabackup and mysqldump backup from vmc_c

 
 
[root@vmc_c fullbackup]# mariabackup --backup --user=backup --password=backup --galera-info --target-dir=/tmp/fullbackup
 
 
 
[root@vmc_c fullbackup]# mariabackup --prepare --target-dir=/tmp/fullbackup/
 
[root@vmc_c fullbackup]# cat xtrabackup_binlog_info
 
mysql_bin_c.000002      389
 
 
 
[root@vmc_c fullbackup]# mysqldump -ubackup -pbackup --master-data=2 --all-databases >/tmp/fullbackup.sql
 
 
 
[root@vmc_c fullbackup]# head -30 /tmp/fullbackup.sql
 
-- MySQL dump 10.16  Distrib 10.2.32-MariaDB, for Linux (x86_64)
 
--
 
-- Host: localhost    Database:
 
-- ------------------------------------------------------
 
-- Server version       10.2.32-MariaDB-log
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8 */;
 
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 
/*!40103 SET TIME_ZONE='+00:00' */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
 
-- Position to start replication or point-in-time recovery from
 
--
 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_c.000002', MASTER_LOG_POS=389;
 
--
 
-- GTID to start replication from
 
--
 
-- SET GLOBAL gtid_slave_pos='13-1-5';

[root@vmc_c fullbackup]# cat xtrabackup_binlog_info
mysql_bin_c.000002 389

 Comments   
Comment by Pramod Mahto [ 2022-03-07 ]

From above testing , what I can see if the value for gtid_current_pos is null , it won't get captured by the mariabackup tool with in xtrabackup_binlog_info file . While mysqldump do capture the same.

Comment by Jan Lindström [ 2023-08-25 ]

If wsrep_gtid_mode=ON is not used, I do not think even backups from cluster will not work correctly. Requirements for GTID to work on Galera cluster are clearly documented on https://mariadb.com/kb/en/using-mariadb-gtids-with-mariadb-galera-cluster/. I'm closing this as a not a bug.

Comment by Jan Lindström [ 2024-01-25 ]

Richard Can you open a new MDEV, provide node configuration and some output from show global variables like '%gtid%'; Why gtid_domain_id != wsrep_gtid_domain_id ?

Generated at Thu Feb 08 09:57:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.