3 node system manual sync with sql file

[zmahomedy]

hi

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

zak

[shockwavecs]

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:

http://www.percona.com/doc/percona-x…tem-index.html

variable wsrep_sst_method [TABLE=“class: wysiwyg_table_wysiwyg_table_docutils wysiwyg_table_wysiwyg_table_field-list”]
[TR=“class: wysiwyg_table_wysiwyg_table_field-odd wysiwyg_table_wysiwyg_table_field”]
[TD=“class: wysiwyg_table_wysiwyg_table_field-body”]Command Line : Yes[/TD]
[/TR]
[TR=“class: wysiwyg_table_wysiwyg_table_field-even wysiwyg_table_wysiwyg_table_field”]
[TD=“class: wysiwyg_table_wysiwyg_table_field-body”]Config File : Yes[/TD]
[/TR]
[TR=“class: wysiwyg_table_wysiwyg_table_field-odd wysiwyg_table_wysiwyg_table_field”]
[TD=“class: wysiwyg_table_wysiwyg_table_field-body”]Scope: Global[/TD]
[/TR]
[TR=“class: wysiwyg_table_wysiwyg_table_field-even wysiwyg_table_wysiwyg_table_field”]
[TD=“class: wysiwyg_table_wysiwyg_table_field-body”]Dynamic: Yes[/TD]
[/TR]
[TR=“class: wysiwyg_table_wysiwyg_table_field-odd wysiwyg_table_wysiwyg_table_field”]
[TD=“class: wysiwyg_table_wysiwyg_table_field-body”]Default Value: rsync[/TD]
[/TR]
[TR=“class: wysiwyg_table_wysiwyg_table_field-even wysiwyg_table_wysiwyg_table_field”]
[TD=“class: wysiwyg_table_wysiwyg_table_field-body”]Recommended: xtrabackup-v2[/TD]
[/TR]
[/TABLE]
This variable sets up the method for taking the State Snapshot Transfer (SST). Available options are:
[LIST]
[]xtrabackup - uses Percona XtraBackup to perform the SST, this method requires wsrep_sst_auth to be set up with : 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 :, 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.
[/LIST]