Clustering 3 node .Why this error 'Percona XtraDB Cluster Node is not synced or non-PRIM' .help me

Hi,
I am trying to setup Percona XtraDB Cluster 5.5 on 3 nodes. I am using ubuntu 12.04 precise.

Node 1: 192.168.X.A
[mysqld]
datadir=/var/lib/mysql
user=mysql

Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.X.A,192.168.X.B,192.168.X.C

In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

Node #2 address

wsrep_node_address=192.168.X.A

SST method

wsrep_sst_method=xtrabackup-v2

Cluster name

wsrep_cluster_name=my_ubuntu_cluster

Authentication for SST method

wsrep_sst_auth=“replica:replica123”

#user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
… … …
Node 2:192.168.X.B

[mysqld]
datadir=/var/lib/mysql
user=mysql

Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.X.B,192.168.X.A,192.168.X.C

In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

Node #2 address

wsrep_node_address=192.168.X.B

SST method

wsrep_sst_method=xtrabackup-v2

Cluster name

wsrep_cluster_name=my_ubuntu_cluster

Authentication for SST method

wsrep_sst_auth=“replica:replica123”

#user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
… …
Node3 :192.168.X.C

[mysqld]
datadir=/var/lib/mysql
user=mysql

Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.X.C, 192.168.X.A, 192.168.X.B

In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

Node #2 address

wsrep_node_address=192.168.X.C

SST method

wsrep_sst_method=xtrabackup-v2

Cluster name

wsrep_cluster_name=my_ubuntu_cluster

Authentication for SST method

wsrep_sst_auth=“replica:replica123”

#user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking


I stoped my mysql instance, There is NO another mysql stance running. Also i configured the Firewall by allowing the following ports for each node
sudo ufw allow 3306/tcp
sudo ufw allow 4567/tcp
sudo ufw allow 4568/tcp
sudo ufw allow 4444/tcp

Moreover, i bootstrapped all the 3 nodes after configuration by running as root : /etc/init.d/mysql bootstrap -pxc .

  • -WHen i check the cluster, the cluster size is 1 instead of three. that means my nodes are synced, bu i dont know the problem.
    NOde 1;
    mysql> SHOW STATUS like ‘%wsrep%’;
    ±---------------------------±-------------------------------------+
    | Variable_name | Value |
    ±---------------------------±-------------------------------------+
    | wsrep_local_state_uuid | 9cb8fb28-7870-11e5-b3fe-c28542dbd610 |
    | wsrep_protocol_version | 4 |
    | wsrep_last_committed | 5 |
    | wsrep_replicated | 4 |
    | wsrep_replicated_bytes | 762 |
    | wsrep_received | 12 |
    | wsrep_received_bytes | 916 |
    | wsrep_local_commits | 0 |
    | wsrep_local_cert_failures | 0 |
    | wsrep_local_replays | 0 |
    | wsrep_local_send_queue | 0 |
    | wsrep_local_send_queue_avg | 0.000000 |
    | wsrep_local_recv_queue | 0 |
    | wsrep_local_recv_queue_avg | 0.000000 |
    | wsrep_flow_control_paused | 0.000000 |
    | wsrep_flow_control_sent | 0 |
    | wsrep_flow_control_recv | 0 |
    | wsrep_cert_deps_distance | 0.000000 |
    | wsrep_apply_oooe | 0.000000 |
    | wsrep_apply_oool | 0.000000 |
    | wsrep_apply_window | 0.000000 |
    | wsrep_commit_oooe | 0.000000 |
    | wsrep_commit_oool | 0.000000 |
    | wsrep_commit_window | 0.000000 |
    | wsrep_local_state | 4 |
    | wsrep_local_state_comment | Synced |
    | wsrep_cert_index_size | 0 |
    | wsrep_causal_reads | 0 |
    | wsrep_incoming_addresses | 192.168.1.31:3306 |
    | wsrep_cluster_conf_id | 5 |
    | wsrep_cluster_size | 1 |
    | wsrep_cluster_state_uuid | 9cb8fb28-7870-11e5-b3fe-c28542dbd610 |
    | wsrep_cluster_status | Primary |
    | wsrep_connected | ON |
    | wsrep_local_bf_aborts | 0 |
    | wsrep_local_index | 0 |
    | wsrep_provider_name | Galera |
    | wsrep_provider_vendor | Codership Oy <info@codership.com> |
    | wsrep_provider_version | 2.12(r318911d) |
    | wsrep_ready | ON |
    | wsrep_thread_count | 2

When check cluster it says, ‘XtraDB Cluster Nod not synced’ and it is true because still my wsrep_cluster_size is 1 as seen in the above result.
node1@ubuntu:~$ sudo clustercheck
[sudo] password for node1:
HTTP/1.1 503 Service Unavailable
Content-Type: text/plain
Connection: close
Content-Length: 57

Percona XtraDB Cluster Node is not synced or non-PRIM.

i am confused i don’t what is wrong with it. i am beginner of this perconnaXtradb clustering. Please help me.

| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 9cb8fb28-7870-11e5-b3fe-c28542dbd610 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |

You only need to bootstrap one node and then the other two nodes you need to start up normally.

Node1:

service mysql start --bootstrap-pxc

Node2 and Node3:

service mysql start

We recommend to use Percona XtraDB Cluster 5.6 as much as possible.

Dear jrivera I changed to Percona XtraDB Cluster 5.6 , but now when I bootstrap the first node using (service mysql start --bootstrap-pxc) or start the other two nodes using (service mysql start), I am getting the below error message:
… …
sudo service mysql start

  • Starting MySQL (Percona XtraDB Cluster) database server mysqld
  • The server quit without updating PID file (/var/run/mysqld/mysqld.pid).
    [COLOR=#FF0000] [fail]
    … … …
    Is there any help to solve this?

you need to check the logs from the bootstrapped node. did node1 (the node you bootstrapped) start up or not? The output you shared does not help in diagnosing the problem. As with anything else, the error log is your friend it will give you an idea on what the problem is and how to resolve it.

Dear jrivera thank your for your generous help. now my 3 node cluster is working fine. But I have problem regarding to accessing the cluster. when I try to log in it gives error message:
ERROR 1129 (HY000): Host ‘192.168.40.3’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

but it needs to be used MySQL >Flush hosts on any one of them and it works in that way. My question is: since using ‘flush hosts’ is trivial work that needs to be done each time this error happens, is there a better alternative way of solving this issue?
Thanks you.

Try increasing max_connect_errors, from the manual https://dev.mysql.com/doc/refman/5.6/en/blocked-host.html