CentOS · CentOS 6 · Database · PerconaDB XtraDB cluster

Percona XtraDB Cluster – 2 Machine setup on Debian Jessie

This article describes how to install and configure PerconaDB xtraDB cluster on Debian 8(Jessie). The installation procedure is same for both the machines.

Installation

Here in this exercise we are going to build a cluster of 2 machines. Let us assume that the 2 machines are having the following IP:

  1. 10.10.10.76
  2. 10.10.10.77


Let us add the PerconaDB repository as the packages are not available in Debian repository

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add the following lines in the file /etc/apt/sources.list

deb http://repo.percona.com/apt jessie main

We can run the following to load the new package list.

apt-get update

Let us upgrade all the packages as well.

apt-get upgrade

Let us install PerconaDB XtraDB cluster using the following command.

apt-get install percona-xtradb-cluster-server-5.6

Configuration on the first node

We need to edit the file /etc/mysql/my.cnf so the following lines look like this

[mysqld]
....
binlog_format = ROW
wsrep_cluster_name = PXC
wsrep_cluster_address = gcomm://10.10.10.76,10.10.10.77
wsrep_node_address = 10.10.10.76
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_sst_method = rsync
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
default_storage_engine=InnoDB

Bootstrapping the first node

Once this is done let us now bootstrap the first node. Let us assume that 10.10.10.76 is the first node.

/etc/init.d/mysql stop
/etc/init.d/mysql bootstrap-pxc

Verification

Let us check if the cluster is initialized by using the following steps:

mysql -u root -p

mysql> show status like 'wsrep%';
+------------------------------+----------------------------------------------+
| Variable_name                | Value                                        |
+------------------------------+----------------------------------------------+
| wsrep_local_state_uuid       | 3b660225-3ae0-11e5-aa40-d33638a17512         |
| wsrep_protocol_version       | 7                                            |
| wsrep_last_committed         | 0                                            |
| wsrep_replicated             | 0                                            |
| wsrep_replicated_bytes       | 0                                            |
| wsrep_repl_keys              | 0                                            |
| wsrep_repl_keys_bytes        | 0                                            |
| wsrep_repl_data_bytes        | 0                                            |
| wsrep_repl_other_bytes       | 0                                            |
| wsrep_received               | 2                                            |
| wsrep_received_bytes         | 125                                          |
| wsrep_local_commits          | 0                                            |
| wsrep_local_cert_failures    | 0                                            |
| wsrep_local_replays          | 0                                            |
| wsrep_local_send_queue       | 0                                            |
| wsrep_local_send_queue_max   | 1                                            |
| wsrep_local_send_queue_min   | 0                                            |
| wsrep_local_send_queue_avg   | 0.000000                                     |
| wsrep_local_recv_queue       | 0                                            |
| wsrep_local_recv_queue_max   | 2                                            |
| wsrep_local_recv_queue_min   | 0                                            |
| wsrep_local_recv_queue_avg   | 0.500000                                     |
| wsrep_local_cached_downto    | 0                                            |
| wsrep_flow_control_paused_ns | 0                                            |
| 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_cert_bucket_count      | 22                                           |
| wsrep_gcache_pool_size       | 131072                                       |
| wsrep_causal_reads           | 0                                            |
| wsrep_cert_interval          | 0.000000                                     |
| wsrep_incoming_addresses     |                                              |
| wsrep_evs_delayed            |                                              |
| wsrep_evs_evict_list         |                                              |
| wsrep_evs_repl_latency       | 1.411e-06/3.5366e-06/6.257e-06/1.89821e-06/5 |
| wsrep_evs_state              | OPERATIONAL                                  |
| wsrep_gcomm_uuid             | 3b657710-3ae0-11e5-83af-eb2791ed7b75         |
| wsrep_cluster_conf_id        | 1                                            |
| wsrep_cluster_size           | 1                                            |
| wsrep_cluster_state_uuid     | 3b660225-3ae0-11e5-aa40-d33638a17512         |
| 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       | 3.11(r93aca2d)                               |
| wsrep_ready                  | ON                                           |
+------------------------------+----------------------------------------------+
58 rows in set (0.01 sec)

Configuration on the second node

We need to edit the file /etc/mysql/my.cnf so the following lines look like this

[mysqld]
....
binlog_format = ROW
wsrep_cluster_name = PXC
wsrep_cluster_address = gcomm://10.10.10.76,10.10.10.77
wsrep_node_address = 10.10.10.77
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_sst_method = rsync
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
default_storage_engine=InnoDB

Second node initialization

Now let us add the second machine to this cluster by running the following command in the second node (10.10.10.77)

service mysql restart

Verification

Let us check whether the new machine is added to the cluster by using the following steps:

mysql -uroot -p

mysql> show status like 'wsrep%';
+------------------------------+---------------------------------------------------+
| Variable_name                | Value                                             |
+------------------------------+---------------------------------------------------+
| wsrep_local_state_uuid       | 3b660225-3ae0-11e5-aa40-d33638a17512              |
| wsrep_protocol_version       | 7                                                 |
| wsrep_last_committed         | 0                                                 |
| wsrep_replicated             | 0                                                 |
| wsrep_replicated_bytes       | 0                                                 |
| wsrep_repl_keys              | 0                                                 |
| wsrep_repl_keys_bytes        | 0                                                 |
| wsrep_repl_data_bytes        | 0                                                 |
| wsrep_repl_other_bytes       | 0                                                 |
| wsrep_received               | 6                                                 |
| wsrep_received_bytes         | 358                                               |
| wsrep_local_commits          | 0                                                 |
| wsrep_local_cert_failures    | 0                                                 |
| wsrep_local_replays          | 0                                                 |
| wsrep_local_send_queue       | 0                                                 |
| wsrep_local_send_queue_max   | 1                                                 |
| wsrep_local_send_queue_min   | 0                                                 |
| wsrep_local_send_queue_avg   | 0.000000                                          |
| wsrep_local_recv_queue       | 0                                                 |
| wsrep_local_recv_queue_max   | 2                                                 |
| wsrep_local_recv_queue_min   | 0                                                 |
| wsrep_local_recv_queue_avg   | 0.166667                                          |
| wsrep_local_cached_downto    | 0                                                 |
| wsrep_flow_control_paused_ns | 0                                                 |
| 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_cert_bucket_count      | 22                                                |
| wsrep_gcache_pool_size       | 131072                                            |
| wsrep_causal_reads           | 0                                                 |
| wsrep_cert_interval          | 0.000000                                          |
| wsrep_incoming_addresses     | ,                                                 |
| wsrep_evs_delayed            |                                                   |
| wsrep_evs_evict_list         |                                                   |
| wsrep_evs_repl_latency       | 0.000333981/0.000573777/0.000770073/0.000147422/6 |
| wsrep_evs_state              | OPERATIONAL                                       |
| wsrep_gcomm_uuid             | 3b657710-3ae0-11e5-83af-eb2791ed7b75              |
| wsrep_cluster_conf_id        | 2                                                 |
| wsrep_cluster_size           | 2                                                 |
| wsrep_cluster_state_uuid     | 3b660225-3ae0-11e5-aa40-d33638a17512              |
| 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       | 3.11(r93aca2d)                                    |
| wsrep_ready                  | ON                                                |
+------------------------------+---------------------------------------------------+
58 rows in set (0.01 sec)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s