The Effect of WSREP_ON

I have a 3 node, multi-master cluster  that maintains a multi-tenant database design.  Each customer has their own database / schema that contains their data.   I have tens of thousands of customers and therefore tens of thousands of databases.  I perform daily backups each night to keep a snapshot of each users’ data for a 2 week period.  This is necessary because it is not uncommon for customers to accidentally delete data from their database and I occasionally need to restore a user’s data from one of the snapshots.  The data is quite critical so the ability to be able to do this on a user-by-user basis is necessary.   

I’ve experimented with the best way to perform the daily backup that is the most efficient, least intrusive, and provides the most practical way to restore a database from the backup when necessary.  I’ve settled on using mysqldump as it provides the most practical way of storing the backups for recovery and also, surprisingly, seems to be the fastest for backing up databases one at a time.  The sql script produced can be loaded back into a running server without any downtime for other customers.  The only problem is that the actual mysqldump process locks tables which can cause a disruption to the customer who’s data is being backed up at that time.  

The best solution to this problem would be to detach the node that the backup is being generated from from the cluster temporarily and then rejoin the cluster once the backup is complete.  However, I’m not sure how to do this.  After some research it appears that setting the WSREP_ON to OFF on the node would be the ideal solution. This is something that could be added to the backup scripts to automatically detach the node temporarily.  However, in experimenting with this variable, setting it to OFF seemed to have no effect at all on the node or the cluster.  

1.  Is WSREP_ON usable?
2. If so, if OFF the correct value?
3. If so, what should I expect to happen when the value is set to OFF.
4. If WSREP_ON cannot be used to achieve the goal of temporarily detaching a node, is there something else that will do this?

Thanks for any recommendations.

Hi,
For WSREP_ON to make an effect on you node, you need to use it as global
SET GLOBAL wsrep_on=OFF
Let me know if this does make a difference.

Thanks for the response; however, trying to set WSREP_ON with SET GLOBAL produces following error:

Error Code: 1228. Variable ‘wsrep_on’ is a SESSION variable and can’t be used with SET GLOBAL

Hi @mwarble
You are right about an error with WSREP_ON , it looks like a bug, I will file one.

What worked for me in this case, on the node you like to run backup, execute following commands before running backup:

mysql&gt; set global&nbsp; pxc_strict_mode=MASTER <br>
mysql&gt; set global wsrep_desync=ON 


after backup, revert both to the original settings.

I’ll give that a try.  Thanks.

That didn’t appear to have any affect on the node.  I set the variables and checked them to make sure they were set to the desired values; however, the node continued to sync.

Node continued to sync, but you should be able to take the backup from that node without affecting other nodes.
Wasn’t it your goal?

That is correct.  I expected the node to stop syncing.  Thanks for the clarification.  I will test the entire process and report my results.