ralf.gebhardt@mariadb.com it's assigned to you because we have 3 customer issues pending a decision from you on it.

Elkin, I have no idea what GeoffMontee is asking me to, I see gtid => I forward to replication.
Maybe mariabackup can do something, but --prepare is just innodb recovery, so if there is some extras on top are asked for, I'd need the guidance what is possible or feasible.

If anyone has any questions for me, please feel free to ask.

Thanks!

wlad, GeoffMontee may correct my understanding of this FR.
As of current mariadbbackup --backup created xtrabackup_binlog_info contains
@@global.gtid_current_pos which is not easy to make use at backup restoring (the var is read-only).

To my reading of the case xtrabackup_binlog_info should instead contain the value of @@global.gtid_binlog_pos

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

of the backup donor "master". And then the slave's var could be set to it.

The references to about-to-be-deprecated (see MDEV-20122) gtid_local_pos are not really necessary here (Geoff's vote was reserved above) (the slave's value would be automatically computed through mentioned rules).

Hi Elkin and wlad,

To my reading of the case xtrabackup_binlog_info should instead contain the value of @@global.gtid_binlog_pos of the backup donor "master". And then the slave's var could be set to it.

This was my original thought when I initially submitted this feature request, but I spoke with you and Kristian about that idea in MDEV-18404, and I found out that it does not make sense to set gtid_binlog_state to a value copied from a different server.

My latest proposal is:

1.) Keep writing gtid_current_pos to xtrabackup_binlog_info. I don't see any reason to change this behavior.

2.) Add some option (e.g. --set-gtid-slave-pos) to control whether gtid_slave_pos is set during the prepare phase:

$ mariadb-backup --prepare \

   --set-gtid-slave-pos \

   --target-dir=/var/mariadb/backup/

If this option is set, then mariadb-backup would do something like this:

a.) Prepare the backup, as normal
b.) Bootstrap mariadbd using the prepared backup
c.) Execute the following against the bootstrapped mariadbd process:

SET GLOBAL gtid_slave_pos='<GTID position read from xtrabackup_binlog_info>';

Steps b and c sound pretty unusual, but mariadb-backup already does something similar to execute FLUSH TABLES ... FOR EXPORT when you execute the following:

$ mariadb-backup --prepare \

   --export \

   --target-dir=/var/mariadb/backup/

When the --export option is provided, mariadb-backup bootstraps mariadbd using the prepared backup here:

https://github.com/MariaDB/server/blob/mariadb-10.5.9/extra/mariabackup/xtrabackup.cc#L1752

And then it executes the SQL script defined here against the bootstrapped mariadbd process::

https://github.com/MariaDB/server/blob/mariadb-10.5.9/extra/mariabackup/xtrabackup.cc#L1726

We could use a similar technique to set gtid_slave_pos during the prepare.

However, the last time I spoke to wlad about this, he said that he really disliked that mariadb-backup had to bootstrap mariadbd to perform operations like this, so I know that he probably does not want to reuse this technique for other new features.

Ideally, it might be best if mariadb-backup could set gtid_slave_pos without executing SQL. The value of gtid_slave_pos is stored in mysql.gtid_slave_pos, which is almost always an InnoDB table. Can mariadb-backup safely write to InnoDB tables without executing SQL?

GeoffMontee: howdy. Sorry for superficial comment above - already 'cos gtid_binlog_pos is read-only.

Now to the proper understanding, the MDEV-18404 explained that the whole gtid state of a slave server generally should be computed through both @@global.gtid_binlog_state and @@global.gtid_slave_pos, and that's why the summary of this request.

Let us work out a plan not involving gtid_current_pos. It feels a fifth wheel which I'd love to (start) dismantling in MDEV-20122.

Do you seen any issue in having both @@global.gtid_binlog_state and @@global.gtid_slave_pos in xtrabackup_binlog_info?

Hi Elkin.

Sorry for superficial comment above - already 'cos gtid_binlog_pos is read-only.

No worries. Right. gtid_binlog_state is the writable system variable that affects gtid_binlog_pos.

Now to the proper understanding, the MDEV-18404 explained that the whole gtid state of a slave server generally should be computed through both @@global.gtid_binlog_state and @@global.gtid_slave_pos, and that's why the summary of this request.

Right.

Let us work out a plan not involving gtid_current_pos. It feels a fifth wheel which I'd love to (start) dismantling in MDEV-20122.

Do you seen any issue in having both @@global.gtid_binlog_state and @@global.gtid_slave_pos in xtrabackup_binlog_info?

Currently, xtrabackup_binlog_info does not contain either gtid_binlog_state or gtid_slave_pos. It only contains gtid_current_pos, which is built from a combination of the two GTID positions. If you completely redesign how MariaDB Backup tracks GTIDs, then maybe it makes sense to store the new details in a new location that uses MariaDB branding, rather than redesigning the file format of a file that uses Percona's Xtrabackup branding. See MDEV-18931 for a related request.

It probably makes sense to back up gtid_binlog_pos / gtid_binlog_state, but it probably does not make sense to restore the value as-is in most cases. The discussion in MDEV-18404 makes it pretty clear that it does *not* make sense to set gtid_binlog_pos / gtid_binlog_state to a GTID value pulled from a server with a different server_id, because the server will simply ignore those GTIDs.

I submitted this feature request, because customers had a specific use case in mind: they want to be able to build replicas using MariaDB Backup without requiring any manual steps to set the proper GTID on the new replica. Since MariaDB Backup does automatically back up the GTID position, it should also be able to automatically set the GTID position.

Since the specific use case involves building replicas, I think the GTID position on the new replica needs to be set to gtid_slave_pos--not gtid_binlog_pos / gtid_binlog_state, even if you back up both gtid_binlog_pos / gtid_binlog_state and gtid_slave_pos. However, the GTID position set to gtid_slave_pos should probably be formed from the combination of gtid_binlog_pos / gtid_binlog_state and gtid_slave_pos in the backup, so that the full GTID position is taken into account.

Are you proposing to implement something that would work like this?:

1.) The customer backs up their primary server:

$ mariadb-backup --backup \

   --target-dir=/var/mariadb/backup/ \

   --user=mariabackup --password=mypassword

In the new implementation:

• Both gtid_binlog_pos / gtid_binlog_state and gtid_slave_pos would be included somewhere in the backup.
• Would gtid_current_pos still be backed up to xtrabackup_binlog_pos, or would it disappear from the backup?

2.) The customer prepares the backup, and provides an option to set gtid_slave_pos:

$ mariadb-backup --prepare \

   --set-gtid-slave-pos \

   --target-dir=/var/mariadb/backup/

In the new implementation:

• MariaDB Backup would prepare the backup, as normal.
• MariaDB Backup would compute the full GTID from the values of gtid_binlog_pos / gtid_binlog_state and gtid_slave_pos.
• MariaDB Backup would set gtid_slave_pos to the full GTID value.

3.) The customer restores the backup to the new replica:

$ mariadb-backup --copy-back \

   --target-dir=/var/mariadb/backup/

$ chown -R mysql:mysql /var/lib/mysql/

$ systemctl start mariadb

In the new implementation:

• No changes.

4.) The customer sets up replication on the new replica:

CHANGE MASTER TO 

   MASTER_HOST="dbserver1", 

   MASTER_PORT=3310, 

   MASTER_USER="repl",  

   MASTER_PASSWORD="password", 

   MASTER_USE_GTID=slave_pos;

START SLAVE;

In the new implementation:

• The customer does not have to set gtid_slave_pos, because the value was automatically set by MariaDB Backup.

Does that sound accurate?

Thanks!

GeoffMontee, wlad, salave. First to answer questions,

> Both gtid_binlog_pos / gtid_binlog_state and gtid_slave_pos would be included somewhere in the backup.
That was the idea. gtid_current_pos would not be saved, as I considered that on the recipient side the above two would suffice.
But actually they can't compose the like of gtid_current_pos for Recipient ({R), e.g when Donor (D) is another slave that locally changed its gtid_binlog_state with a transaction g. Then even though R.gtid_binlog_state possesses g, R.gtid_current_pos does not which clearly is a problem (rather the very problem).

So I'd rather we go with your plan, GeoffMontee. Why won't we just set the slave and binlog state on the recipient from the donor's
current one that is provided by backup configuration: R.gtid_slave_pos = R.gtid_binlog_state = backup.gtid_current_pos ? (I don't write D.gtid_current_pos on purpose, avoiding to refer the variable to be possibly deprecated). Such R would be set to work in either role.

More to

Both gtid_binlog_pos / gtid_binlog_state and gtid_slave_pos would be included

I think they should be added to the backup configuration (xtrabackup_binlog_info) at least for flexibility.
We could also consider another backup restore feature which would make g:s of my previous comment to migrate from
backup.gtid_binlog_state into R.gtid_slave_pos which is an equivalent solution to the current issue.

Hi Elkin,

Why won't we just set the slave and binlog state on the recipient from the donor's current one that is provided by backup configuration: R.gtid_slave_pos = R.gtid_binlog_state = backup.gtid_current_pos ?

If I understand correctly, this approach sounds a bit different than what I am suggesting.

It sounds like you are suggesting to implement it like this:

1.) The customer backs up their primary server:

$ mariadb-backup --backup \

   --target-dir=/var/mariadb/backup/ \

   --user=mariabackup --password=mypassword

In the new implementation:

• Both gtid_binlog_state and gtid_slave_pos would be included somewhere in the backup.

2.) The customer prepares the backup, and provides an option to set both gtid_slave_pos and gtid_binlog_state:

$ mariadb-backup --prepare \

   --set-gtid-pos \

   --target-dir=/var/mariadb/backup/

In the new implementation:

• MariaDB Backup would prepare the backup, as normal.
• MariaDB Backup would set gtid_slave_pos to the the value of gtid_slave_pos from the backup.
• MariaDB Backup would set gtid_binlog_state to the the value of gtid_binlog_state from the backup.

Unfortunately, I don't think this approach will be a viable solution for the problems reported by some customers.

The problem with this approach is the same problem described by MDEV-18404. When you are setting up a brand new slave from a backup of the master, the new slave is going to have a different server_id value. If you try to set gtid_binlog_state on the new slave to the same value retrieved from the master, it will have an entirely different effect on the new slave bcause of the different server_id value. On the new slave, the GTIDs in gtid_binlog_state will not effect replication, regardless of whether you specify MASTER_USE_GTID=current_pos or MASTER_USE_GTID=slave_pos:

• The GTIDs in gtid_binlog_state will not effect replication with MASTER_USE_GTID=current_pos, because the GTIDs with different server_id values in gtid_binlog_state will not affect the position of gtid_current_pos). This is shown by the example in MDEV-18404.
• The GTIDs in gtid_binlog_state will not effect replication with MASTER_USE_GTID=slave_pos, because the GTIDs in gtid_binlog_state do not affect the position of gtid_slave_pos at all. This is expected behavior.

Since the GTIDs in gtid_binlog_state will not be used for replication, this proposal does not seem to provide the solution requested by this Jira issue, which is to make it easier to configure new replication slaves from a backup of the master.

Am I understanding your proposal correctly? If so, what specific use case is your proposal designed to solve?

We could also consider another backup restore feature which would make g:s of my previous comment to migrate from backup.gtid_binlog_state into R.gtid_slave_pos which is an equivalent solution to the current issue.

If we want to make it easier to configure new replication slaves from a backup of the master, then I think this is the approach we should take. If the old master's GTIDs are not migrated from gtid_binlog_state to gtid_slave_pos, then even if we restore gtid_binlog_state on the new slave, those GTIDs in gtid_binlog_state will not be used for replication at all, regardless of whether you specify MASTER_USE_GTID=current_pos or MASTER_USE_GTID=slave_pos.

In my opinion, it makes the most sense to migrate gtid_binlog_state to gtid_slave_pos. However, you may have other use cases in mind than I do.

Thanks!

GeoffMontee, thanks for a pretty detailed reply! To clear out some questions, in
R.gtid_slave_pos = R.gtid_binlog_state = backup.gtid_current_pos

I intended to set both recipient's gtid states to the same value of the current gtid pos computed as if the computation happened to the donor's side variable (that is on the donor).
I thought that such way set slave gtid state of R allows for MASTER_USE_GTID=slave_pos slave role.

But it's rude actually. What if D is a slave that updated binlog locally so gained gtid:s which were not replicated.
Those must not contribute to R.gtid_slave_pos, and then the D side gtid_current_pos computation rule turns to be critical.

And I was not really correct on the master role safety. E.g should binlog files also be copied with the backup image/configuration, possibly not-binlogged (log-slave-updates=0) slave role GTID:s would not be found.

Considering what you said and the above I still strive for the idea that you seem to support:

it makes the most sense to migrate gtid_binlog_state to gtid_slave_pos.

Let backup --prepare sets straight R.gtid_binlog_state = D.gtid_binlog_state and
optionally computes R.gtid_slave_pos = backup.gtid_current_pos (D.server_id must be provided in the backup conf file for computing that).
The user should also be warned that
MASTER_USE_GTID=current_pos is not an option on the backup-restored server.

The new option applies to:

• If D is a master server possibly having a slice of the slave state (e.g from some past time), R.gtid_slave_pos will reflect the total gtid D's state correctly.
• If D is a slave server, not having anything logged locally the computed R's gtid slave state reflects the D total gtid state too, for both {{log-slave-updates=0,1}.

but not

• If D is a slave server that had some "own" local updates binlogged, while R is going to take on the slave role itself. These ones were not supposed to be replicated, so could not be found on any master.
• if D is a master server that bin-logged "foreign" server_id GTID:s - (SET @@server_id="foreign_id" e.g by mysqlbinlog). While those would/could be replicated, computation of backup.gtid_current_pos by backup --prepare would not involve them.
  Since such GTID:s would be part of a part of D.gtid_binlog_state, we may consider to force them info backup.gtid_current_pos. Seems yet another option is necessary. I need to return to this case later.

Could you please review this. Cheers!

GeoffMontee, I'm leaning towards the thought that it's not mariabackup job to do. mariabackup is a backup/restore tool. Its job is to back the data up and to restore it, as close as possible to the original, ideally 1:1. Intentionally modifying tables as a part of the restore (that is, making changes in mysql.gtid_slave_pos table) is contrary to what a backup/restore is. It could be a different tool or a mariabackup wrapper, like "restore a backup and prepare the slave for replication". But I think these are two different steps. First one restores a backup, getting an identical copy of the original, then one prepares it for replication, which causes the data no longer be an "identical copy of the original", so, technically, not a restored backup anymore.