This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and it could serve as a reference architecture.
You will need 4 virtual machines. 3 for PXC and 1 for the client, which will have HaProxy. I used CentOS 6 as the operating system, the instructions are similar for any Linux distribution.
The client node will have HaProxy installed and it will redirect requests to PXC nodes. This approach works well in real-world scenarios too. Running HaProxy on the application servers instead of having them as dedicated entities gives you benefits like you don’t need an extra network roundtrip because of a loadbalancer and scalability of PXC’s load balancing layer scales simply with application servers.
We will use Percona and EPEL repositories for software installation.
To add these follow the instructions for
Percona and EPEL to enable those.
After configuring the repositories you will be able to install software that we will use. First, install PXC on the database nodes.
1 |
# yum -y install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client percona-xtrabackup |
Install haproxy and sysbench on the client node.
1 |
# yum -y install haproxy sysbench |
After installing everything, let’s configure PXC first. On the first node, my.cnf should look something like this on a relatively weak machine.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[mysqld] server_id=1 binlog_format=ROW log_bin=mysql-bin wsrep_cluster_address=gcomm:// wsrep_provider=/usr/lib/libgalera_smm.so datadir=/var/lib/mysql wsrep_slave_threads=2 wsrep_cluster_name=pxctest wsrep_sst_method=xtrabackup wsrep_node_name=ip-10-112-39-98 log_slave_updates innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 innodb_buffer_pool_size=400M innodb_log_file_size=64M |
You can start your first node now. Make sure that you only start second and third nodes when the first node is up and running (it will serve as a donor for SST).
This configuration is for the first node. For the second and third node, you need to change wsrep_cluster_address (alternatively, you can use wsrep_urls in [mysqld_safe] section), which should point to a node in the cluster which is already up, so it will join the cluster. The server_id and wsrep_node_name variables have to be different on each host, for wsrep_node_name, you can use the output of hostname command.
Based on that, for the second node, the differences in the configuration should be the following.
1 2 3 |
server_id=2 wsrep_cluster_address=gcomm://10.116.39.76 # replace this with the IP of your first node wsrep_node_name=ip-10-244-33-92 |
For the third node, the differences look like this.
1 2 3 |
server_id=3 wsrep_cluster_address=gcomm://10.116.39.76 # replace this with the IP of your first node wsrep_node_name=ip-10-194-10-179 |
For SST we use xtrabackup. This means at startup time, the new node will connect to an existing node in the cluster and it takes a backup of that node with xtrabackup and copies it to the new node with netcat. After a successful SST, you should see this in the error log.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
120619 13:20:17 [Note] WSREP: State transfer required: Group state: 77c9da88-b965-11e1-0800-ea53b7b12451:97 Local state: 00000000-0000-0000-0000-000000000000:-1 120619 13:20:17 [Note] WSREP: New cluster view: global state: 77c9da88-b965-11e1-0800-ea53b7b12451:97, view# 18: Primary, number of nodes: 3, my index: 0, protocol version 2 120619 13:20:17 [Warning] WSREP: Gap in state sequence. Need state transfer. 120619 13:20:19 [Note] WSREP: Running: 'wsrep_sst_xtrabackup 'joiner' '10.195.206.117' '' '/var/lib/mysql/' '/etc/my.cnf' '20758' 2>sst.err' 120619 13:20:19 [Note] WSREP: Prepared SST request: xtrabackup|10.195.206.117:4444/xtrabackup_sst 120619 13:20:19 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 120619 13:20:19 [Note] WSREP: Assign initial position for certification: 97, protocol version: 2 120619 13:20:19 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (77c9da88-b965-11e1-0800-ea53b7b12451): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():439. IST will be unavailable. 120619 13:20:19 [Note] WSREP: Node 0 (ip-10-244-33-92) requested state transfer from '*any*'. Selected 1 (ip-10-112-39-98)(SYNCED) as donor. 120619 13:20:19 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 102) 120619 13:20:19 [Note] WSREP: Requesting state transfer: success, donor: 1 120619 13:20:59 [Note] WSREP: 1 (ip-10-112-39-98): State transfer to 0 (ip-10-244-33-92) complete. 120619 13:20:59 [Note] WSREP: Member 1 (ip-10-112-39-98) synced with group. 120619 13:21:17 [Note] WSREP: SST complete, seqno: 105 120619 13:21:17 [Note] Plugin 'FEDERATED' is disabled. 120619 13:21:17 InnoDB: The InnoDB memory heap is disabled 120619 13:21:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120619 13:21:17 InnoDB: Compressed tables use zlib 1.2.3 120619 13:21:17 InnoDB: Using Linux native AIO 120619 13:21:17 InnoDB: Initializing buffer pool, size = 400.0M 120619 13:21:17 InnoDB: Completed initialization of buffer pool 120619 13:21:18 InnoDB: highest supported file format is Barracuda. 120619 13:21:18 InnoDB: Waiting for the background threads to start 120619 13:21:19 Percona XtraDB (https://www.percona.com) 1.1.8-rel25.3 started; log sequence number 246661644 120619 13:21:19 [Note] Recovering after a crash using mysql-bin 120619 13:21:19 [Note] Starting crash recovery... 120619 13:21:19 [Note] Crash recovery finished. 120619 13:21:19 [Note] Server hostname (bind-address): '(null)'; port: 3306 120619 13:21:19 [Note] - '(null)' resolves to '0.0.0.0'; 120619 13:21:19 [Note] - '(null)' resolves to '::'; 120619 13:21:19 [Note] Server socket created on IP: '0.0.0.0'. 120619 13:21:19 [Note] Event Scheduler: Loaded 0 events 120619 13:21:19 [Note] WSREP: Signalling provider to continue. 120619 13:21:19 [Note] WSREP: Received SST: 77c9da88-b965-11e1-0800-ea53b7b12451:105 120619 |