[Maria-discuss] GTID replication and SST method
Hi, I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way: * MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID o Pick a master node from the cluster o Take a backup with xtrabackup-v2 o Apply backup to slave o Connect the slave to the master and replicate using MariaDB GTID o If master goes down, switch master to another node in the Galera Clutser However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes. Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below: * Cluster is running fine * Lots of transactions going through, enough that a new joiner would need an SST * Begin joining a new node to the cluster * SST via xtrabackup-v2 happens during this process * The GTID in the donor is 1-1-12345 * SST completes joiner is now part of the cluster * The GTID on the newly joined node is 1-1-1 * We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down. I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue. Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2? On the MariaDB pages it does say "xtrabackup-v2 and xtrabackup SST methods currently do not support GTID" is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst... Thanks, Craig Evertz UK cbailey@evertz.com<mailto:cbailey@evertz.com> www.evertz.com This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.
Hi Craig, I assume that you use MariaDB Galera Cluster 10.0, in which case GTID's aren't kept in sync across the cluster. I would recommend upgrading to MariaDB Galera Cluster 10.1 and set the two following variables: wsrep_gtid_mode=1 wsrep_gtid_domain_id=N # set this value to a domain_id which isn't allocated in your replication setup. It will be strictly used for Galera If you want to do slave failover with 10.0, this is purely a manual process, and GTID isn't really well supported. An alternative is to use multi-source and create replication connections from each node to the slave. Best regards On Fri, Apr 22, 2016 at 2:25 PM Craig Bailey <cbailey@evertz.com> wrote:
Hi,
I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way:
· MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID
o Pick a master node from the cluster
o Take a backup with xtrabackup-v2
o Apply backup to slave
o Connect the slave to the master and replicate using MariaDB GTID
o If master goes down, switch master to another node in the Galera Clutser
However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes.
Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below:
· Cluster is running fine
· Lots of transactions going through, enough that a new joiner would need an SST
· Begin joining a new node to the cluster
· SST via xtrabackup-v2 happens during this process
· The GTID in the donor is 1-1-*12345*
· SST completes joiner is now part of the cluster
· The GTID on the newly joined node is 1-1-*1*
· We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down.
I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue.
Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2?
On the MariaDB pages it does say “xtrabackup-v2 and xtrabackup SST methods currently do not support GTID” is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst...
Thanks,
Craig
Evertz UK
*cbailey@evertz.com <cbailey@evertz.com> *
*www.evertz.com <http://www.evertz.com>*
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
Hi Guillaume, Thanks for the swift response, you are correct with the version I am using (10.0.23). If I switch to 10.1.x are you saying (assuming I follow your instructions) that the GTID will be kept in sync across my whole cluster, such that at any point I could issue a CHANGE MASTER on my slave to carry on replicating with no issues? If so it looks like I need to look into whether or not I can upgrade 10.1.x Thanks, Craig From: Guillaume Lefranc [mailto:guillaume.lefranc@mariadb.com] Sent: 22 April 2016 13:36 To: Craig Bailey <cbailey@evertz.com>; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] GTID replication and SST method Hi Craig, I assume that you use MariaDB Galera Cluster 10.0, in which case GTID's aren't kept in sync across the cluster. I would recommend upgrading to MariaDB Galera Cluster 10.1 and set the two following variables: wsrep_gtid_mode=1 wsrep_gtid_domain_id=N # set this value to a domain_id which isn't allocated in your replication setup. It will be strictly used for Galera If you want to do slave failover with 10.0, this is purely a manual process, and GTID isn't really well supported. An alternative is to use multi-source and create replication connections from each node to the slave. Best regards On Fri, Apr 22, 2016 at 2:25 PM Craig Bailey <cbailey@evertz.com<mailto:cbailey@evertz.com>> wrote: Hi, I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way: • MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID o Pick a master node from the cluster o Take a backup with xtrabackup-v2 o Apply backup to slave o Connect the slave to the master and replicate using MariaDB GTID o If master goes down, switch master to another node in the Galera Clutser However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes. Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below: • Cluster is running fine • Lots of transactions going through, enough that a new joiner would need an SST • Begin joining a new node to the cluster • SST via xtrabackup-v2 happens during this process • The GTID in the donor is 1-1-12345 • SST completes joiner is now part of the cluster • The GTID on the newly joined node is 1-1-1 • We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down. I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue. Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2? On the MariaDB pages it does say “xtrabackup-v2 and xtrabackup SST methods currently do not support GTID” is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst... Thanks, Craig Evertz UK cbailey@evertz.com<mailto:cbailey@evertz.com> www.evertz.com<http://www.evertz.com> This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.
Craig, yes, you are correct. Please refer to our KB for more info: https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_gti... On Fri, Apr 22, 2016 at 2:51 PM Craig Bailey <cbailey@evertz.com> wrote:
Hi Guillaume,
Thanks for the swift response, you are correct with the version I am using (10.0.23).
If I switch to 10.1.x are you saying (assuming I follow your instructions) that the GTID will be kept in sync across my whole cluster, such that at any point I could issue a CHANGE MASTER on my slave to carry on replicating with no issues?
If so it looks like I need to look into whether or not I can upgrade 10.1.x
Thanks,
Craig
*From:* Guillaume Lefranc [mailto:guillaume.lefranc@mariadb.com] *Sent:* 22 April 2016 13:36 *To:* Craig Bailey <cbailey@evertz.com>; maria-discuss@lists.launchpad.net *Subject:* Re: [Maria-discuss] GTID replication and SST method
Hi Craig,
I assume that you use MariaDB Galera Cluster 10.0, in which case GTID's aren't kept in sync across the cluster.
I would recommend upgrading to MariaDB Galera Cluster 10.1 and set the two following variables:
wsrep_gtid_mode=1
wsrep_gtid_domain_id=N # set this value to a domain_id which isn't allocated in your replication setup. It will be strictly used for Galera
If you want to do slave failover with 10.0, this is purely a manual process, and GTID isn't really well supported. An alternative is to use multi-source and create replication connections from each node to the slave.
Best regards
On Fri, Apr 22, 2016 at 2:25 PM Craig Bailey <cbailey@evertz.com> wrote:
Hi,
I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way:
· MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID
o Pick a master node from the cluster
o Take a backup with xtrabackup-v2
o Apply backup to slave
o Connect the slave to the master and replicate using MariaDB GTID
o If master goes down, switch master to another node in the Galera Clutser
However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes.
Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below:
· Cluster is running fine
· Lots of transactions going through, enough that a new joiner would need an SST
· Begin joining a new node to the cluster
· SST via xtrabackup-v2 happens during this process
· The GTID in the donor is 1-1-*12345*
· SST completes joiner is now part of the cluster
· The GTID on the newly joined node is 1-1-*1*
· We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down.
I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue.
Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2?
On the MariaDB pages it does say “xtrabackup-v2 and xtrabackup SST methods currently do not support GTID” is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst...
Thanks,
Craig
Evertz UK
*cbailey@evertz.com <cbailey@evertz.com> *
*www.evertz.com <http://www.evertz.com>*
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
--
Guillaume Lefranc
Remote DBA Services Manager
MariaDB Corporation This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
I don't think that this problem relates to wsrep_gtid_mode. That just forces a particular gtid_domain_id for Galera transactions if I understand right but I suspect it doesn't solve this case. xtrabackup-v2 attempts to blow away all the existing binary logs on the joiner during SST and attempts to copy the most recent binary log on the donor. This does not work well on MariaDB 10.0 due to the lack of log_bin_basename, so xtrabackup can't necessarily find the binary log to copy on the donor. Internally, xtrabackup defaults to the datadir where there is no log_bin_basename, but if your binary logs are elsewhere I think that nothing really gets copied and the whole sequence would reset on the new node. You might see an error in the donors logs in this case - but I'm pretty sure xtrabackup doesn't actually check the result of a the binlog copy operation. Related to binlog/GTID, there are still a few ugly MariaDB specific xtrabackup-v2 bugs lurking here to be aware of. See: https://jira.mariadb.org/browse/MDEV-9423 https://bugs.launchpad.net/percona-xtrabackup/+bug/1570560 The logs don't get flushed correctly by xtrabackup due to a bad version check - and even when that's fixed xtrabackup still doesn't flush the logs in the right order. This leads to a race condition where the copied binary log doesn't always contain enough information for binlog recovery so SST can fail. ~Andrew On Fri, Apr 22, 2016 at 7:53 AM, Guillaume Lefranc <guillaume.lefranc@mariadb.com> wrote:
Craig,
yes, you are correct. Please refer to our KB for more info: https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_gti...
On Fri, Apr 22, 2016 at 2:51 PM Craig Bailey <cbailey@evertz.com> wrote:
Hi Guillaume,
Thanks for the swift response, you are correct with the version I am using (10.0.23).
If I switch to 10.1.x are you saying (assuming I follow your instructions) that the GTID will be kept in sync across my whole cluster, such that at any point I could issue a CHANGE MASTER on my slave to carry on replicating with no issues?
If so it looks like I need to look into whether or not I can upgrade 10.1.x
Thanks,
Craig
From: Guillaume Lefranc [mailto:guillaume.lefranc@mariadb.com] Sent: 22 April 2016 13:36 To: Craig Bailey <cbailey@evertz.com>; maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] GTID replication and SST method
Hi Craig,
I assume that you use MariaDB Galera Cluster 10.0, in which case GTID's aren't kept in sync across the cluster.
I would recommend upgrading to MariaDB Galera Cluster 10.1 and set the two following variables:
wsrep_gtid_mode=1
wsrep_gtid_domain_id=N # set this value to a domain_id which isn't allocated in your replication setup. It will be strictly used for Galera
If you want to do slave failover with 10.0, this is purely a manual process, and GTID isn't really well supported. An alternative is to use multi-source and create replication connections from each node to the slave.
Best regards
On Fri, Apr 22, 2016 at 2:25 PM Craig Bailey <cbailey@evertz.com> wrote:
Hi,
I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way:
· MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID
o Pick a master node from the cluster
o Take a backup with xtrabackup-v2
o Apply backup to slave
o Connect the slave to the master and replicate using MariaDB GTID
o If master goes down, switch master to another node in the Galera Clutser
However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes.
Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below:
· Cluster is running fine
· Lots of transactions going through, enough that a new joiner would need an SST
· Begin joining a new node to the cluster
· SST via xtrabackup-v2 happens during this process
· The GTID in the donor is 1-1-12345
· SST completes joiner is now part of the cluster
· The GTID on the newly joined node is 1-1-1
· We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down.
I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue.
Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2?
On the MariaDB pages it does say “xtrabackup-v2 and xtrabackup SST methods currently do not support GTID” is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_sst...
Thanks,
Craig
Evertz UK
cbailey@evertz.com
www.evertz.com
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
--
Guillaume Lefranc
Remote DBA Services Manager
MariaDB Corporation
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Andrew Garner -
Craig Bailey -
Guillaume Lefranc