Not the answer you need?
Register and ask your own question!

3 node system manual sync with sql file

zmahomedyzmahomedy EntrantInactive User Role Beginner

I have a 1 gig sql file which I took from the primary node and copied it to my 2nd and 3rd node

I started my second node as a regular mysql server without the galera options and restored the db
I then restarted the db as a cluster node and it synced up very fast

On my 3rd node, I did the same thing however, SST is removing all the databases and syncing from the primary node
which is what I am trying to avoid when deploying out new nodes.. the sync takes couple of hours over my vpn

Im not sure what is needed to be in place so that the manual restore stays and the sst just checks for missing stuff
At this time for testing there are no updates going through the primary node so all data is the same

any help will be appriciated



  • shockwavecsshockwavecs Contributor Inactive User Role Beginner
    edit: disclaimer: I haven't used this before, just thinking it would be the solution.

    I would think you could have you my.cnf say something like "wsrep_sst_method=rsync". On the remote node change it to "wsrep_sst_method=skip". After it syncs you can change it back to rsync. Here is the section of the documentation I found this from:

    variable wsrep_sst_method

    Command Line : Yes

    Config File : Yes

    Scope: Global

    Dynamic: Yes

    Default Value: rsync

    Recommended: xtrabackup-v2

    This variable sets up the method for taking the State Snapshot Transfer (SST). Available options are:
    • xtrabackup - uses Percona XtraBackup to perform the SST, this method requires wsrep_sst_auth to be set up with <user>:<password> which XtraBackup will use on donor. Privileges and permissions needed for running XtraBackup can be found here.
    • xtrabackup-v2 - This is same as xtrabackup SST except that it uses newer protocol, hence is not compatible. This is the recommended option for PXC 5.5.34 and above. For more details, please check Xtrabackup SST Configuration and Percona XtraDB Cluster Errata (as of 5.5.34).
    • rsync - uses rsync to perform the SST, this method doesn’t use the wsrep_sst_auth
    • mysqldump - uses mysqldump to perform the SST, this method requires wsrep_sst_auth to be set up with <user>:<password>, where user has root privileges on the server.
    • custom_script_name - Galera supports Scriptable State Snapshot Transfer. This enables users to create their own custom script for performing an SST.
    • skip - this option can be used to skip the SST, it can be used when initially starting the cluster and manually restore the same data to all nodes. It shouldn’t be used as permanent setting because it could lead to data inconsistency across the nodes.
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.