Difference between revisions of "How to install Percona XtraDB Cluster"

From ppwiki
Jump to navigation Jump to search
Line 264: Line 264:
 
  +--------------------+-------+
 
  +--------------------+-------+
 
  1 row in set (0.00 sec)
 
  1 row in set (0.00 sec)
−
so far everything is going well. Before we start the second and third node, we need to create the sstuser we setup in the my.cnf file  
+
So far everything is going well. Before we start the second and third node, we need to create the sst user we setup in the my.cnf file  
−
   
+
  wsrep_sst_auth="sstuser:password"
−
first on the first node
+
* Login again to mysql
 +
* run the commands below to creast the sst user
  
 
==STEP 5- Start mysql on the second and third node==
 
==STEP 5- Start mysql on the second and third node==

Revision as of 22:58, 15 February 2018

For this tutorial, we will be installing:

  • Percona-XetraDB-cluster 5.7
  • Haproxy version 1.6.3
  • sysbench version 1.0.12

Prerequisites

To complete this tutorial, you'll need the following:

  • Four(4) Debian 9(Stretch) servers

This lab has been tested also on Ubuntu 14.04 and Xenial 16.04.

Server Information
Servername IP adress Node Role
db1 10.192.16.58 First db server
db2 10.192.16.59 Second db server
db3 10.192.16.60 Third db server
web1 10.192.16.61 Test client

STEP 1- Install Debian 9 on all 4 servers

Make sure that you install Debian 9 on all servers and all servers are up to date by runnimg the commands below after the install. 

sudo apt-get update

sudo apt-get -y upgrade

Note: If you are not running a DNS server in your environment, make sure you update you /etc/hosts file on each node. 

127.0.0.1     localhost
10.192.16.58  db1
10.192.16.59  db2  
10.192.16.60  db3
10.192.16.61  web1
# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

STEP 2- Install Percona_XtraDB-Cluster on db1,db2 and db3

  • Login as root
  • Use "vim" or "nano" to create a new file "percona.sh".
  • Copy the script below and paste it into the new file.

Percona install script: 

#!/bin/bash
#update all nodes
sudo apt-get update
sudo apt-get -y upgrade
#Install Percon-Xtradb-cluster
sudo apt-get -y install wget
cd /tmp/
wget https://repo.percona.com/apt/percona-release_0.1-5.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-5.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get -y install percona-xtradb-cluster-57
#Stop mysqld on all nodes
sudo systemctl stop mysql
  • Save the file and close it
  • Make the file executable
chmod +x percona.sh
  • Run the script
./percona.sh

During the install, you will be asked to enter the "mysql root password". Enter a password

Use the same password for all 3 nodes

  • After the install complete, check mysql is not running on all node.The script should stop mysql, but it is always good to check.
systemctl status mysql
Active: inactive (dead)

if mysql is running stop it with: 

systemctl stop mysql

STEP 3- Configue Percona_XtraDB-Cluster

Now is time to configure the servers.It is best to start on the first node (db1) open the file /etc/mysql/my.cnf The file will look like this 

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

At the end of the file add the following lines 

[mysqld]
server_id=1
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.58
wsrep_node_name=db1
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

Once done save the file

db1 configuration file

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
[mysqld]
server_id=1
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.58
wsrep_node_name=db1
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

db2 configuration file

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
[mysqld]
server_id=2
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.59
wsrep_node_name=db2
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

db3 configuration file

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
[mysqld]
server_id=3
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.60
wsrep_node_name=db3
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

After editing /etc/mysql/my.cnf, it is time to bootstrap the first node.

Note: For the first node, it is also possible to not pass any values to wsrep_cluster_address=gcomm and bootstrap the node 

wsrep_cluster_address=gcomm://

If you decide to use this option, make sure that for node 2 and node3 the value for wsrep_cluster_address=gcomm is the IP address of the first node 

wsrep_cluster_address=gcomm://10.192.16.58

STEP 4- Bootstrap the first node (db1)

Use the command below to bootstrap the first node 

/etc/init.d/mysql bootstrap-pxc

if everything goes well you will get the message below 

[ ok ] Bootstrapping Percona XtraDB Cluster database server: mysqld ..
  • Login to mysql
mysql -p
  • Enter the passwword used during installation
  • Type
show status like "wsrep_cluster_size";

You should see 1 for the cluster size value 

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

So far everything is going well. Before we start the second and third node, we need to create the sst user we setup in the my.cnf file 

wsrep_sst_auth="sstuser:password"
  • Login again to mysql
  • run the commands below to creast the sst user

STEP 5- Start mysql on the second and third node

For the second and third node it is very simple. Just run the command before 

systemctl start mysql

If you get no errors, that means we have added the node to the cluster.

To check, run: show status like "wsrep_cluster_size"; on any node. The cluster size value should be now 3 

mysql> show status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

STEP 6- Install the test client node (web1)

Retrieved from "https://papaulgigitech.com/index.php?title=How_to_install_Percona_XtraDB_Cluster&oldid=91"