1. Synchronous Replication: Transaction either committed on all nodes or none.
2. Multi-Master Replication: You can write to any node
3. Parallel applying events on slave. Real “parallel replication”.
4. Automatic node provisioning.
5. Data consistency. No more unsynchronized slaves.

1. The cluster consists of nodes. The cluster’s recommended configuration is to have 3 nodes, however 2 nodes can be used as well.
2. Every node is a regular Mysql / Percona server setup. You can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base or you can detach Node from Cluster and use it as a regular server.
3. Each node will contain full copy of data.

• Whenever you execute a query, it is executed locally. All data is available locally, so no remote access is required.
• No central management. You can loose any node at any time, and cluster will continue functioning.
• It is a good solution for scaling read workload. You can put read queries to any of the nodes.

• Overhead of joining new node. New node will copy all data from an existing node. If it is 100 GB, it will copy 100 GB.
• Not an effective write scaling solution. All writes have to go on all nodes.
• Duplication of data. If you have 3 nodes, there will be 3 duplicates.

Difference between Percona XtraDB Cluster and MySQL Replication

• MySQL replication has: Availability and Partitioning tolerance.
• Percona XtraDB Cluster has: Consistency and Availability.

• Percona Server with XtraDB and includes Write Set Replication patches.

• Galera Library: A generic synchronous Multi-Master replication plugin for transactional applications.
• Galera supports:
• Incremental State Transfer (IST), useful in WAN deployments.
• RSU, Rolling Schema Update. Schema change does not block operations against table.

• Currently replication work only with InnoDB storage engine.

• Unsupported queries:
• LOCK/UNLOCK tables
• lock function (GET_LOCK(), RELEASE_LOCK()….)

• Due to cluster level concurrency control, transaction issuing COMMIT may be aborted at that stage.

• The write throughput of whole cluster is limited by weakest node. If one node becomes slow, whole cluster will become slow.

1. State Snapshot Transfer (SST): SST method performs full copy of data from one node to other. It’s used when a new node joins the cluster. One of the existing node will transfer data to it.
    There are three available methods of SST:

• mysqldump
• rsync
• xtrabackup

2. Incremental State Transfer (IST): If a node is down for a short period of time, and then starts up, the node is able to fetch only those changes made during the period it was down.

• Multi-Master replication stands for the ability to write to any node in the cluster, and not to worry that it will get out-of-sync situation, as it regularly happens with regular MySQL replication if you imprudently write to the wrong server.
• With Percona XtraDB Cluster you can write to any node, and the Cluster guarantees consistency of writes. That is, the write is either committed on all the nodes or not committed at all.

node 1
hostname: percona_xtradb_cluster1
IP: 192.168.1.2

node 2
hostname: percona_xtradb_cluster2
IP: 192.168.1.3

node 3
hostname: percona_xtradb_cluster3
IP: 192.168.1.4

$ echo "deb http://repo.percona.com/apt precise main" >> /etc/apt/sources.list.d/percona.list
$ echo "deb-src http://repo.percona.com/apt precise main" >> /etc/apt/sources.list.d/percona.list
$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

STEP 2 : After adding percona repository, Update apt cache so that new packages can be included in our apt-cache.

$ apt-get update

$ apt-get install -y percona-xtradb-cluster-56 qpress xtrabackup

$ apt-get install -y python-software-properties vim wget curl netcat

[MYSQLD] #This section is for mysql configuration
user = mysql                                                   
default_storage_engine = InnoDB                  
basedir = /usr
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
innodb_autoinc_lock_mode = 2
log_queries_not_using_indexes = 1
max_allowed_packet = 128M
binlog_format = ROW
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_node_address = 192.168.1.2
wsrep_cluster_name="newcluster"
wsrep_cluster_address = gcomm://192.168.1.2,192.168.1.3,192.168.1.4
wsrep_node_name = cluster1
wsrep_slave_threads = 4
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sst:secret

[sst] #This section is for sst(state snapshot transfer) configuration 
streamfmt = xbstream

[xtrabackup] #This section is defines tuning configuration for xtrabackup
compress
compact
parallel = 2
compress_threads = 2
rebuild_threads = 2

$ service mysql bootstrap-pxc

$ mysql -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';"

$ mysql -e "show global status like 'wsrep%';"

[MYSQLD]
user = mysql
default_storage_engine = InnoDB
basedir = /usr
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
innodb_autoinc_lock_mode = 2
log_queries_not_using_indexes = 1
max_allowed_packet = 128M
binlog_format = ROW
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_node_address = 192.168.1.3
wsrep_cluster_name="newcluster"
wsrep_cluster_address = gcomm://192.168.1.2,192.168.1.3,192.168.1.4
wsrep_node_name = cluster2
wsrep_slave_threads = 4
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sst:secret

[sst]
streamfmt = xbstream

[xtrabackup]
compress
compact
parallel = 2

$ service mysql start

$ mysql -e "show global status like 'wsrep%';"

Changes in configuration for node 3 :
      wsrep_node_address = 192.168.1.4
      wsrep_node_name = cluster3

$ service mysql start

<code prettyprint="" style="color: black; word-wrap: no 
mysql>create database opstree;
mysql>use opstree;
mysql>create table nu113r(name varchar(50));
mysql>insert into nu113r values("zukin");
mysql>select * from nu113r;

Check the database on other node by mysql client:

mysql>show databases;

Note : There should be a database named “opstree”.

mysql>use opstree;

mysql>select * from nu113r; 

Note : Data will be same as in the previous node.

Let me know if you have any suggestions. You can also contact me at belwal.mohit@gmail.com.