Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
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.
Attachments
Issue Links
- duplicates
-
-
- Open
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-18404 Setting gtid_binlog_state with non-local server_id does not update gtid_current_pos
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-20552 Remove innobackupex mode from Mariabackup
-
- Stalled
-
-
-
- Closed
-
Activity
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:
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.
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.
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
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.
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.
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.
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?