Percona XtraDB Cluster

Out of the box Morpheus uses MySQL but Morpheus supports any MySQL compliant database. There are many ways to set up a highly available, MySQL dialect based database. One which has found favor with many of our customers is Percona’s XtraDB Cluster. Percona’s product is based off of Galera’s WSREP Clustering, which is also supported.

Important

Currently, you must use a v5.7-compatible version of MySQL/Percona. Complete compatibility information is available in the Compatibility and Breaking Changes page.

Storage Requirements

• 30 GB storage minimum for each database node. This should be monitored and increased if the Morpheus database requires more space.

After database installation ensure that the minimum storage requirement is available for the mysql tmpdir. By default mysql will write temporary files in “/tmp”. The mysql tmpdir configuration can be modified using the following steps for each database node:

1. Create the new directory.

mkdir /path/to/mysql/tmp/directory
chown -R mysql:mysql /path/to/mysql/tmp/directory

1. Edit /etc/my.cnf.

   [mysqld]
   tmpdir=/path/to/mysql/tmp/directory
   

Important

Failing to provide sufficient storage to the mysql tmpdir can result in failed database migrations and Morpheus upgrades.

Permission Requirements

Percona requires the following ports for the cluster nodes. Please create the appropriate firewall rules on your Percona nodes.

• 3306

• 4444

• 4567

• 4568

Configure SElinux (optional)

When SELinux is set to Enforcing, by default it will block Percona Cluster communication.

To allow Percona XtraDB Cluster functionality when SELinux is Enforcing, run the following on each Database Node:

1. Install SELinux utilities

   [root]# yum install -y policycoreutils-python.x86_64
   

2. Configure Percona ports for SELinux:

   [root]# semanage port -m -t mysqld_port_t -p tcp 4444
   [root]# semanage port -m -t mysqld_port_t -p tcp 4567
   [root]# semanage port -a -t mysqld_port_t -p tcp 4568
   

3. Create the policy file PXC.te

   [root]# vi PXC.te
     module PXC 1.0;
     require {
             type unconfined_t;
             type mysqld_t;
             type unconfined_service_t;
             type tmp_t;
             type sysctl_net_t;
             type kernel_t;
             type mysqld_safe_t;
             class process { getattr setpgid };
             class unix_stream_socket connectto;
             class system module_request;
             class file { getattr open read write };
             class dir search;
      }
   
      #============= mysqld_t ==============
   
      allow mysqld_t kernel_t:system module_request;
      allow mysqld_t self:process { getattr setpgid };
      allow mysqld_t self:unix_stream_socket connectto;
      allow mysqld_t sysctl_net_t:dir search;
      allow mysqld_t sysctl_net_t:file { getattr open read };
      allow mysqld_t tmp_t:file write;
   

4. Compile and load the SELinux policy

   [root]# checkmodule -M -m -o PXC.mod PXC.te
   [root]# semodule_package -o PXC.pp -m PXC.mod
   [root]# semodule -i PXC.pp
   

Add Percona Repo

1. Add the percona repo to your Linux Distro.

   [root]# wget https://www.percona.com/downloads/RPM-GPG-KEY-percona && rpm --import RPM-GPG-KEY-percona
   
   [root]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
   

2. The below commands will clean the repos and update the server.

   [root]# yum clean all
   [root]# yum update -y --skip-broken
   

Installing Percona XtraDB Cluster

Installation and configuration of Percona XtraDB Cluster on CentOS/RHEL 7.

Important

This is a sample configuration only. Customer configurations and requirements will vary.

1. Install the Percona XtraDB Cluster software and it’s dependences.

   [root]# yum install -y Percona-XtraDB-Cluster-57
   

2. Enable the mysql service so that the service started at boot.

   [root]# systemctl enable mysql
   

3. Start mysql

   [root]# systemctl start mysql
   

4. Log into the mysql server and set a new password. To get the temporary root mysql password you will need to run the below command.The command will print the password to the screen. Copy the password.

   [root]# grep 'temporary password' /var/log/mysqld.log
   

5. Login to mysql

   [root]# mysql -u root -p
     password: `enter password copied above`
   

6. Change the root user password to the mysql db

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPassword';
   

7. Create the sstuser user and grant the permissions.

   mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstUserPassword';
   

   Note

   The sstuser and password will be used in the /etc/my.cnf configuration.

   mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
   
   mysql> FLUSH PRIVILEGES;
   

8. Exit mysql then stop the mysql services:

   mysql> exit
   Bye
   [root]# systemctl stop mysql.service
   

9. Install Percona on to the other nodes using the same steps.

Once the service is stopped on all nodes move onto the next step.

Add [mysqld] to my.cnf in /etc/

1. Add the following to /etc/my.cnf. The node_name and node_address needs to be unique on each of the nodes.

   DB Node 1

   [root]# vi /etc/my.cnf
   
   [mysqld]
   # pxc_encrypt_cluster_traffic=ON (optional, for TLS only)
   max_connections = 451
   max_allowed_packet = 256M
   wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
   wsrep_provider_options="cert.optimistic_pa=NO"
   wsrep_certification_rules='OPTIMIZED'
   
   wsrep_cluster_name=morpheusdb-cluster
   wsrep_cluster_address=gcomm://10.30.20.10,10.30.20.11,10.30.20.12
   
   wsrep_node_name=morpheus-db-node01
   wsrep_node_address=10.30.20.10
   
   wsrep_sst_method=xtrabackup-v2
   wsrep_sst_auth=sstuser:sstUserPassword
   pxc_strict_mode=PERMISSIVE
   wsrep_sync_wait=2
   
   skip-log-bin
   default_storage_engine=InnoDB
   innodb_autoinc_lock_mode=2
   default-character-set = utf8
   default_time_zone = "+00:00"
   

   DB Node 2

   [root]# vi /etc/my.cnf
   
   [mysqld]
   # pxc_encrypt_cluster_traffic=ON (optional, for TLS only)
   max_connections = 451
   max_allowed_packet = 256M
   wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
   wsrep_provider_options="cert.optimistic_pa=NO"
   wsrep_certification_rules='OPTIMIZED'
   
   wsrep_cluster_name=morpheusdb-cluster
   wsrep_cluster_address=gcomm://10.30.20.10,10.30.20.11,10.30.20.12
   
   # for wsrep_cluster_address=gcomm://Enter the IP address of the primary node first then remaining nodes. Separating the ip addresses with commas
   
   wsrep_node_name=morpheus-db-node02
   wsrep_node_address=10.30.20.11
   
   wsrep_sst_method=xtrabackup-v2
   wsrep_sst_auth=sstuser:sstUserPassword
   pxc_strict_mode=PERMISSIVE
   wsrep_sync_wait=2
   
   skip-log-bin
   default_storage_engine=InnoDB
   innodb_autoinc_lock_mode=2
   default-character-set = utf8
   default_time_zone = "+00:00"
   

   DB Node 3

   [root]# vi /etc/my.cnf
   
   [mysqld]
   # pxc_encrypt_cluster_traffic=ON (optional, for TLS only)
   max_connections = 451
   max_allowed_packet = 256M
   wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
   wsrep_provider_options="cert.optimistic_pa=NO"
   wsrep_certification_rules='OPTIMIZED'
   
   wsrep_cluster_name=morpheusdb-cluster
   wsrep_cluster_address=gcomm://10.30.20.10,10.30.20.11,10.30.20.12
   
   # for wsrep_cluster_address=gcomm://Enter the IP address of the primary node first then remaining nodes. Separating the ip addresses with commas
   
   wsrep_node_name=morpheus-db-node03
   wsrep_node_address=10.30.20.12
   
   wsrep_sst_method=xtrabackup-v2
   wsrep_sst_auth=sstuser:sstUserPassword
   pxc_strict_mode=PERMISSIVE
   wsrep_sync_wait=2
   
   skip-log-bin
   default_storage_engine=InnoDB
   innodb_autoinc_lock_mode=2
   default-character-set = utf8
   default_time_zone = "+00:00"
   

   Note

   The default setting on Morpheus app nodes for max_active database connections is 150. For this example we are setting max_connections = 451 to account for 3 maximum simultaneous morpheus app node connections. If max_active is configured higher on the app nodes, or the number of app nodes is not 3, adjust accordingly for your configuration.

2. Save /etc/my.cnf

Bootstrap DB Node 01

Important

Ensure mysql.service is stopped prior to bootstrap.

1. To bootstrap the first node in the cluster run the below command.

   systemctl start [email protected]
   

   Note

   The mysql service will start during the bootstrap.

   Note

   Startup failures are commonly caused by misconfigured /etc/my.cnf files. Also verify safe_to_bootstrap is set to 1 on Node 01 in /var/lib/mysql/grastate.dat.

Configure Morpheus Database and User

1. Create the Database you will be using with morpheus.

   Login to mysql on Node 01:

   mysql -u root -p
   password:
   
   mysql> CREATE DATABASE morpheus CHARACTER SET utf8 COLLATE utf8_general_ci;
   
   mysql> show databases;
   

2. Next create your morpheus database user. This is the user the morpheus app nodes will auth with mysql.

   mysql> CREATE USER 'morpheusDbUser'@'%' IDENTIFIED BY 'morpheusDbUserPassword';
   

3. Next Grant your new morpheus user permissions.

   mysql> GRANT ALL PRIVILEGES ON *.* TO 'morpheusDbUser'@'%' IDENTIFIED BY 'morpheusDbUserPassword';
   
   mysql> FLUSH PRIVILEGES;
   

   Important

   If you grant privileges to the morpheusDbUser to only the morpheusdb database, you will also need to GRANT SELECT, PROCESS, SHOW DATABASES, SUPER ON PRIVILEGES to the morpheusDbUser on *.* for the Appliance Health service.

   mysql> exit

Copy SSL Files to other nodes (optional, for TLS only)

During initialization of Node 01 the required pem files will be generated in /var/lib/mysql. The ca.pem, server-cert.pem and server-key.pem files need to match on all nodes in the cluster.

1. Copy the following files from Node 01 to the same path (default is /var/lib/mysql) on Node 02 and Node 03:

   /var/lib/mysql/ca.pem
   /var/lib/mysql/server-cert.pem
   /var/lib/mysql/server-key.pem
   
   .. important:: Ensure all 3 files match on all 3 nodes, including path, owner and permissions.
   
   .. note:: The generated certificate is self signed. Consult Percona documentation for [mysqld] and SSL file configuration when providing your own.
   

Start the Remaining Nodes

1. Start mysql on Node 02 and Node 03

   [root]# systemctl start mysql.service
   

   The services will automatically join the cluster using the sstuser we created earlier.

   Note

   Startup failures are commonly caused by misconfigured /etc/my.cnf files.

Verify Configuration

1. Verify SELinux is not rejecting any db cluster communication by running the below on all db nodes:

   [[email protected]]# grep -i denied /var/log/audit/audit.log | grep mysqld_t
   

   If there are any results, address the source or update the SELinux Policy to resolve.

2. Update SELinux if necessary

   [[email protected]]# rm -f PXC.*
   [[email protected]]# grep -i denied /var/log/audit/audit.log | grep mysqld_t | audit2allow -M PXC
   [[email protected]]# semodule -i PXC.pp
   

3. To verify all nodes joined the cluster, on any db node login to mysql and run show status like 'wsrep%';

   [[email protected]]# mysql -u root -p
   
   mysql>  show status like 'wsrep%';
   

4. Verify wsrep_cluster_size is 3 and wsrep_incoming_addresses lists all 3 node ip addresses.

5. From all Morpheus app nodes, verify that you can login to all 3 database nodes

   [[email protected]] cd
   [[email protected]]# ./mysql -u morpheusDbUser -p  -h 10.30.20.10
   [[email protected]]# ./mysql -u morpheusDbUser -p  -h 10.30.20.11
   [[email protected]]# ./mysql -u morpheusDbUser -p  -h 10.30.20.12
   

If you are unable to login to mysql from an app node, ensure credentials are correct, privileges have been granted, and mysql is running.

To validate network accessibility, use telnet to verify app node can reach db nodes on 3306: telnet 10.30.20.10 3306

Once you have your database installed and configured:

1. Create the Database you will be using with morpheus.

   mysql> CREATE DATABASE morpheus CHARACTER SET utf8 COLLATE utf8_general_ci;
   
   mysql> show databases;
   

2. Next create your morpheus database user. The user needs to be either at the IP address of the morpheus application server or use @'%' within the user name to allow the user to login from anywhere.

   mysql> CREATE USER '$morpheus_db_user_name'@'$source_ip' IDENTIFIED BY '$morpheus_db_user_pw';
   

3. Next Grant your new morpheus user permissions to the database.

     mysql> GRANT ALL PRIVILEGES ON morpheus_db_name.* TO 'morpheus_db_user'@'$source_ip' IDENTIFIED BY 'morpheus_db_user_pw' with grant option;
   
   
     mysql>  GRANT SELECT, PROCESS, SHOW DATABASES, SUPER ON *.* TO 'morpheus_db_user'@'$source_ip' IDENTIFIED BY 'morpheus_db_user_pw';
   
   mysql> FLUSH PRIVILEGES;
   

4. Checking Permissions for your user.

   SHOW GRANTS FOR '$morpheus_db_user_name'@'$source_ip';