Bin log files in galera cluster configuration setup

[Sai Ram K]

Hi Guys,

I wanted to know about the usage of mysql-bin log files which will be enabled in the Galera cluster setup.

When we setup a MariaDB 10.1 version to enable the clustering feature we have few mandatory settings like

#clusterconfig

log-bin=/home/fds/galera/mysql-1/data/mysql-bin.log

binlog_format=ROW

query_cache_size=0

innodb_doublewrite=1

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

wsrep_provider=/home/fds/galera/mysql-1/lib/galera/libgalera_smm.so

wsrep_on=ON

wsrep_cluster_name="gcluster"

wsrep_cluster_address="gcomm://NODE2,NODE3"

wsrep_provider_options="pc.weight=1"

wsrep_node_name="gcdeva01"

#State Snapshot Transfer method

wsrep_sst_method=rsync

Here the bin log path is mentioned in every node to enable the cluster feature but i was wandering the how the data will be replicated to all the other nodes.

I don't think that these bin log files are being used to transfer the data to the other nodes.

SST is not using BIN log files.

Please let me know the mechanism behind this.

How the galera cluster will transfer data to all nodes and i am not talking about the transferring methods but the way of syncing data.

For example in master slave concept.

Bin log files are converted to Relay log files in slave and being used

So coming to Galera Cluster how it will be used ?

Thanks in advance.Please help me out.

Regards

SairamK

[hunter86bg]

Dear Sai,

the cluster nodes are communicating with each other via port tcp/4567. Your SST method is rsync, so all the communication between the nodes is using the rsync binary. It is recommended to check the "rsync" and "nc" versions,as incompatibility between the nodes.  By default it's using rsync.Bin-logs are used for regular replication - like regular MySQL master - MySQL slave replication.

By the way, it is not recommended to use the home partition for mysql as all user's home directories are created there by default.

[Sai Ram K]

Thank you for your inputs. I appreciate your help.

[Frédéric Descamps]

Hi Sai, 

With Galera, you need to have binlog_format=ROW that's it, you can even disable binary logs if you want.

Their only usage is:

- if you use asynchronous slave of your cluster

- you need to perform point-in-time recovery

- in some cases for debug

Cheers,

--
You received this message because you are subscribed to the Google Groups "codership" group.
To unsubscribe from this group and stop receiving emails from it, send an email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[James Wang]

Hi Fred,

I need bin logs for backup purposes.  

However, triggers forced me to disable bin logs.

Any ideas please?

Thanks

[hunter86bg]

Most people use percona-xtrabackup as backup solution.
You can also check my experience with : SST Backup via rsync. I don't have enough time to try again SST via xtrabackup.
If you decide to try the SST method, please provide any feedback.

[James Wang]

Agree.  I use percona-xtrabackup as well.  However, bin logs are also needed in cases when I need to restore data which is not in backups yet.

[hunter86bg]

Well, I keep my backups in case someone accidentally deletes something. In all other cases , the rest of the cluster have the data needed and a simultaneous failure of a whole cluster is almost impossible.
By the way you can use Percona's xtrabackup to create incremental backup every hour ,although I don't have experience with that.

[James Wang]

Yes, I do incremental backup as well.

Let me explain why bin log still needed:

1). some one issued "drop table aVeryImportantTable;" by accident

2). whole cluster carried out the command

3). have to restore from backups, say, 1 hour ago

4). if no bin logs, i shall lose 1 hour data

[hunter86bg]

Dear James,

you can't predict every single case and as you have noticed , you can't make every single component in a server foolproof.
That's why you have to implement strict management rules,and restricting access to the production database to a certain amount of qualified personnel .
The politics of our company ,requires  only the admin team to have  access and to implement any changes to the servers and Databases. All actions are implemented on a test machine (which has  exactly the same  software software) ,prior any modification is done on the production machines.
This way you can minimize the risk of someone destroy a database.

In your case , please check the following link : Asyncronous Replication

[James Wang]

Any one can make mistakes ;)

A software bug can also destroy a important table (or whole database) :(

My issue: triggers forced me to disable bin logs.  Please elaborate how "Async Replication" can solve my issue.

Thanks a lot in advance

[hunter86bg]

Dear James,
please ignore the last sentence as it was not intended for you. :)

[Sai Ram K]

Thank you very much for all your comments.

Basically we need bin log enabled to maintain the Galera cluster in sync.

2016-03-15  2:49:04 46935325058816 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'donor' --address 'IP:4444/rsync_sst' --socket '/home/fds/galera/mysql-1/mysql.sock' --datadir '/home/fds/galera/mysql-1/data/'    --binlog '/home/fds/galera/mysql-1/data/mysql-bin' --gtid 'd7c5c50c-d535-11e5-a6f5-1e1b3353c8a3:251927' --gtid-domain-id '0''

As you can see the above line will be executed while data transfer is performed between nodes. So based on this i can tell you that,

Bin logs will be used to transfer the data between nodes in Galera Cluster.

please correct me if am wrong.

Thanks

SaiK

[Sai Ram K]

Hi guys,

Can anyone explain me the grastate.dat file which contains the

Seq no=-1.

Based on this i can see that my grastate.dat file also contains the same as -1 but the data is good and well.

Which does mean that it is not corrupted. But as per the docs if seq no =-1 than it is corrupted.

So please explain me this.

http://galeracluster.com/documentation-webpages/restartingcluster.html

Thanks in advance guys. i appreciate your help.

[hunter86bg]

Dear Sai,

Seqno: -1 means that galera doesn't know it's exact location.
You can use the following command to manually try to recover the position:

mysqld_safe --wsrep-recover

If this doesn't help, you will probably need to do an SST.
Why don't you use the xtrabackup-v2 method for SST ? It's a non-blocking method for the donor, which will allow the cluster to use the donor as well.I've recently moved from rsync to xtrabackup-v2 and although it takes several more minutes than the rsync, It's still pretty fast.

[hunter86bg]

PS: Here is a reference link Recovering Galera