I 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 1C4CBDCDCD2EFD2A
One 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 main
After updating the APT source list, execute:
root@db1:~# apt-get update
After 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.6
This is where I got stuck when trying to connect the nodes. Remove apparmor!!! You can check first if apparmor is running:
root@db1:~# apparmor_status
If its running, remove it before causing problems to Percona.
root@db1:~# apt-get remove apparmor
Important 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.php
Then for the 2nd node:
root@db2:~# php monitor_percona.php
After 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] Aborting
To 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
