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:
- 10.10.10.76
- 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)