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.