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

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

maganmagan EntrantCurrent User Role Beginner
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 <[email protected]> |
| 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.
[email protected]:~$ 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. confused.png

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    | 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.
  • maganmagan Entrant Current User Role Beginner
    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).
    [fail]
    .................................................. .................................................. ...........
    Is there any help to solve this?
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
  • maganmagan Entrant Current User Role Beginner
    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.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Try increasing max_connect_errors, from the manual https://dev.mysql.com/doc/refman/5.6/en/blocked-host.html
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.