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 ; 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 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.commailto:cbailey@evertz.com www.evertz.comhttp://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.netmailto: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.

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 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 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 ; 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 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