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

Add Mariabackup option to set gtid_slave_pos to original server's gtid_current_pos during prepare

    Details

      Description

      Some users would like to use Mariabackup to set up a slave using a process like this:

      https://mariadb.com/kb/en/library/setting-up-a-replication-slave-with-mariabackup/

      One problem with this process is that Mariabackup doesn't back up and restore the original server's entire GTID state, which can be considered to be the value of gtid_current_pos.

      https://mariadb.com/kb/en/library/gtid/#gtid_current_pos

      gtid_current_pos is constructed from two other system variables--gtid_slave_pos and gtid_binlog_pos.

      https://mariadb.com/kb/en/library/gtid/#gtid_slave_pos

      https://mariadb.com/kb/en/library/gtid/#gtid_binlog_pos

      Mariabackup does back up and restores the original server's value for gtid_slave_pos, because that information is stored in mysql.gtid_slave_pos, which is an InnoDB table.

      https://mariadb.com/kb/en/library/mysqlgtid_slave_pos-table/

      Here's an example:

      [ec2-user@ip-172-30-0-198 ~]$ mysql -u root
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 9
      Server version: 10.3.12-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'gtid_slave_pos';
      +----------------+---------+
      | Variable_name  | Value   |
      +----------------+---------+
      | gtid_slave_pos | 3-2-502 |
      +----------------+---------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> SELECT * FROM mysql.gtid_slave_pos;
      +-----------+--------+-----------+--------+
      | domain_id | sub_id | server_id | seq_no |
      +-----------+--------+-----------+--------+
      |         3 |      2 |         2 |    502 |
      +-----------+--------+-----------+--------+
      1 row in set (0.000 sec)
       
      MariaDB [(none)]> \q
      Bye
      [ec2-user@ip-172-30-0-198 ~]$ sudo mariabackup --backup \
      >    --target-dir=/home/ec2-user/backup/ \
      >    --user=root
      Info: Using unique option prefix 'backup' is error-prone and can break in the future. Please use the full name 'backup_encrypted' instead.
      190211 19:16:28 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
      Using server version 10.3.12-MariaDB-log
      ...
      190211 19:16:30 completed OK!
      [ec2-user@ip-172-30-0-198 ~]$ sudo mariabackup --prepare \
      >    --target-dir=/home/ec2-user/backup/
      mariabackup based on MariaDB server 10.3.12-MariaDB Linux (x86_64)
      ...
      190211 19:17:50 completed OK!
      [ec2-user@ip-172-30-0-198 ~]$ sudo systemctl stop mariadb
      [ec2-user@ip-172-30-0-198 ~]$ sudo rm -fr /var/lib/mysql/*
      [ec2-user@ip-172-30-0-198 ~]$ sudo mariabackup --copy-back \
      >    --target-dir=/home/ec2-user/backup/
      mariabackup based on MariaDB server 10.3.12-MariaDB Linux (x86_64)
      ...
      190211 19:19:52 completed OK!
      [ec2-user@ip-172-30-0-198 ~]$ sudo chown -R mysql:mysql /var/lib/mysql/
      [ec2-user@ip-172-30-0-198 ~]$ sudo systemctl start mariadb
      [ec2-user@ip-172-30-0-198 ~]$ mysql -u root
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 9
      Server version: 10.3.12-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'gtid_slave_pos';
      +----------------+---------+
      | Variable_name  | Value   |
      +----------------+---------+
      | gtid_slave_pos | 3-2-502 |
      +----------------+---------+
      1 row in set (0.001 sec)
       
      MariaDB [(none)]> SELECT * FROM mysql.gtid_slave_pos;
      +-----------+--------+-----------+--------+
      | domain_id | sub_id | server_id | seq_no |
      +-----------+--------+-----------+--------+
      |         3 |      2 |         2 |    502 |
      +-----------+--------+-----------+--------+
      1 row in set (0.000 sec)
      

      Mariabackup does not currently back up and restore the original server's gtid_binlog_pos.

      Mariabackup does back up the original server's value for gtid_current_pos in the file xtrabackup_binlog_info.

      https://mariadb.com/kb/en/library/files-created-by-mariabackup/#xtrabackup_binlog_info

      This means that if a user wants to set up replication using a backup, then they need to read the original server's gtid_current_pos from xtrabackup_binlog_info:

      $ cat xtrabackup_binlog_info
      mariadb-bin.000096 568 0-1-2
      

      The user would need to manually set gtid_slave_pos to this value, and then they could set up replication:

      SET GLOBAL gtid_slave_pos='0-1-2';
      CHANGE MASTER TO
        ...
        MASTER_USE_GTID=slave_pos;
      

      Some users would prefer if the original server's gtid_current_pos were backed up and restored with the data. That way, users could just set up replication from a slave created from a backup by doing something like this:

      CHANGE MASTER TO
        ...
        MASTER_USE_GTID=slave_pos;
      

      And the slave would automatically know where to start replicating from.

      To allow this, I think Mariabackup should have an option that would set the backup's value of gtid_slave_pos to the original server's value of gtid_current_pos during the prepare.



      The text in this section is about the possible options for restoring gtid_binlog_pos. However, restoring gtid_binlog_pos on a new server may not be very useful if the purpose of the new server is to function as a slave, since the new server would have a different server_id value. See the comments from Kristian Nielsen in MDEV-18404. If the new server were to function as a slave, then it would probably be more useful to restore the original server's entire value of gtid_current_pos to the new server's gtid_slave_pos. That option is described in the previous section. I do not think the information in this section would be very useful, but I am leaving this section here for context.

      So to allow Mariabackup to back up and restore gtid_current_pos, since gtid_slave_pos is already backed up and restored, we would just have to implement some functionality to allow Mariabackup to back up and restore gtid_binlog_pos as well.

      The value of gtid_binlog_pos is actually controlled by the value of gtid_binlog_state.

      https://mariadb.com/kb/en/library/gtid/#gtid_binlog_state

      Therefore, to backup gtid_binlog_pos, we need to read the value of gtid_binlog_state during the backup:

      MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('gtid_binlog_pos', 'gtid_binlog_state');
      +-------------------+------------------+
      | Variable_name     | Value            |
      +-------------------+------------------+
      | gtid_binlog_pos   | 1-1-101,2-1-2002 |
      | gtid_binlog_state | 1-1-101,2-1-2002 |
      +-------------------+------------------+
      2 rows in set (0.001 sec)
      

      And to restore gtid_binlog_pos, we need to set the value of gtid_binlog_state during the prepare:

      There are two ways to do this.

      1.) One is to use SET GLOBAL. I know that Mariabackup doesn't generally run SQL. However, there is at least one instance where it does--when the --export option is provided during the prepare, it runs FLUSH TABLES FOR EXPORT. See the following source code links:

      https://github.com/MariaDB/server/blob/0a1c3477bf359c55be3e11ec6502bc7b5bb87f29/extra/mariabackup/xtrabackup.cc#L1523

      https://github.com/MariaDB/server/blob/0a1c3477bf359c55be3e11ec6502bc7b5bb87f29/extra/mariabackup/xtrabackup.cc#L1497

      Here's an example of using SET GLOBAL to set gtid_binlog_state:

      MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('gtid_binlog_pos', 'gtid_binlog_state');
      +-------------------+-------+
      | Variable_name     | Value |
      +-------------------+-------+
      | gtid_binlog_pos   |       |
      | gtid_binlog_state |       |
      +-------------------+-------+
      2 rows in set (0.001 sec)
       
      MariaDB [(none)]> SET GLOBAL gtid_binlog_state='1-1-101,2-1-2002';
      Query OK, 0 rows affected (0.005 sec)
       
      MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('gtid_binlog_pos', 'gtid_binlog_state');
      +-------------------+------------------+
      | Variable_name     | Value            |
      +-------------------+------------------+
      | gtid_binlog_pos   | 1-1-101,2-1-2002 |
      | gtid_binlog_state | 1-1-101,2-1-2002 |
      +-------------------+------------------+
      2 rows in set (0.001 sec)
      

      Note that if gtid_binlog_state contains non-local server_id values, then you may have to watch out for MDEV-18404 until that bug is fixed.

      2.) If we do not want Mariabackup to have to run SET GLOBAL, then another option is to have Mariabackup write the gtid_binlog_state to the MASTER-BIN.state file mentioned in the documentation:

      The value of @@gtid_binlog_state is preserved by the server across restarts by writing a file MASTER-BIN.state, where MASTER-BIN is the base name of the binlog set with the --log-bin option. This file is written at server shutdown, and re-read at next server start. (In case of a server crash, the data in the MASTER-BIN.state is not correct, and the server instead recovers the correct value during binlog crash recovery by scanning the binlog files and recording each GTID found).

      https://mariadb.com/kb/en/library/gtid/#gtid_binlog_state

      It looks like the server writes the MASTER-BIN.state file with MYSQL_BIN_LOG::write_state_to_file() in sql/log.cc:

      https://github.com/MariaDB/server/blob/c2318291be7458238729ed80233ea71f1e6a62b8/sql/log.cc#L6057

      Here's an example of using the MASTER-BIN.state file to set gtid_binlog_state:

      [ec2-user@ip-172-30-0-198 ~]$ mysql -u root
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 9
      Server version: 10.3.12-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('log_bin_basename', 'gtid_binlog_pos', 'gtid_binlog_state');
      +-------------------+----------------------------+
      | Variable_name     | Value                      |
      +-------------------+----------------------------+
      | gtid_binlog_pos   | 1-1-101,2-1-2002           |
      | gtid_binlog_state | 1-1-101,2-1-2002           |
      | log_bin_basename  | /var/lib/mysql/mariadb-bin |
      +-------------------+----------------------------+
      3 rows in set (0.001 sec)
       
      MariaDB [(none)]> \q
      Bye
      [ec2-user@ip-172-30-0-198 ~]$ sudo systemctl stop mariadb
      [ec2-user@ip-172-30-0-198 ~]$ sudo cat /var/lib/mysql/mariadb-bin.state
      1-1-101
      2-1-2002
      [ec2-user@ip-172-30-0-198 ~]$ sudo rm /var/lib/mysql/mariadb-bin.state
      [ec2-user@ip-172-30-0-198 ~]$ sudo tee /var/lib/mysql/mariadb-bin.state <<EOF
      > 1-2-2001
      > 2-2-3001
      > EOF
      1-2-2001
      2-2-3001
      [ec2-user@ip-172-30-0-198 ~]$ sudo chown mysql:mysql /var/lib/mysql/mariadb-bin.state
      [ec2-user@ip-172-30-0-198 ~]$ sudo systemctl start mariadb
      [ec2-user@ip-172-30-0-198 ~]$ mysql -u root
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 9
      Server version: 10.3.12-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('log_bin_basename', 'gtid_binlog_pos', 'gtid_binlog_state');
      +-------------------+----------------------------+
      | Variable_name     | Value                      |
      +-------------------+----------------------------+
      | gtid_binlog_pos   | 1-2-2001,2-2-3001          |
      | gtid_binlog_state | 1-2-2001,2-2-3001          |
      | log_bin_basename  | /var/lib/mysql/mariadb-bin |
      +-------------------+----------------------------+
      3 rows in set (0.001 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                vlad.lesin Vladislav Lesin
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated: