Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28015

Mariabackup | GTID value is missing, Galera Cluster , adding async slave to it

    XMLWordPrintable

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sysprg Julius Goryavsky
              Reporter:
              pramod.mahto@mariadb.com Pramod Mahto
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.