[MDEV-18405] Add Mariabackup option to set gtid_slave_pos to original server's gtid_current_pos during prepare Created: 2019-01-28  Updated: 2021-11-18

Status: Stalled
Project: MariaDB Server
Component/s: Backup, mariabackup, Replication
Fix Version/s: None

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 2
Labels: None

Issue Links:
Duplicate
duplicates MDEV-32215 Add Mariabackup option to set gtid_sl... Open
Relates
relates to MDEV-14746 mariabackup doesn't read [mariabackup... Closed
relates to MDEV-18347 mariabackup doesn't read all server o... Closed
relates to MDEV-18404 Setting gtid_binlog_state with non-lo... Closed
relates to MDEV-18917 Don't create xtrabackup_binlog_pos_in... Closed
relates to MDEV-18931 Rename Mariabackup's xtrabackup_* fil... Closed
relates to MDEV-18985 Remove support for XtraDB's changed p... Confirmed
relates to MDEV-19246 Change database and table used for Ma... Closed
relates to MDEV-19264 Better support MariaDB GTID for Maria... Closed
relates to MDEV-20552 Remove innobackupex mode from Mariaba... Stalled
relates to MDEV-20556 Remove references to "xtrabackup" and... Closed

 Description   

Many users use Mariabackup to build slaves using the process outlined in the following documentation page:

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

The value of gtid_current_pos is constructed from the values of 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/

Mariabackup does not currently back up and restore the original server's gtid_binlog_pos, because that information is stored in the binary logs, which are not backed up.

This means that a server restored from a backup is missing some GTID state by default.

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

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

This means that if a user wants to build a slave using a backup, then they can restore the backup, and then they can extract the original server's gtid_current_pos from xtrabackup_binlog_info:

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

And then the user would need to use this value to set the value of gtid_slave_pos, 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 value of gtid_slave_pos would automatically be set to the original server's gtid_current_pos when a backup was prepared. That way, users would not have to worry about manually fixing the GTID state, and they 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.

 Comments   
Comment by Vladislav Vaintroub [ 2019-02-11 ]

GeoffMontee, I think you overestimate what "prepare" does. It does Innodb recovery. It does not execute arbitrary SQL commands.
Saving whatever variables is not a problem though.
Is it sufficient to just store gtid* variables, and external script would then do the rest at the startup?

Comment by Geoff Montee (Inactive) [ 2019-02-11 ]

Hi wlad,

I think you overestimate what "prepare" does. It does Innodb recovery. It does not execute arbitrary SQL commands.
Saving whatever variables is not a problem though.

I wasn't sure if prepare would be capable of running SET GLOBAL gtid_binlog_state/gtid_slave_pos, but I did know that Mariabackup runs FLUSH TABLES FOR EXPORT during the prepare when the --export option is provided:

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

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

So it didn't seem like a stretch to think that Mariabackup would also be able run SET GLOBAL gtid_binlog_state/gtid_slave_pos during the prepare if some other option were provided.

Is it sufficient to just store gtid* variables, and external script would then do the rest at the startup?

That would probably work.

For gtid_binlog_state, there is also another option. The documentation says the following about this variable:

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

So Mariabackup could also create this MASTER-BIN.state file during the prepare. For 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 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)

So that's a relatively easy way to set gtid_binlog_state without actually running SET GLOBAL gtid_binlog_state. What do you think about that?

As for gtid_slave_pos, I just noticed that Mariabackup doesn't need to do anything to restore that. The documentation says that:

The mysql.gtid_slave_pos system table is used to store the contents of global.gtid_slave_pos and preserve it over restarts.

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

And this table is an InnoDB table, so Mariabackup should already back up the consistent slave position with the data. I confirmed that it does:

[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)

So gtid_slave_pos doesn't need to be set.

Comment by Vladislav Vaintroub [ 2019-02-11 ]

I definitely do not want to repeat "FLUSH TABLES FOR EXPORT" hack . I actually would prefer it the way it was before, in 10.1, where mariabackup did not act as full server, with bootstrap.
MASTER-BIN.state sounds much better.

Comment by Geoff Montee (Inactive) [ 2019-02-11 ]

That's understandable.

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

Comment by Andrei Elkin [ 2019-02-13 ]

Took for myself having spoken with Wlad about the issue previously. In-progress status is dropped though as I am having two tickets in the way. ETA to resume with this one is Feb 19th.

Comment by Geoff Montee (Inactive) [ 2019-07-15 ]

If the backup is going to be restored to a new slave with a different server_id value than the original server, then rather than restoring the GTIDs from gtid_binlog_pos to the new server's gtid_binlog_pos, it might make more sense to add the GTIDs from gtid_binlog_pos to the existing GTIDs in the new server's gtid_slave_pos. This is because GTIDs from gtid_binlog_pos in which the server_id of the GTID is not equal to the server's own server_id are effectively ignored. See the comment from knielsen on MDEV-18404.

Comment by Nicolas [ 2021-02-18 ]

Hi, any updates on this?

In 10.5 what is the easiest way to restore, start a node from a backup when using mariabackup?

This still being required?:

SET GLOBAL gtid_slave_pos = "1-1-5693,2-2-74";
 
CHANGE MASTER TO......

Mainly asking because I am trying to automating the process but wondering if maybe a flag when using `mariabackup --move-back ` could include the GTID.

Generated at Thu Feb 08 08:43:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.