C3rd
MySQL + Percona XtraDB Cluster 5.6
Posted: 21 Mar 2015, 21:02pm - SaturdayI have been experimenting on MySQL + Percona XtraDB Cluster (version 5.6). In my case, I used VMWare/VirtualBox. I created two images and label as DB1 and DB2 using Ubuntu 14.04.2 32bit (Trusty Tahr). My goal is to replicate the database from DB1 to DB2. If DB1 will be down, then DB2 will take over DB1. While fixing DB1, DB2 will serve then if DB1 is back online, DB1 will be the "Joiner" to the "Donor" (DB2). ...and vice versa. Installation First thing you need is to prepare the installation of Percona XtraDB Cluster. (must be root or right high privileges on performing this installation)
root@db1:~# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2AOne thing you need to ensure is you have all Ubuntu official repositories plus Percona APT repository. So what I did is replace /etc/apt/source.list with:
# deb cdrom:[Ubuntu-Server 14.04.2 LTS _Trusty Tahr_ - Release i386 (20150218.1)]/ trusty main restricted ###### Ubuntu Main Repos deb http://us.archive.ubuntu.com/ubuntu/ trusty main restricted universe multiverse deb-src http://us.archive.ubuntu.com/ubuntu/ trusty main restricted universe multiverse ## Ubuntu Security Updates deb http://security.ubuntu.com/ubuntu trusty-security main deb-src http://security.ubuntu.com/ubuntu trusty-security main deb http://security.ubuntu.com/ubuntu trusty-security universe deb-src http://security.ubuntu.com/ubuntu trusty-security universe deb http://security.ubuntu.com/ubuntu trusty-security multiverse deb-src http://security.ubuntu.com/ubuntu trusty-security multiverse ## Uncomment the following two lines to add software from Canonical's ## 'partner' repository. ## This software is not part of Ubuntu, but is offered by Canonical and the ## respective vendors as a service to Ubuntu users. deb http://archive.canonical.com/ubuntu trusty partner deb-src http://archive.canonical.com/ubuntu trusty partner ## Uncomment the following two lines to add software from Ubuntu's ## 'extras' repository. ## This software is not part of Ubuntu, but is offered by third-party ## developers who want to ship their latest software. deb http://extras.ubuntu.com/ubuntu trusty main deb-src http://extras.ubuntu.com/ubuntu trusty main ###### Ubuntu Update Repos deb http://us.archive.ubuntu.com/ubuntu/ trusty-security main restricted universe multiverse deb http://us.archive.ubuntu.com/ubuntu/ trusty-updates main restricted universe multiverse deb http://us.archive.ubuntu.com/ubuntu/ trusty-backports main restricted universe multiverse deb-src http://us.archive.ubuntu.com/ubuntu/ trusty-security main restricted universe multiverse deb-src http://us.archive.ubuntu.com/ubuntu/ trusty-updates main restricted universe multiverse deb-src http://us.archive.ubuntu.com/ubuntu/ trusty-backports main restricted universe multiverse # Percona XtraDB Cluster deb http://repo.percona.com/apt trusty main deb-src http://repo.percona.com/apt trusty mainAfter updating the APT source list, execute:
root@db1:~# apt-get updateAfter that, you can install Percona XtraDB Cluster server and client packages:
root@db1:~# apt-get install percona-server-server-5.6 percona-server-client-5.6This is where I got stuck when trying to connect the nodes. Remove apparmor!!! You can check first if apparmor is running:
root@db1:~# apparmor_statusIf its running, remove it before causing problems to Percona.
root@db1:~# apt-get remove apparmorImportant Notes: Do the same installation in the 2nd node or DB2. Then to run the primary node (donor), DB1 in our case:
root@db1:~# service mysql bootstrap-pxc * Bootstrapping Percona XtraDB Cluster database server mysqld [ OK ] root@db1:~#Then run the 2nd node or joiner (DB2):
root@db2:~# service mysql start mysql start/running, process 1550 root@db2:~#~ or ~
root@db2:~# service mysql restart mysql stop/waiting mysql start/running, process 1550 root@db2:~#Note: Always start first the primary node (DB1) then the next node (DB2) Testing Check if primary node (DB1) is working...
root@db1:~# mysql -u root -p -e "show status where Variable_name like '%wsrep_cluster%' OR Variable_name like '%wsrep_ready%';" Enter password: +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 5 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 1fa1e1fc-cf8e-11e4-9664-3ea415c4a429 | | wsrep_cluster_status | Primary | | wsrep_ready | ON | +--------------------------+--------------------------------------+ root@db1:~# mysql -u root -p -e "show binary logs;" Enter password: +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 120 | +------------------+-----------+Next, check the 2nd node (DB2)...
root@db2:/etc/mysql# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.22-72.0-56-log Percona XtraDB Cluster (GPL), Release rel72.0, Revision 978, WSREP version 25.8, wsrep_25.8.r4150 Copyright (coffee) 2009-2014 Percona LLC and/or its affiliates Copyright (coffee) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>More TEST: I created a PHP script for DB1 and DB2 to test the replication. DB1 PHP Script (test_percona.php):
<?php
$connect = mysql_connect('localhost','root','secret');
mysql_select_db('test', $connect);
// insert if table not exists...
@mysql_query("create table if not exists data_test(
id bigint(20) unsigned not null auto_increment primary key,
data varchar(128),
created datetime
);");
// inject data infinitely
while (true) {
$data = sha1(time().rand(100,99999));
echo $data." -> ".date("j M Y g:i.s a - l\n");
@mysql_query("INSERT INTO data_test (data,created) VALUES ('".$data."', NOW())");
}
?>
DB2 PHP Script (monitor_percona.php):
<?php
$connect = mysql_connect('localhost','root','secret');
mysql_select_db('test', $connect);
$last_id = 0;
while (true) {
$result = mysql_query("SELECT * FROM data_test WHERE id > ".$last_id." ORDER BY id ASC");
$max = mysql_num_rows($result);
if ($max > 0) {
for ($i = 0; $i < $max; $i++) {
echo mysql_result($result,$i,"data")." -> ".date("j M Y g:i.s a - l", strtotime(mysql_result($result,$i,"created")))."\n";
$last_id = mysql_result($result,$i,"id");
}
}
}
?>
Then you can run the script on both nodes. To run in DB1 node, do:
root@db1:~# php test_percona.phpThen for the 2nd node:
root@db2:~# php monitor_percona.phpAfter that, you must see same output both nodes.
Encountered Issues (tail -f /var/log/mysql/error.log):
2015-03-21 20:19:11 6613 [Warning] WSREP: Gap in state sequence. Need state transfer. 2015-03-21 20:19:11 6613 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.9.103' --auth 'sstuser:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '6613' '' ' 2015-03-21 20:19:11 6613 [ERROR] execlp() failed: Permission denied 2015-03-21 20:19:11 6613 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.9.103' --auth 'sstuser:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '6613' '' Read: '(null)' 2015-03-21 20:19:11 6613 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.9.103' --auth 'sstuser:secret' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '6613' '' : 1 (Operation not permitted) 2015-03-21 20:19:11 6613 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable. 2015-03-21 20:19:11 6613 [ERROR] AbortingTo solve the issue is to remove the apparmor --- Reference:
- http://www.percona.com/doc/percona-server/5.6/installation/apt_repo.html
- http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/ubuntu_howto.html
- http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/pit_recovery_ibk.html