Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

The Effect of WSREP_ON

mwarblemwarble ContributorCurrent User Role Supporter
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.

Best Answer

Answers

  • vadimtkvadimtk Contributor Percona Staff Role
    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.

  • mwarblemwarble Contributor Current User Role Supporter
    edited June 8
    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

  • vadimtkvadimtk Contributor Percona Staff Role
    edited June 11
    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> set global  pxc_strict_mode=MASTER 
    mysql> set global wsrep_desync=ON 

    after backup, revert both to the original settings.
  • mwarblemwarble Contributor Current User Role Supporter
    I'll give that a try.  Thanks.
  • mwarblemwarble Contributor Current User Role Supporter
    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.
  • mwarblemwarble Contributor Current User Role Supporter
    That is correct.  I expected the node to stop syncing.  Thanks for the clarification.  I will test the entire process and report my results. 
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.